I'm using SQLite 3 in my .NET application. I have a set of parameterized SQL
statements that are executed repeatedly, one statement at a time. Between
each statement the user will provide input for the parameterized value(s) of
next statement (the value of the '?'). The goal is to narrow down a search
to a specific row of data while presenting the user with a valid subgroup in
each step. Ex:
SELECT colA from Table1;
SELECT colB FROM Table1 WHERE colA = ?;
SELECT colC,colD FROM Table1 WHERE colA = ? AND colB = ?;
SELECT colE FROM Table1 WHERE colA = ? AND colB = ? AND colC = ? AND colD =
?;
...
There are many of these steps, and I'm trying to avoid having to write "and
a and b and c and z and a1 and b1..." as well as provide a smaller search
set in the query. I'd like to modify the statements to read something like
this:
SELECT rowid, colA FROM Table1;
SELECT rowid, colB FROM Table1 WHERE rowid IN (?,?,?,...) AND colA = ?;
SELECT rowid, colC,colD FROM Table1 WHERE rowid IN (?,?,?,...) AND colB = ?;
SELECT rowid, colE FROM Table1 WHERE rowid IN (?,?,?,...) AND colC = ? AND
colD = ?;
...
Is this possible to do with parameterized statements? Since the number of
items in the "IN" group changes for each SELECT statement (the subgroup gets
narrower and narrower), I would guess that it's not possible and as well as
defeating some of the usefulness of parameterized statements.
I can't find a DbType that would allow me to add an array as the value of
the subgroup, which further leads me to believe it's not possible.
The only option that I've thought of is to use string concatenation so that
I would end up with something like this:
string subgroup = "('1', '2', '3')";
string sqlQuery = "SELECT rowid, colB FROM Table1 WHERE rowid IN " +
subgroup + " AND colA = ?;
Any other suggestions?
--
-Roy Donaldson
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
