CODEWITHSUNDEEP
sqlitenhibernate
user3132295
user3132295

Reputation: 297

how to insert guid into sqlite UNIQUEIDENTIFIER column

I'm trying to insert the following data using manual script that executed using nhibernate session.CreateSQLQuery(string sql)

the string sql is:

INSERT INTO Patient (Id, RemoteId, FirstName, LastName, ChartNumber, LastScanDate, RowStatus, 
SearchName, DateUpdated, IsAllPropertiesSynced) 
SELECT x'7A2AC599D32D03458608E5C614301387', 3072799, 'iRecord', 'Demo', '', '2017-06-04 07:53', 1, 'iRecordDemoiRecord', '2017-07-23 06:34', 1
WHERE NOT EXISTS(SELECT Id FROM Patient WHERE Id = x'7A2AC599D32D03458608E5C614301387');

but after the insert when I execute get using nhibernate I'm getting this guid

99c52a7a-2dd3-4503-8608-e5c614301387

How can I insert a Guid as UNIQUEIDENTIFIER column of sqlite with out the guid changes?

Thank you

Upvotes: 0

Views: 2769

Answers (1)

Oskar Berggren
Oskar Berggren

Reputation: 5629

Sqlite does not really have a UNIQUEIDENTIFIER, but such values can be stored as BLOBs (as you are doing) or in text format.

If you want to store them as a byte array, you need to be aware that the byte storage order may or may not be the same as the string presentation format, depending on platform and/or UUID variant.

On Microsoft it's common that the first three sections are stored little-endian, which means this is what System.Guid expects when it gets the stored bytes from sqlite. (https://en.wikipedia.org/wiki/Universally_unique_identifier#Encoding)

You must be careful to perform the correct swapping when you are inserting GUIDs as byte arrays yourself. Alternatively, consider storing them in the common string format to avoid any byte swapping issues and to make data more easily comprehensible to someone querying the database directly. For this, you should set BinaryGuid=False in your connection string.

Upvotes: 2

Related Questions