How do I check if a table exists in sqlite3 c++ API?

Question

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?

Answer 1

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);";

Answer 2

Answering my own question.

It's not actually reflected in the code above. It was actually working all along. But I had a typo on the database that I was using. Therefore, when I examined it with the command line sqlite3 tool, it didn't contain the table or any of the data expected.

However, I'm curious as to whether there is a function that will allow me to test if a table exists or not.

Answer 3

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.

Answer 4

Variation on another given answer:

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

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