The Tcl Library

The Tcl library for SQLite is called libtclsqlite.so on Unix platforms and sqlite.dll on Windows systems. The library file should reside in a location that your scripts will be able to load from. Typically this would be a subdirectory of /usr/share/tcl on Unix or C:\tcl\lib on Windows.

In this appendix, commands from the Tcl library are shown first, followed by the explanation.

 package require sqlite 

Imports the sqlite package into a Tcl script.

Opening and Closing a Database

 sqlite dbcmd database-name 

Opens a database called database-nameeither from the current directory or referenced via a relative or absolute pathcreating it if it does not already exist. On success, a new command called dbcmd is registered in Tcl, upon which the methods described in the rest of this appendix can be applied.

 dbcmd close 

Closes the database connection and destroys dbcmd.

Executing a Query

 dbcmd eval query 

Causes SQLite to execute the given query and returns the entire data set fetched as a single list.

 dbcmd eval query array { code-block } 

As SQLite executes the query, for each row fetched an element in array is created for every column returned by the query and the commands in code-block are executed.

Additionally the data types of the returned columns are stored as elements named typeof:column-name in array, and the list of columns returned can be found in array(*).

 dbcmd eval query {} { code-block } 

If the empty string is used in place of array, code-block is still executed once for each row in the dataset; however, the fetched columns are stored to scalar variables with the same name as their respective columns.

Convenience Functions

 dbcmd onecolumn query 

Causes SQLite to execute the given query as eval; however, it returns only the first column from the first row of the dataset.

Finding Information About a Query

 dbcmd last_insert_rowid 

Returns the most recently assigned auto-incrementing value of an INTEGER PRIMARY KEY field following an INSERT operation on dbcmd.

 dbcmd changes 

Returns the number of rows affected by the most recent UPDATE or DELETE operation on dbcmd or the number of rows inserted by an INSERT statement.

Checking SQL Statements

 dbcmd complete query 

Returns true if a complete SQL statement is provided, that is, the statement ends with a semicolon in the appropriate place. Returns false if more characters are required to complete the statement.

Dealing with Locked Database Files

 dbcmd timeout ms 

Specifies the duration in milliseconds for which SQLite should wait for a lock to clear on the database file when performing a write operation. The default value is zero, meaning it will not wait or retry if the file is locked.

 dbcmd busy callback 

Specifies a callback function to be executed in the event that a database lock cannot be obtained for a write operation. The callback should return zero if you want SQLite to continue trying to get a lock, or non-zero to interrupt the SQL statement.

Error Reporting

 dbcmd errorcode 

Returns the numeric error code that resulted from the most recent SQLite operation. The full list of error code values can be found in Appendix E, "C/C++ Interface Reference."

Finding Information About SQLite

The sqlite_version function will return the version of the tclsqlite library in use.

 sqlite> select sqlite_version(*); 2.8.13 

Custom Functions (UDF)

 dbcmd function tcl-func sql-func 

Registers a user-defined SQL function called sql-func that executes the Tcl code in the function with the name tcl-func. The function is available only within that instance of dbcmd.