Appendix G. Tcl Interface Reference

Appendix G. Tcl Interface Reference

This appendix lists the Tcl commands that are used to communicate with a SQLite database.

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-name either 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 .