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

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(at)gmail(dot)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
> 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(at)fastcrypt(dot)com> wrote:
>
>>
>>
>>
>>
>> On 6 December 2017 at 08:26, Rémi Aubel <remi(dot)aubel(at)gmail(dot)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
>>
>> davec(at)postgresintl(dot)com
>> www.postgresintl.com
>>
>>> Rémi.
>>>
>>> Le mer. 6 déc. 2017 à 01:58, Dave Cramer <pg(at)fastcrypt(dot)com> a écrit :
>>>
>>>> So ps.setNull(1, Types.TIMESTAMP);
>>>>
>>>> Doesn't work ?
>>>>
>>>> Dave Cramer
>>>>
>>>> davec(at)postgresintl(dot)com
>>>> www.postgresintl.com
>>>>
>>>> On 5 December 2017 at 15:42, David G. Johnston <
>>>> david(dot)g(dot)johnston(at)gmail(dot)com> wrote:
>>>>
>>>>> On Tue, Dec 5, 2017 at 8:11 AM, Rémi Aubel <remi(dot)aubel(at)gmail(dot)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.
>>>>> ​
>>>>>
>>>>
>>>> --
>>>
>>>
>>>
> --