The
dbase
command is used to interact with database servers. While the command provides a generic, database-independent set of features, the actual interaction is performed via loadable database driver modules. These are either loaded explicitly (via the
dbx load
command) or automatically be referring a known database type name.
The
dbase
command provides the following subcommands:
dbase close all
dbase close ?dbhandle?...
Close open database connections held by the specified database access objects and destroy the connection manager objects. The handles passed to this command are no longer valid after the command has been executed.
The magic handle name all can be used to close all currently opened database connections.
dbase close all
The return value is the number of closed database objects. For the sake of consistency with other object commands, the command
dbase delete
is an alias to this command. Both commands do not delete any database on the server.
dbase colquery dbhandle sqlstatement ?tablehandle?
This command is a variant of the
dbase query
command. By default, the command returns a nested list of rows and columns. This command only returns the first result column, if any are produced, and omits the outer nesting level. This can make the processing of results easier. Example:
set smileslist [dbase colquery $dbhandle “select smiles from moltable”]
If a tareget table handle was supplied, the return value is a list of the table handle, the row count, and the column count, which is always one.
The command can also be accessed under the name columnquery .
dbase connect dbhandle
Establish a connection to the database server. An error is thrown if the connection does not succeed. This statement is primarily useful to verify the correctness of the attributes set by means of
dbase create
and
dbase set
commands. For the execution of database commands it is not required. In case a database connection was not yet established when communication with the server is required, an attempt to open the connection is made automatically.
If the dsn (data source name) attribute has been set, it has precedence over the connection parameters defined by the database host, port, database, user etc. attributes.
dbase create ?attribute value?...
Create a new database access object. Any number of database access objects can be in existence at the same time, and be connected to the same or different databases, potentially using different database drivers. If no attributes are specified, a default database object is created. Some of the default values can be modified via elements in the ::cactvs() control array:
The attributes which may be set by this command the same as in the
dbase set
command and explained there. The return value of the command is the database object handle, which is used to identify the object in all further operations.
dbase create dbtype mysql database samples host db3 user beaker password muppet
Note that this command only sets up the database interface configuration, but does not immediately open a connection. A connection to the database is only opened the first time there is a need to communication with the database server, of the
dbase connect
command is executed. Until then, it is for example possible to set additional parameters via the
dbase set
command which are used when the connection is finally established.
dbase disconnect dbhandle
Close the connection to the database established via a
dbase connect
command or implicitly by data retrieval commands. The interface object remains valid and can, potentially after a change of attributes, reconnect to a database.
In case the interface object was not yet connected, the command does nothing.
dbase dup dbhandle
Duplicate the attributes of an existing database interface object into a new object. The return value is the handle of the new interface object.
This command only copies the configuration options, but does not inherit the database connection, or any related state information. The new interface object is in the same state as if it were created via a
dbase create
statement with a complete set of attribute and value pairs.
dbase exec dbhandle sqlstatement ?tablehandle?
This command is a variant of the
dbase query
command. The difference is that any returned results are discarded and the return value is the current value of the
iserror
attribute. In case of severe errors. a
Tcl
error is generated.
dbase exists dbhandle ?database?
Test whether the specified database is visible via the current connection or not. If no database name is specified, the value of the database attribute of the interface object is used for the test. The return value is the boolean test result. If the connection cannot be established, an error is generated.
dbase flush ?dbhandle?
This command flushes the internal database caches globally or only those associated with the connection. The toolkit remembers certain information, such as database and table names, or database column types, in order to accelerate processing. In case the database content was modified by deleting, adding or altering tables, or full databases have been deleted, renamed, or created, it is advisable to use this command to make sure that the cached information does not become outdated and a source of error.
In circumstances where a database my be accessed via more than one connection, it is best to flush the caches globally, or on all connections which operate on that database if the exact set of affected interface objects is known. Extraneous flushing of the caches does not change any valid results, but can lead to performance degradation.
dbase get dbhandle attribute
Read a database interface object attribute. The list of attributes is explained in the paragraph on the
dbase set
subcommand.
set id [dbase get $dbhandle insertid]
dbase itemquery dbhandle sqlstatement ?tablehandle?
This command is a variant of the
dbase query
command. By default, the query command returns a nested list of rows and columns. This command only returns the first result item, from the first row and first column, if any are returned, and omits the standard two layers of list wrappers. This can make the processing of results easier. Example:
set size [dbase itemquery $dbhandle “select count(*) from moltable”]
If a table handle is specified as target, the return value is a list of the table handle, row count and column count.
dbase query dbhandle sqlstatement ?tablehandle|new?
Execute an
SQL
statement on the database server. The allowed
SQL
syntax is dependent on the capabilities of the connected server.
The default return value is, in the absence of the optional table handle argument, a nested list of rows and columns, with the rows as the outer list level. The maximum number of returned rows can be controlled by means of the maxrows interface object attribute. If table column data type information is available, the internally used Tcl result objects are matched to the column type for increased performance. Otherwise, the returned items are strings.
This command is not limited to the execution of SQL select statements. Any supported statement can be executed. In case it does not return a result tuples, an empty set is returned.
In case the optional target table handle argument is supplied, the result is directly stored in the specified table object. When the argument is present and explicitly set to an empty string, or the magic value
new
, a new table is created, which is automatically destroyed in case the command fails. An attempt is made to map existing table columns to the names of the database query result columns. In case no matching table object columns can be found, they are added automatically on the right with suitable data types, names, precision, width, and so on. Existing table columns which do not receive data from the result set are set to
NULL
values in the new rows. Existing table object rows are not deleted when the command is run. The retrieved rows from the database result set are appended. In this mode, the return value of the command is a list of the table handle, the number of rows and the number of columns of the table after the operation.
set th [table create]
lassign [dbase query $dbhandle \
“select smiles,name from moltable where logp between 5.0 and 6.0” $th] \
dummy nrows ncols]
dbase rowquery dbhandle sqlstatement ?tablehandle?
This command is a variant of the
query
command. By default, that subcommand returns a nested list of rows and columns. This command only returns the first result row, if any are received, and omits the column nesting level. This can make the processing of results easier. Example:
dbase rowquery $dbhandle “select smiles,weight from moltable where cas=’71-43-2’”
If a target table handle was supplied, the return value is a list of the table handle, the table row count (usually one, but in case the command did not return a value, zero) and the column count.
dbase set dbhandle ?attribute value?
Set one or more attributes of the database interface object. Not all attribute changes have an effect after the database connection has been established. Generally, attribute changes which would necessitate the closing and re-opening of the database connection to a different host, or with different access credentials, are ignored after the connection has become active. If such a change is needed, a
dbase reset
command should be issued, or the current interface object should be discarded and a new one created.
Some attributes are read-only. They are listed here nevertheless, because the
dbase get
command refers to this section.
The following attributes are currently supported:
::cactvs(default_database)
if it has not been set explicitly by means of
dbase create
or
dbase set
statements. In case of the Oracle interface, the value is a service/schema name from the Oracle
tnsnames.ora
configuration file, since Oracle does not have a simple concept of a database.This attribute is not the same as databases (plural).
This attribute is not the same as database (singular).
::cactvs(default_database_type)
. If a driver for the specified database type is not yet loaded, an attempt is made to auto-load it. The possible values for this attribute depend on the set of available database interface modules. Examples are
mysql
,
odbc
,
tds
(to connect to
MS SQL Server)
,
postgres
and
oracle
.
::cactvs(data_directory))
. The
DSN
string is reset when the
host, port, user, password
or
database
attributes are modified.
::cactvs(default_database_host)
.
dbase query
command variants honor this attribute on the client side. If this attribute is set to zero or a negative value (the default), no row limit applies.
NULL
database values in
Tcl
return results. By default, it is am empty string, but this is indistinguishable from zero-length strings and therefore it is sometimes useful to change it to something else, such as the string “NULL”.
::cactvs(default_database_options)
.
::cactvs(default_database_password)
.
dbase query/exec/rowquery/colquery/itemquery
commands are run, but there are also some conditions when implicitly assembled
SQL
commands are run which are also tracked here. Setting this attribute is possible, but pointless in normal script environments.
:cactvs(default_database_user),
which again by default is the same as the login user name.