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
