Skip site navigation (1) Skip section navigation (2)

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

From: Valentin Rodionov <valiar(at)scripps(dot)edu>
To: pgsql-jdbc(at)postgresql(dot)org
Subject: setObject(i,null) and general "setNull" problems
Date: 2005-02-21 22:56:38
Message-ID: e4993f12cf78d4a981efc4bad3accbd6@scripps.edu (view raw or flat)
Thread:
Lists: pgsql-jdbc
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


Responses

pgsql-jdbc by date

Next:From: Kris JurkaDate: 2005-02-21 23:15:14
Subject: Re: setObject(i,null) and general "setNull" problems
Previous:From: NicoDate: 2005-02-21 18:45:49
Subject: Re: making a rule and know when it is violated

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group