NO

Author Topic: Does a table exists - SQLITE  (Read 1328 times)

Grincheux

  • Guest
Does a table exists - SQLITE
« on: October 20, 2020, 03:58:21 PM »
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 !
« Last Edit: October 20, 2020, 04:01:13 PM by Grincheux »

Offline John Z

  • Member
  • *
  • Posts: 790
Re: Does a table exists - SQLITE
« Reply #1 on: October 21, 2020, 11:32:38 AM »
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