Thread: "could not determine data type of parameter" with timestamp
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.
I need to execute this PreparedStatement:
select * from my_table where ? is null
with a bound parameter of type Timestamp.
With this form, the driver (version 42.1.4) raises this exception:
org.postgresql.util.PSQLException: ERROR: could not determine data type of parameter $1
Note: I have the same exception if the bound parameter is null. The PreparedStatement should result in
select * from my_table where null is null
which is correct SQL.
The solution given 4 years ago was to explicitly cast the parameter as a timestamp (select * from my_table where ?::timestamp is null).
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.
Any suggestion?
Thanks in advance.
Rémi.
--
On Tue, 2017-12-05 at 15:11 +0000, Rémi Aubel wrote: > Hello, > > > > The solution given 4 years ago was to explicitly cast the parameter > as a timestamp (select * from my_table where ?::timestamp is null). > 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. > > Any suggestion? > > Thanks in advance. > > Rémi. > > Hi Rémi, After creating the Prepared Statement you can do a ResultSetMetaData.getMetaData() which returns an object containing the data types of the columns in your select list. Then for each column in your "where" clause you can obtain its data type and then do setLong, setString, setTimeStamp or whatever, according to its type. Then do the "execute" to obtain the actual result set. HTH, Rob
Hi Rob,
Thank you for your answer.
Unfortunately, in my application, the condition in the where clause does not involve a column, but the bound parameter itself (? is null). Such a condition may be strange but, as I said previously, the request is dynamically generated.
I guess that getMetaData() won't help here.
Rémi.
Le mar. 5 déc. 2017 à 21:22, rob stone <floriparob@gmail.com> a écrit :
On Tue, 2017-12-05 at 15:11 +0000, Rémi Aubel wrote:
> Hello,
>
>
>
> The solution given 4 years ago was to explicitly cast the parameter
> as a timestamp (select * from my_table where ?::timestamp is null).
> 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.
>
> Any suggestion?
>
> Thanks in advance.
>
> Rémi.
>
>
Hi Rémi,
After creating the Prepared Statement you can do a
ResultSetMetaData.getMetaData() which returns an object containing the
data types of the columns in your select list.
Then for each column in your "where" clause you can obtain its data
type and then do setLong, setString, setTimeStamp or whatever,
according to its type. Then do the "execute" to obtain the actual
result set.
HTH,
Rob
--
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.
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:
Hello,I know my problem has already been answered in this list (https://www.postgresql.org/message-id/CA%2BxsaB0EMDaNek0Oky 9c17_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.
Yes, ps.setNull(1, Types.TIMESTAMP) works.
But, once again, with the way my application works, when I bind the parameters, the query is already built. So, if my parameter is null, I have no way to guess its type.
I tried ps.setNull(1, Types.VARCHAR) as a fallback, and it works with a condition like "where ? is null". It does not work with a condition like "where birth_date > ?".
Maybe we could parse the query to use ps.setNull(1, Types.VARCHAR) when we have something like "? is null" (and a null bound parameter), but it seems to be weak (and a little bit dirty).
Why is the driver not able to ignore the parameter type when we just want to compare it to null?
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: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.
--
On 6 December 2017 at 08:26, Rémi Aubel <remi.aubel@gmail.com> wrote:
Yes, ps.setNull(1, Types.TIMESTAMP) works.But, once again, with the way my application works, when I bind the parameters, the query is already built. So, if my parameter is null, I have no way to guess its type.I tried ps.setNull(1, Types.VARCHAR) as a fallback, and it works with a condition like "where ? is null". It does not work with a condition like "where birth_date > ?".Maybe we could parse the query to use ps.setNull(1, Types.VARCHAR) when we have something like "? is null" (and a null bound parameter), but it seems to be weak (and a little bit dirty).
We would really like to avoid parsing the query.
Why is the driver not able to ignore the parameter type when we just want to compare it to null?
This is the way the extended protocol with PostgreSQL works.
Not much help for you but the api has contemplated this problem as there is the above mentioned method.
Dave Cramer
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: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.--
>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.
You can find a bit on that around slides 25-27: https://www.slideshare.net/VladimirSitnikv/postgresql-and-jdbc-striving-for-high-performance/25
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 fallbackPlease, 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.You can find a bit on that around slides 25-27: https://www.slideshare.net/VladimirSitnikv/postgresql-and-jdbc-striving-for-high-performance/25Vladimir
--
Dave and Vladimir - please jump in if I've misunderstood how pgJDBC works in these cases.
Paraphrasing what Rémi wrote:
> Why is the driver not able to ignore the parameter type when we just want to compare it to null?
The pgJDBC driver can ignore the parameter type. Technically it passes the type as unspecified (0) and it passes the value as a string, but the effect is to ignore the parameter type.
The PostgreSQL backend cannot ignore the parameter type because it needs to know which RECEIVE function to call in order to interpret the value that is sent to it over the wire. (see https://www.postgresql.org/docs/current/static/sql-createtype.html) Because your program is using a PreparedStatement and because a PreparedStatement can be executed multiple times with different parameters every time, the backend must be ready to handle both null and non-null values. The PostgreSQL wire level v3 protocol allows pgJDBC to pass the parameter type as 0 (org.postgresql.core.Oid.UNSPECIFIED). However, setting the parameter type to 0 tells the PostgreSQL backend to determine the type of the parameter from the statement context.
In the case of the "where ? is null" query, there isn't enough context to determine the type and PostgreSQL itself sends the "ERROR: could not determine data type of parameter $1" message to pgJDBC.
The same error would happen with an ODBC driver or libpq or any other driver.
When your program calls ps.setNull(1, Types.VARCHAR), pgJDBC sends the parameter type as Oid.VARCHAR and the PostgreSQL backend doesn't have to figure it out from the context. It parses the query as "where ?::varchar is null" and is perfectly happy. It would be just as happy with ps.setNull(1, Types.DATE) which would be interpreted as "where ?::date is null" or some other type. (If PGProperty.STRING_TYPE is set to "unspecified" at connect time and you call rs.setNull(1, Types.VARCHAR), pgJDBC sends the null value as Oid.UNSPECIFIED and your workaround would fail.)
The flip side of that is that you can explicitly pass values as untyped parameters by using Types.OTHER and let PostgreSQL figure out the type for you. For example, "insert into mytable (myjsoncolumn) values (?)" works when you call ps.setObject(1, "{ \"name\":\"John\" }", Types.OTHER). With the same statement, ps.setString(1, "{ \"name\":\"John\" }") would fail with a "ERROR: column " myjsoncolumn" is of type json but expression is of type character varying" message. And ps.setObject(1, "This is not JSON.", Types.OTHER) fails with "ERROR: invalid input syntax for type json". In all 3 cases, the backend knows that myjsoncolumn is a JSON column and deals with the parameter types/values accordingly.
> with "where birth_date > ?" ... ps.setNull(1, Types.VARCHAR) does not work
This is an entirely different issue. In this case the PostgreSQL knows the type of the birth_date column is date and expects the parameter to match or for an implicit conversion between the parameter type and date to exist. That is why ps.setNull(1, Types.VARCHAR) fails with "ERROR: operator does not exist: date > character varying". For this case, you can tell pgJDBC to pass the parameter type as 0 by calling ps.setNull(1, Types.NULL) or you can call "ParameterMetaData pmeta = ps.getParameterMetaData()" to get the meta data for the parameters and then call "ps.setNull(1, pmeta.getParameterType(1))" to set the null with the type that PostgreSQL expects. pmeta.getParameterType(int) is generally the better option because it avoids the performance issues that Vladimir mentions in his presentation.
Note that getParameterMetaData() gets the parameter type information by asking the PostgreSQL backend to determine the types from the statement context. This means that calling getParameterMetaData() for the "where ? is null" case will fail with the "ERROR: could not determine data type of parameter $1" message just like executing it with an unspecified null.
On Wed, Dec 6, 2017 at 7:38 AM, Dave Cramer <pg@fastcrypt.com> wrote:
On 6 December 2017 at 08:26, Rémi Aubel <remi.aubel@gmail.com> wrote:Yes, ps.setNull(1, Types.TIMESTAMP) works.But, once again, with the way my application works, when I bind the parameters, the query is already built. So, if my parameter is null, I have no way to guess its type.I tried ps.setNull(1, Types.VARCHAR) as a fallback, and it works with a condition like "where ? is null". It does not work with a condition like "where birth_date > ?".Maybe we could parse the query to use ps.setNull(1, Types.VARCHAR) when we have something like "? is null" (and a null bound parameter), but it seems to be weak (and a little bit dirty).We would really like to avoid parsing the query.Why is the driver not able to ignore the parameter type when we just want to compare it to null?This is the way the extended protocol with PostgreSQL works.Not much help for you but the api has contemplated this problem as there is the above mentioned method.Dave CramerRé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:Hello,I know my problem has already been answered in this list (https://www.postgresql.org/message-id/CA%2BxsaB0EMDaNek0Oky 9c17_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.--
Hi all,
I'm coming back to this "? is null" problem.
I have understood that it is important to "always use typed nulls" in prepared statements. So, finally, we are adapting our code.
But I'm stuck with UUID. The UUID type does not exist in java.sql.Types.
If I ask to the Portgres JDBC driver the type of a UUID column (for instance using the ResultSet.getJdbcColumnType() method), I get 1111 (OTHER).
But if I use this type for a statement with "? is null", the driver keeps on raising the "ERROR: could not determine data type of parameter $1".
Which SQL type should I use for UUID?
Rémi
Le mer. 31 janv. 2018 à 05:41, Brad DeJong <bpd0018@gmail.com> a écrit :
Dave and Vladimir - please jump in if I've misunderstood how pgJDBC works in these cases.Paraphrasing what Rémi wrote:> Why is the driver not able to ignore the parameter type when we just want to compare it to null?The pgJDBC driver can ignore the parameter type. Technically it passes the type as unspecified (0) and it passes the value as a string, but the effect is to ignore the parameter type.The PostgreSQL backend cannot ignore the parameter type because it needs to know which RECEIVE function to call in order to interpret the value that is sent to it over the wire. (see https://www.postgresql.org/docs/current/static/sql-createtype.html) Because your program is using a PreparedStatement and because a PreparedStatement can be executed multiple times with different parameters every time, the backend must be ready to handle both null and non-null values. The PostgreSQL wire level v3 protocol allows pgJDBC to pass the parameter type as 0 (org.postgresql.core.Oid.UNSPECIFIED). However, setting the parameter type to 0 tells the PostgreSQL backend to determine the type of the parameter from the statement context.In the case of the "where ? is null" query, there isn't enough context to determine the type and PostgreSQL itself sends the "ERROR: could not determine data type of parameter $1" message to pgJDBC.The same error would happen with an ODBC driver or libpq or any other driver.When your program calls ps.setNull(1, Types.VARCHAR), pgJDBC sends the parameter type as Oid.VARCHAR and the PostgreSQL backend doesn't have to figure it out from the context. It parses the query as "where ?::varchar is null" and is perfectly happy. It would be just as happy with ps.setNull(1, Types.DATE) which would be interpreted as "where ?::date is null" or some other type. (If PGProperty.STRING_TYPE is set to "unspecified" at connect time and you call rs.setNull(1, Types.VARCHAR), pgJDBC sends the null value as Oid.UNSPECIFIED and your workaround would fail.)The flip side of that is that you can explicitly pass values as untyped parameters by using Types.OTHER and let PostgreSQL figure out the type for you. For example, "insert into mytable (myjsoncolumn) values (?)" works when you call ps.setObject(1, "{ \"name\":\"John\" }", Types.OTHER). With the same statement, ps.setString(1, "{ \"name\":\"John\" }") would fail with a "ERROR: column " myjsoncolumn" is of type json but expression is of type character varying" message. And ps.setObject(1, "This is not JSON.", Types.OTHER) fails with "ERROR: invalid input syntax for type json". In all 3 cases, the backend knows that myjsoncolumn is a JSON column and deals with the parameter types/values accordingly.> with "where birth_date > ?" ... ps.setNull(1, Types.VARCHAR) does not workThis is an entirely different issue. In this case the PostgreSQL knows the type of the birth_date column is date and expects the parameter to match or for an implicit conversion between the parameter type and date to exist. That is why ps.setNull(1, Types.VARCHAR) fails with "ERROR: operator does not exist: date > character varying". For this case, you can tell pgJDBC to pass the parameter type as 0 by calling ps.setNull(1, Types.NULL) or you can call "ParameterMetaData pmeta = ps.getParameterMetaData()" to get the meta data for the parameters and then call "ps.setNull(1, pmeta.getParameterType(1))" to set the null with the type that PostgreSQL expects. pmeta.getParameterType(int) is generally the better option because it avoids the performance issues that Vladimir mentions in his presentation.Note that getParameterMetaData() gets the parameter type information by asking the PostgreSQL backend to determine the types from the statement context. This means that calling getParameterMetaData() for the "where ? is null" case will fail with the "ERROR: could not determine data type of parameter $1" message just like executing it with an unspecified null.On Wed, Dec 6, 2017 at 7:38 AM, Dave Cramer <pg@fastcrypt.com> wrote:On 6 December 2017 at 08:26, Rémi Aubel <remi.aubel@gmail.com> wrote:Yes, ps.setNull(1, Types.TIMESTAMP) works.But, once again, with the way my application works, when I bind the parameters, the query is already built. So, if my parameter is null, I have no way to guess its type.I tried ps.setNull(1, Types.VARCHAR) as a fallback, and it works with a condition like "where ? is null". It does not work with a condition like "where birth_date > ?".Maybe we could parse the query to use ps.setNull(1, Types.VARCHAR) when we have something like "? is null" (and a null bound parameter), but it seems to be weak (and a little bit dirty).We would really like to avoid parsing the query.Why is the driver not able to ignore the parameter type when we just want to compare it to null?This is the way the extended protocol with PostgreSQL works.Not much help for you but the api has contemplated this problem as there is the above mentioned method.Dave CramerRé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: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.--
In fact, I'm afraid there is a bug that is not yet solved. See the message of Denis Tazhkenov: https://www.postgresql.org/message-id/DUB128-W530EEFFF5B2450BD29CD9EAB5B0%40phx.gbl.
Denis proposed two workarounds:
1. change the prepared statement to add the suffix "::uuid" to the parameter name
2. when the parameter is known to be null, set any type, for instance Types.VARCHAR
None of these workarounds can be applied to our case because:
1. our prepared statements are build earlier and we really don't want to parse them to replace some "? is null" with "?::uuid is null"
2. our expressions are always similar to "(:param is null) or (col = :param)" and using an arbitrary type obviously won't work for the second part of the expression
Any other workaround?
Any hope to have a fix for this issue?
Rémi
Le mar. 27 mars 2018 à 17:17, Rémi Aubel <remi.aubel@gmail.com> a écrit :
Hi all,I'm coming back to this "? is null" problem.I have understood that it is important to "always use typed nulls" in prepared statements. So, finally, we are adapting our code.But I'm stuck with UUID. The UUID type does not exist in java.sql.Types.If I ask to the Portgres JDBC driver the type of a UUID column (for instance using the ResultSet.getJdbcColumnType() method), I get 1111 (OTHER).But if I use this type for a statement with "? is null", the driver keeps on raising the "ERROR: could not determine data type of parameter $1".Which SQL type should I use for UUID?Rémi--Le mer. 31 janv. 2018 à 05:41, Brad DeJong <bpd0018@gmail.com> a écrit :Dave and Vladimir - please jump in if I've misunderstood how pgJDBC works in these cases.Paraphrasing what Rémi wrote:> Why is the driver not able to ignore the parameter type when we just want to compare it to null?The pgJDBC driver can ignore the parameter type. Technically it passes the type as unspecified (0) and it passes the value as a string, but the effect is to ignore the parameter type.The PostgreSQL backend cannot ignore the parameter type because it needs to know which RECEIVE function to call in order to interpret the value that is sent to it over the wire. (see https://www.postgresql.org/docs/current/static/sql-createtype.html) Because your program is using a PreparedStatement and because a PreparedStatement can be executed multiple times with different parameters every time, the backend must be ready to handle both null and non-null values. The PostgreSQL wire level v3 protocol allows pgJDBC to pass the parameter type as 0 (org.postgresql.core.Oid.UNSPECIFIED). However, setting the parameter type to 0 tells the PostgreSQL backend to determine the type of the parameter from the statement context.In the case of the "where ? is null" query, there isn't enough context to determine the type and PostgreSQL itself sends the "ERROR: could not determine data type of parameter $1" message to pgJDBC.The same error would happen with an ODBC driver or libpq or any other driver.When your program calls ps.setNull(1, Types.VARCHAR), pgJDBC sends the parameter type as Oid.VARCHAR and the PostgreSQL backend doesn't have to figure it out from the context. It parses the query as "where ?::varchar is null" and is perfectly happy. It would be just as happy with ps.setNull(1, Types.DATE) which would be interpreted as "where ?::date is null" or some other type. (If PGProperty.STRING_TYPE is set to "unspecified" at connect time and you call rs.setNull(1, Types.VARCHAR), pgJDBC sends the null value as Oid.UNSPECIFIED and your workaround would fail.)The flip side of that is that you can explicitly pass values as untyped parameters by using Types.OTHER and let PostgreSQL figure out the type for you. For example, "insert into mytable (myjsoncolumn) values (?)" works when you call ps.setObject(1, "{ \"name\":\"John\" }", Types.OTHER). With the same statement, ps.setString(1, "{ \"name\":\"John\" }") would fail with a "ERROR: column " myjsoncolumn" is of type json but expression is of type character varying" message. And ps.setObject(1, "This is not JSON.", Types.OTHER) fails with "ERROR: invalid input syntax for type json". In all 3 cases, the backend knows that myjsoncolumn is a JSON column and deals with the parameter types/values accordingly.> with "where birth_date > ?" ... ps.setNull(1, Types.VARCHAR) does not workThis is an entirely different issue. In this case the PostgreSQL knows the type of the birth_date column is date and expects the parameter to match or for an implicit conversion between the parameter type and date to exist. That is why ps.setNull(1, Types.VARCHAR) fails with "ERROR: operator does not exist: date > character varying". For this case, you can tell pgJDBC to pass the parameter type as 0 by calling ps.setNull(1, Types.NULL) or you can call "ParameterMetaData pmeta = ps.getParameterMetaData()" to get the meta data for the parameters and then call "ps.setNull(1, pmeta.getParameterType(1))" to set the null with the type that PostgreSQL expects. pmeta.getParameterType(int) is generally the better option because it avoids the performance issues that Vladimir mentions in his presentation.Note that getParameterMetaData() gets the parameter type information by asking the PostgreSQL backend to determine the types from the statement context. This means that calling getParameterMetaData() for the "where ? is null" case will fail with the "ERROR: could not determine data type of parameter $1" message just like executing it with an unspecified null.On Wed, Dec 6, 2017 at 7:38 AM, Dave Cramer <pg@fastcrypt.com> wrote:On 6 December 2017 at 08:26, Rémi Aubel <remi.aubel@gmail.com> wrote:Yes, ps.setNull(1, Types.TIMESTAMP) works.But, once again, with the way my application works, when I bind the parameters, the query is already built. So, if my parameter is null, I have no way to guess its type.I tried ps.setNull(1, Types.VARCHAR) as a fallback, and it works with a condition like "where ? is null". It does not work with a condition like "where birth_date > ?".Maybe we could parse the query to use ps.setNull(1, Types.VARCHAR) when we have something like "? is null" (and a null bound parameter), but it seems to be weak (and a little bit dirty).We would really like to avoid parsing the query.Why is the driver not able to ignore the parameter type when we just want to compare it to null?This is the way the extended protocol with PostgreSQL works.Not much help for you but the api has contemplated this problem as there is the above mentioned method.Dave CramerRé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: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.--
I believe you should use java.sql.PreparedStatement#setNull(int, int, java.lang.String)
As far as I understand, current implementation ignores typename argument: https://github.com/pgjdbc/pgjdbc/blob/REL42.2.2/pgjdbc/src/main/java/org/postgresql/jdbc/PgPreparedStatement.java#L1240-L1242
however one can file an issue and/or PR to fix that.
Vladimir
Hi Vladimir,
Thank you for your reply.
Indeed, I already tried with all these combinations:
- PreparedStatement.setNull(_, 1111, "uuid")
- PreparedStatement.setNull(_, 1111, "_uuid")
- PreparedStatement.setNull(_, 2003, "uuid")
- PreparedStatement.setNull(_, 2003, "_uuid")
And none of them work. The driver is still unable to "determine data type of parameter $1" :-(
Which is expected regarding the code.
Do you want me to file an issue?
Rémi
Le mer. 28 mars 2018 à 11:57, Vladimir Sitnikov <sitnikov.vladimir@gmail.com> a écrit :
I believe you should use java.sql.PreparedStatement#setNull(int, int, java.lang.String)As far as I understand, current implementation ignores typename argument: https://github.com/pgjdbc/pgjdbc/blob/REL42.2.2/pgjdbc/src/main/java/org/postgresql/jdbc/PgPreparedStatement.java#L1240-L1242however one can file an issue and/or PR to fix that.Vladimir
Rémi>And none of them work
Just as I said. It is a bug.
Rémi>Do you want me to file an issue?
Rémi>Do you want me to file an issue?
Exactly. It would be great if you could file the fix and test as well.
Vladimir
Thank you.
I have reported the issue: https://www.postgresql.org/message-id/CAG2M1fdAFD3N8vZxp4TYLEYz4Rwu5f94ghafEErvHv387oWf8g%40mail.gmail.com
Regards
Remi
Le mer. 28 mars 2018 à 12:18, Vladimir Sitnikov <sitnikov.vladimir@gmail.com> a écrit :
Rémi>And none of them workJust as I said. It is a bug.
Rémi>Do you want me to file an issue?Exactly. It would be great if you could file the fix and test as well.Vladimir
Remi,
While that is the psql-bugs list it will get considerably more attention from us if you report it here https://github.com/pgjdbc/pgjdbc/issues
Thanks
On 28 March 2018 at 09:32, Rémi Aubel <remi.aubel@gmail.com> wrote:
Thank you.I have reported the issue: https://www.postgresql.org/message-id/ CAG2M1fdAFD3N8vZxp4TYLEYz4Rwu5 f94ghafEErvHv387oWf8g%40mail. gmail.com RegardsRemi--Le mer. 28 mars 2018 à 12:18, Vladimir Sitnikov <sitnikov.vladimir@gmail.com> a écrit :Rémi>And none of them workJust as I said. It is a bug.
Rémi>Do you want me to file an issue?Exactly. It would be great if you could file the fix and test as well.Vladimir
While that is the psql-bugs list it will get considerably more attention from us if you report it here https://github.com/pgjdbc/pgjdbc/issues \
This bug is also off-topic for pgsql-bugs; the GitHub Issues is the correct place to put this report.
David J.
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: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.
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_TIMESTAMPData 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émiLe 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: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.
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
Le jeu. 29 mars 2018 à 14:49, Dave Cramer <pg@fastcrypt.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 javasetNull(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 CramerOn 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_TIMESTAMPData 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émiLe 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: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.
Remi,
See this for working code
On 29 March 2018 at 09:07, Rémi Aubel <remi.aubel@gmail.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 $1Unfortunately, AFAIK, setObject has no signature with the type name.What do you suggest?RémiPS: Of course, in my code, I use java.sql.Type--Le jeu. 29 mars 2018 à 14:49, Dave Cramer <pg@fastcrypt.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 javasetNull(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 CramerOn 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_TIMESTAMPData 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émiLe 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: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.
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@fastcrypt.com> a écrit :
Remi,See this for working codeOn 29 March 2018 at 09:07, Rémi Aubel <remi.aubel@gmail.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 $1Unfortunately, AFAIK, setObject has no signature with the type name.What do you suggest?RémiPS: Of course, in my code, I use java.sql.Type--Le jeu. 29 mars 2018 à 14:49, Dave Cramer <pg@fastcrypt.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 javasetNull(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 CramerOn 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_TIMESTAMPData 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émiLe 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: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.
Remi,
You are correct see
The one solution is to use PGTimestamp as the object
On 29 March 2018 at 09:23, Rémi Aubel <remi.aubel@gmail.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@fastcrypt.com> a écrit :Remi,See this for working codeOn 29 March 2018 at 09:07, Rémi Aubel <remi.aubel@gmail.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 $1Unfortunately, AFAIK, setObject has no signature with the type name.What do you suggest?RémiPS: Of course, in my code, I use java.sql.Type--Le jeu. 29 mars 2018 à 14:49, Dave Cramer <pg@fastcrypt.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 javasetNull(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 CramerOn 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_TIMESTAMPData 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émiLe 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: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.
Yes, with PgTimestamp, it's good :-)
Thanks again.
Le jeu. 29 mars 2018 à 15:32, Dave Cramer <pg@fastcrypt.com> a écrit :
Remi,You are correct seeThe one solution is to use PGTimestamp as the objectOn 29 March 2018 at 09:23, Rémi Aubel <remi.aubel@gmail.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@fastcrypt.com> a écrit :Remi,See this for working codeOn 29 March 2018 at 09:07, Rémi Aubel <remi.aubel@gmail.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 $1Unfortunately, AFAIK, setObject has no signature with the type name.What do you suggest?RémiPS: Of course, in my code, I use java.sql.Type--Le jeu. 29 mars 2018 à 14:49, Dave Cramer <pg@fastcrypt.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 javasetNull(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 CramerOn 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_TIMESTAMPData 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émiLe 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: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.
Vladimir> pgjdbc just passes "unknown type" formatted as full timestamp
Vladimir>That backfires for the case "select ?" when backend has no way to tell what was the "expected bind type"
It might be the case the proper solution is something around
setObject(int parameterIndex, Object x, SQLType targetSqlType)
and
setObject(int parameterIndex, Object x, SQLType targetSqlType,
int scaleOrLength)
Of course, those methods just throw "not implemented", however setObject(..., SQLType.TIMESTAMP) might be the way.
Vladimir