Using SQLite ADO.Net Provider Need to Put GUID Directly Into Query's Where Clause Without Use of a Parameter

Question

Is there anyway to just have the GUID be part of the SQL Query itself without using a parameter?

Let me explain exactly what I am trying to do and why. I am working with an existing application that uses an ADO.Net to create and connect to a SQLite database. The current structure of the database and the method for querying it are horrendous. I am in the midst of a redesign of how it works fundamentally. However, that is not something that can be accomplished quickly. While that redesign is being completed, I have a situation that needs a band-aid solution. As a heads up, the reasoning behind the initial implementation of the code is obscure and appears to have been built by someone who had little database knowledge. Refactoring the entire database to not need this situation is the ultimate solution, but for now I'm just looking to work within the existing structure.

The design of the database relies on GUID's to uniquely identify rows. To perform filtered retrieval of the data the system dynamically builds a command that has an IN clause listing the GUIDs that should be retrieved. Right now the GUIDs are inserted into the query by using a parameter with type GUID, so the query will look like

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

The problem comes in when I need to retrieve a relatively large amount of information. SQLite has a limitation of a 1000 parameters in a single query. If I need to pass in more than a 1000 GUIDs for the retrieval the query will just break. When building the above string it loops over a list of GUID's to insert the question marks and create the paramters. My band-aid fix to the problem was going to be to have the GUID value directly inserted into where the question marks are currently and forego having parameters in the query. After all, its kind of using parameters for a purpose they don't need to be used for.

The problem with this 'solution' is that I can't seem to get the GUID to match the data in the column i.e. the query is always returning null. I understand that GUIDs are not a native type to SQLite and underneath it is actually being represented as a BLOB (yes I am sure we are using the BLOB and not the string representation). Yet, I've been unable to get the query to execute correctly.

I've tried all the following so far:

I've tried calling ToString() on the GUID so the query looks like

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

I've tried calling ToString("N") on the GUID so the query looks like

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

I've tried calling ToString("B") on the GUID so the query looks like

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

I've tried calling ToByteArray() on the GUID and putting the result into the query by adding each byte to the string calling ToString("X") on each byte so the query looks like

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

In reading the 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 so it looks like

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

I get an error saying that "x" is not a recognized symbol.

Is it possible to get the GUID into the query string without the use of the parameter?

More than a thousand parameters to a query??? O!M!G! I find it extremely hard to comprehend what sane query would need to hit that limit. — Donal Fellows, Nov 30 '11 at 20:30
It is not exactly a sane query or sane thinking that lead it to exist. The reason it exists is that this is data generated for an iterative process that can run tens of thousands of times. Each iteration inserts new rows. Users then can limit which iterations to return and do so in blocks. I really don't want it to use the paramter because really it is just an IN statement being constructed on the fly. There are also other complicating factors for all of this. We are in the midst of redsigning to get rid of these problems, however, for now we are kind of stuck with it. — Craig Suchanec, Dec 01 '11 at 17:00

score 3 · Answer 1 · edited May 23 '17 at 12:27

3

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

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;

Make sure to wrap a transaction around all of the INSERT INTO statements. This will help with performance by a ton. Also SQLite allows for temp tables to exist entirely in memory by setting PRAGMA temp_store = MEMORY

If you have a multiple users accessing the table at the same time sharing the same connection, you will need to create some uniqueness with the temp table such as adding a random number to the table name such as tbl_guids_9087.

edited May 23 '17 at 12:27

Community

1
1

answered Nov 30 '11 at 17:38

tidwall

6,881
2
36
47

I thought that SQLite temporary tables were per-connection? – Donal Fellows Nov 30 '11 at 20:33
Temp tables are per connectio. As long as the connections are not pooling or being shared by ADO.net you should be safe not needing the unique random number stuff. – tidwall Nov 30 '11 at 20:49
Interesting thought. Only one user is connecting to the database at a time so uniqnueness is not an issue. Do you have any idea what the performance charactersitics of this would be compared to a straight query? It seems like this could be really slow. I'll have to look into it. – Craig Suchanec Dec 01 '11 at 17:02
Depends on how many rows and the disk speed. As long as you have a `BEGIN TRANSACTION` and `END TRANSACTION` around the inserts you should see very good write performance. To further speed up the write and read, run `PRAGMA temp_store=MEMORY`. – tidwall Dec 01 '11 at 17:59

score 0 · Answer 2 · answered May 30 '12 at 05:22

0

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

answered May 30 '12 at 05:22

oillio

4,748
5
31
37

Using SQLite ADO.Net Provider Need to Put GUID Directly Into Query's Where Clause Without Use of a Parameter

2 Answers2