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

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.

Answer

Try use a ? per bind variable as in:

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

and then call sqlite3_bind_text for each.

Advertisement

Answer