SQLite Forum

sqlite3_expanded_sql for carray with single bound parameter
Login

sqlite3_expanded_sql for carray with single bound parameter

(1) By Gary (1codedebugger) on 2020-12-26 01:24:57 [source]

When using parameterized queries in the C API including in carray(?) and test code as

char *tag_list[] = { "name_1","name_2","name_3","name_4","name_5","name_6","name_7","name_8","name_9","name_10" };
rc = sqlite3_carray_bind( stmt, 1, tag_list, 10, CARRAY_TEXT, NULL );
char *exp = sqlite3_expanded_sql( stmt );
printf( "%s\n", exp );
sqlite3_free( exp );

The result of the query appears correct and matches the result run from the command line but the output of printf( "%s", exp ); displays carray(NULL) for carray(?) while the rest of the query and bound parameters appear accurately.

Is this expected or indicative of an error in my code?

Thank you.

The SQL is the same query I've been boring everyone with the past several days.
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 ) = ? )

(2) By jake on 2020-12-26 01:50:28 in reply to 1 [link] [source]

This is as expected. Although not explicity documented for sqlite3_carray_bind, it is essentially a convenience version of sqlite3_bind_pointer:

The sqlite3_bind_pointer(S,I,P,T,D) routine causes the I-th parameter in prepared statement S to have an SQL value of NULL, but to also be associated with the pointer P of type T.

See also:

https://www.sqlite.org/bindptr.html

(4) By Gary (1codedebugger) on 2020-12-26 18:18:51 in reply to 2 [link] [source]

Thank you. If I wasn't so dense at times the fact that there isn't a sqlite3_bind_carray should've told me something.

(3.1) By Keith Medcalf (kmedcalf) on 2020-12-26 02:13:26 edited from 3.0 in reply to 1 [link] [source]

This is correct. That is because the binding created by sqlite3_carray_bind is a pointer to a structure containing the data you passed to it, with a pointer type of "carray-bind".

The generic sqlite3_expanded_sql does not know how to interpret pointer bindings and displays the bound value as NULL. This is to prevent the passing-off of "integers" as "pointers" for security reasons.

Note that you can also do more normal binding as in carray(?,?,?) in which case you bind the three parameters using:

sqlite3_bind_pointer(stmt, 1, tag_list, "carray", SQLITE_STATIC);
sqlite3_bind_integer(stmt, 2, 10);
sqlite3_bind_text(stmt, 3, "char*");

The "pointer" will still show up as NULL because as far as anything that does not know that argument is a pointer and what the correct pointer type is will see it as NULL.

You could also use:

   ... from carray() where pointer=? and count=? and ctype=? ...

with the same bindings.

The pointer will still show up as NULL for the same reason, but count is an ordinary integer and ctype is an ordinary text string ('int32', 'int64', 'double', 'char*'). The tagname "carray" in the sqlite3_bind_pointer API is so that only something expecting a pointer of type "carray" can access the pointer and everything else will see it as NULL.

From the documentation:

The sqlite3_bind_pointer(S,I,P,T,D) routine causes the I-th parameter in prepared statement S to have an SQL value of NULL, but to also be associated with the pointer P of type T. D is either a NULL pointer or a pointer to a destructor function for P. SQLite will invoke the destructor D with a single argument of P when it is finished using P. The T parameter should be a static string, preferably a string literal. The sqlite3_bind_pointer() routine is part of the pointer passing interface added for SQLite 3.20.0.

(5) By Gary (1codedebugger) on 2020-12-26 18:26:25 in reply to 3.1 [link] [source]

Thank you for the explanation and example of alternate method; both are very helpful.

(6) By Keith Medcalf (kmedcalf) on 2020-12-26 19:06:22 in reply to 5 [link] [source]

Note that in:

   ... from carray() where pointer=? and count=? and ctype=? ...

you do not need the (), just the bare table-valued-function-name since the "parameters" are basically being passed as constraints in the WHERE clause. IE:

   ... from carray where pointer=? and count=? and ctype=? ...