How to proprerly bind text to a SQLite prepared statement with sqlite_bind_text

Question

If the following SQL using the sqlite3_prepare_v2 is run, it generates the expected results.

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 ( 'name_1', 'name_2', 'name_5', 'name_8', 'name_43' ) )
    group by media_id
    having count( media_id ) = 5 );

But if it is changed to

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 ) = ? );

and sqlite3_bind_text( stmt, 1, char_ptr, -1, SQLITE_STATIC ) and sqlite3_bind_int( stmt, 2, 5 ) are employed, no results are returned and there are no errors.

If the in clause names are hard coded and only the media_id count of 5 is set with sqlite3_bind_int, then the expected results are returned.

Hard coding the 5 and attempting to bind the names with sqlite3_bind_text does not return results.

In all cases, the statement is prepared successfully, at least an error code is not generated at that step.

If char_ptr is printed to the screen, it is 'name_1', 'name_2', 'name_5', 'name_8', 'name_43'.

I must be misunderstanding something simple. Would you tell what I'm missing?

Thank you.

I posted this question on the SQLite forum also and received a response concerning what may be a relatively new feature that will allow for one ? in the in set, which appears to indicate that the prepared statement can be prepared at compile time even for a variable number of elements in the set. It's called the carray array extension function. I haven't tried it yet but figured I'd better link it now in case I forget later.

user9706 · Accepted Answer · 2020-12-22 06:57:53Z

2

Try use a ? per bind variable as in:

where name in (?, ?, ?, ?, ?)

and then call sqlite3_bind_text for each.

answered Dec 22, 2020 at 6:57

user9706

Sign up to request clarification or add additional context in comments.

4 Comments

Gary Over a year ago

Oh, I hadn't thought of that at all, thanks. I'll give it a try.

user9706 Over a year ago

Sometimes you generate both the sql query itself and the matching bind statements at run-time.

Gary Over a year ago

Thank you. That appears to work. The number and names of desired tags will vary; so, there appears to be no advantage to storing a prepared statement because it'll have to be prepared anew at each request. May as well just include the names and number in the SQL query text itself rather than use question marks and bind statements.

Gary Over a year ago

A response to a post on the SQLite forum described the Carray method that can accept a single ? in the in. I added to the end of my original question. I didn't get to try it yet but think it requires the newest version of SQLite from this month 3.34.

Collectives™ on Stack Overflow

How to proprerly bind text to a SQLite prepared statement with sqlite_bind_text

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related