We have an SQLite virtual-table heavy application with a lot of the GUI
driven by SQL queries, and often times we have queries of the form
select * from some_table where some_column in (...)
where ... is coming from prior selections in the GUI, or filtering, etc...
1) In some places, we create temporary tables and join with those, instead
of using the in (list) where clause.
2) In other places we synthesize the query text by splicing list.join(", ")
in the in (list) where clause. (whether you splice the text of the values,
or a series of %i and do proper binding makes little difference IMHO, in
both cases you need to reparse).
Both solutions are unsatisfactory, because with 1) you have to create dummy
transient tables, for which you need to invent table names, insert, join
with, and then delete/cleanup, and 2) constantly reparse and prepare
queries, which can get super long if the array to "bind" is big.
Any chance SQLite would add true array binding?
For example, given
create table t (name text, type text, primary key (name, type));
select * from t where type in (%1);
and binding would look something like this:
sqlite3_bind_array_begin(stmt, 1 [, types.size()]); // size param?
for (const auto& type: types) {
sqlite3_bind_text(stmt, 1, type.c_str(), type.size(), SQLITE_TRANSIENT);
}
sqlite3_bind_array_end(stmt, 1);
Whether the API allows only homogeneous elements in the array (element type
specified in the sqlite3_bind_array_begin) or it's the usual SQLite duck
typing matters little me.
Obviously I would welcome such a change. I have no clue how difficult to
implement that is of course, but if somehow it could be added, and doesn't
make SQLite that much bigger, then such an addition would be very much
welcome.
If I somehow missed a better work-around to this lack of array-binding, I'm
also interested of course, but obviously I'd prefer real array binding.
Thanks for any insight on this, --DD
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
