AppletTalk.com

Silvio Bierman · Guest

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
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 database 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.

I thought the JDBC spec requires the driver to attempt a conversion when a
setString is done on a non-string paramter. Is this correct or can the
PostgreSQL do this and not break JDBC compatibility?

Regards,

Silvio Bierman

joeNOSPAM@BEA.com · Guest

Hi Silvio. Yes the JDBC dirver should allow setString() for parameters
that
will be interpreted as numeric in the DBMS. In fact for most drivers
there
is no care or translation. The data is merrily sent to the DBMS as a
string (good for retaining precision), and the DBMS silently converts
it
to the numeric value.
Joe Weinstein at BEA

Silvio Bierman · Guest

Hi Joe,

Thanks, I was actually hoping you would have something to say about this Smile

I will talk to the PostgreSQL developers and try to persuade them to change
the behaviour.

Regards,

Silvio Bierman

"joeNOSPAM (AT) BEA (DOT) com" <joe.weinstein (AT) gmail (DOT) com> wrote

Silvio Bierman · Guest

Hello Joe,

I talked to the people who are responsible for the JDBC driver. They seem to
disagree that they broke the spec. I checked but I must say I find the spec
at best ambiguous. Conversions from Java types to SQL types are specified
but the relation between the SQL type passed by te driver and the value type
expected by the database server or any implicit conversions it should
perform stays vague.

Can you point me to any information to support my claim that they broke the
driver when they changed the behaviour the way I described?

For now they tell me to fix the application...

Regards,

Silvio Bierman

"joeNOSPAM (AT) BEA (DOT) com" <joe.weinstein (AT) gmail (DOT) com> wrote

joeNOSPAM@BEA.com · Guest

I can only say that of the JDBC drivers we've made or seen used, I have
never seen any JDBC driver that failed to send a numeric value to the
DBMS via setString(), and never saw a DBMS that didn't convert string
data to the numeric datum for storage. If you do a

stmt.executeUpdate("insert into myTable values( 123.456 )");

the data is going to the DBMS as a string, and the DBMS is converting
the string value to a numeric datum. If you did a

ps = c.prepareStatement("insert into myTable values( ? )");
ps.setString(1, "123.456");

The driver shouldn't get involved, and I have never seen a DBMS that
didn't do the implicit conversion.

Good luck,
Joe Weinstein at BEA