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: (view raw, whole thread or download thread mbox)
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
Version 8 of JDBC driver now disallows setting a "generic" null as a 
for a PreparedStatement (i.e. setObject(i,null,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 
and then the following method is used to set parameters of the 

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

             if(element instanceof String){
                 if(element instanceof Integer){
                     if(element instanceof Double){
                         if(element instanceof byte[]){
                             ByteArrayInputStream is = new 
                             if(element instanceof Boolean){
                                 if(element instanceof 

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,
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 
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 
Thanks a lot...


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-2017 The PostgreSQL Global Development Group