2

I'm using SQLite3 C interface from C++ and need to be able to determine existence of a given table. Is there a means for this without using SQL directly? Example:

bool exists = sqlite3_table_exists("myTable");

Is there any function with similar functionality?

Amani
  • 16,245
  • 29
  • 103
  • 153

2 Answers2

8

To check if table exists, check if any rows are returned by the following query:

SELECT name FROM sqlite_master WHERE type='table' AND name='your_table_name_here'

To run the query & check if rows exist, here is the code:

sqlite3_stmt *pSelectStatement = NULL;
int iResult = SQLITE_ERROR;
bool ret = false;

iResult = sqlite3_prepare16_v2(m_pDb, query, -1, &pSelectStatement, 0);

if ((iResult == SQLITE_OK) && (pSelectStatement != NULL))
{                   
    iResult = sqlite3_step(pSelectStatement);

    //was found?
    if (iResult == SQLITE_ROW) {
        ret = true;
        sqlite3_clear_bindings(pSelectStatement);
        sqlite3_reset(pSelectStatement);
    }
    iResult = sqlite3_finalize(pSelectStatement);
}
Gautam Jain
  • 6,789
  • 10
  • 48
  • 67
-1

Also as alternative that would [probably] work on any SQL database select count(*) from table_that_might_exist limit 1.

aisbaa
  • 9,867
  • 6
  • 33
  • 48
  • That's really a brute force way to implement this. If the table exists and is large, it's probably slow, and you'll have to handle an exception for the case when the table doesn't exist. – Frank Schmitt Apr 08 '14 at 08:43
  • How about now, not slow anymore :D. Yet it is not very easy to execute in sqlite3. Also I wonder what would sqlite3_exec return if table does not exists. – aisbaa Apr 08 '14 at 08:59
  • 1
    Probably faster (with `LIMIT 1`), but won't work on all RDBMs (e.g. Oracle has no support for `LIMIT`) – Frank Schmitt Apr 08 '14 at 12:42