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

[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

[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

[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

[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

[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

Re: [sqlite] WITHOUT ROWID tables

[Richard Hipp]

On 2/14/20, Wayne Collins  wrote:
> My first posting so I hope I get the etiquette correct.
>
> 1st question How can I determine from a C-program if a table was
> generated with the "WITHOUT ROWID" option? I have an application where
> it is important to know if a table has a rowid or not.

To determine if table XYZ is a WITHOUT ROWID table, run "PRAGMA
index_info('XYZ');".  If you get back one or more rows, then XYZ is a
WITHOUT ROWID table.  If you get back no rows, then XYZ is a rowid
table.


>
> 2nd Question After a database is defined and the tables created, I have
> an application which automatically generates C++ DAO and DTO objects for
> each table. At run time, I'd like to be certain that the schema and the
> DAO/DTO match. I had thought to use the schema_version pragma but it
> increments when the db is VACUUMED. Is there some hidden cookie or value
> I can use that I have overlooked?
>

There are no magic cookies that change with the schema but not with VACUUM.

Perhaps you could store a strong hash of the sqlite_master.sql column
for each table with your C++ objects, then compare the hash upon
reconnect to see if anything has changed.

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

[sqlite] Prevent receiving "ok" when using the .dump and .output commands

[Iulian Onofrei]

How can I prevent receiving the "ok" added here: 
https://www.sqlite.org/src/info/b3692c406f7ba625
 when I'm using `sqlite -cmd "PRAGMA key " database.sqlite .output`?



iulianOnofrei

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

[sqlite] WITHOUT ROWID tables

[Wayne Collins]

My first posting so I hope I get the etiquette correct.

1st question How can I determine from a C-program if a table was 
generated with the "WITHOUT ROWID" option? I have an application where 
it is important to know if a table has a rowid or not.


2nd Question After a database is defined and the tables created, I have 
an application which automatically generates C++ DAO and DTO objects for 
each table. At run time, I'd like to be certain that the schema and the 
DAO/DTO match. I had thought to use the schema_version pragma but it 
increments when the db is VACUUMED. Is there some hidden cookie or value 
I can use that I have overlooked?


Thanks in advance


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

[sqlite] Test

[Iulian Onofrei]

test


iulianOnofrei

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

Re: [sqlite] multithreaded app with in-memory database and transactions

[Wim Hoekman]

On 13-Feb-20 23:38, Keith Medcalf wrote:


Correct.  "memory" databases can only be shared between connections in the same process, and then 
only by the sharedcache method.  In effect, a "memory" database is nothing more than a cache, and 
sharing it between connections means sharing the cache.  cache=private uses a separate cache for the 
connection and therefore (by definition) a different "memory" database.

You could, of course, use an "on disk" database where the database resides on a memory resident 
filesystem.  On Linux you would simply mount another filesystem using tmpfs and put your "on disk" 
database there.  Delete the "file" off the tmpfs filesystem when you are done with it.  (Or just 
use /tmp if it is mounted using tmpfs which it usually is.  Just beware that everyone has access to /tmp and 
if you mount your own tmpfs at a different location you can control the access permissions).

On Windows the closest thing I know of that can create an autosizing ramdisk is 
http://winramtech.atwebpages.com/RAMDriv/ramdriv.htm though I have not tried it.

For other OS's, Google is your friend!



My application should run on Linux and Windows. Using tmpfs on Linux 
could be doable, but having to setup a ramdisk on Windows would only 
complicate matters...


Are there any plans on supporting multiple connections to in-memory 
databases with separate caches?


If not, how much effort would be involved in getting this to be supported?

Regards,

Wim.

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