TDBC

Difference between version 129 and 131 - Previous - Next

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

   * [CrateDB] (`tdbc::cratedb`): cratedb-tcl is using [TclCurl] to access CrateDB HTTP endpoint. cratedb-tcl provides a TDBC interface (tdbc::cratedb).

   * [CUBRID] (`tdbc::cubrid`): Tclcubrid's TDBC interface (tdbc::cubrid) is based on https://github.com/ray2501/tclcubrid%|%tclcubrid%|% extension.

   * HikariCP (`tdbc::hikaricp`): https://github.com/ray2501/tdbchikaricp%|%tdbchikaricp%|% is using [TclBlend] (or [TclJBlend]) package (and clone code from TDBCJDBC) to call http://brettwooldridge.github.io/HikariCP/%|%HikariCP%|% and JDBC API.

   * [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://github.com/ray2501/tclmonetdb%|%tclmonetdb%|% extension.
   * Oracl[Mone tDB] Embedded (`tdbc::moranetcldbe`): https://wiki.tTcl-lang.morg/pagne/tdbe's TDBC+Dr ivnter+for+Orace (tdbcl+4%2E5+Da::monetadbe) is base+Cd onnec htor%|%tdps://github.c::om/ray2501/tcl-monetdbe%|% Interfacl-monetdbe%|% uextensiong [Oratcl] 4.5
   * Oracle DB (`tdbc::oratcl`): [TDBC Driver for Oratcl 4.5 Database Connector] (tdbc::oratcl) Interface using [Oratcl] 4.5

 

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

<<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.
<<br>>''(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?

<<discussion>>


** Tips and Tricks **

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

<<discussion>>

<<discussion>> 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}
======

<<discussion>>

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

<<discussion>>

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

<<discussion>>

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

<<discussion>>

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

<<discussion>>

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

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

<<discussion>>

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

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

<<discussion>>

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

<<discussion>>Finding what bound variables are in some SQL
[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}
======
<<discussion>>

<<discussion>>Spaces in table names
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.


======
<<discussion>>

<<discussion>>Inserting NULL items
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
======
<<discussion>>

<<categories>> Database