nscerqueira Some examples of tcl and sqlite usage:
NOTE: the name of the database is opendb
package require sqlite3 sqlite3 opendb "database.db" ; # Open up an existing database or create a new empty one. set tableNames [opendb eval {SELECT tbl_name FROM sqlite_master}] puts $tableNames
opendb eval "SELECT * FROM $tableName LIMIT 1" x {set columnName $x(*)} puts $columnName
APE Note that this does not work if table is empty.
jnc You can also use the PRAGMA statement table_info(table_name) to retrieve column information. See http://sqlite.org/pragma.html for a list of all PRAGMA statements.
set columns [opendb eval "PRAGMA table_info($tableName)"] puts $columns
opendb eval "Select * FROM $i" values { set columnNames $values(*) set rowList {} foreach column $columnNames { lappend rowList $values($column) } puts $rowList }
KWizzz: Above example executes set columnNames $values(*) for each row. You might want to use this instead:
proc columnNames {db query} { # execute query with no actions in order to get column names $db eval $query columns break set h $columns(*) return $h } proc columnValues {columnNames {sep ";"}} { set r {} foreach name $columnNames { upvar $name value set value [string map {\" \"\"} $value] lappend r \"$value\" } set values [join $r $sep] return $values } set sql "Select * FROM $i" set h [columnNames opendb $sql] puts $h opendb eval $sql { puts [columnValues $h }
JOB: Anyone interested in some date arithmetics - the sqlite database can do it all! The classical days between date function the SQL way goes like this:
-- note: sqlite doesn't need select ... from dual SELECT julianday ( current_date ) - julianday( DATE('....-09-25'));
escargo 2012-01-31: Anybody have a clue on how to decipher the SQLite tables used by Firefox 3 bookmarks? I thought it might be easier to read the database directly rather than export HTML and then parse the result, but the structure is not documented anywhere I can find, and there seems to be no "how to" on how to navigate through all the tables.