Databases
StackOverflow2013
Postsdbo
POWhat is the defined behavior of SQLite when interleaving statements that affect each other?
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POWhat is the defined behavior of SQLite when interleaving statements that affect each other?
    text
    copied!<p>In SQLite if I prepare a <code>SELECT</code> statement and begin stepping through it, then before the last row of the results is reached I execute another statement that has an effect on the <code>SELECT</code> statement that I am stepping through, what is the expected result?</p> <p>I can't find anything in the SQLite documentation about what is supposed to happen but it seems like an extremely common case when programming in a multi-threaded environment.</p> <p>Below is a c++ file that can be compiled and run on Windows to demonstrate the situation.</p> <pre><code>#include "stdafx.h" #include "sqlite3.h" #include &lt;Windows.h&gt; #include &lt;iostream&gt; #include &lt;Knownfolders.h&gt; #include &lt;Shlobj.h&gt; #include &lt;wchar.h&gt; #include &lt;comdef.h&gt; using namespace std; int exec_sql(sqlite3 *db, const char* sql) { char *errmsg; int result = sqlite3_exec(db, sql, NULL, NULL, &amp;errmsg); if (result != SQLITE_OK) { cout &lt;&lt; errmsg &lt;&lt; endl; return -1; } return 0; } int _tmain(int argc, _TCHAR* argv[]) { cout &lt;&lt; "Running jsqltst with SQLite version: "; cout &lt;&lt; sqlite3_libversion(); cout &lt;&lt; endl; PWSTR userhome; if (!SUCCEEDED(SHGetKnownFolderPath(FOLDERID_Profile, NULL, NULL, &amp;userhome))) { cout &lt;&lt; "Failed getting user home dir\n"; return -1; } wcout &lt;&lt; "User home: " &lt;&lt; userhome &lt;&lt; endl; wchar_t *ws1 = userhome, *ws2 = L"\\test.sqlite"; wstring dbpath_str(ws1); dbpath_str += wstring(ws2); _bstr_t dbpath(dbpath_str.c_str()); cout &lt;&lt; "DB path: " &lt;&lt; dbpath &lt;&lt; endl; sqlite3 *db; int result = sqlite3_open_v2(dbpath, &amp;db, SQLITE_OPEN_READWRITE|SQLITE_OPEN_CREATE, NULL); if (result != SQLITE_OK) { cout &lt;&lt; sqlite3_errmsg(db) &lt;&lt; endl; return -1; } const char * create_stmt = "CREATE TABLE IF NOT EXISTS atable (id INTEGER PRIMARY KEY, name TEXT, number INTEGER);"; if (exec_sql(db, create_stmt) != 0) { return -1; } const char * delete_stmt = "DELETE FROM atable;"; if (exec_sql(db, delete_stmt) != 0) { return -1; } const char * insert_stmt = "INSERT INTO atable (name,number) VALUES ('Beta',77),('Alpha',99);"; if (exec_sql(db, insert_stmt) != 0) { return -1; } sqlite3_stmt* select_ss; const char * select_stmt = "SELECT * FROM atable;"; result = sqlite3_prepare_v2(db, select_stmt, -1, &amp;select_ss, NULL); if (result != SQLITE_OK) { cout &lt;&lt; sqlite3_errmsg(db) &lt;&lt; endl; return -1; } int i = 0; boolean gotrow; do { result = sqlite3_step(select_ss); gotrow = result == SQLITE_ROW; if (gotrow) { i++; cout &lt;&lt; "I got a row!" &lt;&lt; endl; if (i == 1) { if (exec_sql(db, insert_stmt) != 0) { return -1; } } } } while (gotrow); cout &lt;&lt; "Last result: " &lt;&lt; result &lt;&lt; ", errstr: " &lt;&lt; sqlite3_errstr(result) &lt;&lt; endl; result = sqlite3_finalize(select_ss); if (result != SQLITE_OK) { cout &lt;&lt; sqlite3_errmsg(db) &lt;&lt; endl; return -1; } return 0; } </code></pre>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload