Stands for Tcl Database Connectivity. It's an interface standard for SQL databases and connectivity that aims to make it easy to write portable and secure Tcl scripts that access SQL databases.
Primary Author: | KBK |
Details at: | http://tip.tcl.tk/308.html , corrected by http://tip.tcl.tk/350 |
TCT Status: | TIP status is now Final for Tcl 8.6 and code appears in the Tcl 8.6 cvs head. |
Primary site: | http://tdbc.tcl.tk/ |
Documentation: | http://www.tcl.tk/man/tcl8.6/TdbcCmd/contents.htm |
Mailing List: | http://sourceforge.net/mailarchive/forum.php?forum_name=tcl-tdbc |
Tcl 8.6.0 will ship with four drivers for TDBC, being for:
[Add a pointer to a list of existing drivers, as well as drivers in the process of being developed]
LV Note that I notice that the digital signature listed at the web site says that it expired in Oct. Also, will the various TDBC drivers currently available soon be added to ActiveState's teapot repository? I don't see anything with the letters 'tdbc in it when I run teacup list. Or is it invisible because under Tcl 8.6 it comes as part of the package?
RLH TDBC and the various drivers are in the teapot repo now, but only for the beta ActiveTcl 8.6.
If you have installed the ODBC driver:
package require tdbc::odbc set connStr "DRIVER={Microsoft Access Driver (*.mdb)};FIL={MS Access};" append connStr DBQ= [file native [file normalize mydatabse.mdb]] tdbc::odbc::connection create db $connStr
Or for Excel:
package require tdbc::odbc set connStr "DRIVER={Microsoft Excel Driver (*.xls)};FIL={MS Excel};DBQ=test.xls;pagetimeout=5;readonly=false" tdbc::odbc::connection create db $connStr
Or:
package require Tk tdbc::odbc::connection create db {} -parent . # prompt the user with the system dialogs
After installing the SQLite3 driver (and sqlite software?):
package require tdbc::sqlite3 tdbc::sqlite3::connection create db "/path/to/mydatabase.sqlite3"
If you have a MySQL database, the driver (and perhaps the mysql software?) installed:
package require tdbc::mysql tdbc::mysql::connection create db -user joe -passwd sesame \ -host 127.0.0.1 -database customers
set name "O'Hara" db foreach rec { SELECT firstname FROM customers WHERE surname = :name } { puts "Found \"[dict get $rec firstname]\" in the customers table" }
Excel:
set Name "John" set Email "John@email" ::db allrows {CREATE TABLE [test] (Name text, Email text)} ::db allrows {INSERT INTO [test] (Name, Email) VALUES (:Name, :Email)}
LV 2008 Dec 02 So, what is the connection between TDBC and TclDBI? Was TclDBI an influencing design? Or is TclDBI just another example of an abstraction layer? Just curious about the motivation behind the reference.
Also, where on the WWW is the work and discussion regarding TDBC occurring?
NEM I don't know if TclDBI was an influence. Perhaps KBK could comment. Discussion of the TIP occurred on Tcl-Core and on a TDBC mailing list [L1 ]. There is a site at http://tdbc.tcl.tk/ too. The TIP status is Accepted, but not yet Final, so is just waiting for integration into the Tcl code base, which I guess will happen some time before 8.6b1. The design looks very good to me, but I haven't been successful in building it as an extension. I don't know if anyone else has practical experience with it yet?
KBK TclDBI, nstcl and tclodbc were all influences on the design. It also was compared with jdbc, Perl's DBI, odbc, and several native Tcl interfaces to specific databases (e.g., pgtcl, oratcl, mysqltcl, sqlite). Code was not borrowed directly from any of these, but ideas were lifted liberally.
It's a design goal that it should be possible to write a driver in either C or Tcl.
SRIV I'm partway done making a client/server tdbc interface. Im testing with Sqlite on the server side, accessing it through the tdbcsqlite3 package that KBK wrote. On the client, its a going to be a tdbc compliant package to interact with the server. So far its working very well.
DKF: It might or might not be part of 8.6b1 (that's very soon). IIRC, it was heavily influenced by lots of things that have been done in tcl-and-databases world; KBK's used a great many of them so he knows which parts of those previous attempts sucked and which parts were necessary for production use.
(Indeed, it did make 8.6b1. But you need an external driver; it was only core support modules that were in 8.6b1 itself.)
KBK Drivers are available by downloading the latest tdbc.zip from http://tdbc.tcl.tk . There are also binaries for the Windows versions of the drivers in SourceForge's file release system at https://sourceforge.net/project/showfiles.php?group_id=10894&package_id=305160
RS wonders on 2009-01-12 whether it might make sense to provide a pure-Tcl driver as last fallback solution as well, in the absence of all supported databases. This implementation could implement tables just as lists of lists in memory, and do commit/rollback by just writing/reading them to/from a plaintext file. Most work would probably have to go into an SQL parser there, of course...
DKF: If someone supplies an implementation "driver", sure...
JMN: I'm using the precompiled windows tdbc1.0b9 and tdbcodbc1.0b9 - and I'm getting a strange error with some MS Access MEMO fields. A nul character (\0) is appearing at exactly character position 255 within a field value in a '$resultset foreach row ..' loop. (resultset returned from a query ie: [db prepare "select * from $queryname"] execute] )
I tried producing a simplified version of my program in order to submit a bug report - but couldn't reproduce it in the simple program :( It's got me beat as to where this nul is coming from.
Just flagging it here for now in case someone has some debugging ideas etc..
KBK Please log bugs at http://tdbc.tcl.tk/ . Go to Login in the heading bar, and log in as anonymous. Then go to Tickets in the heading bar and select New Ticket. The Detailed Description panel uses HTML markup; please mark up code samples like
<verbatim> # your code goes here </verbatim>
and use the Preview button. (But don't worry too much if you don't get it quite right, we can go to the database and retrieve your unformatted text.
I've taken the liberty of logging the bug referenced above but haven't had a chance to investigate it yet. It's most likely an off-by-one error when extending the buffer for returned strings. I'll have a look.
HaO - 2009-07-03 03:32:52
I am interesting using TDBC on tcl 8.5. I have read about an "backport" somewhere. So I took the source distribution and compiled with MS-VC6:
TIP #308 says "Values of type time MUST be returned as a integer count of seconds since midnight, to which MAY be appended a decimal point and a fraction of a second." However, this is neither desirable, nor what the reference implementation is doing (I'm using tdbc::postgres). I'm getting results in the form "04:15:00" as expected, so I assume (and hope!) that this is a flaw in the TIP document, not in the implementation. -- GJW 2012-02-08
fh 2011-02-08 The example above for ODBC should also show how to connect using a DSN name like this: I just spent a while figuring that out.
set connStr "DSN=DSN_NAME; UID=user; PWD=password;" tdbc::odbc::connection create db $connStr
HaO 2012-11-27: To list odbc data sources, one may use:
% ::tdbc::odbc::datasources {Text auf c:/test/odbc} {Microsoft Text-Treiber (*.txt; *.csv)} hh {Microsoft Text-Treiber (*.txt; *.csv)} LocalServer {SQL Server}
HaO 2012-11-28: Here are examples of the table metadata commands.
Here is the official documentation . Official keys are marked in bold.
I invite everyone to complete the tables.
% dbodbc tables Barrels Barrels {TABLE_CAT mydb TABLE_SCHEM dbo TABLE_NAME Barrels TABLE_TYPE TABLE} % ::odbc::db tables syssegments syssegments {TABLE_CAT mydb TABLE_SCHEM dbo TABLE_NAME syssegments TABLE_TYPE VIEW}
Returned dictionary:
Key | Value | Description |
---|---|---|
TABLE_CAT | mydb | Data base name |
TABLE_SCHEM | dbo | Schema (data base owner) |
TABLE_NAME | Barrels | Data base table name |
TABLE_TYPE | TABLE | Table type, observed values are 'TABLE' and 'VIEW' |
The schema (here dbo) may not be used as prefix to the table name:
% dbodbc tables dbo.Barrels
Of cause, this works within a select statement:
% dbodbc allrows {select * from dbo.Barrels} {Order 1.0 Barrel 2}
% dbsqlite3 tables article {type table name article tbl_name Article rootpage 2 sql {CREATE TABLE [Article] ([Article] VARCHAR (18) PRIMARY KEY NOT NULL, [ArticleOwn] VARCHAR (18) NOT NULL)}}
Returned dictionary:
Key | Value | Description |
---|---|---|
type | table | Table type |
name | article | Data base table name |
tbl_name | Article | Data base table name (again?) |
rootpage | 2 | ??? |
sql | ... | SQL command to create the table |
I personally wondered, that there is no key 'database' to distinguish between a attached data base tables and the main one. To check this, I attached a copy of the data base file:
% dbsqlite3 allrows {attach database [data/sqlite2.dba] as d2} % dbsqlite3 tables article {type table name article tbl_name Article rootpage 2 sql {CREATE TABLE [Article] ([Article] VARCHAR (18) PRIMARY KEY NOT NULL, [ArticleOwn] VARCHAR (18) NOT NULL)}} % dbsqlite3 allrows {select d2.article.article, main.article.article from d2.article , main.article} {Article ABC} % ::dbsqlite3 tables d2.%
Apparently, attached tables are not listed by the 'tables' method.
% dbodbc columns Barrels Order {table_cat mydb table_schem dbo table_name Barrels column_name Order data_type 6 type_name float column_size 53 buffer_length 8 num_prec_radix 2 nullable 0 sql_data_type 6 ordinal_position 1 is_nullable NO ss_data_type 62 type float precision 53} Barrel {table_cat mydb table_schem dbo table_name Barrels column_name Barrel data_type 12 type_name varchar column_size 2 buffer_length 2 nullable 0 sql_data_type 12 char_octet_length 2 ordinal_position 2 is_nullable NO ss_data_type 39 type varchar precision 2}
(return value formatted by some newlines)
Returned dictionary and imaginated interpretation:
Key | Value | Description |
---|---|---|
table_cat | mydb | Data base name |
table_schem | dbo | Schema (data base owner) |
table_name | Barrels | Data base table name |
column_name | Order | Column name |
data_type | 6 | ??? |
type_name | float | Column data type |
column_size | 53 | ??? |
buffer_length | 8 | ??? |
num_prec_radix | 2 | ??? |
nullable | 0 | Is null value allowed |
sql_data_type | 6 | ??? |
ordinal_position | 1 | Column position within table, starting at 1 |
is_nullable | NO | Is null value allowed (again) |
ss_data_type | 62 | ??? |
type | float | Column data type (again) |
precision | 53 | Column precision value |
The key scale is the only missing of the list of documented keys.
The schema (here dbo) may not be used as prefix to the table name:
% dbodbc columns dbo.Barrels
% dbsqlite3 columns article article {cid 0 name article type {varchar } notnull 1 pk 1 precision 18 scale 0 nullable 0} articleown {cid 1 name articleown type {varchar } notnull 1 pk 0 precision 18 scale 0 nullable 0}
(return value formatted by some newlines)
Returned dictionary and imaginated interpretation:
Key | Value | Description |
---|---|---|
cid | 0 | ??? |
name | article | column name |
type | varchar_ | column type |
notnull | 1 | Is null value allowed? |
pk | 1 | Is column part of primary key |
precision | 18 | Column data type |
scale | 0 | ??? |
nullable | 0 | Is null value allowed (again?) |
All documented keys are returned.
Again, attached data base tables are not listed by this method:
% ::dbsqlite3 columns d2.article
% dbodbc primarykeys Barrels {tableCatalog mydb tableSchema dbo tableName Barrels columnName Order ordinalPosition 1 constraintName PK_Barrels} {tableCatalog mydb tableSchema dbo tableName Barrels columnName Barrel ordinalPosition 2 constraintName PK_Barrels}
(return value formatted by a newline)
A list of dicts is returned. In the sample, there is one unique key on the first and second column.
Key | Value | Description |
---|---|---|
tableCatalog | mydb | Data base name |
tableSchema | dbo | Schema (data base owner) |
tableName | Barrels | Data base table name |
columnName | Order | Column name |
ordinalPosition | 1 | Column position within table, starting at 1 |
constraintName | PK_Barrels | Name of constraint |
The keys missing from the documented list are constraintCatalog and constraintSchema.
The schema (here dbo) may not be used as prefix to the table name:
% dbodbc primarykeys dbo.Barrels
% dbsqlite3 primarykeys article {ordinalPosition 1 columnName Article}
A list of dicts is returned. In the sample, there is one unique key on the first column.
Key | Value | Description |
---|---|---|
ordinalPosition | 1 | Column position within table, starting at 1 |
columnName | Article | Column name |
A schema information is missing (which is IMHO important). Other keys missing from the documented list are: tableCatalog, tableSchema, tableName, constraintName, constraintCatalog and constraintSchema.
Attached data base tables even cause an error:
% dbsqlite3 primarykeys d2.article near ".": syntax error
HaO 2012-11-29: Here is a list of different error messages. Here is the official related documentation .
For me, I require error messages to get informed about a key violation and a deadlock. The corresponding ODBC error codes are:
Please feel free to add your observations.
catch {odbc::db allrows {create table el1005Lookup (Article varchar (16))}} err dErr 1 % set err [Microsoft][ODBC SQL Server Driver][SQL Server]In der Datenbank ist bereits ein Objekt mit dem Namen 'el1005Lookup' vorhanden. (executing the statement) % dict get $dErr -errorcode TDBC SYNTAX_ERROR_OR_ACCESS_RULE_VIOLATION 42S01 ODBC 2714
% catch {dbsqlite allrows {create table Article (Article varchar (16))}} err dErr 1 % set err table Article already exists % dict get $dErr -errorcode NONE
% catch { odbc::db allrows {insert into el1005lookup (Manufacturer, Article, ArticleOwn, Comment) values ('LHE999', 'AQ2', '80203010', 'Sonnenschein') } } err dErr 1 % set err [Microsoft][ODBC SQL Server Driver][SQL Server]Verletzung der PRIMARY KEY-Einschränkung 'PK_EL1005Lookup'. Ein doppelter Schlüssel kann in das EL1005Lookup-Objekt nicht eingefügt werden. [Microsoft][ODBC SQL Server Driver][SQL Server]Die Anweisung wurde beendet. (executing the statement) % dict get $dErr -errorcode TDBC CONSTRAINT_VIOLATION 23000 ODBC 2627 01000 3621
Aparently, the ODBC error code may be extracted by:
if {[lindex [dict get $dErr -errorcode] 3] eq "ODBC"} { set ODBCErrorCode [lindex [dict get $dErr -errorcode] 2] }
% catch {dbsqlite allrows {insert into Article (article,articleown) values ("ABC","OWNABC") }} err dErr 1 % set err column Article is not unique % dict get $dErr -errorcode NONE
I personally use this to detect this error:
if {[string match "column * is not unique" $err]} {
Start two wish shells. First shell:
% odbc::db begintransaction % catch {odbc::db allrows -as lists {insert into el1005lookup (Manufacturer, Article, ArticleOwn, Comment) values ('LHE999', 'AQ3', '80203011', 'Sonnenschein 2') } } err dErr 0
Second shell:
% odbc::db begintransaction % catch {odbc::db allrows -as lists {insert into el1005lookup (Manufacturer, Article, ArticleOwn, Comment) values ('LHE999', 'AQ3', '80203011', 'Sonnenschein 2') } } err dErr
The second wish now stands still, and exceeds the timeout of 10seconds (I waited around 1 minuite)
First shell:
% odbc::db commit
Now, in the second shell, the db command returns with an error:
1 % set err [Microsoft][ODBC SQL Server Driver][SQL Server]Verletzung der PRIMARY KEY-Einschränkung 'PK_EL1005Lookup'. Ein doppelter Schlüssel kann in das EL1005Lookup-Objekt nicht eingefügt werden. [Microsoft][ODBC SQL Server Driver][SQL Server]Die Anweisung wurde beendet. (executing the statement) % dict get $dErr -errorcode TDBC CONSTRAINT_VIOLATION 23000 ODBC 2627 01000 3621
So what happens is, that the transactions are processed one after the other. The deadlock error is not thrown. Well, this was a try to provoke it. I have only observed it at client sides with more recent SQL Server versions.