Updated 2017-06-25 20:39:42 by EMJ

This is a piece of code I wrote when exporting database tables into text files according to one of the modes allowed by sqlite3.

The Tcl interface to sqlite3 is really excellent with a lot of options. However, I was not able to find a way of setting the "dot commands" which I needed to do to export files in the required format. So, my solution is to use the exec command to make an operating system level call to sqlite3 -- similar to calling it from the command line. If anyone knows how to use the interface, comments are greatly appreciated.
#!/bin/tclsh
package require sqlite3

## Uncomment and execute the following database
## creation commands only once at the beginning
## Then, you can comment them out again
# sqlite3 db1 mydb.sqlite3
# db1 eval {CREATE TABLE t1 (a int, b text)}
# db1 eval {INSERT INTO t1 VALUES(1,'hello')}
# db1 eval {INSERT INTO t1 VALUES(2,'goodbye')}
# db1 eval {INSERT INTO t1 VALUES(3,'howdy!')}

## I am fully parameterizing everything here even including the query
## This is to demonstrate the amount of flexibility we have
set mymode "column"
set mydb "mydb.sqlite3"
set myoutput "output.txt"
## The first row is zero
set beginning 1
set end 3
set mytable "t1"
set myquery "select * from $mytable limit [expr {$end-$beginning}] offset $beginning;"

## And finally execute the query and write it into the file
exec sqlite3 $mydb -$mymode $myquery > $myoutput

EMJ The "dot commands" are not part of Sqlite itself, they exist only in the command-line program sqlite3 as a convenience (it is written in C). They are therefore not in the Tcl interface - from its point of view, formatting the results of a query is your problem. However you could probably do the formatting in Tcl by getting ideas from https://www.sqlite.org/src/artifact?ln=on&name=210a913ad63f9f99 (from line 311).