Just another Tech blog from a forgetful mind

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

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

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:

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)

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

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

If batch is true we place the result row into a container to write to file later (all rows into a single file)

If batch is not true (default) we immediately write the row to a file (one file per row)

If output is true the result row is printed to stdout

Disconnect from database and clean up all handles

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