Thanks for the response. Do you suggest me keeping storing TEXT (and declaring the column as TEXT instead of GUID) or storing and declaring BLOB (and remove HEX)?
I think both cases would work, but for performance, does it matter? AS long as my index is binary collated, it probably does not, or does it? Andy Sent from Mail<https://go.microsoft.com/fwlink/?LinkId=550986> for Windows 10 ________________________________ From: sqlite-users <sqlite-users-boun...@mailinglists.sqlite.org> on behalf of Radovan Antloga <radovan.antl...@arsistemi.si> Sent: Friday, February 14, 2020 2:25:01 PM To: sqlite-users@mailinglists.sqlite.org <sqlite-users@mailinglists.sqlite.org> Subject: Re: [sqlite] Searching by Guid index in table (.NET)? I can write and read, but not search by the Guid In SQLite type guid does not exist. Look at this documentation: https://www.sqlite.org/datatype3.html Affinity for guid would be BLOB but in trigger you store TEXT. I do not use .NET but I think you should use different parameter type. I would use TEXT type. Regards R.A. On 14.02.2020 15:09, Andy KU7T 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 _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
