Re: Inserting into time stamp columns

Thank you, Dave!

If I set stringtype to unspecified, I get the following error:

Exception in thread "main" org.postgresql.util.PSQLException: ERROR: permission denied for relation tstest

The code:

        Class.forName("org.postgresql.Driver");

        Properties props = new Properties();

        props.setProperty("user", "jcbtest");

        props.setProperty("password", "ciril");

        props.setProperty("stringtype", "unspecified");

        Connection connection = DriverManager.getConnection("jdbc:postgresql://localhost:5432/jcbtest", props);

        PreparedStatement pstmt = connection.prepareStatement("insert into tstest values(?)");

        pstmt.setObject(1, "1998-06-04 00:00:00+09");

        pstmt.execute();

        connection.close();

After removing the line where the said property is set, I can connect again.

Any thoughts?

Thanks,

Peter

On Wed, Jun 26, 2013 at 4:51 PM, Dave Cramer <pg@fastcrypt.com> wrote:

This is more an artifact of the way bound parameters are handled by the backend. Normally we would bind this to a text type as it is a string. What you can do is try setting stringtype = unspecified as a connection parameter in which case it will let the server determine the type.

The docs here have more of an explanation http://jdbc.postgresql.org/documentation/head/connect.html#connection-parameters

Dave Cramer

dave.cramer(at)credativ(dot)ca
http://www.credativ.ca

On Wed, Jun 26, 2013 at 10:39 AM, Péter Kovács <peter.dunay.kovacs@gmail.com> wrote:

Hi,

Consider the following simple table:

create table tstest (ts timestamp);

The following code snippet

        PreparedStatement pstmt = connection.prepareStatement("insert into tstest values(?)");

        pstmt.setObject(1, "1998-06-04 00:00:00+09");

        pstmt.execute();

results in the following error: 

Exception in thread "main" org.postgresql.util.PSQLException: ERROR: column "ts" is of type timestamp without time zone but expression is of type character varying

  Hint: You will need to rewrite or cast the expression.

Do I understand it correctly that it is a limitation of the JDBC driver not to implement the String -> Timestamp implicit conversion listed in Table 8.5 of this document:

http://docs.oracle.com/javase/1.5.0/docs/guide/jdbc/getstart/mapping.html#1034737 ? Or is something missing in my code to get the friendly behavior of plain SQL which works with time stamp strings such as '1998-06-04 00:00:00+09 without explicit conversion?

Many thanks

Peter