** Summary ** '''TDBC''' is an acronym for [Tcl Database Connectivity], 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. ** See Also ** [Database Interface]: [Comparing Tcl database abstraction layers]: ** Attributes ** |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 archive:| http://sourceforge.net/mailarchive/forum.php?forum_name=tcl-tdbc | |Mailing list subscription:| https://lists.sourceforge.net/lists/listinfo/tcl-tdbc | ** Response ** "sqlite+tdbc are imo light years ahead of any other language with regard to SQLi protection" : - [aspect], [Tcl Chatroom], 2013-09-11 "first time I've looked at a DB API and not immediately thought "right, and once I've reimplemented half of DBI atop it it'll be useful". : - mst, [Tcl Chatroom], 2013-09 ** Documentation ** [http://www.tcl.tk/man/tcl/TdbcCmd/tdbc_connection.htm%|%documentation%|%]: Official keys are marked in bold. [http://www.magicsplat.com/articles/tdbc.html%|%The Tcl Database Connectivity interface%|%]: An introductory article on TDBC ** Description ** Tcl 8.6.0 shipped with four drivers for TDBC: * [MySQL] (`tdbc::mysql`) * [ODBC] (`tdbc::odbc`) * [PostgreSQL] (`tdbc::postgres`) * [SQLite] (`tdbc::sqlite`) ** Other Drivers ** [[Add a pointer to a list of existing TDBC drivers, as well as drivers in the process of being developed]] * [CUBRID] (`tdbc::cubrid`): Tclcubrid's TDBC interface (tdbc::cubrid) is based on https://github.com/ray2501/tclcubrid%|%tclcubrid%|% extension. * [JDBC] (`tdbc::jdbc`): https://github.com/ray2501/TDBCJDBC%|%TDBCJDBC%|% is using [TclBlend] (or [TclJBlend]) package to call JDBC API. * [MonetDB] (`tdbc::monetdb`): Tclmonetdb's TDBC interface (tdbc::monetdb) is based on https://sites.google.com/site/tclmonetdb/%|%tclmonetdb%|% extension. ** 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 ====== [ALX] Or for MS-SQL: ====== package require tdbc::odbc set connStr {Driver=SQL Server;DATABASE=databasename;UID=username;PWD=password;Server=hostname} 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" ====== After installing the '''[PostgreSQL]''' driver and having a database on a server at hand: ====== package require tdbc::postgres set conninfo {-host dbserver.domain.org -database funnydb -user max -password verySecret} # a good idea is: append conninfo { -sslmode required} tdbc::postgres::connection create db {*}$conninfo ====== 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-12-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 [https://lists.sourceforge.net/lists/listinfo/tcl-tdbc%|%TCBC mailing list]. There is a site at http://tdbc.tcl.tk/ too. [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 ---- [MHo] 2014-10-31: Why is there no tdbc::odbc in (only my?) 64bit ActiveState-Tcl? <> ** 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 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 ====== *** 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?)|& All documented keys are returned. 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 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 ====== *** 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|& 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 ====== <> <> Error messages [HaO] 2012-11-29: Here is a list of different error messages. Here is the official [http://www.tcl.tk/man/tcl8.6/TdbcCmd/tdbc_mapSqlState.htm%|%related documentation%|%] and here an official list of [http://www.tcl.tk/cgi-bin/tct/tip/350%|%error states%|%]. 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. <> <> Does TDBC work with tcl 8.5 ? Answerd on tcl-tdbc by Kevin Kenny: As far as I know, only the SQLite3 driver remains usable with 8.5, and even that is something of an accident: it happens not to depend on any 8.6 features. The MySQL, ODBC and PostgreSQL drivers were all rewritten to depend on the functionality described in TIP #357 (Export Tcl_LoadFile). This change turns out to be important for distributors. A distributor can build tdbc::odbc, tdbc::mysql and tdbc::postgres without needing to have the client libraries for the respective databases on the build system. The library references, and the functions therein, are resolved at runtime, when the package is required. Unfortunately, the change was a fairly major fork to the code. The TEA build system is quite complex, and TDBC stresses it some. Figuring out the correct incantations needed to make an 8.5 version link directly to the database client libraries while having an 8.6 version use Tcl_LoadLibrary simply was more than I could handle at the time, and so those drivers - which were in beta state to begin with, targeted to ship together with 8.6 - were shifted to an explicit 8.6 dependency. The intent is not to run TDBC's release cycle in lock-step with Tcl's, but in this specific case, there was a strong dependency on a new feature of 8.6. I'd be willing to help guide a programmer who wants to rework a version 0.18 of the drivers to build and execute against 8.5 (point out what in the code has to change, and so on...) but at present I don't have the bandwidth to take it on myself. This is one of several ODBC projects in need of an implementor. (I'd like to see the Oracle driver finished, have a driver for the SQL Server native API, and have a C version of the SQLite3 driver - the Tcl version that we have is pretty slow and has a couple of unfixable bugs.) <> <> Get number of related records in SQL update or delete [HaO] 2013-01-29: The number of affected rows is not returned by an allrows method for delete or update: ====== % db allrows "delete from el1005lookup where article = 'AQ3B'" ====== Kevin Kenny posted on [http://groups.google.com/group/comp.lang.tcl/browse_thread/thread/c26b34aebbbd3324#%|%clt%|%] how to get it: Execute the SQL statement to get a result set. The result set has no columns, but the row count is the number of affected rows. Here is a test with tdbc::odbc and MS-SQL 7: ====== % set s [db prepare "delete from el1005lookup where Article='AQ3B'"] ::oo::Obj62::Stmt::10 % set r [$s execute] ::oo::Obj81::ResultSet::1 % $r columns % $r rowcount 1 % $s close ====== <> <> Unicode in SQL Statements with MS-SQL ODBC [HaO] 2013-01-30: When I use non latin-1 characters in SQL statements with MS-SQL(97-2008) tdbc::odbc, I only get question marks. It seems that the encoding is fixed to cp1262 (system encoding on my german windows vista 32 bit): ======tcl % db configure -encoding cp1252 % db configure -encoding utf-8 optional function not implemented ====== In consequence, I have to escape all unicode specials by 'nchar(code)'. The following results in an inserted question mark: ======tcl % db allrows "INSERT INTO Table1 (Article,Comment) VALUES ('A1', 'B\u200')" ====== while this works: ======tcl % db allrows "INSERT INTO Table1 (Article,Comment) VALUES ('A1', 'B'+char(0x200))" ====== There are no issues to extract any unicode characters from the data base using 'select'. The result set works for unicode. Here is my function to replace any non-latin characters by '+char(num)+': ======tcl proc SQLStringEscape {string} { set fEscaping 0 set stringLength [string length $string] for {set posCur 0} {$posCur < $stringLength} {incr posCur} { set charCur [string index $string $posCur] if { $charCur > "\xff" } { set CodeCur [scan $charCur %c] if { ! $fEscaping} { set fEscaping 1 set stringNew "'" } append stringNew "+NCHAR($CodeCur)" } else { if {$fEscaping} { set stringNew "+'$charCur" set fEscaping 0 } } if {[info exists stringNew]} { set string [string replace $string $posCur $posCur $stringNew] set Step [string length $stringNew] incr Step -1 incr stringLength $Step incr posCur $Step unset stringNew } } if {$fEscaping} { append string "+'" } return $string } ====== N.B.: For other SQL Servers, "+" must be replaced by "||". But I am not shure, if the 'nchar' function is ANSI SQL. N.B.: I feel quite dumb here, there should be an easier solution. But we tried this with the Microsoft tools and none of them acepted unicode in SQL statements bejond latin1. N.B.: Please correct if you have better solutions. N.B.: If this is realy the case, it might be helpful, to have such a function in tdbc as utility. ---- [gkubu] - 2013-01-31 21:38:27: I remember that some time ago I could update a MySQL database table using unicode characters (but don't remember which flavour of Unicode). For a DB2 database I had to convert the strings. I believe this depends on settings in the odbc driver. The CCSID (IBM equivalent of a codepage) was set to EBCDIC. There is also an option to use UCS-2 (okay, it's not UTF-8). I didn't want to change the setting because this could have influenced other applications. I converted the characters this way (again to UCS-2, not UTF-8): === set target "" foreach z [split $source ""] { append target [format %04x [scan $z %c]] } === alternatively === set target "" foreach z [split $source ""] { append target [format %04x [unicode::fromstring $z]] } === In the SQL statement the unicode hex target string has to be marked with "ux" === INSERT INTO ... VALUES( ux'$target' ) === (partly taken from memory, hope it works... I don't know if the "ux" syntax is part of ANSI SQL) With regard to UTF-8, I imagine that `encoding` could be of use. ---- [gkubu] - 2013-02-01 16:14:38: Some people claim that for MS-SQL you have to put N in front of your unicode-String like N'string' [http://stackoverflow.com/questions/3334489/how-do-i-insert-unicode-into-ms-sql], and that there are parameters to consider [http://stackoverflow.com/questions/11077507/how-to-insert-a-unicode-character-in-sql-server-using-inline-query] What about the SQL CAST function? [HaO] Thank you for the ideas. That all sounds good but not perfect. I have to try the 'N' method which is very simple. [gkubu] This [http://social.msdn.microsoft.com/Forums/de/sqlserverde/thread/32a881a9-6b6f-4d8f-a0ef-b9348e024c2e] entry (sorry, German) says that parameterized statements would be converted automatically. I suspect this will depend on how tdbc passes it on to the driver. NCHAR is a standard SQL keyword, ux and N most probably not [http://oreilly.com/sql/excerpts/sql-in-nutshell/shared-platform-specific-keywords.html] [http://www.postgresql.org/docs/current/static/sql-keywords-appendix.html] [HaO] I checked your references about 'NCHAR'. It seams only be valid in MS-SQL and PostgreSQL, and the PostgreSQL reference tells, that it is only a variable type and not a function. Unfortunately, I use it as a function above. Thus, for PostgreSQL, a cast to 'NCHAR' should propably used... <> <> Feedback on clt of migration from tclodbc [DrS] wrote on 2013-02-27 on [http://groups.google.com/group/comp.lang.tcl/browse_thread/thread/3a7d2c1bf1520456#%|%clt%|%] titled 'TDBC design flaw': ======none I have been using tclodbc for the last several years in a few scripts that help maintain a production database. A month or so ago I had some time to evaluate TDBC after some recommendations from group members. I thought I would share my experience here in case it helps someone else. Overall, the transition from tclodbc to tdbc/odbc is smooth. There is not much documentation on it, especially sample scripts. But after a little trial and error, things become clearer. The command interface is clear and straightforward. Its resultset interface takes a slightly different approach than tclodbc and is in a sense slightly more flexible. It can handle multiple resultsets that some stored procedures generate. I did not test it to see which one was more efficient for lack of time. Unfortunately, I am not replacing tclodbc for one small but important feature. tclodbc gives you information on columns not only for tables but also for resultsets. You can run a query and see what the returned data types are. Tdbc/odbc currently cannot do this. This is important at least for me in that the display of the returned data can be improved (numbers vs. dates vs. strings). This is very important for our users and we cannot do without it. I believe this to be a design flaw. Fixing this would ring tdbc upto par with tclodbc. ====== <> <> ODBC -timeout may be optional feature [HaO] 2013-08-12: I dropped today in the hole that in the Access odbc driver, the option '-timeout' is optional. ====== % db configure -timeout 10000 Optional ODBC feature not implemented ====== <> <> Query that has a 'where like '%Keyword%' part [superlinux], 2015-05: Please notice that the wildcard percent sign (%) has to be typed twice to escape it and force its existence in the SQL statement. ====== set query_all_items_on_search_keyword "select id, name, stock, category from item where name like '%%$search_keyword%%'" set statement [db prepare $query_all_items_on_search_keyword] $statement foreach row { puts [dict values $row] } ====== [aspect]: The above sounds like a bug, but I'm unable to replicate it: ====== % package require tdbc::sqlite3 1.0.3 % tdbc::sqlite3::connection create db "" ::db % db allrows {select 'ax' like '%x%'} 1 % set x %x% %x% % db allrows {select 'ax' like :x} 1 ====== Nor on 1.0.0, nor on mysql can I produce an issue with '%' queries. Can you report which version you are using, and show a self-contained test script? <> <> Options on odbc connect call 2015-09-14 [HaO]: Drivers may limit the availability of connection options. In addition, options may be setable by the '''connection create''' method and not by the '''configure''' method. Thus with MS-SQL 14: try to set isolation level after connect fails: ====== % tdbc::odbc::connection create db "DSN=ms14.dsn" % db configure -timeout 10000 % db configure -encoding cp1252 -isolation readcommitted -readonly 0 -timeout 10000 % db configure -isolation serializable (Microsoft)(SQL Server Native Client 11.0)Der Vorgang ist zu diesem Zeitpunkt ungueltig (setting isolation level of connection) ====== The error means: "at this time not valid" Setting it with the '''connect''' works well: ====== % tdbc::odbc::connection create db "DSN=ms14.dsn" -isolation serializable % db configure -timeout 10000 % db configure -encoding cp1252 -isolation serializable -readonly 0 -timeout 10000 ====== <> <> Database