Searching by Guid index in table (.NET)? I can write and read, but not search by the Guid

classic Classic list List threaded Threaded
9 messages Options
Reply | Threaded
Open this post in threaded view
|

Searching by Guid index in table (.NET)? I can write and read, but not search by the Guid

Andy KU7T
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Searching by Guid index in table (.NET)? I can write and read, but not search by the Guid

Radovan Antloga-2
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
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Searching by Guid index in table (.NET)? I can write and read, but not search by the Guid

Radovan Antloga
In reply to this post by Andy KU7T
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
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Searching by Guid index in table (.NET)? I can write and read, but not search by the Guid

Andy KU7T
In reply to this post by Radovan Antloga-2
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 <[hidden email]> on behalf of Radovan Antloga <[hidden email]>
Sent: Friday, February 14, 2020 2:25:01 PM
To: [hidden email] <[hidden email]>
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
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Searching by Guid index in table (.NET)? I can write and read, but not search by the Guid

Simon Slavin-3
On 14 Feb 2020, at 2:59pm, Andy KU7T <[hidden email]> wrote:

> Do you suggest me keeping storing TEXT (and declaring the column as TEXT instead of GUID) or storing and declaring BLOB (and remove HEX)?

TEXT.  Makes sorting and searching easier.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Searching by Guid index in table (.NET)? I can write and read, but not search by the Guid

Barry Smith
In reply to this post by Andy KU7T
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 <[hidden email]> 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
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Searching by Guid index in table (.NET)? I can write and read, but not search by the Guid

J Decker
In reply to this post by Simon Slavin-3
On Fri, Feb 14, 2020 at 7:36 AM Simon Slavin <[hidden email]> wrote:

> On 14 Feb 2020, at 2:59pm, Andy KU7T <[hidden email]> wrote:
>
> > Do you suggest me keeping storing TEXT (and declaring the column as TEXT
> instead of GUID) or storing and declaring BLOB (and remove HEX)?
>
> TEXT.  Makes sorting and searching easier.
>

memcmp is harder than strcmp?
and with blob I suppose you don't have 'if UTF8 or UTF16 do
different things' so should compare slightly faster?


> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Searching by Guid index in table (.NET)? I can write and read, but not search by the Guid

Simon Slavin-3
On 15 Feb 2020, at 8:12pm, J Decker <[hidden email]> wrote:

> memcmp is harder than strcmp?
> and with blob I suppose you don't have 'if UTF8 or UTF16 do
> different things' so should compare slightly faster?

It's not cut-and-dried, but you can take things into consideration.

Once data is in a database it usually just sits there.  You're mostly interested in input and output.

Think of the number of times you're going to do conversions, and what formats you're converting from and to.  How much processing is involved in each conversion.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Searching by Guid index in table (.NET)? I can write and read, but not search by the Guid

Keith Medcalf

While that is nice, it is likely completely irrelevant.  The issue appears to be the flamboyant conversion of data being performed by dotSnot (.NET).

The fact that data can be stored in several different formats inside the database, and then converted to what dotSnot calls a "GUID" is all very nice and interesting, but not useful in the least (at least not if you want the database engine to do any work, rather than doing all joining and filtering and ordering in the dotSnot application).

What you need to know is how the thing that dotSnot calls a GUID is presented to the database.  If dotSnot sends a GUID to the database as a 16-byte binary blob, then you better store your GUIDs in the database as 16-byte binary blobs if you expect to be able to find them.  Similarly, if dotSnot sends a GUID to the database as a text string formatted with brockets and dashes and lower case hex digits, then you better store your GUIDs in the database in that format if you ever expect to be able to find them.

In other words, it is irrelevant that the conversion from database to dotSnot will interpret 400 different types of things as a dotSnot GUID.  What is important is how dotSnot sends what it thinks calls a GUID to the database, and that it can do that reversibly.

--
The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume.

>-----Original Message-----
>From: sqlite-users <[hidden email]> On
>Behalf Of Simon Slavin
>Sent: Saturday, 15 February, 2020 13:56
>To: SQLite mailing list <[hidden email]>
>Subject: Re: [sqlite] Searching by Guid index in table (.NET)? I can
>write and read, but not search by the Guid
>
>On 15 Feb 2020, at 8:12pm, J Decker <[hidden email]> wrote:
>
>> memcmp is harder than strcmp?
>> and with blob I suppose you don't have 'if UTF8 or UTF16 do
>> different things' so should compare slightly faster?
>
>It's not cut-and-dried, but you can take things into consideration.
>
>Once data is in a database it usually just sits there.  You're mostly
>interested in input and output.
>
>Think of the number of times you're going to do conversions, and what
>formats you're converting from and to.  How much processing is involved
>in each conversion.
>_______________________________________________
>sqlite-users mailing list
>[hidden email]
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users