Download
Table Of Contents
tcldbi n 0.4 Tcl "The Tcl DBI interface"
COMMANDS
The dbi::dbi extension provides access to the 'C'
libdbi generic database interface
library. Currently it has drivers for
sqlite, mysql, msql and postgresql, and soon oracle databases.
Because this extension is mostly a thin wrapper around libdbi,
the documentation here is correspondingly thin.
Instead, where possible the specific libdbi API calls used
in each command are identified with a (USES: ...) footnote.
The libdbi documentation
should be referred to for specific details on functionality.
The dbi::dbi command supports the following command arguments: connections, configure, debug, driver, initialize, new, and version.
dbi::dbi configure ?options ...?
Set/get configuration options.
# Reset the error counter.
set x [dbi::dbi conf -errorcount 0]
dbi::dbi connections
Return a list of all active handle commands.
set curs [dbi::dbi connections]
dbi::dbi debug ?INT?
Set get the debug level.
# Turn debugging off.
set x [dbi::dbi debug 0]
dbi::dbi initialize ?path?
Initialize libdbi via dbi_initialize. Note that it is normally not necessary
to use this unless we wish to specify a directory for drivers different
than the default.
(USES: dbi_initialize)
dbi::dbi initialize /usr/local/lib/dbd
dbi::dbi new DRIVER ?options ...?
Creates and returns a database handle command (dbh)
using the driver specified by DRIVER.
If any options are provided after DRIVER, they are passed in
to the driver and then
the connection is automatically opened for you. With no additional
parameters, the user manually sets options and then
issues an explicit $dbh open with the returned handle.
(USES: dbi_conn_new)
(USES: dbi_conn_connect)
set dbh [dbi::dbi new sqlite dbname mydb.db sqlite_dbdir /tmp]
dbi::dbi validate STR TYPE ATTRIB
Validate that a string matches the type/attrib as returned
from eval. Generates a Tcl
error if there is a mismatch.
This can be used to validate user input matches a
tables fields, without having send an insert to the DB.
if {[catch {dbi::dbi validate $str integer long}]} {
TellUser ...
}
dbi::dbi version
Return the version of libdbi.
(USES: dbi_version)
set ver [dbi::dbi version]
DATABASE HANDLE COMMANDS
A database handle ( $dbh) is created using dbi::dbi new.
OPTIONS
The following options can be queried or set via the configure subcommand.
-opencursors : Number of open cursors.
-numqueries : Number of queries issued.
-numerrors : Number of errors seen.
-open : True if an open has been issued (READONLY).
Database Handle ( $dbh) Subcommands
The following DB handle commands are supported:
capabilities, close, configure, cursors, databases, dbname, driver, errorhandler, eval, evalnull, query, querynull, option, open and tables.
$dbh close
Close the connection and destroy DB handle.
(USES: dbi_conn_close)
# Shutdown connection to database.
$dbh close
$dbh configure ?options ...?
Set/get options.
set conf [$dbh conf]
$dbh cursors
Return the list of open cursors or dbh handles.
set curs [$dbh cursors]
$dbh databases ?PATTERN?
Return the list of databases.
(USES: dbi_conn_get_table_list)
$dbh dbname
Set/get the name of the current database.
(USES: dbi_conn_select_db)
$dbh dbname backupdb
$dbh driver ?options ...?
Retrieve driver specific information from the current database driver.
The following driver subcommands are supported:
capability, date_compiled, description, filename, is_reserved_word, list, maintainer, name, quote_string, url, validate, and version.
$dbh driver capability
Return list of capabilities.
(USES: dbi_driver_get_capabilities)
set cap [$dbh driver capability]
$dbh driver date_compiled
Return date compiled.
(USES: dbi_driver_get_date_compiled)
set date [$dbh driver date_compiled]
$dbh driver description
Return description.
(USES: dbi_driver_get_description)
set desc [$dbh driver description]
$dbh driver filename
Return filename of driver.
(USES: dbi_driver_get_filename)
set file [$dbh driver filename]
$dbh driver is_reserved_word WORD
Return 1 if is a reserved word.
(USES: dbi_driver_get_is_reserved_word)
set rsrv [$dbh driver is_reserved_word SELECT]
$dbh driver list
Return list of all available drivers.
(USES: dbi_driver_get_name)
set dlst [$dbh driver list]
$dbh driver maintainer
Return name of maintainer.
(USES: dbi_driver_get_maintainer)
set god [$dbh driver maintainer]
$dbh driver name
Return the name of the driver.
set drvr [$dbh driver name]
$dbh driver quote_string STRING
Quote a string.
(USES: dbi_driver_get_quote_string)
set str [$dbh driver quote_string $string]
$dbh driver url
Return url of maintainer.
(USES: dbi_driver_get_url)
set url [$dbh driver url]
$dbh errorhandler ?PROC?
Set/get an error handler, a Tcl command to be called upon error.
(USES: dbi_conn_error_handler)
$dbh eval QUERY ?-titles VAR -types VAR -attrs VAR -maxrows N?
Evaluate a query and return all row results in one shot.
Unlike query, eval does not create a dbh cursor,
but instead returns a Tcl list, where each element in the list
is a list of the elements in a single row.
The -titles option specifies the name of a variable in the
callers scope to assign the list of column titles to.
Similarly for -attrs and -types. The -maxrows option
can be used to limit the number of rows comming back.
(USES: dbi_conn_query)
(USES: dbi_result_first_row)
(USES: dbi_result_next_row)
(USES: dbi_result_get_field_*)
set r1 [$db eval "select * from tbl1"]
set r2 [$db eval "select * from tbl2" -titles ttls -types typs -attrib atrs]
$dbh evalnull QUERY ?-titles VAR -types VAR -attrs VAR -maxrows N?
Like eval but handles embedded nulls in a query.
(USES: dbi_conn_query_null)
set r1 [$db evalnull "select * from $bindata"]
$dbh open
Open a connection that was create via dbi::dbi new without
parameters. This alternative approach
allows using the option set command before opening
a connection, thus providing
manual parameter setting before a connection is opened.
set dbh [dbi::dbi new sqlite]
$dbh option set dbname mydb.db
$dbh option set sqlite_dbdir /tmp
$dbh open
$dbh option ?options ...?
The following option subcommands are supported:
"get", "get_numeric", "clear", "clearall",
"names", "set", and "set_numeric".
$dbh option get ?KEY?
Retrieves the string value of the specified option set for a connection.
(USES: dbi_conn_get_option)
set dbname [$dbh option get dbname]
$dbh option get_numeric ?KEY?
Retrieves the integer value of the specified option set for a connection.
(USES: dbi_conn_get_option_numeric)
set max [$dbh option get_numeric maxusers]
$dbh option clear ?KEY?
Removes the target option setting from a connection.
(USES: dbi_conn_clear_option)
$dbh option clear dbname
$dbh option clearall
Clear all options.
(USES: dbi_conn_clear_options)
$dbh option clearall
$dbh option names
Return list of all option names.
(USES: dbi_conn_get_option_list)
set names [$dbh option names]
$dbh option set ?KEY VALUE?
Sets a specified connection option to a string value.
(USES: dbi_conn_set_option)
$dbh option set dbname new.db
$dbh option set_numeric ?KEY VALUE?
Sets a specified connection option to a numeric value.
(USES: dbi_conn_set_option_numeric)
$dbh option set_numeric numusers 1
$dbh query QUERY
Evaluate a query and returns a $cur cursor command.
See the Query Cursor Command section for more details.
(USES: dbi_conn_query)
set cur [$dbh querynull $binaryquery]
set row [$cur first_row]
..
$cur free
$dbh querynull QUERY
Like query but handles embedded nulls in a query.
(USES: dbi_conn_query_null)
set cur [$dbh querynull $binaryquery]
$dbh tables ?PATTERN?
Return the list of all tables in the database.
set tbls [$dbh tables T*]
QUERY CURSOR COMMANDS
A query cursor ( $cur) is created as a result of using the query
command.
The following cursor subcommands are supported:
affected_rows, attributes, conf, current, first_row, free, next_row, titles, numrows, prev_row, seek_row, and types.
$cur affected_rows
Returns the number of rows in the specified result set that were actually modified.
(USES: dbi_result_get_numrows_affected)
set ar [$cur affected_rows]
$cur attributes
Returns the target field's data type attributes in the specified range
(USES: dbi_result_get_field_attribs)
set atr [$cur attributes]
$cur conf ?options ...?
Set/get configuration information for cursor.
set conf [$cur conf]
$cur current
Return the number of the current row (The first is 1).
set n [$cur current]
$cur first_row
Return the data for the first row in a result set.
(USES: dbi_result_get_first_row)
set row [$cur first_row]
$cur free
Frees the cursor and deletes the $cur command.
(USES: dbi_result_free)
$cur free
$cur next_row
Return the data for the next row in a result set.
(USES: dbi_result_get_next_row)
set data [$cur next_row]
$cur numrows
Get the number of rows. May not be valid until after first row if fetched.
(USES: dbi_result_get_numrows)
set n [$cur numrows]
$cur prev_row
Return the data for the previous row in a result set.
(USES: dbi_result_get_prev_row)
set data [$cur prev_row]
$cur seek_row N
Return the data for the given row in a result set.
(USES: dbi_result_get_seek_row)
set data [$cur seek 10]
$cur titles
Return titles for the columns.
(USES: dbi_result_get_field_name)
set ttl [$cur titles]
$cur types
Return titles for the columns.
(USES: dbi_result_get_numrows_affected)
set typ [$cur types]
TABLE OF CONTENTS
- TABLE OF CONTENTS