Re: Inserting into time stamp columns

Thank you, David, for your thoughts and in particular for pointing out that
the table I mentioned about type mapping is specifically meant for the
setObject(int,Object,int) method (i.e. with type information specified).
(My oversight.)

Thanks,

Peter

On Wed, Jun 26, 2013 at 5:37 PM, 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...
>
> 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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-jdbc
>