Using Guid in SQLite Select Where Guid Is Stored in the SQLite Db as Binaries

Using guid in sqlite select where guid is stored in the sqlite db as binaries

The GUID is probably being stored as a binary blob; try:

SELECT * FROM Employee
WHERE Employee.Id = X'a8828ddfef224d36935a1c66ae86ebb3';

SQLite Select Where id in GUID

Ok I figured it out.

There is no such thing as a Guid in Sqlite so it is stored as a blob this means when you are trying to query for a given Guid you need to convert your guid to a Hexidecimal string representation of the Guid as a Byte array

To do this in C# I have the code:

query = string.Format("Select * from [TableName] where ImageId in (x'{0}')", string.Join("',x'", ids.Select(x => SqliteDatabaseExtensions.ByteArrayToString(x.ToByteArray())));

where ids is a List<Guid>

The important bit in this is:

SqliteDatabaseExtensions.ByteArrayToString(new Guid(x.ToString()).ToByteArray())

Where the static method is:

private static string ByteArrayToString(byte[] ba)
{
    StringBuilder hex = new StringBuilder(ba.Length * 2);
    foreach (byte b in ba)
        hex.AppendFormat("{0:x2}", b);
    return hex.ToString();
}

Proper way to store GUID in sqlite

One can essentially add support for GUID datatypes to sqlite3 in Python. You can register conversion functions:

• register_converter(): convert SQLite types to Python types
• register_adapter(): convert Python types to SQLite types

To use these, you need to pass an argument for the detect_types parameter of connect().

Example:

import sqlite3
import uuid

sqlite3.register_converter('GUID', lambda b: uuid.UUID(bytes_le=b))
sqlite3.register_adapter(uuid.UUID, lambda u: buffer(u.bytes_le))

conn = sqlite3.connect('test.db', detect_types=sqlite3.PARSE_DECLTYPES)

c = conn.cursor()
c.execute('CREATE TABLE test (guid GUID PRIMARY KEY, name TEXT)')

data = (uuid.uuid4(), 'foo')
print 'Input Data:', data
c.execute('INSERT INTO test VALUES (?,?)', data)

c.execute('SELECT * FROM test')
print 'Result Data:', c.fetchone()

Output:

Input Data: (UUID('59cc2646-8666-4fb3-9f57-fe76e22603c0'), 'foo')
Result Data: (UUID('59cc2646-8666-4fb3-9f57-fe76e22603c0'), u'foo')

Results:

• I am passing uuid.UUID objects directly to execute(). The adapter lambda u: buffer(u.bytes) tells sqlite3 how to convert those to a buffer (which translates to an X'ABCD....' blob in SQLite.
• fectchone() is returning uuid.UUID objects directly. The converter lambda u: buffer(u.bytes) tells sqlite3 how to create those from a byte array when it encounters a declared type of GUID.
• These GUIDs are being stored as 16-byte binary blobs, in little-endian order.
• I can successfully open/edit the databases using SQLite Expert (in its default configuration).

Read Guid stored as binary from Sqlite database

Have you tried using SqlDataReader.GetGuid

You don't need to convert to string and back again to a Guid

Sqlite Subsonic C#: Guid is saving as Guid with SQL, but with strange characters when using code

It looks like I will need to change IDs to strings instead of Guids.

I already seem to have to do this for bools, but doing this just to wrap IDs would be pointless:

public int LoginEnabledPropertyValue { get; set; }

[SubSonicIgnore]
public bool LoginEnabled
{
     get
     {
          return (LoginEnabledPropertyValue > 0 ? true : false);
     }
     set
     {
          LoginEnabledPropertyValue = (value ? 1 : 0);
     }
 }

Is it possible to Convert a Sqlite Blob column to a HEX string (GUID)?

Using information from this question: Sqlite: How to cast(data as TEXT) for BLOB
and this question: Convert varchar to uniqueidentifier in SQL Server

I got the answer:

SELECT substr(hex(BlobGuidColumn), 1, 8) || '-' || substr(hex(BlobGuidColumn), 9, 4) || '-' || substr(hex(BlobGuidColumn), 13, 4) || '-' || substr(hex(BlobGuidColumn), 17, 4) || '-' || substr(hex(BlobGuidColumn), 21, 12) FROM [MyTable]

Using UUIDs in SQLite

SQLite allows to use any data type as primary key.

UUIDs can be stored either as strings (which are human-readable) or as 16-byte BLOBs (which might be faster if the records are so small that the difference matters).

How do I delete a row from Sqlite using ID where my ID is binary data (GUID)

There are two way of using binary data:

• use a blob literal:

  DELETE FROM Customers WHERE CustomerId = x'1234AB...'
  

• use parameters:

    guid = ...
    sql = "DELETE FROM Customers WHERE CustomerId = ?"
    db.execute(sql, [guid])

---

Related Topics