Thread: PreparedStatement setObject

Setting java.lang.String values with the setObject method in version 8 of t=
he pgsql-jdbc driver (postgresql-8.0-310.jdbc3.jar) does not work as with v=
ersion 7 (pg74.215.jdbc3.jar) of the driver. Example:

Create a prepared statement with sql "select localtimestamp - time ?". Call=
 setObject(1, java.lang.String) with the String value "00:00:00" for instan=
ce. In version 7 this gets executed as "select localtimestamp - time '00:00=
:00'", while version 8 try to execute it as "select localtimestamp - time 0=
0:00:00", which of cause fails since the quotes are missing.

Kobus Walters wrote:

> Create a prepared statement with sql "select localtimestamp - time ?". Call setObject(1, java.lang.String) with the
Stringvalue "00:00:00" for instance. In version 7 this gets executed as "select localtimestamp - time '00:00:00'",
whileversion 8 try to execute it as "select localtimestamp - time 00:00:00", which of cause fails since the quotes are
missing.

I can't reproduce this. With the 8.0-310 driver against an 8.0.0 server,
I get a syntax error with that query:

   ERROR: syntax error at or near "$1"

The query sent by the driver is:

   SELECT LOCALTIMESTAMP - TIME $1

which the backend does not like. The change in behaviour from 7.4-era
drivers is because the 8.0 driver pushes handling of parameters to the
server, so you have to follow the server's rules for where you may place
parameters. Apparently, after TIME isn't one of those places.

To use a string as a time parameter, see the examples in my test code
(attached).

-O
import java.sql.*;

// Run with one argument: a JDBC url to connect to.
public class TestTime {
    public static void main(String[] args) throws Exception {
        Class.forName("org.postgresql.Driver");
        Connection c = DriverManager.getConnection(args[0]);

                // Works:
        PreparedStatement s = c.prepareStatement("SELECT LOCALTIMESTAMP - ?");
                s.setObject(1, "00:00:00", Types.TIME);

                // Works:
        //PreparedStatement s = c.prepareStatement("SELECT LOCALTIMESTAMP - {t '00:00:00'}");

                // Breaks:
        //PreparedStatement s = c.prepareStatement("SELECT LOCALTIMESTAMP - TIME ?");
                //s.setObject(1, "00:00:00", Types.TIME);

        ResultSet rs = s.executeQuery();
        rs.next();
        System.err.println("returned " + rs.getObject(1));
        rs.close();
        s.close();
        c.close();
    }
}