stringtype=unspecified is null check problem
От | David G. Johnston |
---|---|
Тема | stringtype=unspecified is null check problem |
Дата | |
Msg-id | CAKFQuwZA+9+FNovGXA6moj+sDzR6ShyzzP3H77m-05n3dXDv-w@mail.gmail.com обсуждение исходный текст |
Ответ на | stringtype=unspecified is null check problem (Martin Handsteiner <martin.handsteiner@sibvisions.com>) |
Ответы |
Re: stringtype=unspecified is null check problem
AW: stringtype=unspecified is null check problem |
Список | pgsql-jdbc |
On Tuesday, January 10, 2023, Martin Handsteiner <martin.handsteiner@ sibvisions.com> wrote:
If a bind parameter is checked for null, ERROR: could not determine data type of parameter $1 occurs.
Simple testcase:
Connection conn = DriverManager.getConnection("jdbc:postgresql://192.168.1.201 :5432/testdb?stringtype=unspec ified", "test", "test");
PreparedStatement stat = conn.prepareStatement("select 1 where ? is null");
stat.setNull(1, Types.VARCHAR);
stat.executeQuery();
-> Exception
For checking the parameter for null, the data type should not be relevant. At least TEXT or VARCHAR could be implicitly used.
There is a mailing post, that says, that stat.setNull(1, Types.VARCHAR); would strict set the value as VARCHAR.
https://www.postgresql.org/message-id/Pine.BSO.4.64.07021414 39480.24142%40leary.csoft.net
But this seams not to happen in my case.
I have also testet to bind the value with:
stat.setNull(1, Types.NULL); or stat.setNull(1, Types.OTHER); or stat.setString(1, null);
Nothing works in this case.
As far as I understand, the jdbc driver sends the value without type information to the database. The database throws the exception.
So maybe this is a database problem.
The database can assume, that in case of ? is null it doesn't matter, which type the sent null is. So even a UNKOWN null is null or not null.
Strange is, that the following statement will work. So in this case null is unknown, but mapped.
conn.prepareStatement("select 1 where coalesce(?, null) is null");
This is indeed how all of this works in the current design. I suggest you add a cast to the input parameter in the query. Or choose a different value for stringtype…
David J.
В списке pgsql-jdbc по дате отправления: