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

Поиск
Список
Период
Сортировка
От Dave Cramer
Тема Re: "could not determine data type of parameter" with timestamp
Дата
Msg-id CADK3HHKE-RHFUkPq6wc-qFgPZdeuaZdiFF7dWkNwjMQmWR2vBg@mail.gmail.com
обсуждение исходный текст
Ответ на Re: "could not determine data type of parameter" with timestamp  (Rémi Aubel <remi.aubel@gmail.com>)
Ответы Re: "could not determine data type of parameter" with timestamp
Список pgsql-jdbc
Дерево обсуждения
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.aubel@gmail.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@fastcrypt.com> a écrit :
So ps.setNull(1, Types.TIMESTAMP);

Doesn't work ?

On 5 December 2017 at 15:42, David G. Johnston <david.g.johnston@gmail.com> wrote:
On Tue, Dec 5, 2017 at 8:11 AM, Rémi Aubel <remi.aubel@gmail.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.
​

--



В списке pgsql-jdbc по дате отправления: