• TDBC - none, but follows obj method args style
• DIO - incrTcl
• tcldb - incrTcl
• XOSql - XOTcl
• tclodbc - none, but follows obj method args style
• nstcl - none
• nsdbi - none
• sqlite3 - none, but follows obj method args style
• MrPersister - incrTcl

  # Unspecified.

  $db configure -database dbname

  package require DIO
  set dbhandle [::DIO::handle Postgresql -host host -port port -user user -pass pass -db db]
  # Or
  ::DIO::handle Postgresql dbhandle -host host -port port -user user -pass pass -db dbname

  package require tcldb
  tcldb::tdb_postgresqldatabase dbhandle ?options?

  package require xotcl
  xotcl::Sqlinterface loadinterface mysqltcl
  MySql create dbhandle
  ::dbhandle connect {user username dbank dbname}

  package require tclodbc
  database connect dbhandle $datasource $username $password

  package require nstcl
  package require Pgtcl
  nstcl::load_driver postgres
  nstcl::configure_pool postgres dbhandle $numconnections $datasource $username $password

  # Connection pools configured in file. No explicit handle management necessary.

  package require sqlite3
  sqlite3 dbhandle dbfile.db

  package require mrpersister
  # via JNDI datasource
  set ic [java::new javax.naming.InitialContext]
  set ds [java::cast javax.sql.DataSource [$ic lookup $dsname]]
  ::mrpersister::PersistenceManager perman $ds
  perman createDaos daos

  # via JDBC connection
  java::call Class forName $driverClass
  set conn [java::call java.sql.DriverManager getConnection $jdbcUrl $username $password]
  ::mrpersister::PersistenceManager perman
  perman createDaos daos $conn

  dbhandle command ?args ...?

  daos getGenericDao gendao    ;# ORM dao object command 'gendao'
  daos getJdbcDao    jdbcdao   ;# JDBC access dao object command 'jdbcdao'
  daos getMapDao     mapdao    ;# Map access dao object command 'mapdao'

  ::mrpersister::DbObjBuiler Employee -package "" -connection $conn                    ;# class for '''EMPLOYEE''' table
  ::mrpersister::DbObjBuiler Dept     -package "" -connection $conn -table department  ;# class for '''DEPARTMENT''' table
  ::mrpersister::DbObjBuiler EmpDept  -package "" -resultset $resultObj                ;# class for a specific query result

  $db execute {select a, b, from t} rowDict {
      array set arrayVar $rowDict
      break
  }

  $db array {select a, b from t} arrayVar

  set result [$db query 1row {select a, b from t}]
  foreach column {a b} value $result {
      set arrayVar($column) $value
  }

  set rObj [$db query {select a, b from t}]
  foreach column [$rObj columnNames] value [$rObj fetch] {
      set arrayVar($column) $value
  }

  $db read arrayVar {select a, b, from t}

  db_1row stmtName {select a, b from t} -columnVar arrayVar

  dbi_1row -array arrayVar {select a, b from t}

  $db eval {select a, b from t} arrayVar { break }

  # return an object, not an array of values.....
  set emp [gendao readByPrimaryKey_Integer Employee $key]     ;# read object by a primary key, OR
  set emp [gendao read Employee "select * from employee"]     ;# read object by explicit SQL

  # if you absolutely must have an array......
  $emp toArray [java::getinterp] arrayVar

  $db foreach -as lists {select a, b from t} row {
      foreach i $row {
          lappend list $i
      }
  }

  set llist [$db allrows -as lists {select a, b from t}]

  set list [list]
  $db forall {select a, b from t} row {
      lappend list $row(a) $row(b)
  }

  set llist [list]
  $db forall {select a, b from t} row {
      lappend llist [list $row(a) $row(b)
  }

  set list [$db query flatlist {select a, b from t}]
  set llist [$db query list {select a, b from t}]

  set list [list]
  foreach {a b} [$db queryList {select a, b from t}] {
      lappend list $a $b
  }

  set llist [$db queryList {select a, b from t}]

  set list [list]
  proc x {a b} {lappend list $a $b}
  $db eval x {select a, b from t}

  set llist [$db {select a, b from t}]

  set list [list]
  db_foreach stmtName {select a, b from t} {
      lappend list $a $b
  }

  set llist [db_list_of_lists stmtName {select a, b from t}]

  set list [dbi_rows {select a, b from t}]

  foreach {a b} $list {lappend llist [list $a $b]}

  set list [$db eval {select a, b from t}]

  $db function mklist ::list
  set llist [$db eval {select mklist(a, b) from t}]

  # return a Java list of objects, not a list of lists
  set empList [gendao readListByPrimaryKey Employee $keyList]       ;# read objects by a primary keylist, OR
  set empList [gendao readList Employee "select * from employee"]   ;# read objects by explicit SQL

  # This is not really a one-liner...
  $db execute {select a from t where k = 1} row {
      set string [dict values $row]
      break
  }

  set string [$db string {select a from t where k = 1]

  set string [lindex [$db query 1row {select a from t where k = 1}] 0]

  set string [lindex [[$db query {select a from t where k = 1}] fetch] 0]

  set string [lindex [$db {select a from t where k = 1}] 0]

  set string [database_to_tcl_string $db {select a from t where k = 1}]

  set string [dbi_rows -max 1 {select a from t where k = 1} {$a}]

  set string [$db onecolumn {select a from t where k = 1}]

  # return an object, access a single column (i.e., object property)
  set emp [gendao readByPrimaryKey_Integer Employee 1]
  set string [$emp getName]      ;# the 'name' column

  $db execute {select a, b from t} row {
      puts "[dict get $row a] : [dict get $row b]"
  }

  $db forall {select a, b from t} row {
      puts "$row(a) : $row(b)"
  }

  $db query foreach {a b} {select a, b from t} {
      puts "$a : $b"
  }

  set rObj [$db query {select a, b from t}]
  while {[llength [set row [$rObj fetch]]]} {
      puts "[lindex $row 0] : [lindex $row 1]"
  }

  foreach {a b} [$db {select a, b from t}] {
      puts "$a : $b"
  }

  db_foreach stmtName {select a, b from t} {
      puts "$a : $b"
  }

  puts [dbi_rows {select a, b from t} {$a : $b\n}]

  $db eval {select a, b from t} {
      puts "$a : $b"
  }

  # Alternatively
  $db eval {select a, b from t} row {
      puts "$row(a) : $row(b)"
  }

  # as a list of Java objects:
  set empList [gendao readList Employee "select * from employee"]
  java::for {Employee emp} $empList {
    puts "[$emp getName] [$emp getAddress]"
  }

  # as a JDBC result set
  jdbcdao read "select * from employee" rs {
    puts "[$rs getString name] [$rs getString address]"
  }

  set statementHandle [$db prepare {update t set a = 1}]
  set resultHandle [$statementHandle execute]
  set numRows [$resultHandle rows]
  $resultHandle close
  $statementHandle close

  set rObj [$db exec {update t set a = 1}]
  set numRows [$rObj numrows]
  # $rObj destroy ???

  ???

  set rObj [$db execute {update t set a = 1}]
  set numRows [$rObj rows]

  $db statement stmt {update t set a = 1}
  stmt execute
  set numRows [stmt rowcount]
  stmt drop

  db_dml stmtName {update t set a = 1}
  # ???

  set numRows [dbi_dml {update t set a = 1}]

  $db eval {update t set a = 1}
  set numRows [$db changes]

  set numRows [jdbcdao update "update t set a = 1"]

  $db insert $arrayVar -table demo

  set id $arrayVar(id)
  set name $arrayVar(id)
  db_dml statement {
      insert into demo (id , name) values (:id, :name)
  }

  $db insert demo {id name} [list $arrayVar(id) $arrayVar(value)]

  $db exec {insert into demo (id,name) values ('@ID@','@NAME@')} \
      id $arrayVar(id) name $arrayVar(name)

  $db {insert into demo(id,name) values(?,?)}  $id $name

  $db insertRow demo {id name} [list '$arrayVar(id)' '$arrayVar(value)']

  set valuesDict [dict create id 1 name "John Smith"]

  set statementHandle [$db prepare $query]
  $statementHandle execute $valuesDict
  $statementHandle close

  $db {insert into demo(id,name) values($id,$name)}

  # insert row as an object
  set emp [java::new Employee]
  $emp setId   $id
  $emp setName $name
  gendao insert $emp

  $db insert $arrayVar -table demo \
      -keyfield id -autokey 1 -sequence demo_seq

  set name $arrayVar(name)
  db_dml statement {
      insert into demo (id, name)
      values (
          (select * FROM nextval(demo_seq)
      ), :name)
  }

  $db insert_id id {name} [list $arrayVar(name)]

  (same as nstcl)

  $db rowInsertAutoId demo name [list $arrayVar(name)] id $sequencer

  ''No explicit support for automatic ID. As previous DML example''

  (same as nstcl?)

  # insert row as an object
  set emp [java::new Employee]
  # id property defaults to NULL
  $emp setName $name
  gendao insert $emp

  $db delete $key -table demo -keyfield id

  db_dml statement {
     delete from demo where id = :id d
  }

  $db delete demo id $id

  $db {delete from demo where id = ?} $id

  $db execute "delete from demo where id = $id"

  set statementHandle [$db prepare {delete from demo where id = :id}]
  $statementHandle execute [dict create id 1]
  $statementHandle close

  $db eval { delete from demo where id = $id }

  gendao deleteByPrimaryKey_Integer Employee $id

  set statementHandleOne [$db prepare $query1]
  set statementHandleTwo [$db prepare $query2]

  $db transaction {
      $statementHandleOne execute
      $statementHandleTwo execute
  }

  $statementHandleOne close
  $statementHandleTwo close

  $db exec {BEGIN TRANSACTION}
  ...
  # do some operations
  ...
  $db exec {COMMIT TRANSACTION}

  $db transaction {
      ...
      # do some operations
      ...
  }

  $db execute {BEGIN TRANSACTION}
  ...
  # do some operations
  ...
  $db execute {COMMIT TRANSACTION}

  $db set autocommit off
  ...
  # do some operations
  ...
  $db commit

  db_transaction {
     ...
     # do some operations
     ...
  }

  dbi_eval -transaction repeatable {

      # do some operations

      dbi_eval -transaction committed {

          # do some operations in a sub-transaction

      }
  }

  $db transaction ?type? {
     ...
  }

  perman executeTransaction tranDaos {
     # tranDaos object command created for scope of the code
     tranDaos getGenericDao anotherGendao
     # do stuff with 'anotherGendao' object ....
     # Tcl error causes rollback, otherwise commit
  }

• TDBC bind variables mandatory
• DIO* attempts autoquoting of values in queries
• tcldb binding variable for queries with autoquoting functions to quote values prepared statements parameter passing to the database
• XOSql provides escape method for simple value quoting
• tclodbc binding variable for queries with autoquoting prepared statements parameter passing to the database optional type hinting
• nstcl binding variables for queries with autoquoting functions to quote identifiers and values
• nsdbi bind variables mandatory, common syntax
• sqlite3 autoquoting of variables within queries, various syntaxes
• MrPersister object interface eliminates quoting issues, OR many methods can use a JDBC PreparedStatement object and bind variables by SQL datatype

  set match {'\' OR 1==1; --}
  $obj exec "SELECT * FROM users WHERE id = $match AND password = $passwd;"

• TDBC result row dicts with NULL values are missing from the dict. Bind variables...???
• DIO ???
• tcldb ???
• XOSql ???
• tclodbc ???
• nstcl ???
• nsdbi "" (empty string)
• sqlite3 "" by default, change with: $db nullvalue "NULL"
• MrPersister null values returned as NULL, i.e., same as result of [java::null]

• do any of these packages support handle pooling? (nstcl, nsdbi)
• finish section on NULL values, input/output
• binary data, input/output -- do any of these packages support this?
• need to make sure samples being compared are reasonable, e.g. not encouraging SQL injection attacks, missed transaction rollbacks etc.
• performance comparison
• compare other RDBMS abstraction layers: dbConnect, tcl dbi, Oratcl ?
• compare with non-relational DBMS packages? (probably out of scope)

• Home
• Recent changes
• Help
• WhoAmI/Logout
• Create new page
• Random page
• Previous page
• Next page
• 
  
• Add comments
• Edit
• History
• Edit summary
• References