FireDAC+SQLite

[Ruslan 5]

Never worked with SQLite before and now for my project need to use it but some questions and misunderstandings came on application model (VCL).

1. I used to use GUID data type for unique records, in SQLite table the field 'uuid' is declared as binnary 'guid', and I created a trigger to generate the value, in application in FDConnection.Params.GUIDFormat=guiBinary, but when try to execute a clean FDQuery a message shows up:

   [FireDAC][DatS]-10. Fixed length column [uuid] data length mismatch. Value length - [32], column fixed length - [16].

   changing FDConnection.Params.GUIDFormat=guiString show up a 'Wrong guid format error'.
   and here is the trigger for guid generation:

   CREATE TRIGGER demotable_after_insert AFTER INSERT ON demotable 
   FOR EACH ROW
   WHEN (NEW.uuid IS NULL)
   BEGIN
   	UPDATE demotable SET uuid = lower(hex(randomblob(16))) WHERE id = NEW.id;
   END

   So the question is how to deal with guids in Delphi+SQLite? Should I generate them from application? Do I need to make some mappings?

2. The second question is about using generators for auto incremented fields, is this possible?

[corneliusdavid 142]

I would save the GUIDs as TEXT fields, not binary. They could even be stored as numbers in the TEXT field as SQLite is very loose with it's storage compared with Delphi. Read about them here: https://www.sqlite.org/datatype3.html

 

There are no generators in SQLite. You're probably very used to using them in Firebird or InterBase. Instead, you might consider the AUTOINCREMENT feature of the INTEGER type but there are some considerations to using that as well.