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

2020-02-15 Thread 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  On
>Behalf Of Simon Slavin
>Sent: Saturday, 15 February, 2020 13:56
>To: SQLite mailing list 
>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  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
>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

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

2020-02-15 Thread Simon Slavin 
On 15 Feb 2020, at 8:12pm, J Decker  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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

2020-02-15 Thread J Decker 
On Fri, Feb 14, 2020 at 7:36 AM Simon Slavin  wrote:

> On 14 Feb 2020, at 2:59pm, Andy KU7T  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
> 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

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

2020-02-15 Thread Barry Smith 
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  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 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

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

2020-02-14 Thread Simon Slavin 
On 14 Feb 2020, at 2:59pm, Andy KU7T  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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

2020-02-14 Thread Andy KU7T 
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 for Windows 10


From: sqlite-users  on behalf of 
Radovan Antloga 
Sent: Friday, February 14, 2020 2:25:01 PM
To: 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 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

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

2020-02-14 Thread Radovan Antloga 

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 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

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

2020-02-14 Thread radovan5 

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 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] Searching by Guid index in table (.NET)? I can write and read, but not search by the Guid

2020-02-14 Thread 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 for Windows 10

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users