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

Поиск
Список
Период
Сортировка
От Rémi Aubel
Тема Re: "could not determine data type of parameter" with timestamp
Дата
Msg-id CAG2M1fc69LNtP=ELJjkd1Q4zZM9knnHz4TfahezuCGRg4E68Kw@mail.gmail.com
обсуждение исходный текст
Ответ на Re: "could not determine data type of parameter" with timestamp  (Vladimir Sitnikov <sitnikov.vladimir@gmail.com>)
Список pgsql-jdbc
Thank you for this explanation. Of course, it makes sense.
It just means a lot of work to adapt our software.

Thanks and regards,
Rémi.

Le mer. 6 déc. 2017 à 14:49, Vladimir Sitnikov <sitnikov.vladimir@gmail.com> a écrit :
>So, if my parameter is null, I have no way to guess its type.

TL;DR: always use typed nulls in JDBC; use consistent data types (avoid date/varchar flips) for the same sql text; use "?::timestamptz" for edge cases.

>I tried ps.setNull(1, Types.VARCHAR) as a fallback

Please, avoid that. It will backfire with both PostgreSQL and Oracle DB.

1) That's very common pitfall with SQL and JDBC.
nulls are typed in SQL.

Suppose you have a couple of procedures: "procedure test(v varchar)" and "procedure test(v number)".

ps.setNull(1, Types.VARCHAR) and ps.setNull(1, Types.NUMERIC) should result in execution of _different_ procedures.

JDBC driver has no way which one do you want to execute if you "just pass null".

Unfortunately, we cannot go very strict there since everybody just assumes "date is pretty much the same thing as timestamp", so in case client passes "timestamp, date, etc", pgjdbc just passes "unknown type" formatted as full timestamp and assumes backend would parse and use accordingly. For instance, if just a date was expected, it would just ignore time/timezone part.

That backfires for the case "select ?" when backend has no way to tell what was the "expected bind type". Of course it cannot identify datatype. I have no idea how that can be healed and I do think the current behaviour is sane.

2) On top of that, different datatypes might result in different execution plans, thus JDBC/DB has to adapt to "new datatypes" in case you flip types of bind parameters for the same SQL text back and forth.


Vladimir
--


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

Предыдущее
От: Vladimir Sitnikov
Дата:
Сообщение: Re: "could not determine data type of parameter" with timestamp
Следующее
От: Thomas Kellerer
Дата:
Сообщение: Driver version 42.1.4 and Java 9