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

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