FW: PreparedStatement#setString on non-string parameters

Sorry, I originally sent sent this in a reply to a message CC-ed to me
directly...

-----Original Message-----
From: Silvio Bierman [mailto:sbierman@jambo-software.com]
Sent: Tuesday, March 08, 2005 2:51 PM
To: pg@fastcrypt.com
Subject: RE: [JDBC] PreparedStatement#setString on non-string parameters


Hello Dave,

Thanks for the reply and thanks to Guillaume for clearing up my not very
well expressed intentions. I was indeed talking about using
setString(String) where the parameter refers to a numeric value.

The way I read the spec only conversions from Java types to SQL types are
specified. Explicitly the specification says that setObject(Object) should
use the dynamic Java type of the object to determine the conversion. No
extra interpretation of the prepared statement logic is required by
setObject. It is merely a more generic way of setting parameters on the Java
side instead of implying extra interpretation logic.

I guess striktly speaking the spec does not say what should happend when a
driver maps a Java String to SQL VARCHAR and the corresponding parameter is
expected by the database to be numeric. It is probably just like passing a

select count(*) from table where integercol='3'

to a database. Should it do the implicit conversion?

Either the JDBC drivers for the databases I mentioned earlier do the
conversion or the database backends do it on the server side. Any way, this
works in all cases. PostgreSQL is the first database to break our
application due to this behaviour. We have had problems on earlier versions
of MySQL because of lack of subselect support etc. but never these issues.

I really love what I have seen of PostgreSQL until now and I am seriously
considering making it our preferred database backend. The ability to run on
both Linux and Windows is great, performance is excellent (I tried a quite
large database) even when I am still inserting my GUID keys as varchar data
and the admin tool is very good also.

Telling my users to use an old JDBC driver is not an option though...

Regards,

Silvio Bierman


@-----Original Message-----
@From: pgsql-jdbc-owner@postgresql.org
@[mailto:pgsql-jdbc-owner@postgresql.org]On Behalf Of Dave Cramer
@Sent: Tuesday, March 08, 2005 2:10 PM
@To: Guillaume Cottenceau
@Cc: Silvio Bierman; PostgreSQL JDBC
@Subject: Re: [JDBC] PreparedStatement#setString on non-string parameters
@
@
@That makes sense.
@
@I just checked my reference and I can't find any document that suggests
@that setString should do a conversion.
@
@setObject is required to by the spec.
@
@Dave
@
@Guillaume Cottenceau wrote:
@
@>Dave Cramer <pg 'at' fastcrypt.com> writes:
@>
@>
@>
@>>Silvio,
@>>
@>>I presume you are talking about setObject ??? I can't find a
@>>setString(non-string param)
@>>
@>>
@>
@>He says "XML data (..) many numeric values are available as
@>Strings" so I think the SQL column is numeric but the rest is
@>string (a java string containing the string representation of the
@>numeric value).
@>
@>
@>
@
@--
@Dave Cramer
@http://www.postgresintl.com
@519 939 0336
@ICQ#14675561
@
@
@---------------------------(end of broadcast)---------------------------
@TIP 4: Don't 'kill -9' the postmaster