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.



---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

This is a multi-part message in MIME format.
--------------020801060909010501080501
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Kobus Walters wrote:

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

--------------020801060909010501080501
Content-Type: text/plain;
name="TestTime.java"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline;
filename="TestTime.java"

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();
}
}

--------------020801060909010501080501
Content-Type: text/plain
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
MIME-Version: 1.0


---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

--------------020801060909010501080501--