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 extension is installed as a tcl package. The installation is most easily done with an included installation script, setup.tcl.
After installation the extension is loaded simply by command:
package require tclodbc
This command adds the command database to the tcl command interpreter (or actually loads the package on demand). Command database is used for creating database connection objects as well as for configuring the system data sources.
Unzip the distribution file to an arbitrary directory. With pkunzip, switch -d is required to create subdirectories. After that, run the installation script.
c:\temp>pkunzip -d tclodbc.zip
c:\temp>tclsh setup.tcl
Installation successful.
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 (as with update, insert or delete clause), the command returns an integer value giving the amount of rows touched.
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 [db "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"
1
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 |
| maxlength | numeric value |
| rowsetsize | numeric value |
| cursortype | static dynamic forwardonly keysetdriven |
| encoding (requires tcl 8.1) |
any valid encoding name |
| noscan | boolean (0/1, on/off) |
In tcl 8.1 strings are stored in unicode form, but ODBC API uses only old-fashioned 8-bit character strings. Therefore it is possible to set an encoding to a database object. This encoding is used when transforming strings from and to the database. While encoding is a connection specific value, it is even possible to have simultaneous connection to databases with different encodings, and transfer data correctly from one to another. Encoding defaults to system encoding, which is suitable in most cases.
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"
12
% db rollback
OK
% db "update article set groupid=6 where artid > 1000"
12
% 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 ?matchpattern?
Method tables lists all tables in the database which name matches argument. Note, that match is determined using sql syntax, with % and _ as wildcards. With no argument, all tables are listed. 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}
typeinfo typeid
Method typeinfo returns driver specific information of given sql type. The information is a tuple of: {TYPE_NAME DATA_TYPE PRECISION LITERAL_PREFIX LITERAL_SUFFIX CREATE_PARAMS NULLABLE CASE_SENSITIVE SEARCHABLE UNSIGNED_ATTRIBUTE MONEY AUTO_INCREMENT LOCAL_TYPE_NAME MINIMUM_SCALE MAXIMUM_SCALE}
statement id <sql clause>|tables|columns|typeinfo ?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}
eval proc <sql clause> ?argtypedefs? ?args?
Method eval first executes the given sql clause, and then evaluates the given tcl procedure for each row in the result set. The argument count of the procedure must match the column count in the query. Only single row is read in the memory at a time this saving memory compared to e.g: foreach i [db "select..."] {...}
This command is particulary useful, when large tables are to be iterated once. In this case we do not neccessarily need a separate statement object, but also do not want to read the whole table to memory at one time.
Example:
% proc output {name value} {puts $name $value}
% db eval output "select name, value from table1"
{{ab 123}{cd 456}}
read arrayspec <sql clause> ?argtypedefs? ?args?
Method read reads data from the database to a tcl array or arrays. The first data columnn is used as index for the array, and the rest of the columns are stored as to the array. The basic case is a query that results two columns, first of which is a key of a table, and the next a description of some sort for the key value.
The array may be specified in two ways. The first way is to specify a list of array names which are used for array names for each data column. Thus, the count of given names must be equal to the count of returned data columns (minus the first column containing the key). (Example 1)
The second way is to specify only a single arrayname, which is used as two-dimensional array. The array index in this case is constructed from the key column value and column label, separated by comma. (Example 2)
It should be noted, that it is not recommended to read large tables to arrays for two reasons. First, if the data is subject to changes in multi-user database, the changes do not reflect to the data already read in the array. The other thing is memory consumption. Is the table is large, it also consumes pretty much memory if read in at once. It is recommended to use either database eval command, or separate statement object to do the iteration of large amounts of data. On the other hand, this command provides a very simple syntax for reading relatively stable name-value parameter pairs to tcl array, from which they are easily referenced.
Example 1:
% db read {name description} "select id, name, description from
table2"
OK
% puts $name(123)
Name123
% puts $description(123)
Description123
Example 2:
% db read table2 "select id, name, description from table2"
OK
% puts $table2(123,name)
Name123
% puts $table2(123,description)
Description123
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 {3000 222}
1
execute ?args?
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 ?arrayName? ?columnNames?
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.
The first form without arrayName returns the row data, or an empty string when there is nothing more to read.
Example:
% db statement emp_data "select * from employees"
emp_data
% emp_data execute
OK
% while {[set row [emp_data fetch]] != {}} {puts [lindex $row 5]}
2000
3000
4000
%
The other form reads the data to an array. Statement column labels or, if given, defined column names are used as array indices. If the column labels are explicitly defined, their count must match the number of actual data columns.
This provides a convenient way for storing the data straight to named array instead of indexing a straight list. Here the command returns a boolean value indicating successful fetch or do data condition.
Example (same result as above, with substantially more readable code):
% db statement emp_data "select * from employees"
emp_data
% emp_data execute
OK
% while {[read_salary fetch row]} {puts $row(Salary}}
2000
3000
4000
%
rowcount
Method rowcount returns the number of rows affected by last execution of insert, update or delete statement. Certain drivers support also getting the count of rows in open result set after select command, but this is not generally applicable.
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 |
| maxlength | numeric value |
| rowsetsize | numeric value |
| cursortype | static dynamic forwardonly keysetdriven |
| noscan | boolean (0/1, on/off) |
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.
eval proc ?args?
See database eval for description.
read arrayspec ?args?
See database read for description.
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.
The sources completely rewritten to allow conditional compling for both tcl 7.6 and 8.0. Under tcl 7.6 tcl-objects are implemented using reference counted DStrings instead of Tcl_Obj's. The performance in some situations may not be exactly the same as with tcl 8.0, but it works anyway. The command interface has no changes since the previous version.
Added couple of configuration options to database and statement set and get commands. Added arrayName option to the statement fetch command. Removed global private interpreter to ensure thread safety, which is nice with thread safe tcl 8.1 interpreter.
Added read an eval command to both database and statement objects.
Unicode support is added with tcl 8.1. Now it is possible to specify encoding to a database connection. UTF-8 is used as string internal representation, but any strings passed from and to an odbc connection are encoded as specified. More details in tcl 8.1 documentation.
Distribution version now includes compiled dll's for tcl 7.6, 8.0 and 8.1, all in one zipfile. Installation to a tcl package automated with a simple installation script.
Corrected bug related to large column handling. Now should be able to handle properly even large variable length binary columns. There remains still problems with some drivers.
Configuration option noscan for database and statement objects. This is particularily useful when storing tcl strings as string literals.
Added typeinfo command for database object
Added rowcount command for statement object
Some bugs are fixed, most of them relating to variable length column handling.
Syntax: db autocommit on/off was removed. Correct syntax for the same thing
is:
db set autocommit on/off
Also, statement object argument typecast was finally removed from statement execution time. Correct syntax is to define statement arguments at statement object creation time.
Binary column handling changed. In earlier versions binary data was handled in hexadecimal form if the driver supported binary-character conversion. Now binary data is handled in native binary form. Example code of binary data handling is added to samples.
Bugfix in long column handling: handle also SQL_NO_TOTAL return value from SqlGetData().
Corrected a misspelling in option name maxlength. The earlier misspelled form maxlenght is still supported for compatibility reasons.
Return value from update/insert/delete statement changed. Earlier returned always "OK" for successful execution. Now returns the count of rows updated/inserted/deleted. If now rows were touched, returns 0, which normally means an error.
Bugfix in setting database options. Some options always raised an error: unknown encoding ..."
Now works also with MS Access MEMO fields. Older version returns some carbage in memo text data after actul value.