Re: Inserting into time stamp columns

Dave Cramer

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

On Wed, Jun 26, 2013 at 11:37 AM, David Johnston <polobo(at)yahoo(dot)com> wrote:

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

Yes, internally the driver figures out that it is a string. It doesn't
attempt to parse it though

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

It's there to deal with exactly this. The old code used to infer the type
because it didn't really do prepared statements, it just replaced the bound
parameter in the sql, and the backend figured it out.

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

In which case the default would be string = unspecified. This is exactly
what it does.

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

Welcome to the wonderful world of design by committee and specs !

>
>
> 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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-jdbc
>