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

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.
>>> ​
>>>
>>
>> --
>
>
>