Re: prepared statement incompatibility

From: "Byron Nikolaidis" <ByronN(at)Routescape(dot)com>
To: "Dave Cramer" <pg(at)fastcrypt(dot)com>
Cc: <pgsql-jdbc(at)postgresql(dot)org>
Subject: Re: prepared statement incompatibility
Date: 2005-06-28 21:44:37
Message-ID: 6E0907A94904D94B99D7F387E08C4F57073222@FALCON.INSIGHT
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-jdbc

But with the previous driver, we were actually using protocol version 3,
so it doesn't appear that the driver strictness is actually directly
related to protocol version 3. I'm curious as to what is it that
actually changed in the 8.x drivers and why?

Would it be possible to add a connection property to control just this
strict type behavior, something like "strictTypes=false" ?

BTW, we are not directly using JDBC, we are using Hibernate. We have a
lot of code that is not so strict with regard to datatypes in Hibernate
queries and such, and that's why it presents a difficult problem for us.

-----Original Message-----
From: Dave Cramer [mailto:pg(at)fastcrypt(dot)com]
Sent: Tuesday, June 28, 2005 4:44 PM
To: Byron Nikolaidis
Cc: pgsql-jdbc(at)postgresql(dot)org
Subject: Re: [JDBC] prepared statement incompatibility

The new driver is more strict about the use of setXXX. Your only option
is to use protocol version 2 if you do not want to specify the correct
underlying type.

You can use setObject(colnumber, String, type )

Dave

On 28-Jun-05, at 4:25 PM, Byron Nikolaidis wrote:

Hello all,

We are using Postgres 8.0.3, and are attempting to upgrade to the JDBC
8.x drivers, but have discovered that PreparedStatements that used to
work no longer do so. This is seen where the Java type does not match
the database type, such as attempting to use
preparedStatement.setString(1, "1") where the database type is
actually an integer (see stacktrace below). These worked fine on
previous driver versions (such as 7.4).

If we set the protocolVersion=2, the jdbc 8.x drivers will work fine
also, but I'm not so sure we really want to do that.

At first, I thought this was related to the use of server side prepares.
But I have set the prepareThreshold=0 and verified it was being set by
checking for it in the pgconnection and pgstatement, but it appears to
have no effect.

Can anyone clarify what exactly has changed with prepared statements and
why it used to work previously?

Thanks,

Byron

Exception in thread "main" java.sql.SQLException: ERROR: column "ticket"
is of type integer but expression is of type character varying

at
org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecu
torImpl.java:1471)

at
org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImp
l.java:1256)

at
org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:
175)

at
org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Stateme
nt.java:389)

at
org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdb
c2Statement.java:330)

at
org.postgresql.jdbc2.AbstractJdbc2Statement.executeUpdate(AbstractJdbc2S
tatement.java:282)

at test.routescape.orm.util.DriverTest.main(DriverTest.java:49)

Responses

Browse pgsql-jdbc by date

  From Date Subject
Next Message Oliver Jowett 2005-06-28 22:19:20 Re: getBinaryStream and OutOfMemoryException
Previous Message Dave Cramer 2005-06-28 20:43:30 Re: prepared statement incompatibility