RE: [sqlite] Best way to check for existence of a table?

[Mr. Tezozomoc]

Try something like .....
dba_int_t  dba_check_table_exist (dba_void_t *hndArg, dba_char_t *table)
{
/******************************************************************************/
/* Local Variables:                                                          
 */
/******************************************************************************/
   dba_handle_t        *pHnd = (dba_handle_t *)hndArg;
   dba_status_t        status = DBA_TABLE_NOT_EXIST;
   dba_result_t        res, *pResult=&res;
   dba_char_t          statement[200];
   dba_char_t          *pErrMsg = NULL;
   dba_int_t           rc = 0;
   dba_int_t           time_delays = 0;

/******************************************************************************/
/* Function Body:                                                            
 */
/******************************************************************************/

   if ( (pHnd != NULL) && (pHnd->magic == DBA_HANDLE_MAGIC) )
   {
       /* init sqlite database */
       if( ( pHnd->db != NULL ) && (statement != NULL) )
       {
           pResult->result_table = NULL;
           pResult->numrows = 0;
           pResult->numcols = 0;
           sprintf(statement,
           "SELECT name FROM sqlite_master WHERE type='table' and 
name='%s'",
           table);
           do {
               rc = sqlite_get_table(pHnd->db,
                                 statement,
                                             &pResult->result_table,
                                             &pResult->numrows,
                                             &pResult->numcols,
                                             &pErrMsg);
               if ((rc==SQLITE_BUSY)||(rc==SQLITE_LOCKED)){
#ifdef _VXWORKS
                   taskDelay(DBA_DB_WAIT_TIME);
#endif
                   if (time_delays > DBA_DB_WAIT_MAX){
                       /* it looks like there might be a problem with db */
                       /* its time to bail */
                       rc = SQLITE_ERROR;
                       break;
                   }
                   time_delays++;
                   if(dba_debug){
#ifdef _VXWORKS
                       logMsg("%s: # of Delays 
%d\n",__FUNCTION__,time_delays,0,0,0,0);
#endif
                   }

               }
           } while((rc==SQLITE_BUSY)||(rc==SQLITE_LOCKED));
           if (pErrMsg != NULL)
           {
               if(dba_debug)
                   printf("dba_exec: %s\n", pErrMsg);
               dba_free(pErrMsg);
           }
           if ( rc == SQLITE_OK )
           {
               if(pResult->numrows == 1){
                   status = DBA_TABLE_EXIST;
               }
               else {
                   status = DBA_TABLE_NOT_EXIST;
               }
               if(dba_debug){
                   printf("dba_exec: Exists =%s\n", table);
               }
           }
           /* free if any rows */
           dba_free_table (pResult);
       } /* if( ( pHnd->db != NULL ) && (statement != NULL) ) */
   } /* ( (pHnd != NULL) && (pHnd->magic == DBA_HANDLE_MAGIC) ) */
   if (status == DBA_TABLE_EXIST){
       return (status);
   }
   else {
       return (rc);
   }
  /* return (status);*/
} /* end of dba_check_table_exist */
----Original Message Follows----
From: [EMAIL PROTECTED]
Reply-To: sqlite-users@sqlite.org
To: sqlite-users@sqlite.org
Subject: RE: [sqlite] Best way to check for existence of a table?
Date: Mon, 14 Feb 2005 15:35:53 -0500


did you try:
SELECT NULL FROM sqlite_master WHERE tbl_name = 'table';
Regards,
[EMAIL PROTECTED]
NCCI
Boca Raton, Florida
561.893.2415
greetings / avec mes meilleures salutations / Cordialmente
mit freundlichen Grüßen / Med vänlig hälsning

                      "Richard Boyd"
                      <[EMAIL PROTECTED]        To:       
<sqlite-users@sqlite.org>
                      .com>                    cc:
                                               Subject:  RE: [sqlite] Best 
way to check for existence of a table?
                      02/14/2005 03:05
                      PM
                      Please respond to
                      sqlite-users



Thanks for the prompt reply...
I tried what you suggested and I always get the error message:
"SQL error: no such column: table32"
Whether the table exists or not, I always get returned value of 1 from
sqlite3_exec().
The exact command that I use is:
SELECT count(*) FROM sqlite_master WHERE name=table32 AND type='table';
I also tried single quotes around the table32 name:
SELECT count(*) FROM sqlite_master WHERE name='table32' AND type='table';
And get no errors whether the table exists or not....
When I try the other method suggested ("SELECT NULL FROM sqlite_master
WHERE
tbl_name = 'your-table';") I don’t get any error messages whether the 
table
exists or not. The return value is always 0.

I'm obviously missing where the error is being flagged, have you any more
pointers?
Sorry if I'm being dense here but I'm new to SQL databases.
Thanks again,
Richard.

-----Original Message-----
From: D. Richard Hipp [mailto:[EMAIL PROTECTED]
Sent: 13 February 2005 23:46
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Best way to check for existence of a table?
On Sun, 2005-02-13 at 22:58 +0000, Richard Boyd wrote:
>
>
> I need a way to check if a table exists from some C code.
SELECT count(*) FROM sqlite_master WHERE
  name=<tablename> AND type='table';
--
D. Richard Hipp <[EMAIL PROTECTED]>

--
No virus found in this incoming message.
Checked by AVG Anti-Virus.
Version: 7.0.300 / Virus Database: 265.8.7 - Release Date: 10/02/2005

--
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.300 / Virus Database: 265.8.7 - Release Date: 10/02/2005