Re: [sqlite] "SQL logic error or missing database"

[Jay Sprenkle]

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;
}