Does a table exists - SQLITE

[Grincheux]

If you want to make a SELECT you cannot use IF EXISTS like with DROP.
In SQLite the following query gives you 1 if the table exists or 0 if not.

Code: [Select]

SELECT count(*) FROM sqlite_master WHERE type='table' AND name='MyTable';
In C you can do like this:

Code: [Select]

#include "sqlite3.h"

typedef   sqlite3*       HSQLITE ;
typedef   sqlite3_stmt*   HSTMT ;

HSQLITE         hAsteroid ;

//   Returns 1 if the table exists and 0 if it does not exist
//   Call the function like this :
//                                  _iRes = SQLite_TableExists(hAsteroid,"MyNewTable") ;

int SQLite_TableExists(HSQLITE __hSQLite,LPSTR __lpszTableName)
{
   HSTMT      _hFind ;
   int         _iResult ;
   char      _szTmp[1024] ;
   LPSTR      _lpszTmp ;

   _iResult = 0 ;
   _lpszTmp = _szTmp ;

   sqlite3_snprintf(sizeof(_szTmp),_lpszTmp,"SELECT count(*) FROM sqlite_master WHERE type='table' AND name='%s';",__lpszTableName) ;
   if(sqlite3_prepare_v2(__hSQLite,_lpszTmp,-1,&_hFind,NULL) == SQLITE_OK)
   {
      if(sqlite3_step(_hFind) == SQLITE_ROW)
      {
         _iResult = sqlite3_column_int(_hFind,0) ;

         sqlite3_reset(_hFind) ;
         sqlite3_finalize(_hFind) ;
      }
   }

   return (_iResult) ;
}

You can't do like this:

Code: [Select]

sqlite3_snprintf(sizeof(_szTmp),_lpszTmp,"SELECT count(*) FROM sqlite_master WHERE type='table' AND name='%s';",__lpszTableName) ;
_iResult = sqlite3_exec(__hSQLite,_lpszTmp,0,0,NULL) ;
   Because if there is no error in your query, SQLite always returns SQLITE_OK which is equal to 0.
   If there is an error in the query, SQLite returns SQLITE_ERROR which is equal to 1 !

[John Z]

A lot of good tips !  Thanks 

Maybe it would be good to start a thread, or get a 'subcategory', for SQLite under Tips and Tricks so all the helpful codes can reside in one place? 


John Z