Question

[Franco Bruno Borghesi]

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.

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majo...@postgresql.org so that your
message can get through to the mailing list cleanly

[Kris Jurka]

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

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

[Franco Bruno Borghesi]

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.

[Oliver Jowett]

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

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match