[fpc-pascal] PostgreSQL: ERROR: operator does not exists: uuid = text

[LacaK]

Michael Van Canneyt  wrote / napísal(a):
>
>
> On Wed, 4 Sep 2013, silvioprog wrote:
>
>> Hello,
>> I can't understand how to open a query filtering by a field of type 
>> GUID.
>>
>> The error message is below (translated from BR to US):
>>
>> TPQConnection: Preparation of query failed. (PostgreSQL: ERROR: 
>> operator does not exist: uuid = text
>> LINE 3: where t1.id = $ 1
>>                      ^
>> HINT: No operator corresponds with the name(s) and type(s) of 
>> argument(s) informed. You need to add explicit type casts.
>>
>> I'm sending a small example to reproduce this error in attached. I'm 
>> also sending the script that creates the tables.
>>
>> Has anyone had this problem?
>
> Your program is wrong.
>
> Postgres parameters are typed. If you do a
>
>   P.asString:='somestring'
>
> then SQL DB will use a parameter of type string to send the data to 
> postgres.
> Postgres does not automatically convert between strings to uuid fields 
> (see the error you get).
>
> The solution is to change your SQL to typecast the string parameter to 
> a UUID type:
>
>
>     q.SQL.Add('where t1.id = (:id)::uuid');
>
> or even
>
>     q.SQL.Add('where t1.id = cast (:id as uuid)');
>
> And all will work well.
or you can typecast parameter like:
  P.asString:='{somestring}'
  P.DataType:=ftGuid;

-Laco.