Thread: Recent backward compatibility break in PreparedStatement.setObject()

The PostgreSQL JDBC driver recently changed PreparedStatement behavior with calls such as setObject(index, value, Types.VARCHAR). Previously, the driver would call value.toString() before binding all VARCHAR values... meaning any object could be passed as "value." In recent updates (e.g., 9.4.1207), most non-String values now result in an exception:

    Exception in thread "main" org.postgresql.util.PSQLException: Cannot convert an instance of XXX to type String
        at org.postgresql.jdbc.PgStatement.cannotCastException(PgStatement.java:1813)
        at org.postgresql.jdbc.PgStatement.cannotCastException(PgStatement.java:1809)
        at org.postgresql.jdbc.PgStatement.castToString(PgStatement.java:1805)
        at org.postgresql.jdbc.PgStatement.setObject(PgStatement.java:1540)
        at org.postgresql.jdbc.PgStatement.setObject(PgStatement.java:1818)
        at PGTest.main(PGTest.java:25)

This seems to have broken during the refactor to support binary transfer for setObject(), https://github.com/pgjdbc/pgjdbc/issues/328. Was the behavior change intentional? If so, it should probably be documented (e.g., I didn't see any mention in the change log). If not, I'd suggest going back to calling value.toString() for all types. I'm sure ours is not the only application that relies on this long-standing (>10 years) PostgreSQL JDBC behavior. FWIW, other JDBC drivers call toString() in this situation.

Here's a contrived example that demonstrates the behavior change. This code snippet runs fine in 1201 but fails with the exception above in 1207.

    Object param = new Object() {public String toString() {return "tables";}};

    try (PreparedStatement stmt = conn.prepareStatement("SELECT * FROM information_schema.tables WHERE table_name = ?"))   {       stmt.setObject(1, param, Types.VARCHAR);
       try (ResultSet rs = stmt.executeQuery())       {           while (rs.next())               System.out.println(rs.getString(1));       }   }

Thanks,
Adam

I'm the original contributor of that changeset. This is simply a bug. I'll fix it later today. Thanks for reporting! :-)

-Markus

 

From: pgsql-jdbc-owner@postgresql.org [mailto:pgsql-jdbc-owner@postgresql.org] On Behalf Of Dave Cramer
Sent: Mittwoch, 30. Dezember 2015 22:55
To: Adam Rauch
Cc: List
Subject: Re: [JDBC] Recent backward compatibility break in PreparedStatement.setObject()

 

No, this was not intentional.

 

Thanks for the report!

Dave Cramer

davec@postgresintl.com

www.postgresintl.com

 

On 30 December 2015 at 16:45, Adam Rauch <adam@labkey.com> wrote:

The PostgreSQL JDBC driver recently changed PreparedStatement behavior with calls such as setObject(index, value, Types.VARCHAR). Previously, the driver would call value.toString() before binding all VARCHAR values... meaning any object could be passed as "value." In recent updates (e.g., 9.4.1207), most non-String values now result in an exception:

    Exception in thread "main" org.postgresql.util.PSQLException: Cannot convert an instance of XXX to type String
        at org.postgresql.jdbc.PgStatement.cannotCastException(PgStatement.java:1813)
        at org.postgresql.jdbc.PgStatement.cannotCastException(PgStatement.java:1809)
        at org.postgresql.jdbc.PgStatement.castToString(PgStatement.java:1805)
        at org.postgresql.jdbc.PgStatement.setObject(PgStatement.java:1540)
        at org.postgresql.jdbc.PgStatement.setObject(PgStatement.java:1818)
        at PGTest.main(PGTest.java:25)

This seems to have broken during the refactor to support binary transfer for setObject(), https://github.com/pgjdbc/pgjdbc/issues/328. Was the behavior change intentional? If so, it should probably be documented (e.g., I didn't see any mention in the change log). If not, I'd suggest going back to calling value.toString() for all types. I'm sure ours is not the only application that relies on this long-standing (>10 years) PostgreSQL JDBC behavior. FWIW, other JDBC drivers call toString() in this situation.

Here's a contrived example that demonstrates the behavior change. This code snippet runs fine in 1201 but fails with the exception above in 1207.

    Object param = new Object() {public String toString() {return "tables";}};

    try (PreparedStatement stmt = conn.prepareStatement("SELECT * FROM information_schema.tables WHERE table_name = ?"))

    {

        stmt.setObject(1, param, Types.VARCHAR);

        try (ResultSet rs = stmt.executeQuery())

        {

            while (rs.next())

                System.out.println(rs.getString(1));

        }

    }

Thanks,
Adam

 

Thanks, but as I also developed the fix as announced this morning, I now spent time for nothing! Would be good if also the project lead *first* files a bug report and assigns to itself. :-(

 

From: davecramer@gmail.com [mailto:davecramer@gmail.com] On Behalf Of Dave Cramer
Sent: Donnerstag, 31. Dezember 2015 12:32
To: Markus KARG
Cc: Adam Rauch; List

Subject: Re: [JDBC] Recent backward compatibility break in PreparedStatement.setObject()

 

I put a PR in already https://github.com/pgjdbc/pgjdbc/pull/480

Dave Cramer

davec@postgresintl.com

www.postgresintl.com

 

On 31 December 2015 at 03:23, Markus KARG <markus@headcrashing.eu> wrote:

I'm the original contributor of that changeset. This is simply a bug. I'll fix it later today. Thanks for reporting! :-)

-Markus

 

From: pgsql-jdbc-owner@postgresql.org [mailto:pgsql-jdbc-owner@postgresql.org] On Behalf Of Dave Cramer
Sent: Mittwoch, 30. Dezember 2015 22:55
To: Adam Rauch
Cc: List
Subject: Re: [JDBC] Recent backward compatibility break in PreparedStatement.setObject()

 

No, this was not intentional.

 

Thanks for the report!

Dave Cramer

davec@postgresintl.com

www.postgresintl.com

 

On 30 December 2015 at 16:45, Adam Rauch <adam@labkey.com> wrote:

The PostgreSQL JDBC driver recently changed PreparedStatement behavior with calls such as setObject(index, value, Types.VARCHAR). Previously, the driver would call value.toString() before binding all VARCHAR values... meaning any object could be passed as "value." In recent updates (e.g., 9.4.1207), most non-String values now result in an exception:

    Exception in thread "main" org.postgresql.util.PSQLException: Cannot convert an instance of XXX to type String
        at org.postgresql.jdbc.PgStatement.cannotCastException(PgStatement.java:1813)
        at org.postgresql.jdbc.PgStatement.cannotCastException(PgStatement.java:1809)
        at org.postgresql.jdbc.PgStatement.castToString(PgStatement.java:1805)
        at org.postgresql.jdbc.PgStatement.setObject(PgStatement.java:1540)
        at org.postgresql.jdbc.PgStatement.setObject(PgStatement.java:1818)
        at PGTest.main(PGTest.java:25)

This seems to have broken during the refactor to support binary transfer for setObject(), https://github.com/pgjdbc/pgjdbc/issues/328. Was the behavior change intentional? If so, it should probably be documented (e.g., I didn't see any mention in the change log). If not, I'd suggest going back to calling value.toString() for all types. I'm sure ours is not the only application that relies on this long-standing (>10 years) PostgreSQL JDBC behavior. FWIW, other JDBC drivers call toString() in this situation.

Here's a contrived example that demonstrates the behavior change. This code snippet runs fine in 1201 but fails with the exception above in 1207.

    Object param = new Object() {public String toString() {return "tables";}};

    try (PreparedStatement stmt = conn.prepareStatement("SELECT * FROM information_schema.tables WHERE table_name = ?"))

    {

        stmt.setObject(1, param, Types.VARCHAR);

        try (ResultSet rs = stmt.executeQuery())

        {

            while (rs.next())

                System.out.println(rs.getString(1));

        }

    }

Thanks,
Adam

 

 

Hi,

In which version was this bug introduced and will a 9.4.1208 be released
soon ?

Regards,

Ger Timmens

On 12/31/2015 02:24 PM, Dave Cramer wrote:
> Apologies, you are correct I should have announced it.
>
> Dave Cramer
>
> davec@postgresintl.com <mailto:davec@postgresintl.com>
> www.postgresintl.com <http://www.postgresintl.com>
>
> On 31 December 2015 at 07:53, Markus KARG <markus@headcrashing.eu
> <mailto:markus@headcrashing.eu>> wrote:
>
>     Thanks, but as I also developed the fix as announced this morning, I
>     now spent time for nothing! Would be good if also the project lead
>     *first* files a bug report and assigns to itself. :-(____
>
>     __ __
>
>     *From:*davecramer@gmail.com <mailto:davecramer@gmail.com>
>     [mailto:davecramer@gmail.com <mailto:davecramer@gmail.com>] *On
>     Behalf Of *Dave Cramer
>     *Sent:* Donnerstag, 31. Dezember 2015 12:32
>     *To:* Markus KARG
>     *Cc:* Adam Rauch; List
>
>
>     *Subject:* Re: [JDBC] Recent backward compatibility break in
>     PreparedStatement.setObject()____
>
>     __ __
>
>     I put a PR in already https://github.com/pgjdbc/pgjdbc/pull/480____
>
>
>     ____
>
>     Dave Cramer
>
>     davec@postgresintl.com <mailto:davec@postgresintl.com>____
>
>     www.postgresintl.com <http://www.postgresintl.com>____
>
>     __ __
>
>     On 31 December 2015 at 03:23, Markus KARG <markus@headcrashing.eu
>     <mailto:markus@headcrashing.eu>> wrote:____
>
>     I'm the original contributor of that changeset. This is simply a
>     bug. I'll fix it later today. Thanks for reporting! :-)____
>
>     -Markus____
>
>      ____
>
>     *From:*pgsql-jdbc-owner@postgresql.org
>     <mailto:pgsql-jdbc-owner@postgresql.org>
>     [mailto:pgsql-jdbc-owner@postgresql.org
>     <mailto:pgsql-jdbc-owner@postgresql.org>] *On Behalf Of *Dave Cramer
>     *Sent:* Mittwoch, 30. Dezember 2015 22:55
>     *To:* Adam Rauch
>     *Cc:* List
>     *Subject:* Re: [JDBC] Recent backward compatibility break in
>     PreparedStatement.setObject()____
>
>      ____
>
>     No, this was not intentional.____
>
>      ____
>
>     Thanks for the report!____
>
>
>     ____
>
>     Dave Cramer
>
>     davec@postgresintl.com <mailto:davec@postgresintl.com>____
>
>     www.postgresintl.com <http://www.postgresintl.com>____
>
>      ____
>
>     On 30 December 2015 at 16:45, Adam Rauch <adam@labkey.com
>     <mailto:adam@labkey.com>> wrote:____

>
>     The PostgreSQL JDBC driver recently changed PreparedStatement
>     behavior with calls such as setObject(index, value, Types.VARCHAR).
>     Previously, the driver would call value.toString() before binding
>     all VARCHAR values... meaning any object could be passed as "value."
>     In recent updates (e.g., 9.4.1207), most non-String values now
>     result in an exception:
>
>         Exception in thread "main" org.postgresql.util.PSQLException:
>     Cannot convert an instance of XXX to type String
>             at
>     org.postgresql.jdbc.PgStatement.cannotCastException(PgStatement.java:1813)
>             at
>     org.postgresql.jdbc.PgStatement.cannotCastException(PgStatement.java:1809)
>             at
>     org.postgresql.jdbc.PgStatement.castToString(PgStatement.java:1805)
>             at
>     org.postgresql.jdbc.PgStatement.setObject(PgStatement.java:1540)
>             at
>     org.postgresql.jdbc.PgStatement.setObject(PgStatement.java:1818)
>             at PGTest.main(PGTest.java:25)
>
>     This seems to have broken during the refactor to support binary
>     transfer for setObject(),
>     https://github.com/pgjdbc/pgjdbc/issues/328. Was the behavior change
>     intentional? If so, it should probably be documented (e.g., I didn't
>     see any mention in the change log). If not, I'd suggest going back
>     to calling value.toString() for all types. I'm sure ours is not the
>     only application that relies on this long-standing (>10 years)
>     PostgreSQL JDBC behavior. FWIW, other JDBC drivers call toString()
>     in this situation.
>
>     Here's a contrived example that demonstrates the behavior change.
>     This code snippet runs fine in 1201 but fails with the exception

>     above in 1207.____
>
>         Object param = *new *Object() {*public *String toString() {*return **"tables"*;}};____
>
>      ____
>
>     *    try *(PreparedStatement stmt = conn.prepareStatement(*"SELECT */*/*FROM information_schema.tables WHERE table_name = ?"*))____
>
>         {____
>
>             stmt.setObject(1, param, Types.*/VARCHAR/*);____
>
>      ____
>
>         *    try *(ResultSet rs = stmt.executeQuery())____
>
>             {____
>
>             *    while *(rs.next())____
>
>                     System.*/out/*.println(rs.getString(1));____
>
>             }____
>
>         }____
>
>
>     Thanks,
>     Adam____
>
>      ____
>
>     __ __
>
>