The extension is actually a shared function library. The actual form of such library depends of the used operating system. For example, in windows systems these libraries have the extension .dll in their name. The tclodbc library is thus names tclodbc.dll under windows.
The operating systems have some mechanism for specifying a search path, from which there shared libraries are searched when requested. In windows this search path is the same as the application search path, namely the PATH environment variable. The extension library should reside in a directory listed in the search path.
The extension is loaded simply by command:
load tclodbc
This command adds the command database to the tcl command interpreter. This command is used for creating database connection objects.
database connect id datasource ?userid? ?password?
or
database connect id connectionstring
Create a database object identified by id. The object is connected to a ODBC data source. The first form of the command is applied to a datasource name, with userid and password where neccessary. The second form uses a ODBC connection string of the form "Attribute1=Value1;Attribute2=Value2". The command returns the id, which is used to refer to the database object.
Example:
% database db employeebase sysadm xxxxxx
db
Example 2:
% database db "DRIVER=Microsoft Paradox Driver (*.db);DBQ=C:\\db"
db
This is the default command. The keyword "connect" can be omitted. It should be used when it is possible that given id may be one of the other command keywords.
database configure operation driver attributes
Configure ODBC datasources. A datasource may be added to the system, reconfigured, or removed. First argument defines the operation from the set:
Driver argument specifies the driver to be used and the attributes argument a list of driver specific attribute name and value pairs. Usually at least the data source name (DSN) parameter should be provided, the rest are driver dependent.
This form of database command does not create a connection to the database, even if one is added. Connection is created with some of the commands described above.
Example:
% set driver "Microsoft Access Driver (*.mdb)"
% set attributes [list "DSN=mydsn" "DBQ=c:\mydb.mdb"
"FIL=MS Access"]
% database -configure add_dsn $driver $attributes
OK
database datasources
Returns a list of currently configured ODBC data sources in pairs {{datasource name} {driver name}}
database drivers
Returns a list of currently configured ODBC drivers in pairs {{driver name} {list of driver attributes}}
Database object is a dynamically allocated object, created by the database command. The object provides the following methods. The methods are called as commands after the database object identifier.
<any valid SQL-clause> ?argtypedefs? ?args?
Almost all necessary functionality can be provided through this simple syntax. The given SQL-clause is executed in the DBMS, and the result, if any, is returned in a tcl list.
If the command returns a single column result set, the returned string is a straightforward list or an empty string, if nothing is found. If the command returns a multiple column result set, a tcl list of lists is returned, where a sublist represents a single row of the result. If the SQL-clause does not return a result set, the command returns 'OK'.
SQL-arguments may be given after the SQL-clause, while the argument positions are marked with '?' in the SQL- clause. This mechanism is more useful with precompiled statements and explained with statement object interface in detail. A simpler mechanism of argument substitution is the natural way of using tcl variables for substituting values straight to the SQL-clause.
Examples:
% database db employeebase sysadm xxxxxx
db
% set ids [select id from employees where salary < 1000]
{222 333 444}
% set id [lindex $ids 0]
222
% db "select firstname, surname from employees, where id = $id"
{{Jonathan Clarke}}
% db "update employees set salary = 2000 where id = $id"
OK
disconnect
disconnect disconnects the database object from the data source and clears it from the interpreter.
Example:
% database db employeebase sysadm xxxxxx
db
% db disconnect
OK
set option value
Set command is used for setting various connection-specific attributes. Currently supported options and their valid values are listed in following table. Check some odbc reference for detailed description of the different options.
| Option | Values |
|---|---|
| autocommit | boolean (0/1, on/off) |
| concurrency | readonly lock values rowver |
| maxrows | numeric value |
| timeout | numeric value |
| maxlenght | numeric value |
For example: An ODBC-connection is by default in an autocommit-mode. This means that any statement is commited at once, thus leaving no records locked. If special transaction handling should be used, the connection may be set in non-autocommit mode. After this the commit and rollback methods are used for transaction handling.
Examples:
% database db artbase sysadm xxxxxx
db
% db set autocommit off
OK
% db "update article set groupid=5 where artid > 1000"
OK
% db rollback
OK
% db "update article set groupid=6 where artid > 1000"
OK
% db commit
OK
get option
Get command is used for querying current value of various connection-specific attributes. Theck the table above for list of supported options.
commit
With connection in autocommit mode, method commit commits the current transaction. See examples in autocommit section.
rollback
With connection in autocommit mode, method rollback cancels the current transaction. See examples in autocommit section.
tables
Method tables lists all tables in the database. The result is a list of kvintuples: {TABLE_QUALIFIER TABLE_OWNER TABLE_NAME TABLE_TYPE REMARKS}
columns ?tablename?
Method columns lists the columns in the database, or the columns in the specified table, if one given. The result is a list of: {TABLE_QUALIFIER TABLE_OWNER TABLE_NAME COLUMN_NAME DATA_TYPE TYPE_NAME PRECISION LENGTH SCALE RADIX NULLABLE REMARKS}
indexes tablename
Method indexes lists the indexes of a specified table. The result is a list of: {TABLE_QUALIFIER TABLE_OWNER TABLE_NAME NON_UNIQUE INDEX_QUALIFIER INDEX_NAME TYPE SEQ_IN_INDEX COLUMN_NAME COLLATION CARDINALITY PAGES FILTER_CONDITION}
statement id <sql clause>|tables|columns ?argtypedefs?
Method statement creates new statement objects of one of the three types: sql query, table query or column query, depending of the last argument: valid sql-clause, or an identifier 'tables' or 'columns'.
Statements are similar to queries executed directly on a database object, but when created, statement it is merely precompiled for furher use. This is useful for efficiency reasons, should the same query be executed several times. Precompiled statements may be used with sql argumens.
A statement is given a textual id similar to database object. Further, the statement object is called simply by its id, following possible query arguments. The command returns the id
Tclodbc tries automatically determine proper argument types for each argument. Some drivers do not support this function, and therefore the user may explicitly define the correct argument types.
The syntax of an argument type definition is the following:
?type? ?scale? ?precision?
The type cast is interpreted from the left, so that e.g. the scale and precision may be left out, if not needed.
Currently recognised sql type names are standard types:
and the extended types:
Also datatypes standard numerical counterparts may be used instead of the string names.
Examples:
% db statement s "select fullname from article where id=132"
s
% s
{FullName132}
% db statement s2 "select fullname from article where id1=?"
INTEGER
s2
% s2 132
{FullName132}
Statement object is a dynamically allocated object, created by the statement method of a database object command. The object provides the following methods. The methods are called as commands after the statement object identifier.
run ?args?
Command run executes the statement and returns the whole result set immediately. The command may be followed with the list of sql arguments, if the statement expects them.
Empty argument is interpreted as null value. Notice that the arguments are given as a list of arguments. Therefore an argument given as {} is a empty list of arguments, while {{}} is a list of one argument, which is null. Safest way to create argument lists is through the tcl list command.
This is the default command. The keyword "run" can be omitted. It should be used when it is possible that given argument value may be one of the other command keywords.
Example:
% database db employeebase sysadm xxxxxx
db
% db statement readall "select * from employees"
readall
% readall
{{xx yy zz} {aa bb cc} {rr tt yy}}
% db statement read_salary "select salary from employees where id
= ?"
read_salary
% read_salary 222
2000
% db statement update_salary "update employees set salary = ? where
id = ?"
update_salary
% update_salary {FLOAT INTEGER} {3000 222}
OK
execute
Method execute executes the given statement, but does not return the result set. If the statement returns a result set, rows may be read one by one with method fetch.
fetch
Method fetch is used for reading one row from the result set at a time after executing it by method execute. Sometimes, if the result set is large, it is not convenient to load it to memory at once, but rather traverse thru it one record at a time e.g. making neccessary output or update one by one.
columns ?attribute attribute ...?
Method columns returns a list of statement columns attributes. ODBC supports various column attributes. The returned attributes are specified with arguments. The default attribute, in none given, is column label.
Currently supported attributes are:
set option value
Set command is used for setting statement-specific attributes. Currently supported options and their valid values are listed in following table. Check some odbc reference for detailed description of the different options.
| Option | Values |
|---|---|
| concurrency | readonly lock values rowver |
| maxrows | numeric value |
| timeout | numeric value |
| maxlenght | numeric value |
get option
Get command is used for querying current attribute values. Theck the table above for list of supported options.
drop
Method drop drops the statement from the memory and clears the command from the interpreter.
Autocommit on|off command is replaced with more general set and get commands, which are used for setting all kinds of database connection object properties. Old syntax is still supported.
Some drivers returned last non-null values in a column having a null value. Bug was corrected.
Argument type definition was moved from statement execution to statement object creation. Now tclodbc tries to determine the argument types automatically, if the driver supports it, but if it does not, the user has the option of giving types explicitly. The old syntax is still supported, too.