Regardless of whether you decide to store GUIDs as text or binary, things will be easier if you set your connection string appropriately. BinaryGUID is the parameter you want to change.
See https://www.connectionstrings.com/sqlite-net-provider/store-guid-as-text/ For performance binary is better than text representation. The text UID requires twice as many bytes to store, twice as much in cache. If the UID is a significant portion of your row, this could be significant. If you’re running the latest version of SQLite you can make a computed column to get the text representation for manual/CLI access if you need it. Also ‘randomblob(16)’ will not give you a real GUID. It’d work for most purposes but why not call it something else, at least that’d give you a red flag to perhaps expect problems when you pass it to a library that expects a real GUID and might check the format bits. > On 14 Feb 2020, at 6:09 am, Andy KU7T <k...@ku7t.org> wrote: > > Hi, > > I am having trouble searching for a Guid. I think some conversion is missing. > I wonder if someone can point me to the error… > > Column: > [ID] GUID(16) > Index: > CREATE UNIQUE INDEX [ID_INDEX] ON [DXLOG]([ID] COLLATE [BINARY] ASC); > Trigger: > CREATE TRIGGER [AUTOGENERATE_ID] AFTER INSERT ON [DXLOG] FOR EACH ROW WHEN > ([NEW].[ID] IS NULL) > BEGIN > UPDATE > [DXLOG] > SET > [ID] = HEX (RANDOMBLOB (16)) > WHERE > [NEW].[ID] IS NULL AND [DXLOG].[ROWID] = [NEW].[ROWID]; > END; > > Inserting a row simply will create a new guid for me. Good. Reading it like > this: > > SELECT ID FROM [Dxlog] WHERE RowId = @RowID > > Where @RowId is the LastInsertedRowId. > And getting it in code: > > Dim rdr2 As SQLiteDataReader = Nothing > rdr2 = theDB.ExecuteQuery("SELECT ID FROM [Dxlog] WHERE RowId = @RowID", > sqlParameters) > rdr2.Read() > Me.mvarId = rdr2.Item("ID") > > The returned ID is indeed a Guid. In SQLite it looks like this: > > 40FD6722384053ED3BA45CD1C5FDB30D > And in .NET: {40fd6722-3840-53ed-3ba4-5cd1c5fdb30d} > > Now, when I am trying to search for it like this, I get nothing: > > sqlParameters = New List(Of SQLiteParameter)() > AddSqlParam(sqlParameters, "@ID", DbType.Guid, Me.Id) > > rdr2 = theDB.ExecuteQuery("SELECT * FROM [Dxlog] WHERE ID > = @ID", sqlParameters) > rdr2.Read() > > > Do I have to converte the Guid to binary somehow? > > Thanks > Andy > > Sent from Mail<https://go.microsoft.com/fwlink/?LinkId=550986> for Windows 10 > > _______________________________________________ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
