SourceForge Logo 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

  1. TABLE OF CONTENTS