Firebird guids (char(16) character set OCTETS) not recognised

Data Abstract
1

Hi there,

My guids in Firebird are with the format : MYGUID = CHAR(16) CHARACTER SET OCTETS; It’s the recommended GUID format for Interbase.

In table PERSON I have a field ID MYGUID.

This field is recognized as a BLOB by data abstract, and if I manually assign the GUID type to the field, I have errors saying this is a bad GUID.

FireDAC normally recognizes this. It detects the field as a TBytesField, and if the assigned domain name has the substring GUID in it (when the connection fetches ExtendedMetadata), it casts the TBytesField to TGUIDField. Then, when using that field in a table, FireDAC automatically sets the display text to human readable GUIDs instead of bytes.

I can’t store the GUIDs as plain string as it has bad impacts on indexing and other performance reasons.

Could you plan on supporting bytes guids ?

Thank you

Edit

Also, I can’t even use the blob ids. When I do so, i get an error

Exception ‘first chance’ à $764E5B68. Classe d’exception EDatabaseError avec un message ‘Le champ ‘PERSON_ID’ ne peut être un champ calculé ni un champ référence’. Processus EntiteREM2.exe (6504)

(The field 'PERSON_ID' cannot be a calculated field or a reference field)

:764e5b68 KERNELBASE.RaiseException + 0x48
Data.DB.DatabaseError('Le champ ''PERSON_ID'' ne peut être un champ calculé ni un champ référence',???)
Data.DB.DatabaseErrorFmt('Le champ ''%s'' ne peut être un champ calculé ni un champ référence',(...),nil)
uDAMemDataset.TDAMemoryDataset.ValidateFieldForIndex($2B40AD0)
uDAMemDataset.TDAMemIndex.Init('PERSON_ID','','')
uDAMemDataset.TDAMemIndex.Init('PERSON_ID',False,False)
uDAMemDataset.TDAMemoryDataset.intDirectSearch('PERSON_ID','âγFú'#$009D'žJ†'#7'ñËÊêÝ{',[],True,$4C414A0)
uDAMemDataset.TDAMemoryDataset.LocateRecord('PERSON_ID','âγFú'#$009D'žJ†'#7'ñËÊêÝ{',[],True,$4C414A0)
uDAMemDataset.TDAMemoryDataset.Locate('PERSON_ID','âγFú'#$009D'žJ†'#7'ñËÊêÝ{',[])
uDADataTable.TDADataTable.Locate('PERSON_ID',Variant array of Variant,[])
uDADataTable.TDADataTable.MergeDelta
uDADataAdapter.TDABaseDataAdapter.InternalApplyUpdates_ThrowFailures($4BF0798)
uDADataAdapter.TDABaseDataAdapter.InternalApplyUpdatesAndThrowFailures($4BF0768,$4BF07B0)
uDADataAdapter.TDABaseDataAdapter.ApplyUpdates(???,False)
uDADataTable.TDADataTable.InternalApplyUpdates(True)
uDADataTable.TDADataTable.ApplyUpdates(False,True)
fClientForm.TClientForm.ActionApplyUpdatesExecute($2BFEE50)
System.Classes.TBasicAction.Execute
Vcl.ActnList.TCustomAction.Execute
System.Classes.TBasicActionLink.Execute(???)
dxBar.TdxBarItem.DoClick
dxBar.TdxBarItem.DirectClick
dxBar.TdxBarItemControl.ControlUnclick(True)
dxBar.TdxBarButtonControl.ControlUnclick(True)
dxBar.TCustomdxBarControl.DoLButtonUp((514, (), 0, (), 83, 4, (), (83, 4), (), 0))
dxBar.TdxBarControl.DoLButtonUp((514, (), 0, (), 83, 4, (), (83, 4), (), 0))
dxBar.TCustomdxBarControl.WMLButtonUp((514, (), 0, (), 83, 4, (), (83, 4), (), 0))
dxBar.TdxBarDockedControl.WMLButtonUp((514, (), 0, (), 83, 4, (), (83, 4), (), 0))
Vcl.Controls.TControl.WndProc((514, 0, 262227, 0, 0, 0, (), 83, 4, (), 0, 0, ()))
Vcl.Controls.TWinControl.WndProc((514, 0, 262227, 0, 0, 0, (), 83, 4, (), 0, 0, ()))
dxBar.TCustomdxBarControl.WndProc((514, 0, 262227, 0, 0, 0, (), 83, 4, (), 0, 0, ()))
dxBar.TdxBarControl.WndProc((514, 0, 262227, 0, 0, 0, (), 83, 4, (), 0, 0, ()))
Vcl.Controls.TWinControl.MainWndProc(???)
System.Classes.StdWndProc(2690036,514,0,262227)
:75e38e71 user32.CallNextHookEx + 0xb1
:75e390d1 ; C:\Windows\SysWOW64\user32.dll
:75e3a66f ; C:\Windows\SysWOW64\user32.dll
:75e3a6e0 user32.DispatchMessageW + 0x10
Vcl.Forms.TApplication.ProcessMessage(???)
:006269e8 TApplication.ProcessMessage + $F8

EDIT 1

I got it working by changing the field definition in SchemaModeler from BLOB to FixedChar(16). It would still be nice to not have to do this manually, as everytime I refresh the field defs in Schema Modeler, I have to manually change again the BLOB to FixedChar.

Also, could it still be possible to map this kind of data to a standard TGuid data type? Because now when I add a new record, I have to run this for each table (OnNewRecord) :

procedure TClientDataModule.NewRecordGuid(DataTable: TDADataTable);
var
  aCollectionItem: TCollectionItem;
  aField: TDAField;
begin
  for aCollectionItem in DataTable.Fields do
    if aCollectionItem is TDAField then
    begin
      aField := TDAField(aCollectionItem);
      if (aField.InPrimaryKey) and (aField.DataType = datFixedChar) and (aField.Size = 16) then
        aField.AsString := GetGuidString;
    end;
end;

And this for GetText of every guid field

procedure TClientDataModule.GuidGetText(Sender: TDACustomField; var Text: string; DisplayText: Boolean);
begin
  Text := GUIDToString(TGuid.Create(TEncoding.Default.GetBytes(Sender.Value)));
end;
2

Have you tried to specify datGuid type for such fields in DASM?
if FireDAC detects it as TGUIDField, it may work as expected

3

I did try but it did not work. Returned some “Unexpected Format” exception

4

looks like, FireDAC recognizes MYGUID = CHAR(16) CHARACTER SET OCTETS as TBytesField.
it could be a reason for such failure

5

I can use the standard TBytesField without problem with Guids (with standard IBObjects / FireDac datasets). But I think DA does not support BytesField

6

I have tested current solution:

and it works as expected.

this adds GUID into blob

var
  b: AnsiString;
begin
  SetString(b,PAnsichar(@NewGuid),16);
  ServerDataModule.tbl_GUIDTEST.AddRecord(['NEW_FIELD'],[b]);

this gets GUID from blob and shows it as calc field:

procedure TServerDataModule.tbl_GUIDTESTCalcFields(DataTable: TDADataTable);
begin
  DataTable.FieldByName('Item2').AsGuid := PGuid(PAnsichar(DataTable.FieldByName('NEW_FIELD').AsAnsiString))^;
end;

I’ve used

CREATE DOMAIN MYGUID AS
CHAR(16) CHARACTER SET OCTETS
COLLATE OCTETS;

CREATE TABLE GUIDTEST (
    NEW_FIELD  MYGUID /* MYGUID = CHAR(16) */
);
1 Like