/*
   Albert Danial
 
   Demonstrate extracting data from an SQLite database in C.

   Compile:
     gcc query.c -o query -I/usr/local/sqlite-3.2.0/include \
                 -L/usr/local/sqlite-3.2.0/lib -lsqlite3
   
 */
#include <sqlite3.h>
#include <stdio.h>

#define SQL_BLOCK_SIZE 1000
#define SQL_STR_SIZE    200
#define SQL_MAX_COLUMNS  20

static sqlite3 *sql_db; /* a global variable */

int print_col(sqlite3_stmt * pTableInfo, int col)
{
    switch (sqlite3_column_type(pTableInfo, col)) {
    case SQLITE_INTEGER:
        printf("%10d ",   sqlite3_column_int(pTableInfo, col));
        break;
    case SQLITE_FLOAT:
        printf("%12.6e ", sqlite3_column_double(pTableInfo, col));
        break;
    case SQLITE_TEXT:
        printf("%20s ",   sqlite3_column_text(pTableInfo, col));
        break;
    case SQLITE_BLOB:
        printf("<blob>");
        break;
    case SQLITE_NULL:
        printf("<NULL>");
        break;
    default:
        printf(" *Cannot determine SQLITE TYPE* col=%d ", col);
    }
    return 0;
}

main (int argc, char *argv[])
{
    char query[SQL_STR_SIZE];
    sqlite3      *db;
    sqlite3_stmt *plineInfo = 0;
    int   i, rc, nColumns;

    if (argc < 3) {
        printf("\nUsage:   %s <db file>  <query string>\n",argv[0]);
        printf("Perform the specified query.\n");
        exit(0);
    }

    if (sqlite3OsFileExists(argv[1])) {
        if (sqlite3_open(argv[1], &db) != SQLITE_OK) {
            fprintf(stderr, "Can't open database '%s'\n", argv[1]);
            sqlite3_close(db);
            exit(1);
        }
    } else {
        fprintf(stderr, "no such file: %s\n", argv[1]);
        exit(1);
    }

    rc = sqlite3_prepare(db, argv[2], -1, &plineInfo, 0);
    if (rc == SQLITE_OK) {
        nColumns = sqlite3_column_count(plineInfo);
        while ((rc = sqlite3_step(plineInfo)) == SQLITE_ROW) {
            for (i = 0; i < nColumns; ++i) {
                print_col(plineInfo, i);
            }
            printf("\n");
        }
    } else {
        printf("prepare statement failed\n");
    }

    sqlite3_close(sql_db);
}