Обсуждение: PreparedStatement#setString on non-string parameters
Hello all, I have just finished a few hours of testing our product that already runs on Oracle, SQLServer, MySQL, MaxDB and McKOI on PostgreSQL. I always liked what I read about this database apart from the fact that it used to not run natively on Windows. Since many of our customers run Windows servers I never bothered with PostgreSQL before. However, starting with version 8 there is a native Windows version which I installed and ran our product on. At first everything was fine and I was very impressed with both the performance as the ease of use of the admin tools. But when I starting doing writes to the database SQL exceptions started to occur. They where all over the application code but where all the same error: doing a setString on a numeric parameter. I know this is usually suboptimal but since we handle a lot of XML data throughout the (heavily distributed) application many numeric values are available as Strings so it would be a matter of converting them to numeric values ourselves or letting the JDBC driver do that. The JDBC drivers we used for the other databasse I mentioned never complained. Unfortunately PostgreSQL does. I read somewhere that using the pre-version-8 JDBC driver will work with 8 and not show this behaviour, the 8 driver has deliberately changed the behaviour. The JDBC spec requires the driver to attempt a conversion when a setString is done on a non-string parameter. This will break a lot of conformant code, not only ours. Is there any chance this can be corrected? Regards, Silvio Bierman
Silvio, I presume you are talking about setObject ??? I can't find a setString(non-string param) Dave Silvio Bierman wrote: >Hello all, > >I have just finished a few hours of testing our product that already runs on >Oracle, SQLServer, MySQL, MaxDB and McKOI on PostgreSQL. I always liked what >I read about this database apart from the fact that it used to not run >natively on Windows. Since many of our customers run Windows servers I never >bothered with PostgreSQL before. > >However, starting with version 8 there is a native Windows version which I >installed and ran our product on. At first everything was fine and I was >very impressed with both the performance as the ease of use of the admin >tools. > >But when I starting doing writes to the database SQL exceptions started to >occur. They where all over the application code but where all the same >error: doing a setString on a numeric parameter. I know this is usually >suboptimal but since we handle a lot of XML data throughout the (heavily >distributed) application many numeric values are available as Strings so it >would be a matter of converting them to numeric values ourselves or letting >the JDBC driver do that. > >The JDBC drivers we used for the other databasse I mentioned never >complained. Unfortunately PostgreSQL does. I read somewhere that using the >pre-version-8 JDBC driver will work with 8 and not show this behaviour, the >8 driver has deliberately changed the behaviour. > >The JDBC spec requires the driver to attempt a conversion when a >setString is done on a non-string parameter. This will break a lot of >conformant code, not only ours. Is there any chance this can be corrected? > >Regards, > >Silvio Bierman > > >---------------------------(end of broadcast)--------------------------- >TIP 6: Have you searched our list archives? > > http://archives.postgresql.org > > > > -- Dave Cramer http://www.postgresintl.com 519 939 0336 ICQ#14675561
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). -- Guillaume Cottenceau
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
"Silvio Bierman" <sbierman 'at' jambo-software.com> writes: [...] > The JDBC spec requires the driver to attempt a conversion when a > setString is done on a non-string parameter. This will break a lot of > conformant code, not only ours. Is there any chance this can be corrected? Where can you see that? I can see pretty much the opposite, for example the following at http://java.sun.com/j2se/1.4.2/docs/api/java/sql/PreparedStatement.html -=-=---=-=---=-=---=-=---=-=-- Note: The setter methods (setShort, setString, and so on) for setting IN parameter values must specify types that are compatible with the defined SQL type of the input parameter. For instance, if the IN parameter has SQL type INTEGER, then the method setInt should be used. -=-=---=-=---=-=---=-=---=-=-- And even, at http://java.sun.com/j2se/1.4.2/docs/api/java/sql/PreparedStatement.html#setString(int,%20java.lang.String) -=-=---=-=---=-=---=-=---=-=-- setString: The driver converts this to an SQL VARCHAR or LONGVARCHAR value (depending on the argument's size relative to the driver's limits on VARCHAR values) when it sends it to the database. -=-=---=-=---=-=---=-=---=-=-- -- Guillaume Cottenceau
Silvio Bierman wrote: > The JDBC spec requires the driver to attempt a conversion when a > setString is done on a non-string parameter. This will break a lot of > conformant code, not only ours. Is there any chance this can be corrected? AFAIK, the spec doesn't require this. Can you point me at the bit of the spec that makes you think it does? As I read the spec, the only conversions the driver performs for IN parameters are those in table B-2 (for String, to CHAR/VARCHAR/LONGVARCHAR) or B-4 (same mapping for String). See 13.2.2.1 and 13.2.2.2 of the JDBC 3.0 spec. There is a wider set of conversions available on the ResultSet path (notably, integer -> string) -- see 14.2.3.1 and table B-6. -O