SQLite Forum

sqlite3_exec stepping through multiple queries
Login

sqlite3_exec stepping through multiple queries

(1) By anonymous on 2021-02-01 17:44:32 [link] [source]

With single step query execution i.e. sqlite3_exec, given multiple SQL statements e.g.

select * from thisTable;select * from thatTable;

Is there any 'indicator' that the first query as completed i.e. progressed to the next query?

(2) By Keith Medcalf (kmedcalf) on 2021-02-01 19:08:14 in reply to 1 [link] [source]

Only indirectly (as in the number of columns is different or the column names are different).

(3) By anonymous on 2021-02-01 19:25:10 in reply to 2 [link] [source]

Thanks. I had thought of tracking column names; unlike number of columns, column names are much more likely to be different but still not full proof.

select * from mytable where gender = 'M';select * from mytable where gender = 'F';

Mind you, that really ought to be this:

select * from myTable order by gender desc;

Practically speaking, I think tracking column names will work in the absence of a better indicator.

(4) By Scott Robison (casaderobison) on 2021-02-01 20:46:58 in reply to 3 [link] [source]

If you included a "sentinel query" between the two, it could help you identify the transition (as long you know the sentinel uses a different schema than the other queries). For example:

select * from mytable where gender = 'M'; select 'end-1' as EOQ; select * from mytable where gender = 'F'; select 'end-2' as EOQ;

Just a thought. One could just as easily issue two separate sqlite3_exec calls to know when one is done and another starts.

(5) By anonymous on 2021-02-02 07:32:24 in reply to 4 [link] [source]

The solution might be quite messy in practice so much so as to warrant the prohibition of multiple queries by simply having the callback function returning non-zero when the column names are different.

Any intricate solution involving multiple queries is likely to run out of memory.

It is not enough to know which of a set of multiple queries is being executed since that can happen within the callback function and no callback happens when there are no results to be returned. Neither 'DROP TABLE xyz;' nor 'SELECT * FROM MYTABLE WHERE 0=1;' will see the callback being invoked.

The reason for asking the question in the first place was this: imagine a GUI application that stores its inputs by screen/form. It can request those values either one at a time i.e. per screen/form or it can request values for all screens in one go. The callback function needs to build the results by screen/form- easy with single requests but 'messy' with multiple requests at once not least because sqlite3_exec does not hand back control to the application until the callback has processed all queries.

(6) By Jim Morris (jimmorris33215) on 2021-02-02 16:14:39 in reply to 5 [source]

What I've done with unions to differentiate rows is to just include a literal column indicating the source.