1. Computing

Tutorial two - Creating a database, populating it and running a SQL query

By

This tutorial is the second in a series on programming SQLite in C. If you found this tutorial first, please go to First tutorial on Programming SQLite in C.

In the previous tutorial I explained how to set-up Visual Studio 2010/2012 (either the free Express version or the commercial one) for working with SQLite as part of your program or called through a standalone dll. We'll carry on from there.

Databases and Tables

SQLite stores a collection of tables in a single file database, usually ending in .db. Each table is like a spreadsheet, it consists of a number of columns and each row has values.

If it helps, think of each row as being a struct, with the columns in the table corresponding to the the fields in the struct.

A table can have as many rows as will fit on disk. There is an upper limit but its huge 18,446,744,073,709,551,616 to be precise.

You can read the SQLite limits on their website. A table can have up to 2,000 columns or if you recompile the source, you can max it to an awesome 32,767 columns.

THE SQLite API

To use SQLite, we need to make calls to the API. You can find an introduction to this API on the official Introduction to SQLite C/C++ Interface web page. It's a collection of functions and easy to use.

First we need a handle to the database. This is of type sqlite3 and is returned by a call to sqlite3_open( filename, ** ppDB). After that we execute the SQL.

Let's have a slight digression first though and create a usable database and some tables using SQLiteSpy. (See the previous tutorial for links to that and the SQLite Database Browser).

Events and Venues

The database about.db will hold three tables to manage events at several venues. These events will be parties, discos and concerts and will take place at five venues (alpha, beta, charlie, delta and echo). When you are modelling something like this, it often helps to start with a spreadsheet. For simplicities sake, I'll just store a date not a time.

The spreadsheet has three columns: Dates, Venue, Event Type and about ten events like this. Dates run from 21st to 30th of June 2013.

Now SQLite has no explicit date type, so it's easier and faster to store it as an int and the same way that Excel uses dates (days since Jan 1, 1900) have int values 41446 to 41455. If you put the dates in a spreadsheet then format the date column as a number with 0 decimal places, it looks something like this:

Date,Venue,Event Type
41446,Alpha,Party
41447,Beta,Concert
41448,Charlie,Disco
41449,Delta ,Concert
41450,echo,Party
41451,Alpha,Disco
41452,Alpha,Party
41453,Beta,Party
41454,Delta ,Concert
41455,Echo,Part

Now we could store this data in one table and for such a simple example, it would probably be acceptable. However good database design practice requires some normalization.

Unique data items like venue type should be in its own table and the event types (party etc) should also be in one. Finally as we can have multiple event types at multiple venues, ( a many to many relationship) we need a third table to hold these.

The three tables are:

  • venues - holds all five venues
  • eventtypes - holds all three event types
  • events - holds the date plus venue id plus event type id. I also added a description field for this event eg "Jim's Birthday".

The first two tables hold the data types so venues have names alpha to echo. I've added an integer id as well and created an index for that. With the small numbers of venues (5) and event types (3), it could be done without an index, but with larger tables it will get very slow. So any column that is likely to be searched on, add an index, preferably integer

The SQL to create this is:

create table venues(
idvenue int,
venue text)

create index ivenue on venues(ideventtype)

create table eventtypes(
ideventtype int,
eventtype text)

create index ieventtype on eventtypes(idvenue)

create table events(
idevent int,
date int,
ideventtype int,
idvenue int,
description Text)

create index ievent on events(date, idevent, ideventtype,idvenue)

The index on the events table has date, idevent, the event type and venue. That means we can query the event table for "all events on a date", "all events at a venue","all parties" etc and combinations of those such as "all parties at a venue" etc.

After running the SQL create table queries, the three tables are created. Note I've put all that sql in the text file create.sql and it includes data for populating some of the three tables.

If you put ; on the end of the lines as I've done in create.sql then you can batch and execute all the commands in one go. Without the ; you have to run each one by itself. In SQLiteSpy, just click F9 to run everything.

I've also included sql to drop all three tables inside multi-line comments using /* .. */ same as in C. Just select the three lines and do ctrl + F9 to execute the selected text.

These commands inserts the five venues:

insert into venues (idvenue,venue) values (0,'Alpha');
insert into venues (idvenue,venue) values (1,'Bravo');
insert into venues (idvenue,venue) values (2,'Charlie');
insert into venues (idvenue,venue) values (3,'Delta');
insert into venues (idvenue,venue) values (4,'Echo');

Again I've included commented out text to empty tables, with the delete from lines. There's no undo so be careful with these!

Amazingly, with all the data loaded (admittedly not much) the entire database file on disk is only 7KB.

Event Data

Rather than build up a bunch of ten insert statements, I used Excel to create a .csv file for the event data and then used the SQLite3 command line utility (that comes with SQLite) and the following commands to import it.

Note: Any line with a period (.) prefix is a command. Use .help to view all commands. To run SQL just type it in with no period prefix.

.separator ,
.import "c:\\data\\aboutevents.csv" events
select * from events;

You have to use double blackslashes \\ in the import path for each folder. Only do the last line after the .import has succeeded. When SQLite3 runs the default separator is a : so it has to be changed to a comma before the import.

Back to the Code

Now we have a fully populated database, let's write the C code to run this SQL query which returns a list of parties, with description, dates and venues.

select date,description, venue from events,venues
where ideventtype = 0
and events.idvenue = venues.idvenue

This does a join using the idvenue column between the events and venues table so we get the name of the venue not its int idvenue value.

SQLite C API Functions

There are many functions but we only need a handful. The order of processing is:

  1. Open database with sqlite3_open(), exit if have error opening it.
  2. Prepare the SQL with sqlite3_prepare()
  3. Loop using slqite3_step() until no more records
  4. (In the loop) process each column with sqlite3_column...
  5. Finally call sqlite3_close(db)

There's an optional step after calling sqlite3_prepare where any passed in parameters are bound but we'll save that for a future tutorial.

So in the program listed below the pseudo code for the major steps are:

Database Open.
Prepare sql
do {
  if (Step= SQLITE_OK)
    {
      Extract three columns and output)
    }
  } while step == SQLITE_OK
Close Db

The sql returns three values so if sqlite3.step() == SQLITE_ROW then the values are copied from the appropriate column types. I've used int and text. I display the date as a number but feel free to convert it to a date.

  • Download the example code (also listed below).

Listing of Example Code

// sqltest.c : Simple SQLite3 program in C by D. Bolton (C) 2013 http://cplus.about.com

#include <stdio.h>
#include "sqlite3.h"
#include <string.h>
#include <windows.h>

char * dbname="C:\\devstuff\\devstuff\\cplus\\tutorials\\c\\sqltest\\about.db";
char * sql = "select date,description, venue from events,venues where ideventtype = 0 and events.idvenue = venues.idvenue";

sqlite3 * db;
sqlite3_stmt *stmt;
char message[255];

int date;
char * description;
char * venue;

int main(int argc, char* argv[])
{
        /* open the database */
        int result=sqlite3_open(dbname,&db) ;
        if (result != SQLITE_OK) {
                printf("Failed to open database %s\n\r",sqlite3_errstr(result)) ;
                sqlite3_close(db) ;
                return 1;
        }
        printf("Opened db %s OK\n\r",dbname) ;

        /* prepare the sql, leave stmt ready for loop */
        result = sqlite3_prepare_v2(db, sql, strlen(sql)+1, &stmt, NULL) ;
        if (result != SQLITE_OK) {
                printf("Failed to prepare database %s\n\r",sqlite3_errstr(result)) ;
                sqlite3_close(db) ;
                return 2;
        }

        printf("SQL prepared ok\n\r") ;

        /* allocate memory for decsription and venue */
        description = (char *)malloc(100) ;
        venue = (char *)malloc(100) ;

        /* loop reading each row until step returns anything other than SQLITE_ROW */
        do {
                result = sqlite3_step (stmt) ;
                if (result == SQLITE_ROW) { /* can read data */
                         date = sqlite3_column_int(stmt,0) ;
                         strcpy(description, (char *)sqlite3_column_text(stmt,1)) ;
                         strcpy(venue, (char *)sqlite3_column_text(stmt,2)) ;
                         printf("On %d at %s for '%s' \n\r",date,venue,description) ;
                }
       } while (result == SQLITE_ROW) ;

    /* finish off */
        sqlite3_close(db) ;
        free(description) ;
        free(venue) ;
        return 0;
}

In the next tutorial I'll look at update, and insert sql and explain how to bind parameters.

  1. About.com
  2. Computing
  3. C / C++ / C#

©2014 About.com. All rights reserved.