Check whether CREATE TABLE actually creates the table or whether it already existed?

Question

I create table in SQLite without checking every time whether it exists.

sqlite3_stmt* create_stmt = NULL;
if (sqlite3_prepare_v2(db, "CREATE TABLE mytable (sif INTEGER PRIMARY KEY, name VARCHAR, description VARCHAR);", -1, &create_stmt, NULL) == SQLITE_OK)
{
    sqlite3_step(create_stmt);
    sqlite3_finalize(create_stmt);
}

If the table doesn't exist, it will be created; if it exists, nothing happens.

I would like to know if there is some way to get information whether the table is created or just checked?

score 3 · Answer 1 · answered Nov 04 '12 at 14:14

3

I'm not sure if you are still looking for an answer but if the table already exists then sqlite3_finalize returns non zero (not SQLITE_OK) value.

answered Nov 04 '12 at 14:14

Takashi

31
2

score 2 · Accepted Answer · edited May 23 '17 at 12:06

2

There's an equivalent to mysql's describe table in sqlite3 : .schema TABLENAME. See this question for more info about it.

So you can issue a .schema mytable in order to know if it's been created and what it looks like. In order to be more focused on a single table, you can also use this statement :

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

edited May 23 '17 at 12:06

Community

1
1

answered Mar 19 '12 at 08:18

Coren

5,517
1
21
34

OK, that was ways to check if table exists but question is different. Get returned value from sqlite statement based on what they do. Most likely that is not possible (in shown case). – Wine Too Mar 19 '12 at 08:44
@user973238 if you want to follow your database, you can use a dedicated log table of actions you want to follow and associating it with some triggers – Coren Mar 19 '12 at 10:10
Hm, I'm too 'short' for such actions for now, still beginner. Anyway, thanks for helping, "select count(type)" works excellent! – Wine Too Mar 19 '12 at 12:08

score 1 · Answer 3 · answered Mar 19 '12 at 08:07

1

If i recall correctly, in case your table already exists it will be sqlite3_step() that fails, not sqlite3_prepare_v2. Check the result for that too.
Edit: Besides you should check it all the time anyway, to catch corrupted databases or other error conditions ;)

answered Mar 19 '12 at 08:07

Torp

7,924
1
20
18

My sqlite3_step() never fails. It returns 101 no matter table exists or not. – Wine Too Mar 19 '12 at 08:41
1

101 is `SQLITE_DONE`; see [Result Codes](http://www.sqlite.org/c3ref/c_abort.html). According to the ` sqlite3_step` docs, "with the "v2" interface, any of the other result codes or extended result codes might be returned as well"; see [Evaluate An SQL Statement](http://www.sqlite.org/c3ref/step.html). – jww Jan 20 '14 at 22:46

Check whether CREATE TABLE actually creates the table or whether it already existed?

3 Answers3