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?

I am not an expert of PostGres but maybe you are missing the doubled quotes?

q.Params.ParamByName('id').AsString :=
''71d6776e-1564-11e3-ad65-5349928f15b0'';

Cheers, A.

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.

Michael.

Michael Van Canneyt wrote / napísal(a):

or you can typecast parameter like:
  P.asString:='{somestring}'
  P.DataType:=ftGuid;

-Laco.

Thank guys, fixed with:

q.SQL.Add('where t1.id::text = :id');

I'll do this change in all units of my project. :/

silvioprog wrote

Thank guys, fixed with: q.SQL.Add('where t1.id::text = :id');

I recommend against this.

It will do the conversion for each record that is tested. This takes time.

If you do the conversion on the parameter, it will be done only once, when the parameter is set (or so it should).

Michael.

2013/9/5 Michael Van Canneyt

Hm... you're absolutely right. I'll do as you advised me before (with cast in param). Thanks again! (y)