how to set output mode in tcl script via sqlite3 driver

(1) By jarod (jarod.tian) on 2022-03-22 07:55:46 [link] [source]

Hi,

i'm working on a cgi script of tcl to talk to sqlite3. Here are what i have:

os: centos 7
sqlite3: 3.37.2
sqlite-tcl: 3.7.17
tcl: 8.5

I'd like to know how to set the output format, like 'csv or html', when doing the query. E.g.:

sqlite3 db ./data.db
db eval {.mode csv; select * from test}

The tcl compiler complains that there is no such command '.mode' at all. But I can use '.mode' in sqlite3 shell. So I want to know how to config it via sqlite-tcl driver. Because I want sqlite to output in html format in my cgi script.

Thanks!

(2) By Stephan Beal (stephan) on 2022-03-22 10:14:55 in reply to 1 [link] [source]

But I can use '.mode' in sqlite3 shell.

All of the dot-commands are specific to the sqlite3 shell application and are not part of libsqlite3 itself, so you'll need to generate the CSV yourself or use some 3rd-party code to do so. That topic has come up often in the forum, and a search for "CSV" might reveal some useful links to such code.

(3) By Richard Hipp (drh) on 2022-03-22 10:25:52 in reply to 1 [link] [source]

i'm working on a cgi script of tcl to talk to sqlite3.

I recommend that you use Wapp, compiled and statically linked against SQLite 3.38 and Tcl 8.7.

(4) By jarod (jarod.tian) on 2022-03-22 12:16:59 in reply to 2 [link] [source]

Thanks for your advice! Stephan.

BTW, I workaround it with the 'exec' command of tcl to call sqlite3 as external command:

set rst [exec echo $cmd | sqlite3 dbfile]

(5) By jarod (jarod.tian) on 2022-03-22 12:23:59 in reply to 3 [link] [source]

I recommend that you use Wapp, compiled and statically linked against SQLite 3.38 and Tcl 8.7.

Thanks Richard!

At the beginning of my fossil journey, I discovered Wapp. But I have no chance to study it since I know nothing about tcl/tk.

Recently, I found tcl is much more elegant than perl's syntax. The simplicity and consistency of language concept reminds me of the days playing with lisp. So I decided to re-write all my cgi script in tcl.

I'm really enjoying using tcl these days. So it's time to go back to the Wapp again.

(6) By Donal Fellows (dkfellows) on 2022-03-22 13:00:03 in reply to 1 [source]

When you do db eval {select * from test} in a language embedding (as opposed to the sqlite3 shell) then you get the raw rows; it's up to the caller to format them. I'd suggest using the csv package from Tcllib and doing something like:

  package require Tcl 8.6; # NOTE! For lmap
  package require sqlite3
  package require csv

  sqlite3 db ./data.db
  set first true
  db eval {select * from test} ary {
      # Print a header row; remove this if you don't need it
      if {$first} {
          puts [csv::join $ary(*)]
          set first false
      }
      puts [csv::join [lmap key $ary(*) {set ary($key}]]
  }

or otherwise, for old Tcl versions like you say you're using (not really recommended any more!)

  package require sqlite3
  package require csv

  sqlite3 db ./data.db
  db eval {select * from test} ary {
      # Print a header row; remove this if you don't need it
      if {$first} {
          puts [csv::join $ary(*)]
          set first false
      }
      set row {}
      foreach key $ary(*) {
          lappend row $ary($key)
      }
      puts [csv::join $row]
  }

The above code just writes the CSV to standard output; writing it to a file is left as an exercise.