View Single Post
Old Aug 21st, 2010, 9:24 AM   #17
Ancient Dragon
Achieved Level 70

 
Ancient Dragon's Avatar
 
Join Date: Jun 2005
Location: near St Louis, MO. (USA)
Posts: 4,345
Rep Power: 10 Ancient Dragon will become famous soon enoughAncient Dragon will become famous soon enough
Re: Sqlite Starter Program

Here is the same program but written in C language
// Before you compile this program you have to 
// dowload the Sqlite source code and header files from 
// http://www.sqlite.org/download.html
// Then you have to compile the sqlite3.c program
// along with this one and link the two object files
// together as a single program.  Or you could compile sqlite3.c
// into a library and link this program with that library.
#include <stdio.h>
#include <string.h>
#include "sqlite3.h"

// Delete this pragma if you are not using
// a Microsoft compiler.
#pragma warning(disable: 4996)

#define DBNAMESIZE 40
// Function prototypes
void fill(sqlite3* db, char* tbname);
void CreateDatabase(sqlite3** db);
void CreateTable(sqlite3* db, char* tbname);
void display(sqlite3* db, char* tbname);


// This is a callback function that is called by Sqlite to process the 
// resultset of a query.  There are other ways to do it without using
// a callback function, such this is the simplest to implemnent.
//
// Parameters are:
//      argc -- the number of rows in the resultset
//      argv[] -- the data for each row
//      azColName -- the name of each column
int callback(void *NotUsed, int argc, char **argv, char **azColName){
  int i;
  for(i=0; i<argc; i++){
    printf("%s = ",azColName[i]);
    if( argv[i] )
        printf(argv[i]);
    else
        printf("NULL");
     printf("\n");
  }
  return 0;
}

// This function just display an error message that
// may have been returned by the various Sqlite functions.
void dsperr(char**db_err)
{
    if( *db_err )
    {
        printf("db_err\n");
        sqlite3_free(*db_err); // release (free) memory
        *db_err = 0; // reinitialize the caller's pointer
    }
}

// This function is called from several places to get 
// the table name.  
void GetTablename(char* tbname)
{
    printf("Enter the table name\n");
    scanf("%s", tbname);
}


int main()
{
    sqlite3* db = 0; // database connection
    char tbname[DBNAMESIZE];   // table name
    int done = 0;
    while(!done)
    {
        char answer[8] = {0};
        printf("\n\nPlease select one of the folowing options\n");
        printf("1. Create a new or open an existing database\n");
        printf("2. Create a new table\n");
        printf("3. Add some data\n");
        printf("4. List all data\n");
        printf("5. Quit\n");
        scanf("%1s",answer);
        switch(answer[0])
        {
            case '1':
                CreateDatabase(&db);
                break;
            case '2':
                CreateTable(db, tbname);
                break;
            case '3':
                fill(db, tbname);
                break;
            case '4':
                display(db, tbname);
                break;
            case '5':
                done = 1;
                break;
        }

    }
    sqlite3_close(db);
}

// Create a new or open an existing database.
// If the database does not exist it will be
// created.  Note that you can specif the full
// path and filename which may, or may not, contain spaces.
void CreateDatabase(sqlite3** db)
{
    char dbname[DBNAMESIZE];
    int n;
    printf("Please enter the name of the database\n");
    fgets(dbname,sizeof(dbname), stdin);
    if( dbname[strlen(dbname)-1] == '\n')
        dbname[strlen(dbname)-1] = '\0';
    n = sqlite3_open(dbname, db);
    if( n != SQLITE_OK )
    {
        printf("Error opening database.\n");
    }
    else
        printf("Database open ok\n");
}

// Add a new table to the database.  If the table
// name already exists then an error message will be
// displayed.
void CreateTable(sqlite3* db, char* tbname)
{
    char* db_err = 0;
    int n = 0;
    char statement[255] = {0};
    GetTablename(tbname);
    if( tbname[0] == '\0' )
        return;
    sprintf(statement, "CREATE TABLE %s (id integer);", tbname);

    n = sqlite3_exec(db, statement, NULL, 0, &db_err);
    dsperr(&db_err);
    if( n != SQLITE_OK )
    {
        printf("Error executing \"%s\"\n",statement);
    }
    else
        printf("Table created successfully\n");

}


// Just dump some data into the table.  You don't
// have a choice of the kind or quantity of data to be entered.
//
void fill(sqlite3* db, char* tbname)
{
    char* db_err = 0;
    int i = 0;
    int n = 0;
    char buf[80];
    if( strlen(tbname) == 0)
    {
        GetTablename(tbname);
        if( tbname[0] == '\0')
            return;
    }
    for(i = 1; i < 100; i++)
    {
        sprintf(buf,"insert into %s values(%d);", tbname, i);
        n = sqlite3_exec(db, buf, NULL, 0, &db_err);
        dsperr(&db_err);
        if( n != SQLITE_OK )
        {
            printf("Error inserting value %d\n", i);
            break;
        }
    }

}

// Query the database for all the data in the table and
// display it in the callback function.  
void display(sqlite3* db, char* tbname)
{
    char* db_err = 0;
    char select[255] = {0};
    if( strlen(tbname) == 0)
    {
        GetTablename(tbname);
        if( tbname[0] == '\0' )
            return;
    }
    if( strlen(tbname) > 0)
    {
        sprintf(select, "select * from %s;", tbname);
        sqlite3_exec(db, select, callback, 0, &db_err);
        dsperr(&db_err);
    }

}
__________________
PFO's FAQ is here
Forum Rules

There is no cow level (Diablo III)
If you never push yourself you will never improve (Diablo III)
Ancient Dragon is offline