dbConnect is an Itcl object that allows working with different databases. it was developed base on ns_database idea but it has better performance as it does not add a lot of wrapping around the sql. we use it in clearance gateway. it works on unix/windows using different database. currently dbConnect support mysql(mysqltcl or sql package),oracle( version 3,4),sqlServer with tclodbc. adding support for another database should not be a hard task. dbConnect needs of cource the database specific package. db it is support transaction the base interface is: dbConnect ::db db loadDriver $type type is db type mysql oracle sqlserver odbc-mysql odbc-oracle db connect $user $password $name $host name is database name host is where db is (localhost) simple query db sql $sql db commit - for commiting changes (the default is autocommit) db rollback - for rollback
database specific function can be used by db now db getDate db toDate db year db day db month
db insertGetID $sql $idField ($sequenceName only in oracle) insert to seuqence or auto_increment column and getting the new id where sql is the sql statement and idField is the id field.
on create command dbConnect will format the create statement to database create types. support fields are varchar,char,tinyint,smallint,int,bigint,date and UNIQUE_ID for auto_increment integer (or oracle sequence)
we use this object in production code (although it is beta) and it works fine.
package provide dbConnect 1.1
package require Itcl
itcl::class dbConnect {
private variable driver
private variable user
private variable password
private variable database
private variable conn
private variable selectCommand
private variable execCommand
private variable commitCommand
private variable rollbackCommand
private variable setAutoCommitCommand
private variable autoCommit 1
private variable connectCommand
private variable disconnectCommand
private variable sequenceCommand
private variable limitCommand
private variable formatCreate
private variable insertGetIdCommand
private variable functions
constructor {{driverName ""}} {
initFunctions
if {$driverName!=""} {
loadDriver $driverName
}
}
destructor {
catch {eval $disconnectCommand} msg
}
private method initFunctions {} {
array set functions [list \
toDate '\$mytimefmt' \
getDate \$column
]
}
public method loadDriver {{driverName ""}} {
set driver $driverName
set dbType $driverName
switch -glob $driverName {
"oracle" {
if {[catch { package require Oratcl 4 }]} {
package require Oratcl 3
set selectCommand "oracleOratcl3Select"
} else {
set selectCommand "oracleOratcl4Select"
}
set execCommand "oracleExec"
set connectCommand "oracleConnect"
set disconnectCommand "oracleDisconnect"
}
"mysql" {
if {[catch {package require mysqltcl}]} {
package require sql
set connectCommand "mysqlSqlConnect"
set disconnectCommand "mysqlSqlDisconnect"
set selectCommand "mysqlSqlSelect"
set execCommand "mysqlSqlExec"
} else{
set disconnectCommand "mysqlMysqltclDisconnect"
set connectCommand "mysqlMysqltclConnect"
set selectCommand "mysqlMysqltclSelect"
set execCommand "mysqlMysqltclExec"
}
}
"odbc*" {
package require tclodbc
set connectCommand "odbcConnect"
set disconnectCommand "odbcDiconnect"
set selectCommand "odbcSelect"
set execCommand "odbcExec"
set setAutoCommitCommand "odbcSetAutoCommitCommand"
set commitCommand "odbcCommit"
set rollbackCommand "odbcRollback"
set dbType [lindex [split $driverName "-"] 1]
}
default {
error "Unsupported driver $driverName. supported drivers are : oracle,mysql,odbc,odbc-mysql,odbc-sqlserver,odbc-oracle"
}
}
switch $dbType {
"mysql" {
set insertGetIdCommand "mysqlInsertGetID"
set sequenceCommand "mysqlSequence"
set limitCommand "mysqlLimit"
set formatCreate "mysqlSqlFormat"
set functions(now) "now()"
set setAutoCommitCommand "mysqlSetAutoCommitCommand"
set commitCommand "mysqlCommitCommand"
set rollbackCommand "mysqlRollbackCommand"
}
"" -
"sqlserver" {
set sequenceCommand "sqlserverSequence"
set insertGetIdCommand "sqlServerInsertGetId"
set limitCommand "sqlServerLimit"
set formatCreate "sqlserverFormat"
set functions(now) "getdate()"
set functions(getDate) "convert(varchar(19),\$column,20)"
}
"oracle" {
set sequenceCommand "oracleSequence"
set insertGetIdCommand "oracleInsertGetId"
set limitCommand "OracleLimit"
set formatCreate "oracleFormat"
set setAutoCommitCommand "oracleSetAutoCommitCommand"
set commitCommand "oracleCommitCommand"
set rollbackCommand "oracleRollbackCommand"
set functions(now) "sysdate"
set functions(toDate) "to_date('\$mytimefmt','YYYY-MM-DD HH24:MI:SS')"
set functions(getDate) {to_char(\$column,'YYYY-MM-DD HH24:MI:SS')}
}
default {
error "Unsupported odbc driver. supported drivers are odbc,odbc-mysql,odbc-sqlserver,odbc-oracle"
}
}
return 0
}
public method connect {inUser inPassword inDatabase {inHost ""} {autoCommit 1}} {
$connectCommand $inUser $inPassword $inDatabase $inHost
setAutoCommit $autoCommit
}
public method disconnect {} {
eval $disconnectCommand
}
public method sql {sql} {
set isError [catch {
set command [string tolower [string range [string trimleft $sql " "] 0 5]]
switch -glob -- $command {
"select" {
set result [$selectCommand $sql]
}
"create" {
set sql [$formatCreate $sql]
if {[catch {
set result [$execCommand $sql]
} msg]} {
error "$msg. sql: $sql"
}
}
"insert" -
"delete" -
"update" -
"alter*" -
"drop*" {
set result [$execCommand $sql]
}
default {
error "wrong sql command $command"
}
}
} msg]
if {$isError} {
error $msg
}
return $result
}
public method select {sql} {
return [eval $selectCommand]
}
public method toDate {seconds} {
set mytimefmt [clock format $seconds -format "%Y-%m-%d %H:%M:%S"]
return [subst $functions(toDate)]
}
public method getDate {column} {
return [subst $functions(getDate)]
}
public method getSequence {sequenceName} {
return [eval $sequenceCommand]
}
public method insertGetId {sql {idField ""} {sequenceName ""}} {
return [$insertGetIdCommand $sql $idField $sequenceName]
}
public method now {} {
return $functions(now)
}
public method limitSql {sql count} {
return [$limitCommand $sql $count]
}
public method rollback {} {
return [$rollbackCommand]
}
public method commit {} {
return [$commitCommand]
}
public method setAutoCommit {value} {
return [$setAutoCommitCommand $value]
}
private method mysqlSqlExec {sql} {
::sql exec $conn $sql
}
private method mysqlSqlSelect {sql} {
::sql query $conn $sql
set result ""
while {[set row [::sql fetchrow $conn]]!=""} {
lappend result $row
}
::sql endquery $conn
return $result
}
private method mysqlSqlDisconnect {} {
::sql disconnect $conn
}
private method mysqlSqlConnect {user password database host} {
set conn [::sql connect $host $user $password]
::sql selectdb $conn $database
}
private method mysqlInsertGetID {sql {idField ""} {sequenceName ""}} {
$execCommand $sql
set sql "select last_insert_id()"
return [$selectCommand $sql]
}
private method mysqlSequence {sql} {
set sql "UPDATE $sequenceName set id=LAST_INSERT_ID(id+1)"
$execCommand
set sql "select last_insert_id() from $sequenceName"
return [$selectCommand]
}
private method mysqlSqlFormat {sql} {
regsub -nocase -all {UNIQUE_ID} $sql {integer auto_increment} sql
regsub -nocase -all {\sdate} $sql { datetime} sql
regsub -nocase -all {\svarchar\((2[5-9][0-9]|[1-9][0-9]{3,})\)} $sql { text} sql
return $sql
}
private method mysqlMysqltclConnect {user password database host} {
set conn [mysqlconnect -user $user -password $password -db $database -host $host]
}
private method mysqlMysqltclDisconnect {} {
mysqlclose $conn
}
private method mysqlMysqltclSelect {sql} {
mysqlsel $conn $sql
set result ""
while {[set row [mysqlnext $conn]]!=""} {
lappend result $row
}
return $result
}
private method mysqlMysqltclExec {sql} {
mysqlexec $conn $sql
}
private method mysqlLimit {sql count} {
append sql " limit $count"
sql $sql
}
private method mysqlSetAutoCommitCommand {flag} {
set sql "set autocommit=$flag"
return [$execCommand $sql]
}
private method mysqlCommitCommand {} {
return [$execCommand "commit"]
}
private method mysqlRollbackCommand {} {
return [$execCommand "rollback"]
}
private method oracleOratcl3Select {sql} {
set aconn [oraopen $conn]
orasql $aconn $sql
set result ""
while {[set row [orafetch $aconn]]!=""} {
lappend result $row
}
return $result
}
private method oracleOratcl4Select {sql} {
set aconn [oraopen $conn]
orasql $aconn $sql
set result ""
while {![orafetch $aconn -datavariable row]} {
lappend result $row
}
oraclose $aconn
return $result
}
private method oracleExec {sql} {
set aconn [oraopen $conn]
set result [orasql $aconn $sql ]
oraclose $aconn
return $result
}
private method oracleConnect {user password database host} {
set conn [oralogon $user/$password@$database]
}
private method oracleDisconnect {} {
oralogoff $conn
}
private method oracleFormat {sql} {
array set Entity {
tiny 2
small 4
"" 9
big 13
}
regsub -all -nocase {varchar} $sql {varchar2} sql
regsub -all -nocase {UNIQUE_ID} $sql {number(9)} sql
regsub -all -nocase {\s([a-z]{0,5})int\s} $sql {number($Entity(\1))} sql
set sql [subst -nocommand -nobackslash $sql]
set tableName [lindex $sql 2]
regsub -all -nocase {primary key} $sql "constraint pk_$tableName primary key" sql
return $sql
}
private method oracleSequence {sql} {
set sql "select ${sequenceName}.nextval from dual"
set sequenceNum [sql $sql]
}
private method oracleInsertGetId {sql sequenceName idField} {
set tableName [lindex $sql 2]
if {$sequenceName==""} {
set sequenceName "seq_$tableName"
}
set sqlSeq "select ${sequenceName}.nextval from dual"
set sequenceValue [sql $sqlSeq]
set sqlInsert "insert into $tableName ($idField,[string range [lindex $sql 3] 1 end] values ($sequenceValue,[string range [lrange $sql 5 end] 1 end]"
sql $sqlInsert
return $sequenceValue
}
private method oracleLimit {sql count} {
incr count
if {[string first "where" $sql]==-1} {
append sql " where "
} else {
append sql " and "
}
append sql "rownum<$count"
sql $sql
}
private method oracleSetAutoCommitCommand {flag} {
return [oraautocom $conn $flag]
}
private method oracleCommitCommand {} {
return [oracommit $conn]
}
private method oracleRollbackCommand {} {
return [oraroll $conn]
}
private method sqlserverSequence {sql} {
set sql ""
sql $sql
set sql ""
set sequenceNum [sql $sql]
}
private method sqlServerInsertGetId {sql {sequenceName ""} {idField ""}} {
sql $sql
set getIdSql "select @@identity"
set id [sql $getIdSql]
}
private method sqlServerLimit {sql count} {
sql "set rowcount $count"
sql $sql
sql "set rowcount 0"
}
private method sqlserverFormat {sql} {
regsub -nocase {UNIQUE_ID} $sql {integer identity(1,1)} sql
regsub -nocase {\sdate} $sql { datetime} sql
set tableName [lindex $sql 2]
regsub -all -nocase {primary key} $sql "constraint pk_$tableName primary key" sql
return $sql
}
private method odbcConnect {user password database host} {
set conn [database conn $database $user $password]
}
private method odbcDiconnect {} {
$conn disconnect
}
private method odbcSelect {sql} {
$conn $sql
}
private method odbcExec {sql} {
$conn $sql
}
private method odbcSetAutoCommitCommand {flag} {
set sql "set autocommit=$flag"
return [$execCommand $sql]
}
private method odbcCommitCommand {} {
return [$execCommand "commit"]
}
private method odbcRollbackCommand {} {
return [$execCommand "rollback"]
}
}