sqlite3 error: no such table: when checking if table exists

Question

I have read several posts but still cannot figure out what is wrong here. I have a c++ wrapper to call the sqlite. I want to test if the table exists before it is created, by reading check-in-sqlite-whether-a-table-exists, I use the following sql statement to check a table exists

"SELECT name FROM test.db WHERE type='table' AND name='table1';"

The main code is the following:

static int callback(void *db, int argc, char **argv, char **azColName){
  for(int i=0; i<argc; ++i){
    printf("%s = %s\n", azColName[i], argv[i] ? argv[i] : "NULL");
  }
  return 0;
}

class SqliteAccessor {
public:
open(){ sqlite3_open("test.db", &m_db); }
createTable1(){
  string sql = "CREATE TABLE table1(one TEXT);";
  char *zErrMsg = 0;
  int rc = sqlite3_exec(m_db, sql.c_str(), callback, 0, &zErrMsg);
  if( rc != SQLITE_OK ){
    printf("SQL error: %s", zErrMsg);
    sqlite3_free(zErrMsg);
  }
}     
hasTable1(){
  string sql = "SELECT name FROM test.db WHERE type='table' AND name='table1'";
  char *zErrMsg = 0;
  int rc = sqlite3_exec(m_db, sql.c_str(), callback, 0, &zErrMsg);
  if( rc != SQLITE_OK ){
    printf("SQL error: %s", zErrMsg); // Error: no such table: test.db 
    sqlite3_free(zErrMsg);
  } 
}
private:
sqlite3* m_db;
}

main(){
  SqliteAccessor sql;
  sql.open(); // success;
  sql.createTable1(); // success;
  sql.hasTable1(); // fail
}

I also tried with cli api:

sqlite3 test.db
sqlite> create table table1(one varchar(10));
sqlite> SELECT * FROM test.db;
Error: no such table: test.db
sqlite> SELECT name FROM test.db WHERE type='table' AND name='table1';
Error: no such table: test.db
// however, if I run .tables, then it is there.
sqlite> .tables
table1

it is the same error, but why such error? it is the lastest sqlite amalgamation version.

perencia · Accepted Answer · 2015-06-03T16:15:46.607

1

Look into sqlite_master instead.

SELECT * FROM sqlite_master WHERE name LIKE '%your_table_name%'

edited Jun 03 '15 at 16:15

answered Jun 03 '15 at 16:10

perencia

1,498
3
11
19

1

aha, It seems I have to select from "test.db.sqlite_master", is this "sqlite_master" a built-in table that lists all the tables? – pepero Jun 03 '15 at 16:18
If i use "SELECT name FROM sqlite_master WHERE type='table' AND name='table1';" it will be OK. but how can I specify which database then? – pepero Jun 03 '15 at 16:25
Do you have more than one database? I'm not sure if it's possible yo hace several "databases" – perencia Jun 03 '15 at 16:34
yes. One is working, the other one is the saved backup – pepero Jun 03 '15 at 16:35
So you'd hace yo open two different connections. – perencia Jun 03 '15 at 16:43

sqlite3 error: no such table: when checking if table exists

1 Answers1