10

I have a table Employee in the SQLite database. Whose primary key is "ID" of GUID type. I try to find the record with id = guid 'a8828ddf-ef22-4d36-935a-1c66ae86ebb3' with the following query with no luck:

SELECT * FROM Employee
WHERE Employee.Id = 'a8828ddf-ef22-4d36-935a-1c66ae86ebb3'

Can anyone tell me how should I write the query?

Thanks,

sean717
  • 11,759
  • 20
  • 66
  • 90
  • how do you know that is the right ID? Can you just `select * from Employee` or can you `select * from employee where employee.id like '%8828%'` (may return multiple records) to check the value returned – Michael Durrant Oct 19 '11 at 01:27
  • I know there is a row with such ID exist in the database by showing the whole table in sqlite server explorer in VS. – sean717 Oct 19 '11 at 01:30
  • Maybe VS do some changing to view it nicely (like adding hyphens). Try selecting without them. Try using other UI to the SQLite. – Yaakov Shoham Oct 19 '11 at 01:41
  • Thanks, but in general, what's the syntax look like for querying Guid using sql? – sean717 Oct 19 '11 at 01:43
  • I don't think there is "general" way. It's implementation depend. In SQLite there is no hard typing of fields - you can read about it here http://www.sqlite.org/datatype3.html . So it's depends on the way you added it in; it's just a string (or binary data; depends on who you inserted it). Other DBs (MySQL, MS SQL Server, Oracle, ...) may have specific type for GUID, and some specific syntax to specify it. – Yaakov Shoham Oct 19 '11 at 01:48
  • Don't forget the terminal semi-colon. Do you get anything back at all? How about `Id LIKE 'a882%'`? – Larry Lustig Oct 19 '11 at 02:07

4 Answers4

15

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

SELECT * FROM Employee
WHERE Employee.Id = X'a8828ddfef224d36935a1c66ae86ebb3';
Doug Currie
  • 40,708
  • 1
  • 95
  • 119
  • 1
    Yes, I found that the GUID is indeed stored as a binary. But the syntax you gave still not working, thanks. – sean717 Oct 19 '11 at 17:40
  • Try: select hex(Id) from Employee limit 10; – Doug Currie Oct 19 '11 at 18:09
  • sean717. have you found a solution? – andySF Dec 07 '11 at 11:13
  • 3
    Seams that the solution is here http://stackoverflow.com/questions/1039461/how-to-display-blob-value-using-xabc-binary-string-literal-syntax – andySF Dec 07 '11 at 11:24
  • For anyone who is interested. The SQLite Manager Add-on for Firefox is a useful alternative to the Server Explorer in the Visual Studio. – sean717 Jan 30 '13 at 18:19
  • Btw, This seems to work with the Entity Framwork if you set the SQLite column type afinity to uniqueidentifier. EF picks up that keyword and will treat it as a GUID. And if you open the file up in SQLiteStudio, you can query the uniqueidentifier as per this answer. – John May 26 '17 at 18:54
14

This is a old question, but comes up with searches and while the selected answer is correct, it might not work for everyone easily. For example when using .NET bindings to SQLite.

The reason is that when the GUID is in format a8828ddf-ef22-4d36-935a-1c66ae86ebb3 you cannot just remove the dashes and put it to X'...'. The GUID is in several parts and the database is storing the binary differently.

When the GUID is a8828ddf-ef22-4d36-935a-1c66ae86ebb3 it will be stored in little endian as X'df8d82a822ef364d5a93b3eb86ae661c'. Note that all parts are reversed within the string.

Of course if you insert the data directly by removing the dashes as X'a8828ddfef224d36935a1c66ae86ebb3' you can also retrieve it with that, but if you let .NET to handle the conversion, GUID will be stored as little endian byte representations.

(This all assumes little endian architecture, haven't tested on big endian)

Sami Kuhmonen
  • 30,146
  • 9
  • 61
  • 74
2

The answer of Sami pointed me in the right direction. .NET outputs a Guid in a different way, so if you store it by using .NET and than use rawsql to fetch the data, then the guid in the rawsql need to be transformed in the same way. Howerver, in my case it was not the little endian notation. With the following code you can find the correct string:

var byteArray = new Guid("a8828ddf-ef22-4d36-935a-1c66ae86ebb3").ToByteArray();
string hex = BitConverter.ToString(byteArray).Replace("-", string.Empty);

On my computer, this returns: "DF8D82A822EF364D935A1C66AE86EBB3" Hence my raw sql will have to be

"select * from [tablename] where ID = X'DF8D82A822EF364D935A1C66AE86EBB3'"

Why I needed this; because I use an in-memory SQLite DB that I populate using EF Core, where I want to test a rawSql command. This might be handy for others in the same situation. However, keep in mind that a rawSql that works in SQLite might not work on another DB (and vice versa).

Sam
  • 81
  • 4
2

Sqlite can also be configured to store GUIDs as text. This is done in the connection string.

Data Source=c:\mydb.db;Version=3;BinaryGUID=False;

See https://www.connectionstrings.com/sqlite/

CasualCoder
  • 41
  • 3