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?...
db.close()
Dbase.Close(?dbref/dbhandle?,...)
Dbase.Close(“all”)
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 columnquery dbhandle sqlstatement ?tablehandle|new?
db.columnquery(query=,?table=?)
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 table handle is specified as target, or a table was created, the return value is the table handle or reference.
The command can also be accessed under the shortened name colquery .
dbase connect dbhandle
db.connect()
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.
The command returns the original database connection handle or reference.
dbase create ?attribute value?...
dbase create dict
Dbase(?attribute,value?,...)
Dbase(dict)
Dbase.Create(?attribute,value?,...)
Dbase.Create(dict)
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
db.disconnect()
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.
The command returns the original database connection handle or reference.
dbase dup dbhandle
db.dup()
Duplicate the attributes of an existing database interface object into a new object. The return value is the handle or reference 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?
db.exec(query=,?table=?)
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 boolean value of the
iserror
database connection attribute. In case of severe errors. a
Tcl
or
Python
error is generated.
dbase exists dbhandle ?database?
db.exists(?database=?)
Dbase.Exists(connection=,?database=?)
Test whether the specified database is visible via the current connection or not. If no database name is specified, the current 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?
db.flush()
Dbase.Flush()
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
db.get(attribute)
db.attribute
db[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|new?
db.itemquery(query=,?table=?
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, or a table was created, the return value is the table handle or reference.
dbase list ?pattern?
Dbase.List(?pattern=?)
Return a list of all currently defined database connector handles.
dbase query dbhandle sqlstatement ?tablehandle|new?
db.query(query=,?table=?)
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 or Python 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 data 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 to 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 the table handle.
set th [table create]
dbase query $dbhandle \
“select smiles,name from moltable where logp between 5.0 and 6.0” $th
set nrows [table get $th nrows]
For convenient handling of single-row, single-column and single-item queries, there are the command variants
rowquery
,
columnquery
and
itemquery
which omit one or two levels of list nesting, and only retrieve a single row, column or item, ignoring any additional data returned by the database.
dbase matrixquery
is an alias for this command which emphasizes the relationship of this command to the
rowquery
,
columnquery
and
itemquery
command variants.
Earlier versions of the toolkit returned not just the table handle, but a list of the handle and the row and column counts if the query result data destination was a new or existing table. This was changed in version 3.4.6.18 because it was not found useful for practical scripting.
dbase queryloop dbhandle sqlstatement varname ?offset? ?maxrows? body
Execute a loop over a result set returned by the
SQL
query. For every result row, the row values are stored in a list variable and then the body code is executed. The row variable is locally visible from within the body code. Within the body code, standard loop control statements like
continue
and
break
can be used in the normal fashion. Optionally, an offset into the result set may be specified (default 0) and a maximum number of iterations (default -1, no limit).
dbase queryloop $dh “select * from molecules” row { puts $row}
dbase read dbhandle ?sqlfile?...
db.read(?sqlfile?,...)
Open the specified file(s) and extract
SQL
commands one by one. Every
SQL
command is then processed by the equivalent of a
dbase exec
statement. Processing stops if an error is encountered.
Individual
SQL
statements in the file may span multiple lines, and the file may contain empty lines or
SQL
-compatible comments, which are skipped. Extended $-quoting (as in nested
$a$
vs.
$b$
sections) is currently not handled as explicit quoting which temporarily suspends the detection of the end of a statement. Only standard single-character quoting and $$ quoting (as well as their escaping within quoted sections) is explicitly parsed, so extended quoting must only be used within standard-quoted sections if their contents could be misconstrued as containing statement ends.
The command returns the original database connection handle or reference.
Dbase.Ref(dbhandle)
Python -only method to get a reference of the connector from its handle.
dbase rowquery dbhandle sqlstatement ?tablehandle|new?
db.rowquery(query=,?table=?)
This command is a variant of the
dbase
query
command. By default, that command 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 table handle is specified as target, or a table was created, the return value is the table handle or reference.
dbase set dbhandle ?attribute value?
dbase set dbhandle dictionary
db.set(?attribute,value?,...)
db.set(dict)
db.attribute = value
db[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/exec/rowquery/columnquery/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 not very useful in normal script environments.
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”. For
Python
,
None
values are always used.
::cactvs(default_database_options)
.
::cactvs(default_database_password)
.
:cactvs(default_database_user),
which again by default is the same as the login user name.