I have been having intermittent difficulties with GUID fields in
Interbase. I have finally been able to determine that I can reliably
recreate the FireDAC exception by making sure that the low order byte of
the GUID posted to the database has the value X'00'. Likewise, if I
ensure that the low order byte does not have this value the FireDAC
exception never appears.
This looks suspiciously like something is counting a length by using
null-terminated string logic. A GUID is a 128 bit number and any value
from all X'00' through all X'FF' is valid, so null-terminated logic
can't be applied.
Using Delphi XE8.1 and Interbase Developer XE7 64-Bit.
The full text of the error message I'm receiving is:
[FireDAC][DatS]-10. Fixed length column [PRIMARYKEY] data length
mismatch. Value length - [15], column fixed length - [16].
Also note that FieldByName('name').ASGUID is a compiler error. This is a
minor irritation and the workaround is
TGUIDField(FieldByName('name')).ASGUID
or
FieldBYyName('name').ASString := GUIDToString(SomeTGUID)
but this last workaround is ugly.
This is a show-stopper for me and prevents me from completing a project.
See the end of this message for a proposed work-around.
The test database metadata is:
SET SQL DIALECT 3;
/* CREATE DATABASE '127.0.0.1/gds_db:GUID Test' USER 'SYSDBA' PASSWORD
'Enter Password here' PAGE_SIZE 4096
DEFAULT CHARACTER SET UTF8 */
COMMIT;
/*CONNECT '127.0.0.1/gds_db:GUID Test' USER 'SYSDSO' PASSWORD 'Enter
Password here'*/
/* ALTER DATABASE SET SYSTEM ENCRYPTION PASSWORD 'Enter Password here';*/
COMMIT;
/* CONNECT '127.0.0.1/gds_db:GUID Test' USER 'SYSDBA' PASSWORD 'Enter
Password here'*/
/* Domain definitions */
CREATE DOMAIN "ID_GUID" AS CHAR(16) CHARACTER SET OCTETS NOT NULL;
/* Table: GUIDTABLE, Owner: SYSDBA */
CREATE TABLE "GUIDTABLE"
(
"PRIMARYKEY" "ID_GUID" NOT NULL,
"INTEGERFIELD" INTEGER NOT NULL,
"VARCHARFIELD" VARCHAR(32) NOT NULL,
PRIMARY KEY ("PRIMARYKEY")
);
I have a small VCL application that allows the testing of various ways
of creating table rows, both with a GUID that has a trailing X'00' and
without.
The info tab of the FireDAC Connection Editor shows:
================================
Connection definition parameters
================================
Database=D:\Databases\Interbase\GUIDTEST.IB
User_Name=SYSDBA
Password=*****
CharacterSet=UTF8
ExtendedMetadata=True
DriverID=IB
Pooled=False
================================
FireDAC info
================================
Tool = RAD Studio XE8
FireDAC = 12.0.1 (Build 78853)
Platform = Windows 32 bit
Defines = FireDAC_NOLOCALE_META;FireDAC_MONITOR
================================
Client info
================================
Loading driver IB ...
Brand = InterBase
Client version = 1200029900
Client DLL name = C:\Windows\system32\gds32.dll
================================
Session info
================================
Current catalog =
Current schema =
Server version = WI-V12.0.2.317
The application code is:
unit Unit1;
interface
uses
Winapi.Windows, Winapi.Messages, System.SysUtils, System.Variants,
System.Classes, Vcl.Graphics,
Vcl.Controls, Vcl.Forms, Vcl.Dialogs, FireDAC.Stan.Intf,
FireDAC.Stan.Option,
FireDAC.Stan.Error, FireDAC.UI.Intf, FireDAC.Phys.Intf, FireDAC.Stan.Def,
FireDAC.Stan.Pool, FireDAC.Stan.Async, FireDAC.Phys, FireDAC.Phys.IB,
FireDAC.Phys.IBDef, FireDAC.Stan.Param, FireDAC.DatS, FireDAC.DApt.Intf,
FireDAC.DApt, System.Actions, Vcl.ActnList, Vcl.StdCtrls, Data.DB,
FireDAC.Comp.DataSet, FireDAC.Comp.Client, FireDAC.VCLUI.Wait,
FireDAC.Comp.UI,
Vcl.ExtCtrls, Vcl.DBCtrls, Vcl.Grids, Vcl.DBGrids;
type
TForm1 = class(TForm)
GuidtestConnection: TFDConnection;
GuidtableTable: TFDQuery;
Button1: TButton;
Button2: TButton;
Button3: TButton;
ActionList1: TActionList;
aClear: TAction;
aPopulate00: TAction;
aRead: TAction;
FDGUIxWaitCursor1: TFDGUIxWaitCursor;
Button4: TButton;
aPopulate01: TAction;
DataSource1: TDataSource;
DBGrid1: TDBGrid;
DBNavigator1: TDBNavigator;
aDisconnect: TAction;
Button5: TButton;
procedure aPopulate00Execute(Sender: TObject);
procedure aPopulate01Execute(Sender: TObject);
procedure aReadExecute(Sender: TObject);
procedure aDisconnectExecute(Sender: TObject);
procedure aClearExecute(Sender: TObject);
private
{ Private declarations }
public
{ Public declarations }
end;
var
Form1: TForm1;
implementation
{$R *.dfm}
procedure TForm1.aClearExecute(Sender: TObject);
begin
GuidtableTable.Active := True;
GuidtestConnection.ExecSQL('delete from GUIDTABLE');
end;
procedure TForm1.aDisconnectExecute(Sender: TObject);
begin
GuidtableTable.Active := False;
GuidtestConnection.Connected := False;
end;
procedure TForm1.aPopulate00Execute(Sender: TObject);
var
LNewGUID: TGUID;
begin
LNewGUID := TGUID.NewGuid;
LNewGUID.D4[7] := $00;
GuidtableTable.Active := True;
GuidtableTable.Insert;
TGuidField(GuidtableTable.FieldByName('PRIMARYKEY')).AsGuid := LNewGUID;
// GuidtableTable.FieldByName('PRIMARYKEY').AsString :=
GUIDToString(LNewGUID);
GuidtableTable.FieldByName('INTEGERFIELD').AsInteger := 42;
GuidtableTable.FieldByName('VARCHARFIELD').AsString := 'Delphi';
GuidtableTable.Post;
end;
procedure TForm1.aPopulate01Execute(Sender: TObject);
var
LNewGUID: TGUID;
begin
LNewGUID := TGUID.NewGuid;
LNewGUID.D4[7] := $01;
GuidtableTable.Active := True;
GuidtableTable.Insert;
TGuidField(GuidtableTable.FieldByName('PRIMARYKEY')).AsGuid := LNewGUID;
// GuidtableTable.FieldByName('PRIMARYKEY').AsString :=
GUIDToString(LNewGUID);
GuidtableTable.FieldByName('INTEGERFIELD').AsInteger := 42;
GuidtableTable.FieldByName('VARCHARFIELD').AsString := 'Delphi';
GuidtableTable.Post;
end;
procedure TForm1.aReadExecute(Sender: TObject);
begin
GuidtestConnection.Connected := True;
GuidtableTable.Active := True;
end;
end.
Workaround:
The most reasonable way I can think of to work around the FireDAC
Exception is to check each GUID before I post it to the database. If the
GUID has a trailing X'00', change it to something else, like X'01'. This
works if you're the one creating the GUIDs, but not if you're getting
them from some external source.
Can someone either tell me what I'm doing wrong or how to fix this?
Thanks in advance,
--
Milan Vydareny