Thread: prepared statement incompatibility

prepared statement incompatibility

From
"Byron Nikolaidis"
Date:

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(QueryExecutorImpl.java:1471)

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

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

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

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

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

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

 

Re: prepared statement incompatibility

From
Dave Cramer
Date:
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(QueryExecutorImpl.java:1471)

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

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

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

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

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

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

 


Re: prepared statement incompatibility

From
"Byron Nikolaidis"
Date:

 

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@fastcrypt.com]
Sent: Tuesday, June 28, 2005 4:44 PM
To: Byron Nikolaidis
Cc: pgsql-jdbc@postgresql.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(QueryExecutorImpl.java:1471)

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

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

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

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

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

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

 



 

Re: prepared statement incompatibility

From
Oliver Jowett
Date:
Byron Nikolaidis wrote:

> 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?

It *is* related to use of protocol version 3. However the 7.4-era
drivers used the simple query protocol which doesn't let you
parameterize queries, and did parameter substitution on the driver side
-- this was basically a dumb translation of the v2 code.

The 8.0-era drivers use the v3 extended query protocol to parameterize
queries and bind parameters, even when the resulting statement is not
preserved (i.e. prepareThreshold has not been reached). This requires
supplying types for each parameter, and the driver believes what you
tell it at the JDBC layer. You then get into trouble with the backend's
strict typing rules.

See http://www.postgresql.org/docs/8.0/static/protocol.html for protocol
docs -- compare "simple query" vs "extended query".

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

Not easily, see the archives for previous discussion. Passing "unknown
type" for a String parameter doesn't always work and has the potential
to hide real errors.

-O