Kobus Walters wrote:
Create a prepared statement with sql "select localtimestamp - time ?". Call setObject(1, java.lang.String) with the String value "00:00:00" for instance. In version 7 this gets executed as "select localtimestamp - time '00:00:00'", while version 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();
}
}
---------------------------(end of broadcast)--------------------------- TIP 8: explain analyze is your friend
