1

I have to check if a table exists before it is created, by reading How do I check in SQLite whether a table exists?, I use sqlite3_exec to do one step query with

SELECT name FROM sqlite_master WHERE type = 'table' AND name ='table1';

and use the callback to set a flag to identify the table exists or not. Unfortunately, the callback will not be called, if the table is not yet created.

Why the callback is not being called? I know that callback is not called for queries without output results, e.g. "create table" etc, and only called with "SELECT" queries. But I am not aware that it may not even be called for "SELECT".

The following is the code sample to reproduce the problem. 

#include <stdio.h>
#include <sqlite3.h>

bool isExist=false;
static int callback(void *NotUsed, int argc, char **argv, char **azColName){      
  printf("I am being called\n");
  if (argc>0) isExist = true;
  return 0;
}

int main(int argc, char **argv){
  sqlite3 *db;
  char *zErrMsg = 0;
  int rc;

  rc = sqlite3_open("test.db", &db);
  if( rc ){
    fprintf(stderr, "Can't open database: %s\n", sqlite3_errmsg(db));
    sqlite3_close(db);
    return(1);
  }

  //char* sqlCreateTable = "CREATE TABLE table1(name TEXT);";
  //rc = sqlite3_exec(db, sqlCreateTable, callback, 0, &zErrMsg);

  // callback will not be called if table is not yet created
  char* sql_hasTable = "SELECT name FROM sqlite_master WHERE type = 'table' AND name ='table1';";
  rc = sqlite3_exec(db, sql_hasTable, callback, 0, &zErrMsg);
  if( rc!=SQLITE_OK ){
    fprintf(stderr, "SQL error: %s\n", zErrMsg);
    sqlite3_free(zErrMsg);
  }
  sqlite3_close(db);
  return 0;
}
Community
  • 1
  • 1
pepero
  • 7,095
  • 7
  • 41
  • 72
  • 3
    The callback function for a select in sqlite3 is only called if there is a valid return. AKA at least 1 result. If the table doesn't exist, then the lack of a result from that query shouldn't be unexpected. Create the table, then run that query and see if your callback gets hit. – Aumnayan Jun 04 '15 at 15:01
  • I would have loved to point you at the documentation that stated it's behavior in a zero-return condition on the [ edit]sqlite3_exec[/edit], but I was unable to find it in the time I gave to search. Here is the API doc: http://www.sqlite.org/c3ref/exec.html – Aumnayan Jun 04 '15 at 15:07
  • the callback will be called if the table is created before. 0 result is also a valid return. no? – pepero Jun 04 '15 at 15:07
  • My guess would be no. Since the exec handles all sql statement it probably only executes the callback if there is at least 1 valid result. As I stated above, the API document doesn't specify one way or the other. So you'll have to take the observed behavior at face value. – Aumnayan Jun 04 '15 at 15:10
  • thanks for the link, that is why i am quite surprised that no such statement. I also try to use gdb to trace the sqlite internally, but I soon get lost. if the callback is executed based on the result, to be honest, I think that is bad design. usually the reaction is in the callback, now, you do not know what to do when you do the query, since it may or may not be called. – pepero Jun 04 '15 at 15:10
  • 1
    Why not use CREATE TABLE IF NOT EXISTS – ravenspoint Jun 04 '15 at 15:23

0 Answers0