1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

sqlite and C

Discussion in 'C' started by hobbyist, Jul 26, 2012.

  1. hobbyist

    hobbyist New Member

    Joined:
    Jan 7, 2012
    Messages:
    141
    Likes Received:
    0
    Trophy Points:
    0
    For anyone interested, I've thrown together a bit of code for using sqlite and c. As always, if anyone sees a problem, please point it out.

    Code:
     
    /* simple program to interface sqlite and C
    
       required: sqlite3.h and sqlite3.c
    
                 compile sqlite3.c as part of your project
                 #include "sqlite3.c" is incorrect
    
                 source files - http://www.sqlite.org/download.html/sqlite-amalgamation-3071300.zip
    */
    
    #include <stdio.h>
    #include <stdlib.h>
    #include <string.h>
    #include <sqlite/sqlite3.h>
    
    typedef struct {
       int id;
       char name[33];
    } vdata;
    
    /* sqlite callback prototype; 
       this thing is not very useful on its own */
    
    int some_function_name(void *, int, char **, char **);
    
    int main(void) {
    
       sqlite3 *db;  // database object
       char *errmsg; /* sqlite error message you must
                        free(errmsg) if an error occurs) */
       int status;   // sqlite return status
    
       status = sqlite3_open("tryit.db", &db); /* creates a database file if
                                                  it doesn't exist already */
    											
       if(status != SQLITE_OK) { // show error and abort program
          fprintf(stderr, "error code: %d; error msg: %s", status, errmsg);
          free(errmsg);
          return(1);
       } else {   
            
          char cmd[BUFSIZ] = { 0 };	 
          int i = 1; 
          vdata void_d;
    
    	  /* just want 10 records for testing; drop the table if it exists */
    	  
    	  if(sqlite3_exec(db, "drop table if exists tbl_test;", 0, 0, &errmsg) != SQLITE_OK) {
    	     fprintf(stderr, "error msg: %s", errmsg);
                 free(errmsg);
                 sqlite3_close(db);
                 return(1);
              } 
    	  
    	  // made it here, so try creating a table to test with */ 										  
    											   
          sprintf(cmd, "create table if not exists tbl_test(\
                           id integer primary key autoincrement,\
                           name varchar(33) not null default '');");
    
          status = sqlite3_exec(db, cmd, 0, 0, &errmsg); // no callback, just check status
    
    	  if(status != SQLITE_OK) { // show error and abort program
    	     fprintf(stderr, "error code: %d; error msg: %s", status, errmsg);
                 free(errmsg);
                 sqlite3_close(db); // db was opened, so close it 
                 return(1);
              }
    
    	  // made it here, so try inserting some data
    
    	  while(i <= 10) {
    	     sprintf(cmd, "insert into tbl_test values(NULL, 'user %04d');", i);
                 status = sqlite3_exec(db, cmd, 0, 0, &errmsg); // no callback, just check status
    
                 if(status != SQLITE_OK) { // show error and abort program
                    fprintf(stderr, "error code: %d; error msg: %s", status, errmsg);
                    free(errmsg);
                    sqlite3_close(db); // db was opened, so close it
                    return(1);
                 }
    
                 ++i;
             }
    
    	  // made it here, so use callback to see what was stored
    	  // show the results where id is a multiple of 3
    	  // should be in descending order based on id
    	  
    	  status = sqlite3_exec(db, "select * from tbl_test where id%3=0;", some_function_name, 0, &errmsg);
    
    	  if(status != SQLITE_OK) { // show error and abort program
    	     fprintf(stderr, "error code: %d; error msg: %s", status, errmsg);
                 free(errmsg);
                 sqlite3_close(db); // db was opened, so close it
                 return(1);
              }
    
    	  // make use of the void * parameter by fetching the 4 th record
    	  // be sure to cast the appropriate type	  
    
    	  sprintf(cmd, "select * from tbl_test where id=4 limit 1;");
    	  status = sqlite3_exec(db, cmd, some_function_name, (vdata *)&void_d, &errmsg);
    
    	  if(status != SQLITE_OK) {
    	     fprintf(stderr, "error code: %d; error msg: %s", status, errmsg);
                 free(errmsg);
                 sqlite3_close(db);
                 return(1);
             }
    
    	  printf("\nrecord 4\nid = %d; name = %s\n\n", void_d.id, void_d.name);
    
    	  // done with the database, so close it and exit program
    
    	  sqlite3_close(db);
       }
    
       return 0;
    }
    
    int some_function_name(void *vptr, int count, char **col_data, char **col_name) {
       
       int i = 0;
       vdata *tmp = (vdata *)vptr;    
       
       while(i < count) {
    
          /* results are always strings; use conversion functions if
              needed.  to pass data between the callback and the main
              program, use the vptr.	     
          */
          
          if(tmp != NULL) {
    	     if(strcmp(col_name[i], "id") == 0)
    	        sscanf(col_data[i], "%d", &tmp->id);
                 if(strcmp(col_name[i], "name") == 0)
                    strcpy(tmp->name, col_data[i]);
          } else 	  		 	  
    	     printf("%s = %s\n", col_name[i], col_data[i]);
    	  
    	  ++i;
       }
       
       putchar('\n');
       
       return 0;
    }
     

Share This Page