Geek Answers Handbook

Using SQLite ADO.Net Provider you must immediately insert the GUID in the query, where the item is without using the parameter

Is there anyway that the GUID is part of the SQL query itself without using a parameter?

Let me explain what exactly I'm trying to do and why. I am working with an existing application that uses ADO.Net to create and connect to an SQLite database. The current database structure and method for querying it is horrific. I am in the middle of redesigning how it works fundamentally. However, this is not something that can be quickly completed. While this redesign is ending, I am faced with a situation requiring a solution for group assistance. As heads-up, the reasoning behind the initial implementation of the code is obscure and was apparently created by someone who had little database knowledge. Refactoring an entire database that does not need this situation is the final solution, but at the moment I'm just looking for work within the existing structure.

The database design is based on a GUID to uniquely identify strings. To perform filtered data retrieval, the system dynamically creates a command that has an IN clause, listing the GUIDs to be received. Currently, GUIDs are inserted into the request using a parameter with the type GUID, so the request will look like

SELECT * FROM data_table WHERE guid_col IN( ?, ?, ?, ?)

The problem arises when I need to get a relatively large amount of information. SQLite has a limit of 1000 parameters in a single query. If I need to pass over 1000 GUIDs to search, the request will simply break. When building the above line, it iterates over the GUID list to insert question marks and create parameters. My group guide fixes the problem of having a GUID value directly pasted where the question marks currently have no parameters in the query. In the end, its use of parameters for use for which they do not need to be used.

The problem with this “solution” is that I cannot get the GUID to match the data in the column, that is, the query always returns null. I understand that GUIDs are not a native SQLite type, and underneath it actually displays as BLOBs (yes, I'm sure we are using BLOBs and not a string representation). However, I could not execute the request correctly.

I tried all of the following:

I tried calling ToString () in a GUID so that the request looked like

 SELECT * FROM data_table WHERE guid_col IN ( 'b5080d4e-37c3-4286-9c3a-413e8c367f36', 'aa0ff789-3ce9-4552-9840-5ed4d73c1e2c')

I tried calling ToString ("N") in the GUID so that the request looked like

 SELECT * FROM data_table WHERE guid_col IN ( 'b5080d4e37c342869c3a413e8c367f36', 'aa0ff7893ce9455298405ed4d73c1e2c')

I tried calling ToString ("B") in the GUID so that the request looked like

 SELECT * FROM data_table WHERE guid_col IN ( '{b5080d4e-37c3-4286-9c3a-413e8c367f36}', '{aa0ff789-3ce9-4552-9840-5ed4d73c1e2c}')

I tried calling ToByteArray () in the GUID and putting the result in the request by adding each byte to the line calling ToString ("X") in each byte, so the request looks like

SELECT * FROM data_table WHERE guid_col IN ('4ED8B5C33786429C3A413E8C367F36', '89F7FAAE93C524598405ED4D73C1E2C')

When reading SQLite documentation, I read the following “BLOB literals are string literals containing hexadecimal data and preceded by a single“ x ”or“ X ”character. If I try to apply this to my query, it looks like

 SELECT * FROM data_table WHERE guid_col IN ( x'4ED8B5C33786429C3A413E8C367F36', x'89F7FAAE93C524598405ED4D73C1E2C')

I get a message that "x" is not a recognized character.

Is it possible to get a GUID in a query string without using a parameter?

+4
source share
2 answers

I suggest you use a temporary table for something like this. For instance...

 PRAGMA temp_store = MEMORY; CREATE TEMP TABLE tbl_guids (guid_col text); INSERT INTO tbl_guids VALUES ('b5080d4e-37c3-4286-9c3a-413e8c367f36'); INSERT INTO tbl_guids VALUES ('aa0ff789-3ce9-4552-9840-5ed4d73c1e2c'); ... more inserts ... SELECT * FROM data_table WHERE guid_col IN ( SELECT guid_col FROM tbl_guids); DROP TABLE tbl_guids;

Be sure to wrap the transaction around all INSERT INTO . This will help in productivity per ton. In addition, SQLite allows temp tables to fully exist in memory by setting PRAGMA temp_store = MEMORY

If you have several users who access the table at the same time, sharing the same connection, you will need to create some uniqueness in the temp table, for example, add a random number to the table name, for example tbl_guids_9087 .

+3
source

You can pass the GUID as strings if you set "BinaryGuid = False" in the connection string. If this is done, "ToString ()" should work fine.

0
source

More articles:


All Articles