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