Tag Archives: Sqlite

My sqltofile cheat sheet

Ever wanted to go directly from an sql to xml files. I do 🙂 Here is a little tool I built a while back. It takes an xml (or any character structure) and an sql and returns one file per row returned result.
For example:

sqltofile -t "<?xml version='1.0'?>%user%%timestamp%" -s "SELECT user, timestamp FROM user" -h mysqlite.db

Will output (one file per row):

row0:
<?xml version='1.0'?>niklas1363615417
row1:
<?xml version='1.0'?>miew1363615493
row2:
<?xml version='1.0'?>emma1363615604
...

The words surrounded with % – signs in the -t argument above gets substituted by the values in the columns with the same name (minus the % -sign ).

Options to use:
-t Is the template string that contains the substitution variables surrounded by % – signs
-s This is the sql to run agains the database (* can be used)
-h Is the filename of the sqlite3 database to run the query agains
-o Outputs the result to stdout (optional)
-b Writes all lines to one file called row0 (optional)

You can find the source and an brief explanation of sqltofile here

From SQL to text file using my own sqltofile tool

sqltofile is a little tool I created a while back to solve the problem of creating XML files fast and efficient from the result of a SQL query. It is build in C++ and currently supports Sqlite3. In the source there is also an Interface class for building support for more databases in the future. I will here explain briefly how it is built

We start with the interface class

#ifndef DATABASEINTERFACE_H
#define DATABASEINTERFACE_H

#include <vector>
#include <string>

using namespace std;

class DatabaseInterface {
			
   public:		
      virtual ~DatabaseInterface(){}
		
      virtual int connect(string &filename, string &user, string &password) = 0;
      virtual vector<vector<pair<string, string> > > query(string &sql) = 0;		
      virtual int disconnect() = 0;

};
#endif

The connect function takes hostname/filename, username and password as arguments. This function should be called first to establish a connection to the database
The query function runs the SQL against the database and returns a vector of vectors of pairs where the ‘first’ member variable contains the column name och the ‘second’ has the column value
The disconnect function is to be called last to clean up and disconnect from the database

And here is the main program

#include <string>
#include <stdio.h>
#include <stdlib.h>
#include <fstream>
#include <iostream>

#include <getopt.h>

#include "database.h"

// Forward declarations
void findAndReplace(string &subject, const string &search, const string &replace);
void saveToFile(string str, int row);
void printUsage();

int main(int argc, char const *argv[]) {
	
	// Variables
	string searchchr = "%";
	string searchstr = "";
	string batchstr = "";
	string tmplstr = "";
	string sql = "";
	string host = "";
	string user = "";
	string password = "";
	string result = "";
	
	bool batch = false;
	bool output = false;
	
	int option = 0;
	int conn;

	// Objects
	Database *db = new Database();
	vector<vector<pair<string, string> > > data;
	
	if (argc < 3) {
		cout << "Too few arguments" << endl;
		printUsage();
		exit(EXIT_FAILURE);
	}

	// Handle arguments
	while ((option = getopt(argc, (char **) argv, "bot:s:h:")) != -1) {		
        switch (option) {
            case 'b' : batch = true;
                break;
            case 'o' : output = true;
                break;
            case 't' : tmplstr = optarg; 
                break;
            case 's' : sql = optarg;
                break;
            case 'h' : host = optarg; 
            	break;	   
            default: printUsage(); 
                exit(EXIT_FAILURE);
        }
    }
        // Connect to database	
	conn = db->connect(host, user, password);

        // Query database
	data = db->query(sql);

	// For every result row
	for (size_t n = 0; n < data.size(); n++) {
		result = tmplstr;
		// For every column in result row
		for (size_t m = 0; m < data[n].size(); m++) {
			searchstr = searchchr + data[n][m].first + searchchr;		
			findAndReplace(result, searchstr, data[n][m].second);				
		}

		// If batch save row to string (newline at end)
		if (batch) {			
			batchstr += result;
			batchstr += "\n";
		}
		else { // If not batch write to new file			
			saveToFile(result, n);
		}

		// If output print to screen
		if (output) {
			cout << result << endl;
		}
	}
        // If batch mode and we have recieved any rows - write to file
	if (batch && batchstr.length() > 0) {
		saveToFile(batchstr, 0);
	}
       
        // Disconnect from database and clean up
	db->disconnect();
		
	return 0;
}

First we have a bunch of variable declarations. I’m mostly using the std:string here for easy string handling. Then I take care of all the arguments using the getopt function – all pretty basic stuff. Now we get to the core of the program. It works something like this:

  1. Call connect() with host/filename, user and password details. Since my database class is made for Sqlite the connect function calls sqlite3_open_v2() with the SQLITE_OPEN_READONLY option (sqltofile only supports SELECT – statements)
  2. Call query() with our SQL query. Results are returned as an vector of vectors of pairs. This might need some more explanations. I use std:pair to hold the column name (first member variable) and value (second member variable). Every pair is then put into a vector representing a result row, and finally the result row is placed into another vector representing the whole result set
  3. Loop thought the result set and perform variable substitutions. In the templete string there should be “variables” surrounded by % -signs. These get substitutet with the value that belongs to the column with the same name as the “variable” without the % – sign
  4. If batch is true we place the result row into a container to write to file later (all rows into a single file)
  5. If batch is not true (default) we immediately write the row to a file (one file per row)
  6. If output is true the result row is printed to stdout
  7. Disconnect from database and clean up all handles

That was a brief description of sqltofile. The full source code can be found here

Tested on OSX 10.7.5 with gcc version 4.2.1 and Sqlite 3.7.11

How to access a Sqlite database in C

I’m here going to demonstrate a simple connection and query to a SQLite database using C.

First you need to download and build (if necessary) the SQLite code so you get the sqlite2.so (or sqlite3.lib on Windows) and the necessary header and c files (sqlite3.h, sqlite3.c). I will not go into how this is done here.

After the environment is setup we start with connecting to the SQLite database:

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

int main(int argc, char const *argv[]) {
        
    sqlite3 *conn; // Database struct handle
    const char *dbname = "test.db"; // Database file name
    int dbhandle; // Resource handle

    // Open database
    dbhandle = sqlite3_open_v2(dbname, &conn, SQLITE_OPEN_READONLY, NULL);

    if(dbhandle == SQLITE_OK) {
        fprintf(stdout, "Database open\n");
    }
    else {		
        fprintf(stderr, "Could not open database: %s\n", sqlite3_errmsg(conn) );
        return 1;
   }

   // Cleanup
   sqlite3_close(conn);

   return 0;

}

In short this code will do the following:
1. Create necessary variables for connection, resource handle and the database file name
2. Call sqlite3_open_v2() to open a read only connection to the SQLite database (test.db)
3. Check if we succeeded and report result to console
4. Cleanup connection object

I choose here to use the sqlite3_open_v2() function due to two main reasons:
1. I don’t want to create the database if it does not exist. This is the default behavior of sqlite3_open()
2. I want to be able to open the database as readonly (SQLITE_OPEN_READONLY) or as readwrite (SQLITE_OPEN_READWRITE)

Now we are also going to query the database and present the result. We start with adding a callback function to handle the output like this:

static int callback(void *NotUsed, int argc, char **argv, char **colname){

	NotUsed = 0;
	int i;
	for (i = 0; i < argc; i++){

		printf("%s = %s\n", colname[i], argv[i] ? argv[i] : "NULL");
	}
	printf("\n");
	return 0;
}

This function is going to be called from sqlite2_exec() function later. Once per row of results from query. It will print a list with "column name = cell value" rows

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

int main(int argc, char const *argv[]) {
    
    // Connection variables    
    sqlite3 *conn; // Database struct handle
    const char *dbname = "test.db"; // Database file name
    int dbhandle; 

    // Query variables
    int stmthandle;  // Statement handel
    char *errorMsg = 0; // Error message holder
    const char *query = "SELECT * FROM user";  // Query to run 

    // Open database
    dbhandle = sqlite3_open_v2(dbname, &conn, SQLITE_OPEN_READONLY, NULL);

    if(dbhandle == SQLITE_OK) {
        fprintf(stdout, "Database open\n");
    }
    else {		
        fprintf(stderr, "Could not open database: %s\n", sqlite3_errmsg(conn) );
        return 1;
    }
   
    // Execute query
    stmthandle = sqlite3_exec(conn, query, callback, 0, &errorMsg);

    if (stmthandle == SQLITE_OK) {
        fprintf(stdout, "Query successful\n");
    }
    else {
         fprintf(stderr, "Could not run query: %s\n", errorMsg );
         return 1;
    }

    // Cleanup
    sqlite3_close(conn);
    
    return 0;

}

I have now added the sqlite3_exec() wrapper function (it handles sqlite3_prepare_v2(), sqlite3_step() and sqlite3_finalize() all into one nifty function) and the necessary query variables (statement handle, error message holder and SQL statement).

The sqlite3_exec() function takes the following input:
1. Connection handle to the database
2. SQL query
3. Name of callback function to call for every row of results from database
4. First argument to callback function (the NotUsed argument in the callback function above)
5. Error message holder to return error messages in (if any)

After these arguments have all been satisfied, sqlite2_exec() will call the callback function with the results. Here is an example of output:

Database open
id = 1
login = anders
password = dator123

id = 2
login = niklas
password = dator123

id = 3
login = miew
password = dator123

Query successful

and that is it. We now have a small and simple example of how you connect and query a SQLite database using C.

NOTE: When compiling do not forget to add the -lsqlite3 argument to gcc:

 
gcc test.c -o test -lsqlite3 -Wall

This has been tested on SQLite 3.7.15.2, gcc 4.2 and OSX 10.7.5