Is it possible to bind a single ? in a parameterized query to a listing of values for an in clause?
(1.1) By Gary (1codedebugger) on 2020-12-22 07:33:32 edited from 1.0 [link] [source]
I'd like to store the prepared statement of the query below (as a _v3 I think) because it'll be run many, many times per user session.
If the number of question marks in the inner most
in set is not known at compile time, is there a way to get the equivalent of a single
? that can be bound to a comma-separated listing of names?
I tried to do so with
sqlite3_bind_text where the char pointer pointed to
'tag_1', 'tag_8', 'tag_11', 'tag_15', for example, and it doesn't error but also doesn't generate any results.
select id, path, name
where id in (
where tag_id in (
where name in ( ?, ?, ? ) )
group by media_id
having count( media_id ) = ? );
(2) By jake on 2020-12-22 09:42:21 in reply to 1.1 [link] [source]
The carray() extension function ("c-array") is used for this.
It can be used directly in the query as a table-valued function:
SELECT * FROM tags WHERE name IN carray(?);
Or inserted into a TEMP table if an index is useful:
CREATE TEMP TABLE list AS SELECT value FROM carray(?); CREATE INDEX list_idx ON list(value); SELECT * FROM tags WHERE name IN list;
Note: These examples use the clearly documented but rarely mentioned SQLite specific functionality:
The right-hand side of an IN or NOT IN operator can be a table name or table-valued function name in which case the right-hand side is understood to be subquery of the form "(SELECT * FROM name)";
(3) By Gary (1codedebugger) on 2020-12-22 19:42:24 in reply to 2 [link] [source]
This is great. Thank you very much for the examples and both links.
(4.2) By Gary (1codedebugger) on 2020-12-23 05:39:43 edited from 4.1 in reply to 2 [source]
May I ask a follow-up question please?
If the original query is changed to:
char sql = "select id, name, path "
"from media "
"where id in ( "
"select media_id "
"from media_tags "
"where tag_id in ( "
"select id "
"from tags "
"where name in carray( ? ) ) "
"group by media_id "
"having count( media_id ) = ? )";
And then attempt to prepare the statement with:
if ( ( rc = sqlite3_prepare_v2( dbEN, sql, -1, &stmt, NULL ) ) != SQLITE_OK )
printf( "Error at prepare rc = %dn", rc );
printf( "errno : %d, strerror : %sn", errno, strerror( errno ) );
The return code
1 but the
0 and the
Am I doing something wrong?
I'm using version 3.34 and the minGW-W64 compiler.
I compiled sqlite3.c as gcc -O2 -c sqlite3.c -DSQLITE_ENABLE_JSON1
carray.c as gcc -O2 -c carray.c
and then my test code as
gcc -O2 sqlite3.o carray.o mediaquery.c -o mediaquery.exe
The carray.h header is included in mediaquery.c.
I'm probably doing something stupid again. Thank you.
(5) By Stephan Beal (stephan) on 2020-12-23 04:45:32 in reply to 4.0 [link] [source]
The return code rc is 1 but the errno is 0 and the strerr(errno) is "No error".
errno is a "historical artifact," not used by any modern libraries except maybe system-level ones. Use sqlite's error-reporting API instead:
(6.6) By Gary (1codedebugger) on 2020-12-23 06:39:52 edited from 6.5 in reply to 5 [link] [source]
Thank you. I copied the wrong error block, not to the post but to my code. I think I got it out of the old Kernighan and Ritchie ANSI C book.
Using the correct ones as you instructed, the
1 and the
"No such table: carray".
That's no surprise since that is the new item.
So, the question now is what needs to be done to have
carray recognized as a table-valued function rather than a table?
Perhaps it must be a .dll and what I did to compile was just wrong. I know just enough to make mistakes regarding compiling.
I think from these SQLite instructions https://www.sqlite.org/loadext.html, I want to statically link because all will still be in one executable file.
(7) By Larry Brasfield (LarryBrasfield) on 2020-12-23 05:41:16 in reply to 6.3 [link] [source]
You can find source for the carray extension here. You can either include it statically into your build or create a loadable extension with that source. Until you do one or the other, 'carray' will probably remain an unknown identifier which, if used to reference a table, will produce errors such as you report.
(8.1) By Gary (1codedebugger) on 2020-12-23 06:37:02 edited from 8.0 in reply to 7 [link] [source]Deleted
(9) By Gary (1codedebugger) on 2020-12-23 22:35:37 in reply to 7 [link] [source]