Re: Inserting into time stamp columns

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(dot)dunay(dot)kovacs(at)gmail(dot)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(at)fastcrypt(dot)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(dot)dunay(dot)kovacs(at)gmail(dot)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
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>
>