SQLite Forum

Is it possible to bind a single ? in a parameterized query to a listing of values for an in clause?

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

Thank you.

select id, path, name
from media
where id in (
select media_id
from media_tags
where tag_id in (
select id
from tags
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 [link] [source]

May I ask a follow-up question please?

If the original query is changed to:

char sql[500] = "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 rc is 1 but the errno is 0 and the strerr(errno) is "No error".

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 sqlite3_errcode is 1 and the sqlite3_errmsg is "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]


(9) By Gary (1codedebugger) on 2020-12-23 22:35:37 in reply to 7 [link] [source]

Thank you.