// module sqlite_test.cpp
// Tests access to SQlite database via the basic sqlite3 C-API calls.
// Compile (Linux): g++ -o sqlite_test sqlite_test.cpp -l sqlite3
// The same program works also with little or no changes under Windows
// Written by Dr.E.Huckert 03-2011 - private Copyright

#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!
  // Note: SQLite code is linked to the application - there is no
  // "database connection" as with Oracle or SQL-Server. The database
  // is a simple file. You can create database via the command line
  // interface sqlite3!
  ret = sqlite3_open("./testdb", &conn);
  printf("result open(): %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: %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: %d\n", ret);
  //
  // insert two data records in this table
  ret = sqlite3_exec(conn, 
         "insert into telefon values('edgar','hickelt','06121/53797')", 
         NULL, NULL, NULL);
  printf("result exec() fuer insert: %d\n", ret);
  ret = sqlite3_exec(conn, 
         "insert into telefon values('gisela','duckel','06121/53797')", 
         NULL, NULL, NULL);
  printf(" exec() fuer insert: %d\n", ret);
  //
  // retrieve (select) these two records again
  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);
  //
  ret = sqlite3_close(conn);
  printf("result close(): %d\n", ret);
  //
  return 0;
}   // end main()