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

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