Question

Hi all.

I've donwloaded pg80b1.308.jdbc3.jar from jdbc.postgresql.org and now my
calls to PreparedStatement.setObject(int, Object) are failing, when they
used to work with the previous driver version.

The message I get is: "Cant infer the SQL type to use for an instance of
{0}. Use setObject() with an explicit Types value to specify the type to
use.".

Calling setObject(int, Object, Types.xxxxx) works ok, but I need
setObject(int, Object). I've tryied setObject(int, Character) and
setObject(int, java.util.Date) and both fail with the same message.
Haven't tested other cases.

Is there anything that has changed? I've searched the lists but I didn't
find anything about it.
Thanks in advance.

On Sat, 13 Nov 2004, Franco Bruno Borghesi wrote:

> Hi all.
>
> I've donwloaded pg80b1.308.jdbc3.jar from jdbc.postgresql.org and now my
> calls to PreparedStatement.setObject(int, Object) are failing, when they
> used to work with the previous driver version.
>
> The message I get is: "Cant infer the SQL type to use for an instance of
> {0}. Use setObject() with an explicit Types value to specify the type to
> use.".
>
> Calling setObject(int, Object, Types.xxxxx) works ok, but I need
> setObject(int, Object). I've tryied setObject(int, Character) and
> setObject(int, java.util.Date) and both fail with the same message.
> Haven't tested other cases.

setObject(int, Object) has a limited number of types that it knows about
(and Character and java.util.Date aren't in them). Using String and
java.sql.Date will work but you probably don't want to do that. Supposing
we did add these two types to setObject's knowledge, what does
java.util.Date map to? With java.sql.Date/Time/Timestamp you know what
datatype you are really talking about.

Kris Jurka

Thanks for the answer Kris. I've fixed a generic replace method that I
had written, so it looks like this now (I've simplified it for the example):

private void replateArgs(PreparedStatement stmt, Object[] values, int
offset) {

for (int i, count=values.length; i<count; i++) {

//is it a Character?
if (values[i] instanceof Character)
pstmt.setObject(offset+i, values[i].toString());
else {

//is it a Date?
if (values[i] instanceof java.util.Date)
pstmt.setObject(offset+i, values[i], Types.TIMESTAMP);
//all my tables have timestamp dates
else //anything else seems to work
pstmt.setObject(offset+i, values[i]); //any other thing
}
}
}

It works fine, except for NULL values. With the old driver,
setObject(int, null) was ok, but I've seen that I should be using
setNull(int, int) with the new version.

As you see, in my current code I have no way to know to which datatype
the field maps to. So, is there any easy/generic way to set NULLs, no
matter what datatype the field is?

Thanks again.

Kris Jurka wrote:

>On Sat, 13 Nov 2004, Franco Bruno Borghesi wrote:
>
>
>
>>Hi all.
>>
>>I've donwloaded pg80b1.308.jdbc3.jar from jdbc.postgresql.org and now my
>>calls to PreparedStatement.setObject(int, Object) are failing, when they
>>used to work with the previous driver version.
>>
>>The message I get is: "Cant infer the SQL type to use for an instance of
>>{0}. Use setObject() with an explicit Types value to specify the type to
>>use.".
>>
>>Calling setObject(int, Object, Types.xxxxx) works ok, but I need
>>setObject(int, Object). I've tryied setObject(int, Character) and
>>setObject(int, java.util.Date) and both fail with the same message.
>>Haven't tested other cases.
>>
>>
>
>setObject(int, Object) has a limited number of types that it knows about
>(and Character and java.util.Date aren't in them). Using String and
>java.sql.Date will work but you probably don't want to do that. Supposing
>we did add these two types to setObject's knowledge, what does
>java.util.Date map to? With java.sql.Date/Time/Timestamp you know what
>datatype you are really talking about.
>
>Kris Jurka
>
>
>

Franco Bruno Borghesi wrote:

> As you see, in my current code I have no way to know to which datatype
> the field maps to. So, is there any easy/generic way to set NULLs, no
> matter what datatype the field is?

No. The driver has exactly the same problem as you ran into, namely that
there's no way to infer a type given just a bare null.

You should provide a SQL Types value via setNull(i,type) or
setObject(i,null,type). You may want to change your API to pass down
type information when you are dealing with nulls.

The backend does have a mechanism to infer the type of a parameter, but
it's sufficiently unpredictable for arbitary queries that we decided not
to use it in the driver, given that the rest of JDBC is quite strongly
typed.

-O