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 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

<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:

• 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?

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.

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:

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:

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:

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:

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.

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.

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 and here an official list of 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 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.

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.)

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 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

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):

% 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:

% db allrows "INSERT INTO Table1 (Article,Comment) VALUES ('A1', 'B\u200')"

while this works:

% 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)+':

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' [L1 ], and that there are parameters to consider [L2 ]

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 [L3 ] 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 [L4 ] [L5 ]

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...

DrS wrote on 2013-02-27 on clt titled 'TDBC design flaw':

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. 

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

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?

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

DKF: SQL isn't nice to parse because of the various quoting syntaxes it uses for various things, and it is sometimes nice to find out what variables it is looking for. Here's how:

% set sql "SELECT foo FROM bar WHERE x = :xyz AND y = ':abc' AND z = :def; -- :ghi is a comment"
...
% set variables [lmap {fragment var} [tdbc::tokenize $sql] {if {$var eq ""} continue;set var}]
:xyz :def

The key is tdbc::tokenize, a core service of TDBC provided to make driver writing easier, which knows the awkward stuff about what is and what isn't a variable.

Here's the full list it returns, alternating between SQL text and variable name:

% set sql "SELECT foo FROM bar WHERE x = :xyz AND y = ':abc' AND z = :def; -- :ghi is a comment"
...
% tdbc::tokenize $sql
{SELECT foo FROM bar WHERE x = } :xyz { AND y = ':abc' AND z = } :def {; -- :ghi is a comment}

Tables with spaces are written as

set Stmt [db prepare {SELECT * FROM [Table with Spaces]}] in MS SQL Server and other MS databases. The table name is still not case sensitive. Non-ASCII characters are allowed in table name.

set Stmt [db prepare {SELECT * FROM "Table with Spaces"}] in all ANSI/ISO compliant databases. The table name is case sensitive. Non-ASCII characters are allowed in table name.

The question arose on the Tcl'ers Chat of how to insert a new item. If you've prepared a statement like:

% set stmt [$db prepare {insert into theTable(a, b, c) values (:a, :b, :c)}]

you can insert a NULL by leaving the given item out of the data:

% $stmt allrows [list b $b c $c]; # inserts a NULL for a

2024-08-14 RKzn I got this error on (TCL 8.6.12) on package require tdbc:mysql. I had a libmariadb.so.3 so I eventually got it working by creating a soft link to it, about like so

# su root
# cd /usr/lib64
# ln -s libmariadb.so.3 libmariadb.so.15
# ldconfig