Re: "could not determine data type of parameter" with timestamp

Yes, with PgTimestamp, it's good :-)
Thanks again.

Le jeu. 29 mars 2018 à 15:32, Dave Cramer <pg(at)fastcrypt(dot)com> a écrit :

> Remi,
>
> You are correct see
>
> https://github.com/pgjdbc/pgjdbc/blob/3e0491ac3833800721b98e7437635cf6ab338162/pgjdbc/src/main/java/org/postgresql/jdbc/PgPreparedStatement.java#L1325
>
>
>
>
> The one solution is to use PGTimestamp as the object
>
> Dave Cramer
>
> davec(at)postgresintl(dot)com
> www.postgresintl.com
>
> On 29 March 2018 at 09:23, Rémi Aubel <remi(dot)aubel(at)gmail(dot)com> wrote:
>
>> Dave,
>>
>> If I am not wrong, the "working code" use an "insert" statement. Not a
>> "select" statement with a where clause like "? IS NULL OR ? = C_TIMESTAMP".
>>
>> Rémi
>>
>> Le jeu. 29 mars 2018 à 15:13, Dave Cramer <pg(at)fastcrypt(dot)com> a écrit :
>>
>>> Remi,
>>>
>>> See this for working code
>>>
>>> https://github.com/pgjdbc/pgjdbc/blob/b1581e99b6da96b6e44753ce231ec3acf9869fea/pgjdbc/src/test/java/org/postgresql/test/jdbc2/TimestampTest.java#L259
>>>
>>>
>>>
>>>
>>>
>>> Dave Cramer
>>>
>>> davec(at)postgresintl(dot)com
>>> www.postgresintl.com
>>>
>>> On 29 March 2018 at 09:07, Rémi Aubel <remi(dot)aubel(at)gmail(dot)com> wrote:
>>>
>>>> Sorry, Dave, I switched back to the version 42.2.1 of the driver, which
>>>> has not your fix.
>>>> That's true, with your fix, setNull with the time name works well.
>>>>
>>>> But, I've another related problem.
>>>> With the same query, I'm not able to bind a *non-null* timestamp value.
>>>>
>>>> Timestamp timestamp = new Timestamp(new Date().getTime());
>>>> statement.setObject(1, timestamp, Types.TIMESTAMP);
>>>> statement.setObject(2, timestamp, Types.TIMESTAMP);
>>>> statement.execute();
>>>> => ERROR: could not determine data type of parameter $1
>>>>
>>>> Unfortunately, AFAIK, setObject has no signature with the type name.
>>>>
>>>> What do you suggest?
>>>>
>>>> Rémi
>>>>
>>>> PS: Of course, in my code, I use java.sql.Type
>>>>
>>>> Le jeu. 29 mars 2018 à 14:49, Dave Cramer <pg(at)fastcrypt(dot)com> a écrit :
>>>>
>>>>> Remi,
>>>>>
>>>>> Why are you using absolute values for java.sql.Types instead of
>>>>> java.sql.TIMESTAMP.
>>>>>
>>>>> Timestamp is a bit strange as we tend to let the backend intuit which
>>>>> type it is due to the fact that before java 9 there was no
>>>>> TIMESTAMP_WITH_TIMEZONE type in java
>>>>>
>>>>> setNull(pos, DATE) works because we don't have that issue.
>>>>>
>>>>> pstmt = con.prepareStatement("SELECT * FROM TEST_TABLE WHERE ? IS NULL OR ? = C_TIMESTAMP");
>>>>> pstmt.setNull(1,Types.TIMESTAMP, "timestamp");
>>>>> pstmt.setNull(2,Types.OTHER, "timestamp");
>>>>>
>>>>>
>>>>> Worked fine for me...
>>>>>
>>>>>
>>>>>
>>>>>
>>>>> Dave Cramer
>>>>>
>>>>> On 29 March 2018 at 08:31, Rémi Aubel <remi(dot)aubel(at)gmail(dot)com> wrote:
>>>>>
>>>>>> Hi,
>>>>>>
>>>>>> Setting null for a bound value for a UUID column works fine with
>>>>>> Dave's fix (https://github.com/pgjdbc/pgjdbc/pull/1160)
>>>>>>
>>>>>> Now, I'm not able to set a null value for a timestamp.
>>>>>>
>>>>>> My table:
>>>>>> CREATE TABLE TEST_TABLE (C_TIMESTAMP TIMESTAMP)
>>>>>>
>>>>>> My query:
>>>>>> SELECT * FROM TEST_TABLE WHERE ? IS NULL OR ? = C_TIMESTAMP
>>>>>>
>>>>>> Data type and type name for my column C_TIMESTAMP are respectively 93
>>>>>> and "timestamp" (checked with the metadata provided by the driver).
>>>>>>
>>>>>> Using PreparedStatement.setNull(pos, 93) or PreparedStatement.setNull(pos,
>>>>>> 93, "timestamp") for pos = 1, 2 does not work (could not determine
>>>>>> data type of parameter).
>>>>>> Using PreparedStatement.setNull(pos, 91) does work!
>>>>>>
>>>>>> Is it expected?
>>>>>>
>>>>>> Rémi
>>>>>>
>>>>>>
>>>>>> Le mer. 6 déc. 2017 à 01:58, Dave Cramer <pg(at)fastcrypt(dot)com> a écrit :
>>>>>>
>>>>>>> So ps.setNull(1, Types.TIMESTAMP);
>>>>>>>
>>>>>>> Doesn't work ?
>>>>>>>
>>>>>>> Dave Cramer
>>>>>>>
>>>>>>> davec(at)postgresintl(dot)com
>>>>>>> www.postgresintl.com
>>>>>>>
>>>>>>> On 5 December 2017 at 15:42, David G. Johnston <
>>>>>>> david(dot)g(dot)johnston(at)gmail(dot)com> wrote:
>>>>>>>
>>>>>>>> On Tue, Dec 5, 2017 at 8:11 AM, Rémi Aubel <remi(dot)aubel(at)gmail(dot)com>
>>>>>>>> wrote:
>>>>>>>>
>>>>>>>>> Hello,
>>>>>>>>>
>>>>>>>>> I know my problem has already been answered in this list (
>>>>>>>>> https://www.postgresql.org/message-id/CA%2BxsaB0EMDaNek0Oky9c17_8UX3-epWVP11%2BvTNgrAPoY2s9FA%40mail.gmail.com)
>>>>>>>>> but it was a long time ago and I would like to know if any other solution
>>>>>>>>> exists now.
>>>>>>>>>
>>>>>>>>
>>>>>>>> ​Not that I am aware.​
>>>>>>>>
>>>>>>>>
>>>>>>>>> But this workaround is not really useful for me, because my
>>>>>>>>> application uses generated (dynamic) queries and targets multiple database
>>>>>>>>> types (not only PosgreSQL). So, when I know the targeted database, I do not
>>>>>>>>> know my parameter types anymore.
>>>>>>>>>
>>>>>>>>
>>>>>>>> ​I'd probably perform the null test in Java and pass the true/false
>>>>>>>> boolean result along to the query:
>>>>>>>>
>>>>>>>> SELECT * FROM my_table WHERE ?::bool;
>>>>>>>> ps.setBoolean(1, tsvar = null); //my Java is rusty but you get the
>>>>>>>> idea.
>>>>>>>>
>>>>>>>> That should work in any database.
>>>>>>>>
>>>>>>>> David J.
>>>>>>>> ​
>>>>>>>>
>>>>>>>
>>>>>>> --
>>>>>>
>>>>>>
>>>>>>
>>>>> --
>>>>
>>>>
>>>>
>>> --
>>
>>
>>
> --