DIO — Database Interface Objects
Synopsis
::DIO::handle interface ?objectName? (-option | option | -option | option | ...)
Description
DIO is designed to be a generic,
object-oriented interface to SQL databases. Its main goal
is to be as generic as possible, but since not all SQL
databases support the exact same syntaxes, keeping code
generic between databases is left to the abilities of the
programmer. DIO simply provides a way to keep the Tcl
interface generic.
interface - The name of the database
interface. Currently supported interfaces are
Postgresql and Mysql.
If objectName is specified, DIO creates
an object of that name. If there is no
objectName given, DIO will automatically
generate a unique object ID
Options
-host ?hostname?
The hostname of the computer to connect to. If none
is given, DIO assumes the local host.
-port portNumber
The port number to connect to on hostname.
-user username
The username you wish to login to the server as.
-pass password
The password to login to the server with.
-db database
The name of the database to connect to.
-table tableName
The default table to use when using built-in commands
for storing and fetching.
-keyfield keyFieldname
The default field to use as the primary key when using
built-in commands for storing and fetching.
-autokey 1 | 0
If this option is set to 1, DIO will attempt to
determine an automatic key for
keyField when storing and fetching.
In most databases, this requires that the
sequence also be specified. In the
case of MySQL, where sequences do not exist, autokey
must be used in conjunction with a table which has a
field specified as AUTO.
-sequence sequenceName
If DIO is automatically generating keys, it will use
this sequence as a means to gain a unique number for
the stored key.
DIO Object Commands
objectName array request
Execute request as a SQL query and
create an array from the first record found. The
array is set with the fields of the table and the
values of the record found.
objectName autokey (value | boolean)
Return the current autokey value. If
value is specified, it sets a new
value for the autokey option.
objectName close
Close the current database connection. This command is
automatically called when the DIO object is destroyed.
objectName db ?value?
Return the current database. If
value is specified, it sets a new
value for the database. In most cases, the DIO object
will automatically connect to the new database when
this option is changed.
objectName delete key (-option | option | ...)
Delete a record from the database where the primary
key matches key.
objectName destroy
Destroy the DIO object.
objectName errorinfo ?value?
errorinfo contains the value of
the last error, if any, to occur while executing a
request. When a request fails for any reason, this
variable is filled with the error message from the SQL
interface package.
objectName exec request
Execute
request as an SQL query.
When the exec command is called, the query is
executed, and a DIO result object is returned. From
there, the result object can be used to obtain
information about the query status and records in a
generic way. See
Result
Object Commands
objectName fetch key arrayName (-option | option | ...)
Fetch a record from the database where the primary key
matches key and store the result in
an array called arrayName.
objectName host ?value?
Return the current host value. If
value is specified, it sets a new
value for the host.
objectName keyfield ?value?
Return the current keyfield. If
value is specified, it sets a new
value for the keyfield.
objectName keys ?pattern? (-option | option | ...)
Return a list of keys in the database. If
pattern is specified, only the keys
matching will be returned.
objectName lastkey
Return the last key that was used from
sequence. If sequence has not been
specified, this command returns an empty string.
objectName list request
Execute request as a SQL query and
return a list of the first column of each record
found.
objectName nextkey
Increment sequence and return the
next key to be used. If sequence has not been
specified, this command returns an empty
string.
objectName open
Open the connection to the current database. This
command is automatically called from any command which
accesses the database.
objectName pass ?value?
Return the current pass value. If
value is specified, it sets a new
value for the password.
objectName port ?value?
Return the current port value. If value is
specified, it sets a new value for the port.
objectName sequence ?value?
Return the current sequence value. If value is
specified, it sets a new value for the sequence.
objectName store arrayName (-option | option | ...)
Store arrayName in the database.
objectName string request
Execute request as a SQL query and
return a string containing the first record
found.
objectName table ?value?
Return the current table. If
value is specified, it sets a new
value for the table.
objectName user ?value?
Return the current user value. If
value is specified, it sets a new
value for the user.
Result Object Commands
resultObj autocache ?value?
Return the current autocache value. If
value is specified, it sets a new
value for autocache.
If autocache is true, the result object will
automatically cache rows as you use them. This means
that the first time you execute a forall command, each
row is being cached in the result object itself and
will no longer need to access the SQL result.
Default is true.
resultObj cache
Cache the results of the current SQL result in the
result object itself. This means that even if the
database connection is closed and all the results of
the DIO object are lost, this result object will still
maintain a cached copy of its records.
resultObj errorcode ?value?
Return the current errorcode value. If value
is specified, it sets a new value for errorcode.
errorcode contains the current code from the
SQL database which specifies the result of the query
statement which created this object. This variable
can be used to determine the success or failure of a
query.
resultObj errorinfo ?value?
Return the current errorinfo value. If value
is specified, it sets a new value for errorinfo.
If an error occurred during the SQL query, DIO
attempts to set the value of errorinfo to the
resulting error message.
resultObj fields ?value?
Return the current fields value. If
value is specified, it sets a new
value for fields.
fields contains the list of fields
used in this query. The fields are in order of the
fields retrieved for each row.
resultObj forall -type varName body
Execute body over each record in the
result object.
Types:
-array
Create varName as an array where the indexes are
the names of the fields in the table and the
values are the values of the current row.
-keyvalue
Set varName to a list containing key-value pairs
of fields and values from the current
row. (-field value -field value)
-list
Set varName to a list that contains the values
of the current row.
resultObj next -type ?varName?
Retrieve the next record in the result object.
Types:
-array
Create varName as an array where the indexes are
the names of the fields in the table and the
values are the values of the current row.
-keyvalue
Set varName to a list containing key-value pairs
of fields and values from the current
row. (-field value -field value)
-list
Set varName to a list that contains the values
of the current row.
resultObj numrows ?value?
Return the current numrows value. If value is
specified, it sets a new value for numrows.
numrows is the number of rows in this result.
resultObj resultid ?value?
Return the current resultid value. If value is
specified, it sets a new value for resultid.
resultid in most databases is the result
pointer which was given us by the database. This
variable is not generic and should not really be used,
but it's there if you want it.
resultObj rowid ?value?
Return the current rowid value. If value is
specified, it sets a new value for rowid.
rowid contains the number of the
current result record in the result object. This
variable should not really be accessed outside of the
result object, but it's there if you want it.