How to query on a GUID field?

BIZ TMS Aurelius
1

Hi! Let's say I have a Firebird table:

create table MyTable (
  uuid char(16) character set octets not null, 
  name varchar(60), 
  primary key (uuid));

Mapped on Aurelius:

  [Entity]
  [Table('MyTable')]
  [Id('FUUID', TIdGenerator.None)]
  TMyTable = class
  private
    [Column('UUID', [TColumnProp.Required], 16)]
    FUUID: TGuid;
    [Column('NOME', [], 60)]
    FName: Nullable<string>;
  public
    property UUID: TGuid read FUUID write FUUID;
    property Name: Nullable<string> read FName write FName;
  end;

I can find an object by its UUID using:

var
  AGuid: TGuid;
  AObj: TMyTable;
begin
  AGuid := GuidFromString('...');
  AObj := Manager.Find<TMyTable>(AGuid);

BUT, how to use the UUID field with Linq ? Example:

var
  AGuid: TGuid;
  AObj: TMyTable;
begin
  AGuid := GuidFromString('...');
  AObj := Manager.Find<TMyTable>
      .Where(Linq['UUID']  = AGuid)
      .UniqueResult;

It does not work.

2

Hi Anderson,
What is the error message you get? What components are you using for database connection?

3

Hi Wagner, it's a compile error

'Operator not applicable to this operand type'

on

.Where(Linq['UUID'] = AGuid)

Regards,

4

I guess only Variants are accepted and I'm having trouble passing a Guid or TBytes

5

Ah right, Just use GuidToString:

.Where(Linq['UUID'] = GuidToString(AGuid))
6

Yes, tryed that but It does not work in this scenario.

GuidToString returns a string representation of 38 chars while the field is a 16 bytes. Than I get a 'string truncation' error from Firebird (Firedac connection)

A solution would be to use a database function to convert the field to this same string representation, eg: uuid_to_char(a.UUID) = :StringParam

BUT, I'm looking for a solution, if possible, to just pass the param value as a raw 16 bytes data.

Similar to what it happens when using

AObj := Manager.Find<TMyTable>(AGuid);

7

Sorry. Doing more tests I realize Find(AGuid) also do not work (same problem). It works only for cached objects. It brakes when executing the SQL 'where a.UUID=?'

8

I figure it out a solution to work with Firebird UUIDs using Linq:

S := '4A9C76F6-8C4A-4B66-9DE7-759C538F549F';
MyObj := Manager.Find<TTeste>
    .Where(Linq['UUID'] = Linq.SqlFunction('char_to_uuid', nil, Linq.Value<string>( S )))
    .UniqueResult;

Need to register the char_to_uuid function fisrt:

TSQLGeneratorRegister.GetInstance.GetGenerator('FIREBIRD3')
    RegisterFunction('char_to_uuid', TSimpleSQLFunction.Create('char_to_uuid'));

Of course, still can't use Find() like:

S := '4A9C76F6-8C4A-4B66-9DE7-759C538F549F';
MyObj = Manager.Find<TTeste>( S );

Regards,

9

That is not the same thing. Use GuidToString, which adds brackets around it.
In any case, this is probably something that should be set at FireDAC level, all Aurelius does is set a parameter value in FireDac using AsString.

1 Like
10

I used the string representation without brackets -- as an example -- 'couse it's the expected format for the Firebird char_to_uuid function used as a workaround.

Can't use GuidToString (and I guess AsString also will not work) since DB field is not a varchar(32/36/38) to keep a string (hex format) representation but a char(16) character set octets (not a 'string' but more of an array of 16 bytes). So, to insert/update is another problem.

Fortunatly, most of the problems are resolved and works quite well when defining FUUID as TBytes insread of string. The only downside is that Aurelius do not support TBytes type for ID columns.

Many thanks Wagner for your help !

1 Like