13

I'm opening a database file and potentially creating it if it doesn't exist.

But for some reason, this doesn't create the table. Any ideas?

const char* sql = "CREATE TABLE IF NOT EXISTS blocks(id text primary_key,length numeric)";

sqlite3_stmt *stmt;
rc = sqlite3_prepare_v2(db_, create_table_sql, -1, &stmt, NULL);
rc = sqlite3_step(stmt);

I haven't got it in here by yes I'm checking the return code at each point. There are no errors.

Perhaps there is a better way to accomplish this?

hookenz
  • 36,432
  • 45
  • 177
  • 286

3 Answers3

17

Variation on another given answer:

select count(type) from sqlite_master where type='table' and name='TABLE_NAME_TO_CHECK';

Will return 0 if table does not exist, 1 if it does.

MPelletier
  • 16,256
  • 15
  • 86
  • 137
  • For some reason this always returns 1 to me, whether the table is present or not, i type in exactly what is above apart from changing 'table_name_to_check' to my table name – Daniel Peedah Jul 18 '18 at 09:53
  • 1
    @DanielPeedah If you do `select *` instead of `select count(type)` is there a difference whether the table exists or not? – MPelletier Jul 18 '18 at 18:10
6

Execute the following SQL:

select 1 from sqlite_master where type='table' and name='TABLE_NAME_TO_CHECK'

If you get a row then the table exists. If the result set is empty then it doesn't.

Anthony Williams
  • 66,628
  • 14
  • 133
  • 155
2

It looks like you're missing a right parenthesis in the SQL. It should be:

const char* sql = "CREATE TABLE IF NOT EXISTS blocks(id text primary_key,length numeric);";
Matthew Flaschen
  • 278,309
  • 50
  • 514
  • 539
  • Sorry, the example above is slightly different to the actual to hide who I work for. In the actual code the create table doesn't function still. – hookenz Aug 17 '10 at 04:32