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

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