[schlenk] ''2005-11-15'' There is a wealth of different, somewhat similar database abstraction layers available for Tcl. None of the available solutions has achieved some kind of champion status as most developers use the native bindings to their database instead of one of these wrappers currently. So on this page an side by side comparison of the different available options is presented. '''Packages''' * [nstcl]-database * [DIO] * [XOSQl] * tcldb [http://www.sourceforge.net/projects/dqsoftware] * [TclODBC], also applies to [SnODBC] '''Supported Databases''' MySQL nstcl, DIO, XOSql, tcldb, tclodbc PostgreSQL nstcl, DIO, XOSql, tcldb, tclodbc Sybase (equiv to MS SQL) nstcl, tclodbc SQLite nstcl, DIO, XOSql, tcldb, tclodbc Oracle nstcl, XOSql, tcldb, tclodbc Solid nstcl, tclodbc ODBC nstcl, XOSql, tcldb, tclodbc DB2 tclodbc, * use Perl DBI XOSQL (* DB2 probably supported by any package that supports ODBC; many other databases supported by ODBC) The amount of work needed to add support for a database varies. The DIO, nstcl and XOSql group need slightly less work for a new database then tcldb, as their APIs are smaller. '''License''' DIO Apache License nstcl MIT/X11 License XOSql GNU General Public License tcldb BSD tclodbc BSD '''OO Style''' DIO incrTcl tcldb incrTcl nstcl none XOSql XOTcl tclodbc none, but follows ''obj method args'' style '''Basic Functionalities''' ''executing a SQL query'' -------------------------------------------- DIO $db exec $query nstcl db_dml statement1 $query (*) XOSql $db execute $query tcldb $db exec $query tclodbc $db $query -------------------------------------------- (* This is for a query modifing a value) ''getting a single value from an SQL query'' -------------------------------------------- DIO set string [$db string $query] nstcl set string [database_to_tcl_string $dbhandle $query] XOSql set string [lindex [[$db query $query] fetch] 0] tcldb set string [lindex [$db query 1row $query] 0] tclodbc set string [lindex [$db $query] 0] -------------------------------------------- ''getting one row of a SQL query into an array'' -------------------------------------------- DIO $db array $query $arrayVar nstcl* db_1row statement2 $query -columnVar $arrayVar XOSql set rObj [$db query $query] set keys [$rObj columNames] set values [$rObj fetch] foreach key $keys value $values { set $arrayVar($key) $value } tcldb** set result [$db query 1row $query] foreach key $keys value $result { set $arrayVar($key) $value } tclodbc $db read arrayVar $query -------------------------------------------- (* nstcl errors out if 0 or more than one row is returned) (** tcldb has no easy way to discover column names, but provides an extra class for table management) ''loop over the rows of a SQL query'' (Example: set query "SELECT id,name FROM person") -------------------------------------------- DIO set rObj [$db exec $query] $rObj forall -array result { puts "$result(id) -> $result(name)" } nstcl db_foreach statement3 $query { puts "$id -> $name" } XOSql set rObj [$db query $query] while {[llength [set row [$rObj fetch]]]} { puts "[lindex $row 0] -> [lindex $row 1]" } tcldb $db query foreach {id name} $query { puts "$id -> $name" } tclodbc proc printRows {id name} {puts "$id -> $name"} $db eval printRows $query -------------------------------------------- nstcl's style risks accidental overwriting of variables, which could lead to security problems. ''getting the first column of a SQL query as list'' ----------------------------------------------- DIO set list [$db list $query] nstcl set list [db_list statement4 $query] XOSql set rObj [$db query $query] set list [list] while {[llength [set row [$rObj fetch]]]} { lappend list [lindex $row 0] } tcldb set list [list] set llist [$db query list $query] foreach row $llist { lappend list [lindex $row 0] } tclodbc foreach row [$db $query] { lappend list [lindex $row 0] } ----------------------------------------------- ''Getting the whole result as nested list of lists'' ------------------------------------------------ DIO set llist [list] set rObj [$db exec $query] $rObj forall -list row {lappend llist $row} nstcl set llist [db_list_of_lists statement5 $query] XOSql set llist [$db queryList $query] tcldb set llist [$db query list $query] tclodbc set llist [$db $query] ------------------------------------------------ ''Get the number of affected rows for a query'' ------------------------------------------------ DIO set rObj [$db exec $query] set numRows [$rObj numrows] nstcl* ? XOSql set rObj [$db execute $query] set numRows [$rObj rows] tcldb** tclodbc $db statement stmt $query stmt execute set numRows [stmt rowcount] stmt drop ------------------------------------------------ (* no idea after just looking at the docs) (** not exposed, there is a protected internal function) ''Inserting a new row into the database'' ------------------------------------------------ DIO $db insert $arrayVar -table demo nstcl* set id $arrayVar(id) set name $arrayVar(id) db_dml statement { insert into demo (id , name) values (:id, :name) } XOSql $db insertRow demo {id name} [list '$arrayVar(id)' '$arrayVar(value)'] tcldb $db insert demo {id name} [list $arrayVar(id) $arrayVar(value)] tcldb $db exec {insert into demo (id,name) values ('@ID@','@NAME@')} id $arrayVar(id) name $arrayVar(name) tclodbc $db {insert into demo(id,name) values(?,?)} $id $name -------------------------------------------------------- (* not sure if the bind variable feature supports arrays, the first two lines may be superfluous.) ''Inserting a new row with automatic id'' -------------------------------------------------------- DIO $db insert $arrayVar -table demo \ -keyfield id -autokey 1 -sequence demo_seq nstcl* set name $arrayVar(name) db_dml statement { insert into demo (id, name) values ( (select * FROM nextval(demo_seq) ), :name) } XOSql $db rowInsertAutoId demo name [list $arrayVar(name)] id $sequencer ** tcldb*** $db insert_id id {name} [list $arrayVar(name)] tclodbc* (same as nstcl) as nstcl) -------------------------------------------------------- (* basically no support for automatic ids, use what the underlying database provides) (** I couldn't figure out from the docs what exactly has to be provided by sequencer) (*** Tcldb has support functions to create a db specific auto increment serial key.) ''Delete a record from the database by primary key'' -------------------------------------------------------- DIO $db delete $key -table demo -keyfield id nstcl db_dml statement { delete from demo where id = :id d } XOSql* $db execute "delete from demo where id = $id" tcldb $db delete demo id $id tclodbc $db {delete from demo where id = ?} $id -------------------------------------------------------- (* not sure if any quoting is done, may be a security problem) ''Transaction support'' -------------------------------------------------------- DIO $db exec {BEGIN TRANSACTION} ... # do some operations ... $db exec {COMMIT TRANSACTION} nstcl* db_transaction { ... # do some operations ... } XOSql $db execute {BEGIN TRANSACTION} ... # do some operations ... $db execute {COMMIT TRANSACTION} tcldb $db transaction { ... # do some operations ... } tclodbc $db set autocommit off ... # do some operations ... $db commit --------------------------------------------------------- Basically neither DIO nor XOSql seem to have any real transaction support. nstcl supports optional code to eval in case of errors during a transaction to decide on commit or rollback. Tcldb controls commit/rollback based on the Tcl exit code from the code block. ''Quoting support, for dynamic queries'' --------------------------------------------------------- DIO* attempts autoquoting of values in queries, XOSql provides escape method for simple value quoting nstcl binding variables for queries with autoquoting functions to quote identifiers and values tcldb binding variable for queries with autoquoting functions to quote values prepared statements parameter passing to the database tclodbc binding variable for queries with autoquoting prepared statements parameter passing to the database optional type hinting --------------------------------------------------------- * The current practice in DIO is insecure and is an SQL injection attack vector. Only values are quoted. Identifiers (table and field names) are passed to the database without any quoting. Value quoting seems broken and incomplete. It does not take SQL quoting rules into account, which may lead to data inconsistencies. Example of SQL Injection: set table users set match {'\' OR 1==1; --} $obj exec "SELECT * FROM $table WHERE id = $match AND password = $passwd;" Similar issues may be present in XOSql and nstcl but i did not take a closer look. ''NULL Handling'' How does the binding represent NULL values in retrieved data. ---------------------------------------------------------- DIO nstcl XOSql tcldb tclodbc ---------------------------------------------------------- Todo: add more formatting, add other dba layers like [dbConnect], [tcl dbi], [Oratcl] ---- [jcw] - Terrific overview. It might be useful to compare this with SQLite3's API, which is well-adjusted to Tcl. A bit secondary, but perhaps also useful would be comparisons with non-SQL databases (e.g. [Metakit], [OOMK], and [Ratcl], though it may be a bit early for the last). I admit that this strays somewhat from the term "abstraction", but knowing how several specific bindings solve problems which are really very similar may be of use in evaluating the trade-offs made in the other approaches. [schlenk] - I did/do not use TclODBC so if someone else feels it should be added, feel free to do so. Comparing things with SQLite3's api is possible and probably interesting, as the sqlite api is really a good fit for Tcl. On the current level of examples (basically simple things), a direct comparison with non-SQL databases like Metakit could be easily done. If that is the intent, this page should be refactored into one page per example, where the example could be described and then implementet with multiple different DB interfaces , and a general overview just listing interfaces, supported databases, sql support, license model etc. with links to the individual examples. [jcw] - FWIW, I've set up a tentative comparison for Ratcl at [http://www.vlerq.org/vqr/276]. [TP] - added TclODBC examples [LV] It would be useful to document how each binding deals with distinguishing the difference between a value of "" and a non-existing value in the databases. These two values often need distinguished. [schlenk] Very good idea. [tjk] - Thankyou for this excellent review. I would very much like to use a db extention to interface with MySQL. To date I have been reluctent to commit to an extention because of lack of good comparitive data (currently I use pure tcl). My real fear, when making a selection, is selecting a package with missing capabilities so a section that highlights known missing features would be a nice addition. [schlenk] The problem with comparing missing capabilities is there are so many unique features for individual database access libraries that a database abstraction layer either has to carry lots of emulation code around or target a functional overlap between all the supported database layers. I for example like the way tcldb abstracts some of the differences between databases away, others may prefer a light weight abstraction layer that abstracts sending queries and doing the connection stuff, but does not help in writing portable SQL queries. So the only way to do it: Make a list of capabilities you want to use/expect from one of these db layers, and then this page can probably be extended with further comparisions for the capabilities not yet listed/compared. ---- [Category Database]