Обсуждение: Inserting into time stamp columns

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

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

Peter,

Strange; can you insert it in psql using the same credentials ?

Dave Cramer

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

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

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

Top-posting to follow existing thread...

The documentation referenced indicates:

"Specify the type to use when binding PreparedStatement parameters set via
setString()"

The OP is calling setObject; though I presume that because the supplied
object is a string that internally setString() is called to perform the
actual call...

Is the "stringtype" property meant to be strictly used for backward
compatibility?  This does seem like something that could be generally useful
depending on how it interacts with the JDBC standard.  There is no
"unknown:string-like" type in Java like there is in PostgreSQL.

My thinking is that a call to setObject with a string input would seem to be
best described as "let the database convert this string representation of an
object into whatever type it needs" while a call to setString should imply
that I intend for the supplied value to be treated as a string
(text/varchar) in the database and if the context of its use does not call
for a string that I am in error in my usage.

You can also:

INSERT INTO ... VALUES (?::timestamptz)

or, even better, supply an actual date instance.

You could also setup an implicit cast between text/varchar and timestamp
although the ones that existed pre-8.something were removed for, IMO, good
reason.

Note that in reference to Table 8.5 those conversions *are not implicit* but
occur only if a type designation is supplied to the setObject call.  Since
you did not include a type the default mapping listed in the prior
(unnumbered) table occurs which is one of JDBC CHAR, VARCHAR, or
LONGVARCHAR.  The text in the second paragraph previous to Table 8.5 imply
this though it is arguably worded somewhat poorly - the "these" starting
that paragraph refers to the prior table and not Table 8.5


David J.



Dave Cramer-8 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@

> > 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
>>
>>
>>
>>
>>
>>
>>

--
View this message in context: http://postgresql.1045698.n5.nabble.com/Inserting-into-time-stamp-columns-tp5761151p5761169.html
Sent from the PostgreSQL - jdbc mailing list archive at Nabble.com.


--
Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-jdbc