Re: DatabaseMetaData and Transactions

Just a quick note to say thanks to all that offered advice - all the
reported problems have been solved!

Regards,

Carl

-----Original Message-----
From: Oliver Jowett [mailto:oliver(at)opencloud(dot)com]
Sent: Wednesday, June 08, 2005 5:09 AM
To: Tom Lane
Cc: Carl Olivier; pgsql-jdbc(at)postgresql(dot)org
Subject: Re: [JDBC] DatabaseMetaData and Transactions

Tom Lane wrote:
> Oliver Jowett <oliver(at)opencloud(dot)com> writes:
>
>>You're going to have to pass metadata down, or change your queries to
>>explicitly cast the parameters in the SQL itself. The driver has
>>exactly the same problem as your code does -- with the v3 protocol it
>>needs to provide a type for the parameter, but if it's just provided
>>as a string the only type it can assume is text..
>
>
> Is there any chance of a win in passing the type across to the backend
> as "unknown", and seeing if the backend can infer something reasonable?
> For example given
> WHERE int4col = ?
> it'd be reasonable to infer the type of the ? symbol as int4, and that
> logic has been built into the backend since forever.

It's a possibility, but:

a) really this is an application bug (admittedly a common one) .. the JDBC
API provides type info for parameters, and if the application claims that a
parameter is a String, why should the driver second-guess it?

b) there were some cases (? IS NULL and some cases with function args, from
memory?) which didn't work when an unknown OID was used, so to some extent
you're exchanging one problem for another.

We do pass the unknown OIDs for "untyped" (JDBC Types.OTHER) NULLs already.
Passing unknown for setObject(column, value, Types.OTHER) seems reasonable
given what we do with setNull().. but that doesn't solve the problem of
applications using setString() for parameters that actually need to be some
other type.

-O