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

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