Testing sqlite3

Features of sqlite3

Sqlite3 is an embedded database, i.e. it does not exists as as separate large program with associated persistent memory but can be linked to a application. In the simplest case (like my program) no driver is needed. A command line client called sqlite3 exists for Windows and Linux so that you are not forced to link the database code in your application. The main features of sqlite3:

• the complete code is laid down in a single C-program (sqlite3.c) and an associated include file (sqlite3.h>
• it supports a large subset of SQL (even BLOBs and transactions)
• the database is a normal portable file between operating systems
• drivers for integration in JAVA, Python etc exist (even ODBC)
• it offers fast access times
• people familiar with SQL can use it without much training

sqlite3 cannot be compared with the usual huge database products like Oracle or DB/2. It lacks features like maintainablity (by a database operator). It only has basic concurrent access (caution if running in multi thread programs or multi user applications). The usual database utilities like export and import programs are also very basic.

I have used SQLITE3 in a server program for access control in manufacturing plants and never had problems with it.

The test program

Here is my test program written in C++. I have tested it under Windows 7 and Linux/Ubuntu. Note these features:

• the program creates its own database called testdb in the actual directory
• it creates a table telefon in this database. An old table with the same name is dropped.
• it inserts some simple phonebook like entries
• it retrieves these entries

• sqlite3 can be installed: the a DLL(Windows) named sqlit3.dll and/or a library sqlite3.lib should exists
• If none of these libraries exist then search for the sqlite3 source code (standard C) on the Internet: you should find two source code files: sqlite3.c and sqlite3.h - that's all.

// module sqlite_test.cpp
// Needs module sqlite3.c under Windows and sqlite3.lib under Linux
// Tests access to SQlite database via the basic sqlite3 C-API calls.
// Generates or uses database with name "testdb" and table "telefon" in it.

// This same program works under Linux and Windows (is portable)
// Written by Dr.E.Huckert 03-2011
// Retested 05-2020 under Windows(GNU g++): OK
// Retested 05-2020 under Linux/Ubuntu: OK

// Compile  under Digital Mars C++ (Windows):
//    dmc -mn -Jm sqlite_test.cpp sqlite3.obj (compile error 05-2020)
// with GNU gcc (Windows 7):
//    g++ -c sqlite3.c
//    g++ -o sqlite_test.exe sqlite_test.cpp sqlite3.o
// Compile/Link(Linux): g++ -o sqlite_test sqlite_test.cpp -lsqlite3
// or (if lib sqlite3 ot installed): 
//   gcc -o sqlite3.c
//   g++ -o sqlite_test sqlite_test.cpp sqlite3.o -lpthread -ldl

#include <stdio.h>
#include <stdlib.h>

#include "sqlite3.h"

//   ----------------------------------------------------------------
int main(int argc, char *argv[])
{
  sqlite3 *conn;
  int ret;
  int nRows, nColumns, n, m;
  char **pResults;
  char *caddr;
  char *pError;
  //
  // Open the database file - note: a full or relative path is required!
  ret = sqlite3_open("./testdb", &conn);
  printf("result open ) for DB file testdb: %d\n", ret);
  if (ret != SQLITE_OK)
  {
    printf("DB open error - we exit\n");
    ::exit(1);
  }
  //
  // drop an oldtable "telefon" (if existing)
  ret = sqlite3_exec(conn, 
                     "drop table telefon", 
                      NULL, NULL, NULL);
  printf("result exec() for drop table telefon: %d\n", ret);
  //
  // (re)create this table "telefon"
  ret = sqlite3_exec(conn, 
         "create table telefon(vname varchar(30),name varchar(30),nummer varchar(20))", 
         NULL, NULL, NULL);
  printf("result exec() for create table telefon: %d\n", ret);
  if (ret != SQLITE_OK)
  {
    printf("ERROR create table statement - we exit\n");
    goto zurueck;
  }
  //
  // insert two data records in this table
  printf("Starting insert statements\n");
  ret = sqlite3_exec(conn, 
         "insert into telefon values('edgar','hickelt','06121/53797')", 
         NULL, NULL, NULL);
  printf("result exec() fuer insert: %d\n", ret);
  if (ret != SQLITE_OK)
  {
    printf("ERROR insert statement 1 - we exit\n");
    goto zurueck;
  }
  ret = sqlite3_exec(conn, 
         "insert into telefon values('gisela','duckel','06121/53797')", 
         NULL, NULL, NULL);
  printf(" exec() fuer insert: %d\n", ret);
  if (ret != SQLITE_OK)
  {
    printf("ERROR insert statement 2 - we exit\n");
    goto zurueck;
  }
  //
  // retrieve (select) these two records again
  printf("Starting select statement\n");
  ret = sqlite3_get_table(conn, 
                          "select name,nummer from telefon",
                          &pResults,
                          &nRows, 
                          &nColumns,
                          &pError);
  printf("result get_table(): %d\n", ret);
  printf("result get_table(): nRows=%d\n", nRows);
  printf("result get_table(): nColumns%d\n", nColumns);
  if (ret == SQLITE_OK)
  {
    // show the results on the console
    // Note: the first row is the column names!
    int i = 0;
    for (n=0; n < nColumns; n++)
    {
      for (m=0; m < (nRows + 1); m++)
      {
        caddr = (char *)(pResults[i++]);
        if (caddr == NULL) continue;
        printf("\t%s\n", caddr);
      }
    }
  }
  //
  sqlite3_free_table(pResults);
  printf("Everything is OK\n");
  //
  zurueck:
  ret = sqlite3_close(conn);
  printf("result close(): %d\n", ret);
  //
  return 0;
}   // end main()

Copyright for all images, texts and software on this page: Dr. E. Huckert