odbc Tcl Command

NAME

odbc - Utilize SQL databases from Tcl

SYNOPSIS

::dmh::odbc connect cmdname database user password

::dmh::odbc help

::dmh::odbc sources

cmdname SQL_statement

cmdname process_statement

cmdname auto_commit ?{1|0}?

cmdname catalog tables

cmdname disconnect

cmdname get_info infotype ?datatype?

cmdname help

cmdname null_number_map ?value?

cmdname null_string_map ?value?

cmdname schema tablename

::dmh::odbc_tabwin ?window? ?cmdname? ?title?

::dmh::odbc_table_win cmdname tablename

DESCRIPTION

This command allows you to utilize SQL databases from within your Tcl application. ODBC is an acronym for Open Database Connectivity, a standard programming interface defined by Microsoft. Although this standard was created for Windows, a compatible version of this command has been implemented by Hume Integration for Oracle on POSIX (Unix) platforms. The odbc Tcl command is built into the usual Windows dmh_wish.exe program and is always available. The POSIX Oracle odbc Tcl command is available in the separate executable dmh83_sql_tcl after executing "package require ora_odbc".

Hume Integration has also developed a Tcl application script, dmh_SQLsrv which uses the odbc command to provide a Graphical User Interface to a persistent database. The dmh_SQLsrv application also can be used to process SQL command messages received in a DMH mailbox as part of a distributed application. It is desirable to use the DMH message system to queue SQL statements that are intended for a persistent database, when the persistent database interface would be a performance bottleneck if used directly in a high performance application such as realtime data collection. A typical design is to collect data into an in-memory Datahub table, and to update a persistent database using a table subscription that feeds SQL messages to a separate dmh_SQLsrv process.

COMMAND SUMMARY

In order to use the odbc command on Windows NT, you must install the Win32 32 bit ODBC DLL drivers provided by your database vendor. You should also have 32 bit ODBC administration support installed on your system. If this has been done, you will have an ODBC administration icon on your Control Panel window, and/or an ODBC administration icon in a different program group. The ODBC administrator is used to configure which databases are available on your system when using ODBC drivers.

odbc sources

The subcommand odbc sources is only supported on Windows. Use this command to display which databases are available through ODBC. The command returns a list of pairs where each pair is the name and description of an ODBC data source. This list is created and maintained using the ODBC administrator application.

odbc connect cmdname database user password

This command is used to establish a connection to a specified database. The cmdname argument is a name that you assign to the connection, and it becomes a command name in the Tcl interpreter. The database argument is the "Data Source" name that was specified to the ODBC administrator. The user and password arguments are the security credentials that are passed to the database in order to establish the connection. These arguments are configured using the database vendor's program. For example, to connect to the Watcom sample database, you could enter

odbc connect sql {WSQL 4.0 Sample} admin school

In this example, the cmdname is sql. After connecting, the sql command is used to interact with the database.

With the POSIX Oracle version, the database can be specified as an empty string if it is implicit from the ORACLE_SID environment variable, or specified in the user item such as "scott@cim5". If you have problems connecting, you should verify that the database and user environment are properly setup by using Oracle's sqlplus utility application to connect to the database. The user typically needs to set environment variables such as ORACLE_HOME and add $ORACLE_HOME/bin to his PATH. Also, for the POSIX Oracle version, the API does not support connecting to more than one database simultaneously from a single process. If you need to work with multiple databases, use multiple processes, and integrate them using the DMH messaging system. Also, the POSIX Oracle version adds the Tcl command "sql" to the interpreter as part of its odbc implementation. Therefore you cannot use the name "sql" as the cmdname argument to the odbc connect command.

cmdname SQL_statement

Use this subcommand to execute any SQL statement supported by the vendor's ODBC interface. The most common statement types are insert, update, delete, and select. There is limited support for selecting when you have very large database fields in a table. The interface will not ask for more than 64kb per field in a table row. The select reply may be considerably larger than this as it combines multiple fields in a row, and multiple rows. The format of a select reply is explained below. For other types of SQL statements, if the SQL statement is executed successfully, an empty string is returned. A command such as a conditional update or a conditional delete is considered to have executed successfully even if no rows are changed. If the command is faulty or there is an error condition with the database connection, a diagnostic message is returned as an error result. You can use the Tcl catch command to trap error results.

cmdname process_statement

This subcommand is used to process the data of a select query using specified Tcl code. The Tcl code is executed for each row of data, as the data is fetched. Only one row of data is in memory at a time so it is feasible to work with large tables. If the Tcl code returns an error, return, or break result code (see the return command) the command returns without processing more data rows. For example, you can process only the first row of an ordered query by returning the break result code. Here is the syntax of the process_statement and examples:

  process select_clause with tclcode

  process * from my_table where a>0 with puts

  process [join $cols ,] from big_table with [list my_proc $cols]

The select_clause is any select statement supported by your database with the leading select keyword removed. You can use order by and other standard SQL select options to control the result.

The current implementation of the process statement will not work properly if the with keyword can be found in the select_clause. You can execute any of the usual SQL statements in your tclcode, but you cannot execute another process statement. You can however use another connection to achieve the same result.

The data values of a row are formatted as a list and appended as a single argument to the tclcode for evaluation. If the evaluation returns the usual ok code, the next data row is fetched, and the process is repeated. If there has not been an error, the reply will show the number of rows that have been processed in a format similar to the Select Reply Format described below.

The process statement is not a standard SQL statement type; it has been developed by Hume Integration to leverage the benefits of the Tcl language environment without requiring the end user to deal with the complexity of SQL cursors.

cmdname auto_commit ?{1|0}?

If auto_commit is set true, the database commits each SQL statement as it is executed. This is the default behavior. If auto_commit is set false, you are able to use the SQL rollback statement to undo statements that have been executed since the last commit. Use this subcommand without the 1 or 0 value to query the current value of the auto_commit property. Use the subcommand with a 1 or 0 value to set the property true or false.

cmdname catalog tables

This subcommand returns information on the tables that exist in the database. The data is formatted as a select reply; see the explanation below. The returned columns are TABLE_QUALIFIER, TABLE_OWNER, TABLE_NAME, TABLE_TYPE, and REMARKS. A table will have a TABLE_TYPE value of "TABLE".

cmdname disconnect

This subcommand is used to close the database connection. It eliminates the Tcl command cmdname as well.

cmdname get_info infotype ?datatype?

This command invokes the SQLGetInfo( ) call to obtain information. The information call is poorly supported by most drivers. The infotype argument is an integer from the set defined in the header file SQL.h. The datatype argument is a string which contains one of the sequences INT, SMALL, or STRING to indicate one of the API choices SQLUINTEGER, SQLUSMALLINT, or a null terminated character string. Most of the information numeric items are obtainable as INT and this is the default value. When an item is not supported, the typical result is the value 0 or an empty string. Here is a short list of potentially useful queries:

infotype name datatype

2 DATA_SOURCE_NAME STRING

13 SERVER_NAME STRING

17 DBMS_NAME STRING

18 DBMS_VERSION STRING

30 MAX_COLUMN_NAME_LEN SMALL

100 MAX_COLUMNS_IN_SELECT SMALL

101 MAX_COLUMNS_IN_TABLE SMALL

104 MAX_ROW_SIZE INT

105 MAX_STATEMENT_LEN INT

infotype	name	datatype
2	DATA_SOURCE_NAME	STRING
13	SERVER_NAME	STRING
17	DBMS_NAME	STRING
18	DBMS_VERSION	STRING
30	MAX_COLUMN_NAME_LEN	SMALL
100	MAX_COLUMNS_IN_SELECT	SMALL
101	MAX_COLUMNS_IN_TABLE	SMALL
104	MAX_ROW_SIZE	INT
105	MAX_STATEMENT_LEN	INT

cmdname help

This subcommand returns the command synopis; a brief summary of the subcommands described here.

cmdname null_number_map ?value?

This subcommand is used to query or set the value returned in selection results for number values that are null. The default is "0". You are able to specify any text for the value, not just numeric characters, up to 40 bytes in length.

cmdname null_string_map ?value?

This subcommand is used to query or set the value returned in selection results for character or date values that are null. The default is an empty string. You are able to specify any text for the value, up to 40 bytes in length.

cmdname schema tablename

This subcommand returns the schema of a database table formatted as a select reply. The tablename can optionally include specification of the table owner separated with a period, as in owner.tablename. (With some database products, the owner is referred to as the schema or table qualifier.)

Each field in the table is returned as a row in a selection result. For example:

% sql schema a
select <tablename> {TABLE_QUALIFIER TABLE_OWNER TABLE_NAME \
 column_name DATA_TYPE TYPE_NAME PRECISION LENGTH SCALE RADIX \
 NULLABLE REMARKS} <keylist> 2 {} {{{} admin a a 11 timestamp \
 26 16 6 10 1 {}} {{} admin a b 4 integer 10 4 0 10 1 {}}} {\
 VARCHAR(32767) VARCHAR(128) VARCHAR(128) VARCHAR(128) SMALLINT\
 VARCHAR(128) INTEGER INTEGER SMALLINT SMALLINT SMALLINT VARCHAR(254)}

odbc_tabwin ?window? ?cmdname? ?title?

This procedure creates a Tk window which can display the tables in the database, and allows for browsing and changing table data. The menu also supports creating command entry windows for SQL commands or Tcl commands. The window argument defaults to "."; the main window. The cmdname argument defaults to "sq1" which is the connection name used by the dmh_SQLsrv application. The title argument can be used to set the title of the table display window.

odbc_table_win cmdname tablename

This procedure creates a Tk window which displays the data of an individual table. Menu actions are provided to delete or insert rows.

SELECT REPLY FORMAT

The result of executing an SQL select statement is the 8 element list described next. The first seven elements of this list correspond to the Tcl Reply Format used by the Hume Datahub for the select command, and for certain subscription notications. The additional element of the reply contains schema information. The elements of the reply list are:

Element	Index	Description
notification	0	This element will be the literal string select for the return value of a select or telect query.
tablename	1	With the odbc software, there may not be a single identifyable table that your select statement is based upon. For example, your select query may be based on a join between tables or a system catalog. Therefore no attempt is made to return the actual tablename, and the tablename element will be the literal string <tablename>.
columns	2	This element will be a list of the column names in the same sequence as the data items in the data_list element. You may have a column that does not have a name, for example, it corresponds to an expression. In this case, the text of the column name will be vendor specific. In many cases it will be exactly the expression text you had in your select statement. Also, it is typical that the column names are all uppercase.
key_columns	3	This element will be the literal string <keylist>. When this format is used by the Hume Datahub, a list of the primary keys of the table are returned. With an odbc query, the possible use of indexes and joins makes it impossible to return an equivalent result.
row_count	4	This element returns a count of the data rows in the data_list element.
error_info	5	This element is not used by the odbc command. If your query has an error such as referring to a column or table that does not exist, or a syntax error, a Tcl error result is returned, and the reply is a diagnostic message instead of the usual Select Reply Format. The application dmh_SQLsrv does use this element. For the dmh_SQLsrv application, the underlying error is caught; you always get the Select Reply Format for a select statement, and the error message appears as this element. If you have a valid query that does not find any data rows, it is not an error, and the error_text element is an empty string.
data_list	6	This element is a list of data rows. Each data row has the elements named in the columns element, in the same order. Character strings that have imbedded white space are delimited in Tcl fashion using braces instead of single quotes. There are as many rows as specified by the row_count element.
type_list	7	This element is a list of the data types of the columns. The order corresponds to the order of the columns elements. The following types are possible, where n and m denote integers: CHAR(n), VARCHAR(n), LONG VARCHAR(n), DECIMAL(n,m), NUMERIC(n,m), SMALLINT, INTEGER, REAL, FLOAT, DOUBLE PRECISION, DATE, TIME, TIMESTAMP, BINARY(n), VARBINARY(n), LONG VARBINARY(n), BIT, TINYINT, BIGINT, and UNKNOWN. Refer to your vendor's documentation for information on the field types used for your databases. With the Oracle POSIX version, the schema information is only approximate and is limited to indicating VARCHAR(n), NUMBER, or DATE - the basic information needed to determine if quoted string notation is appropriate for a field or not.

AUTHOR

KEYWORDS

ODBC SQL database