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|
|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/|
Tcl 8.6.0 will ship with four drivers for TDBC, being for:
* [MySQL] (`tdbc::mysql`)
* [ODBC] (`tdbc::odbc`)
* [PostgreSQL] (`tdbc::postgres`)
* [SQLite] (`tdbc::sqlite`)
<>
[[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.
<>
----
**Examples**
***Opening a connection to a database***
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 '''[SQLite]3''' 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
======
***Simple Querying***
======
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)}
======
----
**Discussion**
<>
[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
[https://lists.sourceforge.net/lists/listinfo/tcl-tdbc]. 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
======
# your code goes here
======
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:
* Generic package compiles out of the box with the makefile in win and `TCLDIR` macro set. sub library must be installed manually.
* For the [odbc] driver, there is no win makefile (of course [mingw] is present). I used a version from TDBC with the following modifications:
** ''Precompiler:'' `-D inline=_inline -D USE_TCLOO_STUBS -D USE_TDBC_STUBS -D USE_TK_STUBS`
** ''Additional include folders:'' `tdbc\generic TclOO0.6\generic`
** ''Additional libraries:'' `odbc32.lib odbccp32.lib user32.lib "$(TCLDIR)\lib\tkstub85.lib" tdbcstub10.lib tclOOstub06.lib`
----
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
<>
----
**Tips and Tricks**
<> ODBC connect with DSN name
[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
======
----
<>
<> List odbc data sources
[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}
======
----
<>
<> Return dictionaries of the metadata query commands
[HaO] 2012-11-28: Here are examples of the table metadata commands.
I invite everyone to complete the tables.
**tables**
***ODBC***
======
% 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}
======
***SQLite3***
======
% 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.
**columns**
***ODBC***
======
% 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 schema (here dbo) may not be used as prefix to the table name:
======
% dbodbc columns dbo.Barrels
======
***SQLite3***
======
% 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?)|&
Again, attached data base tables are not listed by this method:
======
% ::dbsqlite3 columns d2.article
======
**primarykeys**
***ODBC***
======
% 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 schema (here dbo) may not be used as prefix to the table name:
======
% dbodbc primarykeys dbo.Barrels
======
***SQLite3***
======
% 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|&
Attached data base tables even cause an error:
======
% dbsqlite3 primarykeys d2.article
near ".": syntax error
======
<>
<> Error messages
[HaO] 2012-11-29: Here is a list of different error messages.
For me, I require error messages to get informed about a key violation and a deadlock.
The corresponding ODBC error codes are:
* 23000 - Integrity violation - double record
* 40001 - Deadlock and we lost - please repeat later
Please feel free to add your observations.
**SQL Syntax error**
***ODBC***
****MS-SQL-Server 6.0****
======
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
======
***SQLite3***
======
% catch {dbsqlite allrows {create table Article (Article varchar (16))}} err dErr
1
% set err
table Article already exists
% dict get $dErr -errorcode
NONE
======
**23000 - Integrity violation - double record**
***ODBC***
****MS-SQL-Server 6.0****
======
% 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 [http://msdn.microsoft.com/en-us/library/windows/desktop/ms714687%28v=vs.85%29.aspx%|%ODBC error code%|%] may be extracted by:
======
if {[lindex [dict get $dErr -errorcode] 3] eq "ODBC"} {
set ODBCErrorCode [lindex [dict get $dErr -errorcode] 2]
}
======
***SQLite3***
======
% 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]} {
======
**40001 - Deadlock and we lost - please repeat later**
***ODBC***
****MS-SQL-Server 6.0****
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.
<>
**See also**
* [Comparing Tcl database abstraction layers]
* [TclDBI]
<> Database