jpayne@68: # tdbcodbc.tcl -- jpayne@68: # jpayne@68: # Class definitions and Tcl-level methods for the tdbc::odbc bridge. jpayne@68: # jpayne@68: # Copyright (c) 2008 by Kevin B. Kenny jpayne@68: # See the file "license.terms" for information on usage and redistribution jpayne@68: # of this file, and for a DISCLAIMER OF ALL WARRANTIES. jpayne@68: # jpayne@68: # RCS: @(#) $Id: tdbcodbc.tcl,v 1.47 2008/02/27 02:08:27 kennykb Exp $ jpayne@68: # jpayne@68: #------------------------------------------------------------------------------ jpayne@68: jpayne@68: package require tdbc jpayne@68: jpayne@68: ::namespace eval ::tdbc::odbc { jpayne@68: jpayne@68: namespace export connection datasources drivers jpayne@68: jpayne@68: # Data types that are predefined in ODBC jpayne@68: jpayne@68: variable sqltypes [dict create \ jpayne@68: 1 char \ jpayne@68: 2 numeric \ jpayne@68: 3 decimal \ jpayne@68: 4 integer \ jpayne@68: 5 smallint \ jpayne@68: 6 float \ jpayne@68: 7 real \ jpayne@68: 8 double \ jpayne@68: 9 datetime \ jpayne@68: 12 varchar \ jpayne@68: 91 date \ jpayne@68: 92 time \ jpayne@68: 93 timestamp \ jpayne@68: -1 longvarchar \ jpayne@68: -2 binary \ jpayne@68: -3 varbinary \ jpayne@68: -4 longvarbinary \ jpayne@68: -5 bigint \ jpayne@68: -6 tinyint \ jpayne@68: -7 bit \ jpayne@68: -8 wchar \ jpayne@68: -9 wvarchar \ jpayne@68: -10 wlongvarchar \ jpayne@68: -11 guid] jpayne@68: } jpayne@68: jpayne@68: #------------------------------------------------------------------------------ jpayne@68: # jpayne@68: # tdbc::odbc::connection -- jpayne@68: # jpayne@68: # Class representing a connection to a database through ODBC. jpayne@68: # jpayne@68: #------------------------------------------------------------------------------- jpayne@68: jpayne@68: ::oo::class create ::tdbc::odbc::connection { jpayne@68: jpayne@68: superclass ::tdbc::connection jpayne@68: jpayne@68: variable statementSeq typemap jpayne@68: jpayne@68: # The constructor is written in C. It takes the connection string jpayne@68: # as its argument It sets up a namespace to hold the statements jpayne@68: # associated with the connection, and then delegates to the 'init' jpayne@68: # method (written in C) to do the actual work of attaching to the jpayne@68: # database. When that comes back, it sets up a statement to query jpayne@68: # the support types, makes a dictionary to enumerate them, and jpayne@68: # calls back to set a flag if WVARCHAR is seen (If WVARCHAR is jpayne@68: # seen, the database supports Unicode.) jpayne@68: jpayne@68: # The 'statementCreate' method forwards to the constructor of the jpayne@68: # statement class jpayne@68: jpayne@68: forward statementCreate ::tdbc::odbc::statement create jpayne@68: jpayne@68: # The 'tables' method returns a dictionary describing the tables jpayne@68: # in the database jpayne@68: jpayne@68: method tables {{pattern %}} { jpayne@68: set stmt [::tdbc::odbc::tablesStatement create \ jpayne@68: Stmt::[incr statementSeq] [self] $pattern] jpayne@68: set status [catch { jpayne@68: set retval {} jpayne@68: $stmt foreach -as dicts row { jpayne@68: if {[dict exists $row TABLE_NAME]} { jpayne@68: dict set retval [dict get $row TABLE_NAME] $row jpayne@68: } jpayne@68: } jpayne@68: set retval jpayne@68: } result options] jpayne@68: catch {rename $stmt {}} jpayne@68: return -level 0 -options $options $result jpayne@68: } jpayne@68: jpayne@68: # The 'columns' method returns a dictionary describing the tables jpayne@68: # in the database jpayne@68: jpayne@68: method columns {table {pattern %}} { jpayne@68: # Make sure that the type map is initialized jpayne@68: my typemap jpayne@68: jpayne@68: # Query the columns from the database jpayne@68: jpayne@68: set stmt [::tdbc::odbc::columnsStatement create \ jpayne@68: Stmt::[incr statementSeq] [self] $table $pattern] jpayne@68: set status [catch { jpayne@68: set retval {} jpayne@68: $stmt foreach -as dicts origrow { jpayne@68: jpayne@68: # Map the type, precision, scale and nullable indicators jpayne@68: # to tdbc's notation jpayne@68: jpayne@68: set row {} jpayne@68: dict for {key value} $origrow { jpayne@68: dict set row [string tolower $key] $value jpayne@68: } jpayne@68: if {[dict exists $row column_name]} { jpayne@68: if {[dict exists $typemap \ jpayne@68: [dict get $row data_type]]} { jpayne@68: dict set row type \ jpayne@68: [dict get $typemap \ jpayne@68: [dict get $row data_type]] jpayne@68: } else { jpayne@68: dict set row type [dict get $row type_name] jpayne@68: } jpayne@68: if {[dict exists $row column_size]} { jpayne@68: dict set row precision \ jpayne@68: [dict get $row column_size] jpayne@68: } jpayne@68: if {[dict exists $row decimal_digits]} { jpayne@68: dict set row scale \ jpayne@68: [dict get $row decimal_digits] jpayne@68: } jpayne@68: if {![dict exists $row nullable]} { jpayne@68: dict set row nullable \ jpayne@68: [expr {!![string trim [dict get $row is_nullable]]}] jpayne@68: } jpayne@68: dict set retval [dict get $row column_name] $row jpayne@68: } jpayne@68: } jpayne@68: set retval jpayne@68: } result options] jpayne@68: catch {rename $stmt {}} jpayne@68: return -level 0 -options $options $result jpayne@68: } jpayne@68: jpayne@68: # The 'primarykeys' method returns a dictionary describing the primary jpayne@68: # keys of a table jpayne@68: jpayne@68: method primarykeys {tableName} { jpayne@68: set stmt [::tdbc::odbc::primarykeysStatement create \ jpayne@68: Stmt::[incr statementSeq] [self] $tableName] jpayne@68: set status [catch { jpayne@68: set retval {} jpayne@68: $stmt foreach -as dicts row { jpayne@68: foreach {odbcKey tdbcKey} { jpayne@68: TABLE_CAT tableCatalog jpayne@68: TABLE_SCHEM tableSchema jpayne@68: TABLE_NAME tableName jpayne@68: COLUMN_NAME columnName jpayne@68: KEY_SEQ ordinalPosition jpayne@68: PK_NAME constraintName jpayne@68: } { jpayne@68: if {[dict exists $row $odbcKey]} { jpayne@68: dict set row $tdbcKey [dict get $row $odbcKey] jpayne@68: dict unset row $odbcKey jpayne@68: } jpayne@68: } jpayne@68: lappend retval $row jpayne@68: } jpayne@68: set retval jpayne@68: } result options] jpayne@68: catch {rename $stmt {}} jpayne@68: return -level 0 -options $options $result jpayne@68: } jpayne@68: jpayne@68: # The 'foreignkeys' method returns a dictionary describing the foreign jpayne@68: # keys of a table jpayne@68: jpayne@68: method foreignkeys {args} { jpayne@68: set stmt [::tdbc::odbc::foreignkeysStatement create \ jpayne@68: Stmt::[incr statementSeq] [self] {*}$args] jpayne@68: set status [catch { jpayne@68: set fkseq 0 jpayne@68: set retval {} jpayne@68: $stmt foreach -as dicts row { jpayne@68: foreach {odbcKey tdbcKey} { jpayne@68: PKTABLE_CAT primaryCatalog jpayne@68: PKTABLE_SCHEM primarySchema jpayne@68: PKTABLE_NAME primaryTable jpayne@68: PKCOLUMN_NAME primaryColumn jpayne@68: FKTABLE_CAT foreignCatalog jpayne@68: FKTABLE_SCHEM foreignSchema jpayne@68: FKTABLE_NAME foreignTable jpayne@68: FKCOLUMN_NAME foreignColumn jpayne@68: UPDATE_RULE updateRule jpayne@68: DELETE_RULE deleteRule jpayne@68: DEFERRABILITY deferrable jpayne@68: KEY_SEQ ordinalPosition jpayne@68: FK_NAME foreignConstraintName jpayne@68: } { jpayne@68: if {[dict exists $row $odbcKey]} { jpayne@68: dict set row $tdbcKey [dict get $row $odbcKey] jpayne@68: dict unset row $odbcKey jpayne@68: } jpayne@68: } jpayne@68: # Horrible kludge: If the driver doesn't report FK_NAME, jpayne@68: # make one up. jpayne@68: if {![dict exists $row foreignConstraintName]} { jpayne@68: if {![dict exists $row ordinalPosition] jpayne@68: || [dict get $row ordinalPosition] == 1} { jpayne@68: set fkname ?[dict get $row foreignTable]?[incr fkseq] jpayne@68: } jpayne@68: dict set row foreignConstraintName $fkname jpayne@68: } jpayne@68: lappend retval $row jpayne@68: } jpayne@68: set retval jpayne@68: } result options] jpayne@68: catch {rename $stmt {}} jpayne@68: return -level 0 -options $options $result jpayne@68: } jpayne@68: jpayne@68: # The 'evaldirect' evaluates driver-native SQL code without preparing it, jpayne@68: # and returns a list of dicts (similar to '$connection allrows -as dicts'). jpayne@68: jpayne@68: method evaldirect {sqlStatement} { jpayne@68: set stmt [::tdbc::odbc::evaldirectStatement create \ jpayne@68: Stmt::[incr statementSeq] [self] $sqlStatement] jpayne@68: set status [catch { jpayne@68: $stmt allrows -as dicts jpayne@68: } result options] jpayne@68: catch {rename $stmt {}} jpayne@68: return -level 0 -options $options $result jpayne@68: } jpayne@68: jpayne@68: # The 'prepareCall' method gives a portable interface to prepare jpayne@68: # calls to stored procedures. It delegates to 'prepare' to do the jpayne@68: # actual work. jpayne@68: jpayne@68: method preparecall {call} { jpayne@68: jpayne@68: regexp {^[[:space:]]*(?:([A-Za-z_][A-Za-z_0-9]*)[[:space:]]*=)?(.*)} \ jpayne@68: $call -> varName rest jpayne@68: if {$varName eq {}} { jpayne@68: my prepare \\{CALL $rest\\} jpayne@68: } else { jpayne@68: my prepare \\{:$varName=CALL $rest\\} jpayne@68: } jpayne@68: jpayne@68: if 0 { jpayne@68: # Kevin thinks this is going to be jpayne@68: jpayne@68: if {![regexp -expanded { jpayne@68: ^\s* # leading whitespace jpayne@68: (?::([[:alpha:]_][[:alnum:]_]*)\s*=\s*) # possible variable name jpayne@68: (?:(?:([[:alpha:]_][[:alnum:]_]*)\s*[.]\s*)? # catalog jpayne@68: ([[:alpha:]_][[:alnum:]_]*)\s*[.]\s*)? # schema jpayne@68: ([[:alpha:]_][[:alnum:]_]*)\s* # procedure jpayne@68: (.*)$ # argument list jpayne@68: } $call -> varName catalog schema procedure arglist]} { jpayne@68: return -code error \ jpayne@68: -errorCode [list TDBC \ jpayne@68: SYNTAX_ERROR_OR_ACCESS_RULE_VIOLATION \ jpayne@68: 42000 ODBC -1] \ jpayne@68: "Syntax error in stored procedure call" jpayne@68: } else { jpayne@68: my PrepareCall $varName $catalog $schema $procedure $arglist jpayne@68: } jpayne@68: jpayne@68: # at least if making all parameters 'inout' doesn't work. jpayne@68: jpayne@68: } jpayne@68: jpayne@68: } jpayne@68: jpayne@68: # The 'typemap' method returns the type map jpayne@68: jpayne@68: method typemap {} { jpayne@68: if {![info exists typemap]} { jpayne@68: set typemap $::tdbc::odbc::sqltypes jpayne@68: set typesStmt [tdbc::odbc::typesStatement new [self]] jpayne@68: $typesStmt foreach row { jpayne@68: set typeNum [dict get $row DATA_TYPE] jpayne@68: if {![dict exists $typemap $typeNum]} { jpayne@68: dict set typemap $typeNum [string tolower \ jpayne@68: [dict get $row TYPE_NAME]] jpayne@68: } jpayne@68: switch -exact -- $typeNum { jpayne@68: -9 { jpayne@68: [self] HasWvarchar 1 jpayne@68: } jpayne@68: -5 { jpayne@68: [self] HasBigint 1 jpayne@68: } jpayne@68: } jpayne@68: } jpayne@68: rename $typesStmt {} jpayne@68: } jpayne@68: return $typemap jpayne@68: } jpayne@68: jpayne@68: # The 'begintransaction', 'commit' and 'rollback' methods are jpayne@68: # implemented in C. jpayne@68: jpayne@68: } jpayne@68: jpayne@68: #------------------------------------------------------------------------------- jpayne@68: # jpayne@68: # tdbc::odbc::statement -- jpayne@68: # jpayne@68: # The class 'tdbc::odbc::statement' models one statement against a jpayne@68: # database accessed through an ODBC connection jpayne@68: # jpayne@68: #------------------------------------------------------------------------------- jpayne@68: jpayne@68: ::oo::class create ::tdbc::odbc::statement { jpayne@68: jpayne@68: superclass ::tdbc::statement jpayne@68: jpayne@68: # The constructor is implemented in C. It accepts the handle to jpayne@68: # the connection and the SQL code for the statement to prepare. jpayne@68: # It creates a subordinate namespace to hold the statement's jpayne@68: # active result sets, and then delegates to the 'init' method, jpayne@68: # written in C, to do the actual work of preparing the statement. jpayne@68: jpayne@68: # The 'resultSetCreate' method forwards to the result set constructor jpayne@68: jpayne@68: forward resultSetCreate ::tdbc::odbc::resultset create jpayne@68: jpayne@68: # The 'params' method describes the parameters to the statement jpayne@68: jpayne@68: method params {} { jpayne@68: set typemap [[my connection] typemap] jpayne@68: set result {} jpayne@68: foreach {name flags typeNum precision scale nullable} [my ParamList] { jpayne@68: set lst [dict create \ jpayne@68: name $name \ jpayne@68: direction [lindex {unknown in out inout} \ jpayne@68: [expr {($flags & 0x06) >> 1}]] \ jpayne@68: type [dict get $typemap $typeNum] \ jpayne@68: precision $precision \ jpayne@68: scale $scale] jpayne@68: if {$nullable in {0 1}} { jpayne@68: dict set list nullable $nullable jpayne@68: } jpayne@68: dict set result $name $lst jpayne@68: } jpayne@68: return $result jpayne@68: } jpayne@68: jpayne@68: # Methods implemented in C: jpayne@68: # init statement ?dictionary? jpayne@68: # Does the heavy lifting for the constructor jpayne@68: # connection jpayne@68: # Returns the connection handle to which this statement belongs jpayne@68: # paramtype paramname ?direction? type ?precision ?scale?? jpayne@68: # Declares the type of a parameter in the statement jpayne@68: jpayne@68: } jpayne@68: jpayne@68: #------------------------------------------------------------------------------ jpayne@68: # jpayne@68: # tdbc::odbc::tablesStatement -- jpayne@68: # jpayne@68: # The class 'tdbc::odbc::tablesStatement' represents the special jpayne@68: # statement that queries the tables in a database through an ODBC jpayne@68: # connection. jpayne@68: # jpayne@68: #------------------------------------------------------------------------------ jpayne@68: jpayne@68: oo::class create ::tdbc::odbc::tablesStatement { jpayne@68: jpayne@68: superclass ::tdbc::statement jpayne@68: jpayne@68: # The constructor is written in C. It accepts the handle to the jpayne@68: # connection and a pattern to match table names. It works in all jpayne@68: # ways like the constructor of the 'statement' class except that jpayne@68: # its 'init' method sets up to enumerate tables and not run a SQL jpayne@68: # query. jpayne@68: jpayne@68: # The 'resultSetCreate' method forwards to the result set constructor jpayne@68: jpayne@68: forward resultSetCreate ::tdbc::odbc::resultset create jpayne@68: jpayne@68: } jpayne@68: jpayne@68: #------------------------------------------------------------------------------ jpayne@68: # jpayne@68: # tdbc::odbc::columnsStatement -- jpayne@68: # jpayne@68: # The class 'tdbc::odbc::tablesStatement' represents the special jpayne@68: # statement that queries the columns of a table or view jpayne@68: # in a database through an ODBC connection. jpayne@68: # jpayne@68: #------------------------------------------------------------------------------ jpayne@68: jpayne@68: oo::class create ::tdbc::odbc::columnsStatement { jpayne@68: jpayne@68: superclass ::tdbc::statement jpayne@68: jpayne@68: # The constructor is written in C. It accepts the handle to the jpayne@68: # connection, a table name, and a pattern to match column jpayne@68: # names. It works in all ways like the constructor of the jpayne@68: # 'statement' class except that its 'init' method sets up to jpayne@68: # enumerate tables and not run a SQL query. jpayne@68: jpayne@68: # The 'resultSetCreate' class forwards to the constructor of the jpayne@68: # result set jpayne@68: jpayne@68: forward resultSetCreate ::tdbc::odbc::resultset create jpayne@68: jpayne@68: } jpayne@68: jpayne@68: #------------------------------------------------------------------------------ jpayne@68: # jpayne@68: # tdbc::odbc::primarykeysStatement -- jpayne@68: # jpayne@68: # The class 'tdbc::odbc::primarykeysStatement' represents the special jpayne@68: # statement that queries the primary keys on a table through an ODBC jpayne@68: # connection. jpayne@68: # jpayne@68: #------------------------------------------------------------------------------ jpayne@68: jpayne@68: oo::class create ::tdbc::odbc::primarykeysStatement { jpayne@68: jpayne@68: superclass ::tdbc::statement jpayne@68: jpayne@68: # The constructor is written in C. It accepts the handle to the jpayne@68: # connection and a table name. It works in all jpayne@68: # ways like the constructor of the 'statement' class except that jpayne@68: # its 'init' method sets up to enumerate primary keys and not run a SQL jpayne@68: # query. jpayne@68: jpayne@68: # The 'resultSetCreate' method forwards to the result set constructor jpayne@68: jpayne@68: forward resultSetCreate ::tdbc::odbc::resultset create jpayne@68: jpayne@68: } jpayne@68: jpayne@68: #------------------------------------------------------------------------------ jpayne@68: # jpayne@68: # tdbc::odbc::foreignkeysStatement -- jpayne@68: # jpayne@68: # The class 'tdbc::odbc::foreignkeysStatement' represents the special jpayne@68: # statement that queries the foreign keys on a table through an ODBC jpayne@68: # connection. jpayne@68: # jpayne@68: #------------------------------------------------------------------------------ jpayne@68: jpayne@68: oo::class create ::tdbc::odbc::foreignkeysStatement { jpayne@68: jpayne@68: superclass ::tdbc::statement jpayne@68: jpayne@68: # The constructor is written in C. It accepts the handle to the jpayne@68: # connection and the -primary and -foreign options. It works in all jpayne@68: # ways like the constructor of the 'statement' class except that jpayne@68: # its 'init' method sets up to enumerate foreign keys and not run a SQL jpayne@68: # query. jpayne@68: jpayne@68: # The 'resultSetCreate' method forwards to the result set constructor jpayne@68: jpayne@68: forward resultSetCreate ::tdbc::odbc::resultset create jpayne@68: jpayne@68: } jpayne@68: jpayne@68: #------------------------------------------------------------------------------ jpayne@68: # jpayne@68: # tdbc::odbc::evaldirectStatement -- jpayne@68: # jpayne@68: # The class 'tdbc::odbc::evaldirectStatement' provides a mechanism to jpayne@68: # execute driver-name SQL code through an ODBC connection. The SQL code jpayne@68: # is not prepared and no tokenization or variable substitution is done. jpayne@68: # jpayne@68: #------------------------------------------------------------------------------ jpayne@68: jpayne@68: oo::class create ::tdbc::odbc::evaldirectStatement { jpayne@68: jpayne@68: superclass ::tdbc::statement jpayne@68: jpayne@68: # The constructor is written in C. It accepts the handle to the jpayne@68: # connection and a SQL statement. It works in all jpayne@68: # ways like the constructor of the 'statement' class except that jpayne@68: # its 'init' method does not tokenize or prepare the SQL statement, and jpayne@68: # sets up to run the SQL query without performing variable substitution. jpayne@68: jpayne@68: # The 'resultSetCreate' method forwards to the result set constructor jpayne@68: jpayne@68: forward resultSetCreate ::tdbc::odbc::resultset create jpayne@68: jpayne@68: } jpayne@68: jpayne@68: #------------------------------------------------------------------------------ jpayne@68: # jpayne@68: # tdbc::odbc::typesStatement -- jpayne@68: # jpayne@68: # The class 'tdbc::odbc::typesStatement' represents the special jpayne@68: # statement that queries the types available in a database through jpayne@68: # an ODBC connection. jpayne@68: # jpayne@68: #------------------------------------------------------------------------------ jpayne@68: jpayne@68: jpayne@68: oo::class create ::tdbc::odbc::typesStatement { jpayne@68: jpayne@68: superclass ::tdbc::statement jpayne@68: jpayne@68: # The constructor is written in C. It accepts the handle to the jpayne@68: # connection, and (optionally) a data type number. It works in all jpayne@68: # ways like the constructor of the 'statement' class except that jpayne@68: # its 'init' method sets up to enumerate types and not run a SQL jpayne@68: # query. jpayne@68: jpayne@68: # The 'resultSetCreate' method forwards to the constructor of result sets jpayne@68: jpayne@68: forward resultSetCreate ::tdbc::odbc::resultset create jpayne@68: jpayne@68: # The C code contains a variant implementation of the 'init' method. jpayne@68: jpayne@68: } jpayne@68: jpayne@68: #------------------------------------------------------------------------------ jpayne@68: # jpayne@68: # tdbc::odbc::resultset -- jpayne@68: # jpayne@68: # The class 'tdbc::odbc::resultset' models the result set that is jpayne@68: # produced by executing a statement against an ODBC database. jpayne@68: # jpayne@68: #------------------------------------------------------------------------------ jpayne@68: jpayne@68: ::oo::class create ::tdbc::odbc::resultset { jpayne@68: jpayne@68: superclass ::tdbc::resultset jpayne@68: jpayne@68: # Methods implemented in C include: jpayne@68: jpayne@68: # constructor statement ?dictionary? jpayne@68: # -- Executes the statement against the database, optionally providing jpayne@68: # a dictionary of substituted parameters (default is to get params jpayne@68: # from variables in the caller's scope). jpayne@68: # columns jpayne@68: # -- Returns a list of the names of the columns in the result. jpayne@68: # nextdict jpayne@68: # -- Stores the next row of the result set in the given variable in jpayne@68: # the caller's scope as a dictionary whose keys are jpayne@68: # column names and whose values are column values. jpayne@68: # nextlist jpayne@68: # -- Stores the next row of the result set in the given variable in jpayne@68: # the caller's scope as a list of cells. jpayne@68: # rowcount jpayne@68: # -- Returns a count of rows affected by the statement, or -1 jpayne@68: # if the count of rows has not been determined. jpayne@68: jpayne@68: }