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

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