Thread: setObject(i,null) and general "setNull" problems

setObject(i,null) and general "setNull" problems

From
Valentin Rodionov
Date:
Hi all.
This problem/question has already come up before a couple of times,
however, so far no acceptable solution is available, and I am asking
for help
again.
Version 8 of JDBC driver now disallows setting a "generic" null as a
value
for a PreparedStatement (i.e. setObject(i,null,Types.OTHER),
setNull(i,Types.OTHER)
no longer work).
Why in the world would the driver care what type of "nothing" do I have
completely escapes me (there might be some specialized uses for that,
of course, but
for most people just trying to develop a relatively simple application
this is
just an additional source of major Pain-In-The-Neck to work around).
For example, if I use a simple (not prepared) statement, I can set any
value to "null"
just by typing "SET foo=null WHERE pkey=something".
Not so with PreparedStatement - and I do not understand why in the
world does a simple
thing like that have to be so convoluted.
I think that this kind of "strong-typed" behavior should be an option
for those
who want to use it, and not the default - and the only possible -
behavior of the driver.

Here is an example of what I am trying to do.
I am developing an application that makes a lot of similar (but
different) table
updates. Values (some of them null) are passed to the updating method
as a Java vector, the SQL string
for the PreparedStatement is constructed based on the contents of the
Vector,
and then the following method is used to set parameters of the
PreparedStatement:

private void feedStatement(int index, PreparedStatement statement,
Object element) throws SQLException{
         if(element==null){

         }else{
             if(element instanceof String){
                 if(((String)element).length()==0){
                     statement.setNull(index,java.sql.Types.VARCHAR);
                 }else{
                     statement.setString(index,(String)element);
                 }
             }else{
                 if(element instanceof Integer){

statement.setInt(index,((Integer)element).intValue());
                 }else{
                     if(element instanceof Double){

statement.setDouble(index,((Double)element).doubleValue());
                     }else{
                         if(element instanceof byte[]){
                             ByteArrayInputStream is = new
ByteArrayInputStream((byte[])element);

statement.setBinaryStream(index,is,is.available());
                         }else{
                             if(element instanceof Boolean){

statement.setBoolean(index,((Boolean)element).booleanValue());
                             }else{
                                 if(element instanceof
java.sql.Timestamp){

statement.setTimestamp(index,(java.sql.Timestamp)element);
                                 }
                             }
                         }
                     }
                 }
             }
         }
     }

The (element == null) block is left intentionally blank - because
nothing reasonable works there.
I have tried setNull and setObject as outlined above - they do not work
because they want to know the
type, and Java null does not know its own type.
I have tried using getParameterMetaData, but that method is not yet
implemented in PGSQL driver.
I have also tried fiddling with DatabaseMetaData to extract the
necessary column types information
as needed, but, alas, I could not find a way to extract any useful
information from the PreparedStatement
object (i.e. I can not find out what table it is trying to update,
which column my parameter corresponds to,
etc).
My only 2 options right now seem to be:
  *find out if we are dealing with null values at the stage of statement
string construction and manually
substitute "=null" in the correct places. Hard, not very general, and
incredibly kludgy.
  *modify my program to pass the type information with every message
that gets passed to the queryManager
class (the one that does the queries). That will take a lot of effort
to recode, will create some unneeded
overhead, and in general will be no less a kludge than the first
approach.
Does anyone have a better idea on how to accomplish what I am trying to
do in a less kludgy fashion?
Is there any possibility to make this "NULL typing" thing optional? I
would bet most users of the JDBC driver
don't care for typing their null values!
Even if this behavior is a part of the JDBC standard (is it?), it is
highly illogical
and only adds pain to a process that has to be extremely
straightforward...
Thanks a lot...
  Valentin


Re: setObject(i,null) and general "setNull" problems

From
Kris Jurka
Date:

On Mon, 21 Feb 2005, Valentin Rodionov wrote:

> Hi all. This problem/question has already come up before a couple of
> times, however, so far no acceptable solution is available, and I am
> asking for help again. Version 8 of JDBC driver now disallows setting a
> "generic" null as a value for a PreparedStatement (i.e.
> setObject(i,null,Types.OTHER), setNull(i,Types.OTHER) no longer work).

Update to build 310.

> I have tried using getParameterMetaData, but that method is not yet
> implemented in PGSQL driver.

Update to build 310.

Kris Jurka

Re: setObject(i,null) and general "setNull" problems

From
Oliver Jowett
Date:
Valentin Rodionov wrote:

[...]

Most of this is irrelevant because the driver has already changed back
to the old behaviour, as Kris has pointed out, but..

> Is there any possibility to make this "NULL typing" thing optional? I
> would bet most users of the JDBC driver
> don't care for typing their null values!
> Even if this behavior is a part of the JDBC standard (is it?)

The JDBC standard is set up so that drivers have type information for
all parameters available, even if NULL. For example, see the javadoc for
setNull. The users may not care, but there are cases where the driver or
db can't sensibly/safely infer that type information when it needs to..

It's not clear if setObject(i,null) should be used by a spec-compliant
application or not. It seems like an oversight that this isn't
forbidden, as there's no other way to get an untyped NULL into the
driver. I have asked the JDBC expert group to clarify this but haven't
had a response yet.

There are good robustness reasons for typing all your parameters, even
if NULL, but it seems that noone cares about that :(

-O

Re: setObject(i,null) and general "setNull" problems

From
Valentin Rodionov
Date:
>
>> I have tried using getParameterMetaData, but that method is not yet
>> implemented in PGSQL driver.
>
> Update to build 310.
>
>
Thanks for a very quick reply!
I have not realized so much major reworking could have been done on the
driver in the 2 months since I have downloaded my old version... I am
very impressed.
So is the getParameterMetaData method fully implemented now?
Right now I have implemented my NULL setting method using
setNull(index, Types.OTHER),
and it seems to work with the "310" build fine.
Will there be any advantage over what I have done if I rewrite my
method using getParamaterMetaData
and type my NULLs  correctly (the only thing I am doing in my app is
very simple selects,
updates and inserts)?
Thanks a lot!