"could not determine data type of parameter" with timestamp

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
9 messages Options
Reply | Threaded
Open this post in threaded view
|

"could not determine data type of parameter" with timestamp

Rémi Aubel
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. 

--


Reply | Threaded
Open this post in threaded view
|

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

rob stone-2


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

Reply | Threaded
Open this post in threaded view
|

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

Rémi Aubel
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 <[hidden email]> 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
--


Reply | Threaded
Open this post in threaded view
|

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

David G Johnston
In reply to this post by Rémi Aubel
On Tue, Dec 5, 2017 at 8:11 AM, Rémi Aubel <[hidden email]> 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.
Reply | Threaded
Open this post in threaded view
|

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

Dave Cramer-8
So ps.setNull(1, Types.TIMESTAMP);

Doesn't work ?


On 5 December 2017 at 15:42, David G. Johnston <[hidden email]> wrote:
On Tue, Dec 5, 2017 at 8:11 AM, Rémi Aubel <[hidden email]> 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.

Reply | Threaded
Open this post in threaded view
|

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

Rémi Aubel
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 <[hidden email]> a écrit :
So ps.setNull(1, Types.TIMESTAMP);

Doesn't work ?

On 5 December 2017 at 15:42, David G. Johnston <[hidden email]> wrote:
On Tue, Dec 5, 2017 at 8:11 AM, Rémi Aubel <[hidden email]> 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.

--


Reply | Threaded
Open this post in threaded view
|

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

Dave Cramer-8




On 6 December 2017 at 08:26, Rémi Aubel <[hidden email]> 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 <[hidden email]> a écrit :
So ps.setNull(1, Types.TIMESTAMP);

Doesn't work ?

On 5 December 2017 at 15:42, David G. Johnston <[hidden email]> wrote:
On Tue, Dec 5, 2017 at 8:11 AM, Rémi Aubel <[hidden email]> 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.

--


Reply | Threaded
Open this post in threaded view
|

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

Vladimir Sitnikov
In reply to this post by Rémi Aubel
>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
Reply | Threaded
Open this post in threaded view
|

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

Rémi Aubel
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 <[hidden email]> 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
--


Previous Thread Next Thread