Re: BUG: Unable to bind a null value typed as a UUID in a PreparedStatement

@Dave,

I have just tested it. It works fine with setNull(_, 1111, "uuid").
Thanks again :-)

Rémi

Le mer. 28 mars 2018 à 16:38, Dave Cramer <davecramer(at)gmail(dot)com> a écrit :

> On 28 March 2018 at 10:30, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
>> =?utf-8?Q?R=C3=A9mi_Aubel?= <remi(dot)aubel(at)gmail(dot)com> writes:
>> > I need to bind a UUID parameter which may be null in a statement like
>> > "select * from test table where ? is null or ? = c_uuid".
>> > Whatever approach I use, the driver rejects my request with "ERROR:
>> could
>> > not determine data type of parameter $1".
>>
>> Some experimentation suggests that it'd probably work if you wrote the
>> clauses in the other order:
>>
>> regression=# create table test_table (c_uuid uuid);
>> CREATE TABLE
>> regression=# prepare foo as select * from test_table where $1 is null or
>> $1 = c_uuid;
>> ERROR: could not determine data type of parameter $1
>> LINE 1: prepare foo as select * from test_table where $1 is null or ...
>> ^
>> regression=# prepare foo as select * from test_table where $1 = c_uuid or
>> $1 is null;
>> PREPARE
>>
>> In an ideal world, perhaps the order of the parameter references would not
>> matter, but AFAICS making that work would be mighty hard. For now, PG's
>> parser wants to resolve the type of an otherwise-unlabeled parameter
>> symbol the first time it sees it --- and the context "IS NULL" offers
>> no clue what type it should be.
>>
>> Alternatively, you could force the issue with an explicit cast in the
>> text of the query:
>>
>> regression=# prepare foo2 as select * from test_table where $1::uuid is
>> null or $1 = c_uuid;
>> PREPARE
>>
>
> Tom,
>
> This is just a simple example of a bigger problem. One for which there is
> a solution in the driver which was not implemented.
>
> I have implemented it now.
>
> Thanks,
>
> Dave
>
--