Thread: Upgrade to pg and driver version 8.x code compatibilities

We've upraged out development and qa servers to postgresql 8.  When using our java application, we get errors at
variouspoints in the code 
similar to the following:

Unable to create new submission. org.apache.commons.dbcp.DelegatingPreparedStatement@180b22ejava.sql.SQLException:
ERROR: column "job_experience" is of type smallint but expression is of type character varying

If I downgrade the driver to the 7.4.x version the error goes away and we can run the application.

I realize this is probably pointing to bad coding practices, which will be fixed, I believe, as our developers work on
thecode, but we want to 
avoid an all out push to get the code all fixed (similar problems exist in a *lot* of places in the code).  We'd like
toprocede with 
upgrading to 8.x server -- Two questions arise

1.  What changed between the driver versions that generate this error?

2.  What is the downside of continuing to use the 7.x version of the driver -- or are there better alternatives (patch,
newversion, etc).  I 
am using build 311 of the driver.

Tom Arthurs wrote:

> ERROR: column "job_experience" is of type smallint but expression is of
> type character varying

Most likely you are using setString() where you should be using setShort().

> 1.  What changed between the driver versions that generate this error?

The driver started to use server-side prepared statements for
parameterization of queries (i.e. the driver translates ? to $n in the
main query string, and sends the actual parameter values out-of-band
from the query itself). One sideeffect of this is that parameters are
more strongly typed than in the 7.4.x versions where the driver would do
literal parameter substitution into the query string before sending it
to the backend. Also, you can use parameters in fewer places (they must
fit the backend's idea of where parameterizable expressions are allowed)
-- e.g. see the recent thread about "ORDER BY ?" changing behaviour with
the newer driver.

> 2.  What is the downside of continuing to use the 7.x version of the
> driver -- or are there better alternatives (patch, new version, etc).  I
> am using build 311 of the driver.

Most active development happens on the 8.0 version; 7.4.x is maintained
for bugfixes but that's about it, you won't get the benefit of any
performance improvements or added features that go into 8.0. Also, the
7.4.x driver won't necessarily work with servers >= 8.0.

In the longer term, the 7.4.x version will eventually become unmaintained.

-O