snichols I've written another ODBC extension for Tcl and have tested it extensively on Windows using SQL Server 7 and SQL Server 2000. However, it should be able to work with Oracle, Access, etc. The original version was written a few years ago. I took a college course on C/C++ and wanted to apply what I had learned, and thought this would be something fun to program. I work I most program in strait Tcl and VB and do not get much of an opportunity to program in C or C++. A few months ago I added a few new Tcl commands to expose a few more ODBC methods to Tcl. At the time when I wrote it, I didn't even know there was already another flavor of Tcl ODBC available until after it was released. When I wrote this extension I was still fairly new to Tcl and did not know about making an extension feel Tclish. If anyone is interested I can update the extension to be more Tclish. FYI..I've tested it with Tcl Threads and it seems stable in those too. The only gotcha I know is to make sure this runs in a different Tcl Thread then Tdom. The more popular ODBC Tcl extension TclODBC is much more Tclish', but please let me know what you think of this one.
The project files and the Windows compiled library (DLL) can be downloaded from SourceForge.net at the following link.
https://sourceforge.net/projects/tclsql/
I have included the sources, and directions in the zipped file if you wish to review them.
Also, I've included a sample Win32 Tk application under TclSQL 1.2 file releases of a Tcl kit file and starpack of how the extension might be used. It's called DBTool.kit and DBTool.exe. It allows a user to make changes to already existing SQL tables: insert rows, add rows, alter fields, etc. using Tktable. It seems to work fine with SQL Server 7 and 2000. It should work with other DB's too, but the SQL update query in the Tcl source may have to be updated. The Tcl sources also show how to embed widgets into cells so a user does not fat finger a critical DB field.
Directions and sample Tcl code I pasted from a Word document:
ODBC 3.0 Tcl Extension
Authored By: Scott Nichols
Date: February 23rd, 2005
Table of Contents SQL_TCL 1.2 ODBC TCL Extension Overview 3 ODBC TCL Extension Installation 3 About the TCL Programming Examples 3 SQL TCL Commands 4 SQLConnect 4 SQLDriverConnect 5 SQLExecDirect 8 SQLFetch 9 SQLFetchAll 11 SQLMoreResults 13 SQLDisconnect 15 SQLCloseCursor 16 SQLFreeStmt 17 SQLCancel 18 SQLGetDiagRec 21 SQLRowCount 22 SQLPrimaryKeys 22 SQLTables 22 SQLForeignKeys 22 SQLStatistics 22 SQLTablePrivileges 22 SQLProcedures 23 SQLSpecialColumns 23 SQLDataSources 23 This command is not yet documented but functional.TCL Programming Suggestions From the Extension’s Author 23 TCL Programming Suggestions From the Extension’s Author 24 Known Issues and Caveats 25 Legal Disclaimer 25
SQL_TCL 1.2 ODBC TCL Extension Overview The SQL_TCL ODBC 3.0 TCL extension provides away of integrating Microsoft Windows Platform 8.3 TCL interpreters to ODBC 3.0 compliant Database Management Systems (DBMS). The C source code for the extension incorporates two APIs: The TCL 8.3 API and the Microsoft ODBC 3.0 API. This extension includes the following features:
1) Can support simultaneous database connections to different DBMS’s from within one TCL interpreter. There is currently no limit to the number of DBMS’s the extension can connect to other than free memory on the computer from which the interpreter is running. 2) Supports both synchronous and asynchronous SQL query requests. An asynchronous request statement is useful if you know the query will take time to run and do not want to tie up the user. 3) Supports both ODBC system DSN database connections and ODBC driver connections. 4) Allows the TCL application programmer to optionally choose to set the SQL requests to return meta data (column headers). Meta data can be useful for ad hoc queries. 5) Empowers The TCL application programmer to decide whether or not to retrieve one row at time from the result set or all of the rows. 6) Supports the retrieving and parsing of more than one result set from one SQL statement. This is useful for executing and retrieving results from SQL batch queries.
ODBC TCL Extension Installation
1. Extract all of the contents of the SQL_TCL.zip to a temporary location. 2. Copy the folder, SQL_TCL to your TCL lib directory. Example: D:\TCL\Lib\SQL_TCL 3. There are two ways to load the extension into the TCL interpreter: A. package require SQL B. load <Insert File Path>/SQL_TCL.dll
About the TCL Programming Examples
All of the TCL ODBC coding examples within this document were done using a Microsoft SQL Server 7.0 database (DBMS), and he Northwind database that comes with the install of Microsoft SQL Server. But, the TCL extension is compatible with other ODBC windows drivers such as Microsoft Access, Sybase 11, Sybase Adaptive Server Anywhere, Oracle’s DBMS, IBM DB2, etc.
SQL TCL Commands
SQLConnect This command is used to connect to an existing Open Database Connection (ODBC) system data source name (DSN).
Syntax SQLConnect DSN UID Password timeout
Parameters DSN This is the system ODBC DSN connection to use.
UID This is the user ID to use with the connection.
Password This is the password to use with the connection.
Timeout This is an optional parameter that sets the timeout in seconds to connect and login to the DBMS. The library extension is expecting a numeric value.
Return Values If the connection and login are successful an ODBC handle is returned to the TCL interpreter. This handle incorporates three handles in one: an environment handle, connection handle, and statement handle.
If the connection fails a TCL error is raised and a handle is not returned, and description of the connection error is returned to the TCL interpreter.
Example
package require SQL
namespace import -force ::SQL::*
if { [catch {
# Connect to the Northwind ODBC system DSN with a UID of sa,
# a blank password, and a 10 second timeout.
set odbcHandle [SQLConnect Northwind sa “” 10]
} doRequestCatch] } {
puts “Connection and Login Failure: $odbcHandle”
}
SQLDriverConnect This command is used to connect to a DBMS using a connection string.
Syntax SQLDirverConnect ConnectionString timeout
Parameters ConnectionString This is the connection string to use to connect to the DBMS. At a minimum this should include the driver name, server name, database name, UID, and password within the connection string.
Timeout This is an optional parameter that sets the timeout in seconds to connect and login to the DBMS. The library extension is expecting a numeric value for this.
Return Values If the connection and login are successful an ODBC handle is returned to the TCL interpreter. This handle incorporates three handles in one: an environment handle, a connection handle, and a statement handle.
If the connection fails a TCL error is raised and a handle is not returned. A description of the error is returned to the TCL interpreter.
Example
package require SQL
namespace import -force ::SQL::*
if { [catch {
# Connect to the Northwind database using a valid
# connection string
set odbcHandle [SQLDriverConnect “Driver={SQL Server};server=localhost;database=Northwind;UID=sa;PWD=”]
} doRequestCatch] } {
puts “Connection and Login Failure: $odbcHandle”
}
SQLSetStmtAttrBefore using this command you must first have a valid connection handle to the DBMS. This is created using either SQLConnect or SQLDriverConnect. This command can be used to set three statement handles attributes before executing a SQLExecDirect request:
1) Maximum rows returned from future queries. 2) Query timeout to be used by future queries in seconds. 3) Asynchronous statement requests or synchronous statement synchronous is the default requests. The value can either be 1 (ON) or 0 (OFF) values.
Syntax
$odbcHandle SQLSetStmtAttr attribute value
Parameters
$odbcHandle
This is the handle that was already created with either using SQLConnect or SQLDriverConnect.
SQLSetStmtAttrr This tells the TCL library extension you are performing a statement handle attribute change request. All statement attribute change requests need to be done before running the SQLExecDirect command.
attribute This can be one of three possible choices (please let me know what others you would like to see): · SQL_ATTR_MAX_ROWS By default there is no limit on the number of rows in a result set. · SQL_ATTR_QUERY_TIMEOUT · SQL_ATTR_ASYNC_ENABLE
value The new statement value to use. The library extension is expecting a numeric value.
Return Values If the statement change request is successful a return code of 0 is returned. Anything other than 0 is an error. For a listing of possible error codes please view the error codes section in this document.
Example
package require SQL
namespace import -force ::SQL::*
if { [catch {
# Connect to the Northwind database using a valid
# connection string
set odbcHandle [SQLDriverConnect “Driver={SQL Server};server=localhost;database=Northwind;UID=sa;PWD=”]
# Set max return rows to 10
set rc [$odbcHandle SQLSetStmtAttr SQL_ATTR_MAX_ROWS 10]
puts “Statement Change Max Requests: $rc”
# Set SQL Query Timeout to 5 seconds
set rc [$odbcHandle SQLSetStmtAttr SQL_ATTR_QUERY_TIMEOUT 5]
puts “Statement Change Query Timeout: $rc”
# Set SQL Queries to Asynchronous
set rc [$odbcHandle SQLSetStmtAttr SQL_ATTR_ASYNC_ENABLE 1]
puts “Statement Change Asynchronous: $rc”
} doRequestCatch] } {
puts “Connection and Login Failure: $odbcHandle”
}SQLExecDirect This TCL command is used to run a SQL statement query. It can be a Select, Update, Insert, or stored procedure call. It can also include more than one SQL query within the statement. Before using this command you must first have a valid connection handle to the DBMS, and the SQL cursor must not be in the middle of a fetch or execution. To ensure the cursor is in the correct state use the SQLCloseCursor TCL command or SQLFreeStmt TCL command first before using SQLExecDirect. These commands are described later in this document. Syntax
$odbcHandle SQLExecDirect query
Parameters
$odbcHandle
This is the handle that was already created with either using SQLConnect or SQLDriverConnect.
query This is the SQL query to execute.
Return Values If the SQL query is successful a return code of 0 or 1 is returned to the TCL interpreter. A return code of 2 means the query is still running. You will get a return code of 2 if you have turned on asynchronous request statements. A negative return code is an error. To get an English description of the error use the SQLGetDiagRec TCL command described later in this manual.
Example
package require SQL
namespace import -force ::SQL::*
if { [catch {
set myHandle [SQLDriverConnect "Driver={SQL Server};server=localhost; database=Northwind;UID=sa;PWD="]
# Create a SQL query
set query "Select * from Customers"
set rc [$myHandle SQLExecDirect $query]
if { $rc !=0 && $rc !=1} {
set rc [$myHandle SQLGetDiagRec myError SQL_HANDLE_STMT]
puts $rc
}
} doRequestCatch] } {
puts "SQL_ERROR: $doRequestCatch"
}
SQLFetch
This TCL command is used to get one row of the result set. Before using this command you must first have created a valid DBMS handle and ran a SQL query using the SQLExecDirect TCL command.
Syntax
$odbcHandle SQLFetch myRow [myHeaders]
Parameters
$odbcHandle
This is the handle that was already created with either using SQLConnect or SQLDriverConnect.
myRow This is the TCL variable to store the row of data. The data is returned in the form of a TCL list.
myHeaders This is the TCL variable to store the column headers (A.K.A meta data). This argument is optional. The data is returned in the form of a TCL list
Return Values The return value is the return code from the DBMS. And, if it is successful returns either 0 or 1. A return code of 100 is a either a no match condition or you have retrieved all of the rows. A negative return code is an error. To get a English description of the error use SQLGetDiagRec described later in this document.
Example
package require SQL
namespace import -force ::SQL::*
if { [catch {
set myHandle [SQLDriverConnect "Driver={SQL Server};server=localhost;database=Northwind;UID=sa;PWD="]
puts $myHandle
# Initialize TCL variables to NULL
set rc ""
set headers ""
# Create a SQL Query
set query "Select * from Customers;"
# Set Max return rows to 100
$myHandle SQLSetStmtAttr SQL_ATTR_MAX_ROWS 100
# Set Query Time out to 10 secons
$myHandle SQLSetStmtAttr SQL_ATTR_QUERY_TIMEOUT 10
# Some possible return values:
#
# SQL_SUCCESS 0
# SQL_SUCCESS_WITH_INFO 1
# SQL_NO_DATA 100
# SQL_ERROR (-1)
# SQL_INVALID_HANDLE (-2)
# SQL_STILL_EXECUTING 2
# SQL_NEED_DATA 99
#
set rc [$myHandle SQLExecDirect $query]
# Was the query successful?
while {$rc == 0 || $rc == 1} {
# Get the row of data
set rc [$myHandle SQLFetch result headers]
if { $rc == 0 || $rc == 1} {
puts $headers
puts $result
}
}
# Disconnect from the data source
$myHandle SQLDisconnect
} doRequestCatch] } {
puts "SQL_ERROR: $doRequestCatch"
}
SQLFetchAll
This TCL command is used to get all of the rows in a result set. Before using this command you must first have created a valid DBMS handle and ran a SQL query using the SQLExecDirect TCL command. Caution when using this command, unless you have set the maximum return rows attribute this could take a while and use up resources on the computer where the TCL interpreter is running and the DBMS. You will get better performance out of SQLFetch.
Syntax
$odbcHandle SQLFetchAll myRows [myHeaders]
Parameters $odbcHandle This is the handle that was already created with either using SQLConnect or SQLDriverConnect.
myRows This is the TCL variable to store the rows of data in. The data is returned in the form of a two dimensional TCL list.
myHeaders This is the TCL variable to store the column headers (A.K.A meta data). This argument is optional. The data is returned in the form of a TCL list
Return Values The return value is the return code from the DBMS. And, if it is successful returns either 0 or 1. A return code of 100 is a either a no match condition. A negative return code is an error. To get an English description of the error use SQLGetDiagRec described later in this document
Example
package require SQL
namespace import -force ::SQL::*
if { [catch {
set myHandle [SQLDriverConnect "Driver={SQL Server};server=localhost;database=Northwind;UID=sa;PWD="]
puts $myHandle
# Intialize TCL variables to NULL
set rc ""
set headers ""
# Create a SQL batch statement
# This is called a batch statement because there is more than one result set.
set query "Select * from Customers;\n"
append query "Select * from Orders;\n"
append query "Select * from Products;"
# Set Max return rows to 10
$myHandle SQLSetStmtAttr SQL_ATTR_MAX_ROWS 10
# SQL_SUCCESS 0
# SQL_SUCCESS_WITH_INFO 1
# SQL_NO_DATA 100
# SQL_ERROR (-1)
# SQL_INVALID_HANDLE (-2)
# SQL_STILL_EXECUTING 2
# SQL_NEED_DATA 99
set rc [$myHandle SQLExecDirect $query]
# Were only interested in the data if it was successful: a one or zero return code.
if {$rc == 0 || $rc == 1} {
# Get the first result set
set rc [$myHandle SQLFetchAll result headers]
puts $headers
puts $result
# Display the remaining result sets
while {$rc==0 || $rc==1} {
set rc [$myHandle SQLMoreResults]
if { $rc == 0 || $rc == 1} {
set returnCode [$myHandle SQLFetchAll result headers]
puts $headers
puts $result
}
}
}
# Get the Statement Handle Error Description
if { $rc < 1 } {
set rc [$myHandle SQLGetDiagRec myError SQL_HANDLE_STMT]
if {$rc == 0} {
puts $myError
}
}
# Disconnect from the data source
$myHandle SQLDisconnect
} doRequestCatch] } {
puts "SQL_ERROR: $doRequestCatch"
}
SQLMoreResults
This TCL command is used to get the next result set if there is one, and points the SQL cursor at the next result set. Before using this command you must first have a valid DMBS handle and have executed a statement using SQLExecDirect.
Syntax $odbcHandle SQLMoreResults
Parameters $odbcHandle This is the handle that was already created with either using SQLConnect or SQLDriverConnect.
Return Values The return value is the return code from the DBMS. And, if it is successful returns either 0 or 1. A return code of 100 means there are no more result sets. A negative return code is an error. To get an English description of the error use SQLGetDiagRec described later in this document
Example
package require SQL
namespace import -force ::SQL::*
if { [catch {
set myHandle [SQLDriverConnect "Driver={SQL Server};server=localhost;database=Northwind;UID=sa;PWD="]
puts $myHandle
# Intialize TCL variables to NULL
set rc ""
set headers ""
# Create a SQL batch statement
# This is called a batch statement because there is more than one result set.
set query "Select * from Customers;\n"
append query "Select * from Orders;\n"
append query "Select * from Products;"
# Set Max return rows to 10
$myHandle SQLSetStmtAttr SQL_ATTR_MAX_ROWS 10
# SQL_SUCCESS 0
# SQL_SUCCESS_WITH_INFO 1
# SQL_NO_DATA 100
# SQL_ERROR (-1)
# SQL_INVALID_HANDLE (-2)
# SQL_STILL_EXECUTING 2
# SQL_NEED_DATA 99
set rc [$myHandle SQLExecDirect $query]
# Were only interested in the data if it was successful: a one or zero return code.
if {$rc == 0 || $rc == 1} {
# Get the first result set
set rc [$myHandle SQLFetchAll result headers]
puts $headers
puts $result
# Display the remaining result sets
while {$rc==0 || $rc==1} {
set rc [$myHandle SQLMoreResults]
if { $rc == 0 || $rc == 1} {
set returnCode [$myHandle SQLFetchAll result headers]
puts $headers
puts $result
}
}
}
# Get the Statement Handle Error Description
if { $rc < 1 } {
set rc [$myHandle SQLGetDiagRec myError SQL_HANDLE_STMT]
if {$rc == 0} {
puts $myError
}
}
# Disconnect from the data source
$myHandle SQLDisconnect
} doRequestCatch] } {
puts "SQL_ERROR: $doRequestCatch"
}
SQLDisconnect
This TCL command is used to disconnect from the database (DBMS) and free up handles.
Syntax
$odbcHandle SQLDisconnect
Parameters
$odbcHandle
This is the handle that was already created with either using SQLConnect or SQLDriverConnect.
Return Values The return value is the return code from the DBMS. And, if it is successful returns either 0 or 1. A negative return code is an error. NOTE: This command also deletes the TCL odbc handle command from memory. Once, you have disconnected you must create a new handle using SQLConnect or SQLDriverConnect.
Example
package require SQL
namespace import -force ::SQL::*
if { [catch {
set myHandle [SQLDriverConnect "Driver={SQL Server};server=localhost;database=Northwind;UID=sa;PWD="]
# Disconnect from the data source
$myHandle SQLDisconnect
} doRequestCatch] } {
puts “Connection and Login Failure: $odbcHandle”
}
SQLCloseCursor
This TCL command closes the SQL cursor and prepares the statement handle for another SQLExecDirect request. Before running another SQLExecDirect you must first use SQLFreeStmt or SQLCloseCursor.
Syntax $odbcHandle SQLCloseCursor
Parameters
$odbcHandle
This is the handle that was already created with either using SQLConnect or SQLDriverConnect.
Return Values The return value is the return code from the DBMS. And, if it is successful returns either 0 or 1. A negative return code is an error. To view the English version of the error use the TCL command SQLGetDiagRec
Example
package require SQL
namespace import -force ::SQL::*
if { [catch {
set myHandle [SQLDriverConnect "Driver={SQL Server};server=localhost;database=Northwind;UID=sa;PWD="]
set query "Select * from Customers;"
set rc [$myHandle SQLExecDirect $query]
# Close the SQL cursor before running another query
set rc [$myHandle SQLCloseCursor]
set query "Select * from Products;"
set rc [$myHandle SQLExecDirect $query]
# Disconnect from the data source
$myHandle SQLDisconnect
} doRequestCatch] } {
puts “Connection and Login Failure: $odbcHandle”
}SQLFreeStmt This TCL command frees the SQL statement handle. This is an alternate way to prepare for another SQLExecDirect query.
Syntax $odbcHandle SQLFreeStmt
Parameters $odbcHandle This is the handle that was already created with either using SQLConnect or SQLDriverConnect.
Return Values The return value is the return code from the DBMS. And, if it is successful returns either 0 or 1. A negative return code is an error.
Example
package require SQL
namespace import -force ::SQL::*
if { [catch {
set myHandle [SQLDriverConnect "Driver={SQL Server};server=localhost;database=Northwind;UID=sa;PWD="]
set query "Select * from Customers;"
set rc [$myHandle SQLExecDirect $query]
# Create a fresh statement handle
set rc [$myHandle SQLFreeStmt]
set query "Select * from Products;"
set rc [$myHandle SQLExecDirect $query]
# Disconnect from the data source
$myHandle SQLDisconnect
} doRequestCatch] } {
puts “Connection and Login Failure: $odbcHandle”
}SQLCancel This TCL command stops a asynchronous SQL query.
Syntax $odbcHandle SQLCancel
Parameters $odbcHandle This is the handle that was already created with either using SQLConnect or SQLDriverConnect.
Return Values The return value is the return code from the DBMS. And, if it is successful returns either 0 or 1. A negative return code is an error. To view the English version of the error use the TCL command SQLGetDiagRec.
Example
package require SQL
namespace import -force ::SQL::*
if { [catch {
set myHandle [SQLDriverConnect "Driver={SQL Server};server=localhost;database=Northwind;UID=sa;PWD="]
puts $myHandle
# Intialize TCL variables to NULL
set rc ""
set headers ""
# Create a SQL batch statement
# This is called a batch statment because there is more than one result set.
set query "Select * from Customers;\n"
append query "Select * from Orders;\n"
append query "Select * from Products;"
# Turn on Asynchronous requests
$myHandle SQLSetStmtAttr SQL_ATTR_ASYNC_ENABLE 1
# SQL_SUCCESS 0
# SQL_SUCCESS_WITH_INFO 1
# SQL_NO_DATA 100
# SQL_ERROR (-1)
# SQL_INVALID_HANDLE (-2)
# SQL_STILL_EXECUTING 2
# SQL_NEED_DATA 99
set rc [$myHandle SQLExecDirect $query]
# A return code of two means the query is still executing.
while { $rc == 2} {
set rc [$myHandle SQLExecDirect $query]
# I'm tired of waiting for the results Im cancelling the SQL request.
set rc [$myHandle SQLCancel]
puts "SQLCancel: $rc"
# Were only interested in the data if it was successful: a one or zero return code.
if {$rc == 0 || $rc == 1} {
# Get the first result set
set rc [$myHandle SQLFetchAll result headers]
puts $headers
puts $result
# Display the remaining result sets
while {$rc==0 || $rc==1} {
set rc [$myHandle SQLMoreResults]
if { $rc == 0 || $rc == 1} {
set returnCode [$myHandle SQLFetchAll result headers]
puts $headers
puts $result
}
}
}
# Get the Statement Handle Error Description
if { $rc < 1 } {
set rc [$myHandle SQLGetDiagRec myError SQL_HANDLE_STMT]
if {$rc == 0} {
puts $myError
}
}
}
# Disconnect from the data source
$myHandle SQLDisconnect
} doRequestCatch] } {
puts "SQL_ERROR: $doRequestCatch"
}
SQLGetDiagRecThis TCL command is used to get the English description of a negative return code.
Syntax $odbcHandle SQLGetDiagRec myErrorVariable handleType
Parameters $odbcHandle This is the handle that was already created with either using SQLConnect or SQLDriverConnect.
myErrorVariable This is the TCL variable to place the error description in.
handleType This is optional and tells the extension which handle you wish to inspect. Possible values are: SQL_HANDLE_ENV SQL_HANDLE_DBC SQL_HANDLE_STMT The default is statement if the handle type is not given.
Return Values The return value is the return code from the DBMS. And, if it is successful returns either 0 or 1. If 0 or 1 is not returned the myErrorVariable is not set.
Example
package require SQL
namespace import -force ::SQL::*
if { [catch {
set myHandle [SQLDriverConnect "Driver={SQL Server};server=localhost;database=Northwind;UID=sa;PWD="]
puts $myHandle
# Create a bad SQL Query to return an error.
set query "Select * from bogus;"
set rc [$myHandle SQLExecDirect $query]
# A negative return code is an error.
if { $rc < 0 } {
set rc [$myHandle SQLGetDiagRec myError SQL_HANDLE_STMT]
# The myError varible is only set if successful.
if { $rc == 0 || $rc == 1} {
puts $myError
}
}
# Disconnect from the data source
$myHandle SQLDisconnect
} doRequestCatch] } {
puts "SQL_ERROR: $doRequestCatch"
}
SQLRowCount This TCL command is used to get the number of rows affected by an insert, update, or delete SQL command. Before using this command you should have a valid database handle and have already ran a SQLExecDirect TCL command.
Syntax $odbcHandle SQLRowCount myVariable
Parameters $odbcHandle This is the handle that was already created with either using SQLConnect or SQLDriverConnect.
myErrorVariable This is the TCL variable to place the number of row affected by the SQL insert, delete, or update. A -1 is returned if the
Return Values The return value is the return code from the DBMS. And, if it is successful returns either 0 or 1. If a negative number is returned use the TCL command, SQLGetDiagRec to find out what the description of the error is.
SQLPrimaryKeys
This command is not yet documented but functional. SQLTables
This command is not yet documented but functional. SQLForeignKeys This command is not yet documented but functional. SQLStatistics This command is not yet documented but functional. SQLTablePrivileges This command is not yet documented but functional. SQLProcedures This command is not yet documented but functional. SQLSpecialColumns This command is not yet documented but functional. SQLDataSources This command is not yet documented but functional. TCL Programming Suggestions From the Extension’s Author
1. Other than the TCL commands SQLConnect and SQLDriverConnect all of the commands return a return code in the form of a numeric value. A negative return code is bad. This means that there may be something wrong with your SQL query etc. I chose a numeric return code because this is much easier to program TCL for loops and while loops than using an English error description for the return value. If you are interested in the English description use the TCL command SQLGetDiagRec to view the description of the error. 2. SQLDriverConnect and SQLConnect return a new TCL command, a SQL handle, if they are able to connect to the database, else a description of the connection error is returned. 3. Because a TCL error can be raised if something goes wrong you should encapsulate all of your TCL ODBC commands with a TCL catch command. 4. Before executing another SQLExecDirect command you must put the SQL cursor in a valid state by using either SQLCloseCursor or SQLFreeStmt TCL commands 5. All of the TCL commands used in this extension, except SQLFetchAll and SQLFreeStmt, are the direct C ODBC ISO 92 API calls. I did this so TCL application programmer have a better understanding of what is going on, and can review the MSDN ODBC library for more information on the native C ODBC API calls. 6. Many DBMS’s have a limit on the number of concurrent database connections. This is usually in the form of a license that is purchased with the DBMS. Because of this, I recommend disconnecting from the database after processing each SQL result. This way a license is freed up. Unless, it takes a long time to connect I would connect, disconnect, and reconnect every time. During my testing, SQL Server’s connection time is instantaneous even on a remote computer. 7. Please let me know what other TCL ODBC API calls you would like to see added, I have added the ones I know I would use, but someone else may need another ODBC API need.
Known Issues and Caveats 1. If the asynchronous statement attribute is turned ON, and parsing more than one result set within the same statement returns a -1 error. Parsing more than one result set works fine when the statement attribute is using the default synchronous attribute. 2. All TCL commands in this extension, except SQLDriverConnect and SQLConnect return a return code. A successful return code is 0 or 1. A return code of 100 is a nomatch. A negative return code is an error. Use SQLGetDiagRec to find out the English description.
You are free to use/modify this code but leave this header intact. Bibliography and Additional Resources 1 Practical Programming in TCL and TK / Brent B. Welch. – 3rd ed. P. cm. ISBN 0-13-022028-0
2 Microsoft MSDN Library: ODBC 3.0 API Reference Section: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odbc/htm/odbcodbc_api_reference.asp