SQLite Forum

Nesting of statements
Login
I'm looking for rules on nesting SQL statements at the C API level and can't find anything definitive on this site.

Here's an example of the sort of think I'm trying to do:

    sqlite3_prepare_v2(db, "SELECT id FROM table", -1, ps, NULL);

    while(sqlite3_step(ps) == SQLITE_ROW)
    {
      int id = sqlite3_column_int(ps, 0);

      sqlite3_execf(db, "UPDATE table SET x=1 WHERE id=?", id);
    }

    sqlite3_finalize(db);


This example is very simple and contrived - it's easily converted into a single SQL statement which is better in every way.  However, in the real cases there is some complex business logic between the select and the update with features not easily replicated in SQL.

If the above isn't in a transaction, it looks like we get API misuse returned when trying the update.  If in a transaction, it is okay (except the risk of SQLITE_BUSY due to upgrade from reader to writer - using an immediate transaction avoids that).

So I *think* I've worked out the rules, but it would nice to have a canonical reference in case things change in the future!