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

[Ran]

On 5/23/06, Jay Sprenkle <[EMAIL PROTECTED]> wrote:

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...
...

Thanks for your efforts!

I am afraid that the delete is not connected to the problem. Also the
prepare/step is not. Even the reset/finalize are not connected to it. The
script below gives the problem, while all the create statements are run by
sqlite3_exec (so no reset/finalize are used there at all). The problem
happens only when stepping the select. See the updated script below:

// compile with: gcc -g bug.cpp -lsqlite3 -o bug
#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 *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.
 sqlite3_exec(db1, "begin", 0, 0, 0);
 rc = sqlite3_exec(db1, "create table bla(c int,d int)", 0, 0, 0);
 if (rc != SQLITE_OK) { // if we failed, we show it.
   printf("Failed to exec statement: %s\n", sqlite3_errmsg(db1));
 }
 sqlite3_exec(db1, "commit", 0, 0, 0); // here we commit the transaction.

 // here we, optionally, create another connection to the same database,
 // and then create other table in a transaction.
 if (argc > 1) {
   rc = sqlite3_open("bug.db", &db2); // create the second connection.
   if (rc) {
     printf("Cannot open database again: %s\n", sqlite3_errmsg(db2));
     exit(1);
   }
   else {
     printf("Opened the database.\n");
   }

   // create table foo
   sqlite3_exec(db2, "begin", 0, 0, 0); // start the transaction.
   rc = sqlite3_exec(db2, "create table foo(c int,d int)", 0, 0, 0);
   if (rc != SQLITE_OK) { // if we failed, we show it.
     printf("Failed to exec statement: %s\n", sqlite3_errmsg(db2));
   }
   sqlite3_exec(db2, "commit", 0, 0, 0);
 }

 // select from table bla using the first connection.
 sqlite3_exec(db1, "begin", 0, 0, 0);
 rc = sqlite3_prepare(db1,                // Database handle
                      "select * 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("Failed to prepare statement: %s\n", sqlite3_errmsg(db1));
 }
 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_reset(pStmt3);
 sqlite3_exec(db1, "commit", 0, 0, 0);
 rc = sqlite3_finalize(pStmt3);
}