hmmm...
it gives a schema changed because 'delete * from x' actually drops the table
but I'm not sure why it gave an error since the prepare was done after the
other change was committed...
program output:
Opened the database.
Opened the database.
Failed to step statement: database schema has changed
finalize failed: database schema has changed
program code:
#include <stdio.h>
#include <stdlib.h>
//#include <unistd.h>
#include <sqlite3.h>
int main(int argc, char** argv)
{
int rc;
sqlite3* db1;
sqlite3* db2;
sqlite3_stmt *pStmt1;
sqlite3_stmt *pStmt2;
sqlite3_stmt *pStmt3;
unlink("bug.db"); // for the test, we make sure we have a new database.
// create first connection to the database: db1.
rc = sqlite3_open("bug.db", &db1);
if (rc)
{
printf("Cannot open database: %s\n", sqlite3_errmsg(db1));
exit(1);
}
printf("Opened the database.\n");
// create table bla using the first connection db1, inside a transaction.
rc = sqlite3_exec(db1, "begin", 0, 0, 0);
if (rc != SQLITE_OK)
{
printf("begin failed: %s\n", sqlite3_errmsg(db1));
exit(1);
}
rc = sqlite3_prepare(db1, // Database handle
"create table bla(a int,b int)",
-1, // Length of the statement
&pStmt1, // OUT: Statement handle
0); // OUT: Pointer to unused portion
// of the statement
if (rc != SQLITE_OK)
{
printf("prepare failed: %s\n", sqlite3_errmsg(db1));
exit(1);
}
rc = sqlite3_step(pStmt1);
if (rc != SQLITE_DONE)
{ // if we failed, we show it.
printf("Failed to step statement: %s\n", sqlite3_errmsg(db1));
}
rc = sqlite3_finalize(pStmt1);
if (rc != SQLITE_OK)
{
printf("finalize failed: %s\n", sqlite3_errmsg(db1));
exit(1);
}
sqlite3_exec(db1, "commit", 0, 0, 0); // here we commit the transaction.
if (rc != SQLITE_OK)
{
printf("commit failed: %s\n", sqlite3_errmsg(db1));
exit(1);
}
// now we suppose to have inside the database the table bla.
// here we, optionally, create another connection to the same database,
// and then create other table in a transaction.
rc = sqlite3_open("bug.db", &db2); // create the second connection.
if (rc) {
printf("Cannot open database again: %s\n", sqlite3_errmsg(db2));
exit(1);
}
printf("Opened the database.\n");
// create table foo
rc = sqlite3_exec(db2, "begin", 0, 0, 0); // start the transaction.
if (rc != SQLITE_OK)
{
printf("begin failed: %s\n", sqlite3_errmsg(db2));
exit(1);
}
rc = sqlite3_prepare(db2, // Database handle
"create table foo(c int,d int)",
-1, // Length of the statement
&pStmt2, // OUT: Statement handle
0); // OUT: Pointer to unused portion
if (rc != SQLITE_OK)
{
printf("prepare failed: %s\n", sqlite3_errmsg(db2));
exit(1);
}
rc = sqlite3_step(pStmt2);
if (rc != SQLITE_DONE) { // if we failed, we show it.
printf("Failed to step statement: %s\n", sqlite3_errmsg(db2));
}
rc = sqlite3_finalize(pStmt2);
if (rc != SQLITE_OK)
{
printf("finalize failed: %s\n", sqlite3_errmsg(db2));
exit(1);
}
sqlite3_exec(db2, "commit", 0, 0, 0);
if (rc != SQLITE_OK)
{
printf("commit failed: %s\n", sqlite3_errmsg(db2));
exit(1);
}
// delete from table bla using the first connection.
sqlite3_exec(db1, "begin", 0, 0, 0);
if (rc != SQLITE_OK)
{
printf("begin failed: %s\n", sqlite3_errmsg(db1));
exit(1);
}
rc = sqlite3_prepare(db1, // Database handle
"delete from bla",
-1, // Length of the statement
&pStmt3, // OUT: Statement handle
0); // OUT: Pointer to unused portion
// of the statement
if (rc != SQLITE_OK)
{
printf("prepare failed: %s\n", sqlite3_errmsg(db1));
exit(1);
}
rc = sqlite3_step(pStmt3);
if (rc != SQLITE_DONE) { // if we failed, we log it.
printf("Failed to step statement: %s\n", sqlite3_errmsg(db1));
}
else {
printf("deleted all from bla successfully\n");
}
rc = sqlite3_finalize(pStmt3);
if (rc != SQLITE_OK)
{
printf("finalize failed: %s\n", sqlite3_errmsg(db1));
exit(1);
}
sqlite3_exec(db1, "commit", 0, 0, 0);
if (rc != SQLITE_OK)
{
printf("commit failed: %s\n", sqlite3_errmsg(db1));
exit(1);
}
return 0;
}
