NAME

dmh85_SQLsrv - Version specific name of dmh_SQLsrv

dmh85_sql_tcl - POSIX executable with Oracle code used by the dmh_SQLsrv

SYNOPSIS

The dmh_SQLsrv is designed to provide compatibilty with older CIM applications that existed before Hume Integration developed the Tcl SQL command for managing in-memory SQL tables in any Tcl process.   When the dmh_SQLsrv application is used, the Tcl SQL command is replaced with a compatible one that executes SQL statements against the persistent database.  In other words, in the dmh_SQLsrv application process, you cannot use in-memory SQL tables.  If you wish to access persistent database tables and in-memory tables in the same process, do not use the dmh_SQLsrv application; just code directly to the odbc command interface.

The dmh_SQLsrv application has been designed for robust message-based distributed applications where the queuing features of the underlying message system handle peaks in data production that would cause an RPC interface to have performance bottlenecks, or fail.

COMPATIBILITY NOTES

1. The order of the connect command arguments has become "connect database user password" in order to be consistent with the odbc command.  The database name can be passed as an empty string for the cases where it is implicit from the environment or user argument.
2. The SEMATECH version provided console interaction until the EOF command was issued.  This version does not provide equivalent console interaction.  A developer can have similar interaction using a Tk provided window for SQL commands.  When running in background, the inspect application can be used to inspect the command mailbox of the process.
3. The dmh_SQLsrv creates and uses an odbc command connection with the name of "sq1" (that is a one not the letter el).  The "sq1" odbc connection can be used by the developer as a vendor independent way to query schema information.

CONCEPTS

When used as part of a DMH-connected distributed application, the server reads command messages from a message system mailbox.  The format of the command and reply messages are closely compatible with those used by the Hume Datahub. In part, the message formats provide compatibility with older applications that were designed for a non-Tcl environment.  The command messages are ASCII text; usually structured query language (SQL) statements. The dmh_SQLsrv, acting as a persistent database server, sends reply messages from the database to the reply mailbox associated with the command message. These reply messages are also ASCII text, and their format is presented in this document. All of the usual data manipulation language commands of SQL are fully supported - queries (select), insert, update, delete, etc. Also, full support is provided for other SQL statements such as commit, rollback, grant, create, drop, alter, etc. The dmh_SQLsrv accepts and processes certain statements that are not standard SQL, which allow the clients to access the Tcl commands and features of the dmh_SQLsrv.   For example, a client uses the connect subcommand to connect to a specific database.

The dmh_SQLsrv application creates its own version of the Tcl SQL command, and uses a single odbc connection command named "sq1".  Like the Datahub, the dmh_SQLsrv has a small set of data items that are assigned values using the SQL set command or queried using the SQL query command. These data parameters are used to configure specific aspects of the server's behavior and are described under the SQL set command.

The GROUP parameter specifies the DMH message group name that the server will use when initializing DMH message communication. The value defaults to "mbx". The SERVER parameter specifies the mailbox name that the server will use to receive SQL message commands from the DMH messaging system. The value of the SERVER parameter defaults to "ENGDB". The SQL EOF command is used to initialize message system communication. Once message communication has been initialized, the values of the SERVER and GROUP parameters cannot be changed. It is typical to specify these parameters, and to initialize DMH message communication on the server's command line.   It is also typical to establish the connection to the persistent database on the command line.  A typical startup might  look like:

dmh_SQLsrv  "set group=apc" "connect cim5 scott tiger"  eof &

The example startup attempts to join the DMH message group "apc".  A datahub or other DMH server application would need to be started beforehand for the connection to succeed.

The dmh_SQLsrv is designed to connect to one database at a time. Multiple instances of the server can be executed to simultaneously connect to multiple databases.

The server can be dynamically disconnected from a database and connected to another one, or reconnected to a previous one. These actions are supported by the commands connect and disconnect.

The exit command or the act of killing the server implicitly causes the disconnect action to occur.

The server fully supports commit and rollback. The application can send the server a set of SQL statements expressing a logical transaction in one or more physical transactions. Upon success of the entire set of physical transactions, the application can send the commit command to make the changes permanent. The rollback command reverts the database to the condition it was at the last commit.

If commits are not issued from the application, and the server process terminates abnormally, data changes will be lost. However, the server does support an automatic commit feature. When the parameter auto_commit is set to non-zero (true), a commit is executed following each SQL statement that is not a rollback, or a commit statement. By default the auto_commit parameter is set to 1 (true) and each statement is committed as executed.

The server process establishes a single database session for all of its clients. It is the application's responsibility to coordinate use of the server by multiple clients which could confound multiple logical transactions by interleaving messages. It is possible to execute multiple instances of the server connected to the same database in order to provide separate database sessions and avoid any possible physical interleaving of logical transactions.   Physical interleaving can also be avoided by sending more than one SQL statement in a Tcl message using the eval command, or executing a Tcl procedure in the dmh_SQLsrv which executes
multiple SQL statements.

To begin work with a database, use the connect command.   Usually this is done on the command line.  On Windows, you can use the Tcl odbc sources command to display available databases. You need to configure available databases using the ODBC administrator supplied by your database vendor.

The dump command can be used to see if the server is presently connected to a database.

The default format of reply messages for the select command is nearly identical to that provided by the Hume Datahub.  For a message system client, each row of data is sent in its own message, with values labeled with their column names. Text values are delimited with single quotes. All data types are converted to either real numbers or text; thus date types become text.

Unlike the Hume Datahub, table names are not indicated in a select reply. A select statement can join multiple tables and views so that in many cases a table name is not readily expressible.

There is an alternative output format for select messages that is designed for convenient parsing in the Tcl programming environment. See the Select Reply Format described with the odbc command.

The formats of insert, update, and delete statements are compatible with SQL subscription output of the Hume Datahub. Thus, it is possible to populate a persistent database by subscription to one or more realtime databases. The application should either use the auto_commit feature, or have other provision for commits when subscription feeds are used.

Here are example commands sent to a datahub which will cause data to be added to a persistent database whenever it is added to the in-memory database:

"open sub engdb1 to process_run sendto=ENGBD  insert *"

"open sub engdb2 to process_run_material sendto=ENGDB  insert run_id, lot_id, cassette_id, slot_id"

Client applications can take advantage of the Tcl interpreter by using the eval message command. This command can execute any Tcl/Tk code including defining or invoking procedures. Tcl procedures can be written and available on the file system where they will be dynamically loaded when used because of Tcl's auto_load library feature. Or, Tcl procedures can be explicitly loaded using the source message command.

Inside of Tcl code, it is possible to access the database using the the SQL or the execute Tcl commands.

For the most part there are no fixed limits to the number of data fields or size of data that can be transferred. The server dynamically allocates and resizes buffers used to transfer data from the database. For the DMH message system, there is no fixed limit on the size of a message and it is possible to send large amounts of data in a single message. However, no single field in a table row should exceed 64000 bytes.

The exit command can be used to terminate the server.   The dmh_SQLsrv application defines a procedure mh_app_lostserver which causes it to exit if the message system connection is lost.

Running the Server

The dm85_sql_tcl executable was originally created to handle web server CGI scripts written in Tcl.  The dmh85_sql_tcl executable is just like dmh_wish except it has an interface to Oracle using the Tcl command name "sql".  This command is a functional subset of the odbc command.  You are encouraged to code to the odbc interface instead of the "sql" command.  The odbc command becomes available  after executing "package require ora_odbc".

Using the combination of command line options -file, -notk, and -console makes it possible to use file and console input without X-windows in a CGI web server script. The -console option was compiled-in behavior of the dmh_sql_tcl executable prior to Tcl 8.2. Now this capability is available as a command line option for both dmh_sql85_tcl, and dmh_wish.

On POSIX systems, If you are not running in background with the -notk option, you need to define X-Windows DISPLAY in the environment or use -d explicitly on the command line. Eg.,

$ dmh_SQLsrv -d kestrel:0.0 "set group=test_mbx" eof &

On Windows NT the X-Windows option -display is not applicable. If you are using the MS-DOS command shell, pathnames used by NT should be separated using backslashes. For pathnames that are processed by Tcl, you can use Unix style slashes.

   rem startup Watcom
  C> \usr\local\bin\dmh_wish -f /usr/local/bin/dmh_SQLsrv \
   "set group=cim1" "set server=ENGDB" "connect SPC2 DBA SQL" \
   "set option Timestamp_format='YYYY-MM-DD HH:NN:SS'" \
   "eval lappend auto_path %SMART%/tcllib" EOF

#
dmh_SQLsrv "set group=CIM1" \
 "set server=ENGDB" "set auto_commit=1"  \
 "connect T:metallica:ASSY cimuser pass_cimuser" \
 "alter session set NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS'"\
 "set NULL_NUMBER_MAP=0"\
 "set NULL_STRING_MAP='' " EOF >engdb.log 2>&1 &

Once the server is started, all of the commands listed in the next section of this document are available. To view a list of all message commands understood by the server, use the help message command. To view a list of all parameters understood by the server and their current settings, use the query message command. All variable values that can be changed are set using the set command. To shut down the server, use the exit message command.

You create and change database tables using the create and alter message commands. The message command drop is used to delete tables when they are no longer useful. Data rows are added to tables using the insert command. The update command is used to modify stored data. The delete command is used to remove data from tables, and the select command is used to obtain selections of the stored data.

If you have not used the "-notk" command line option, you are presented with a simple windowing interface for viewing your database tables. You may wish to create your own custom interface using the dmh_SQLsrv script as a starting point.

Tcl Procedure Reference

SQL - Execute an SQL Statement

Tcl Command Syntax

SQL sql_command

Description

 The SQL command processes the sql_command argument as described in the next section SQL Command Reference. This command is the workhorse Tcl command that you can use in your custom Tcl procedures in order to access persistent database functionality.   This SQL command replaces the ordinary SQL command which is used to manipulate in-memory Datahub tables, with a version that manipulates persistent database tables.

execute - Execute an SQL Statement

Tcl Command Syntax

execute sql_command

Description

 The execute command exists for compatibility with older software. New Tcl code should use the SQL command to access database functionality of the dmh_SQLsrv.

The Tcl execute command is nearly synonymous with the Tcl SQL command. The command processes the sql_command argument as described in the next section. The return value is a Tcl list where each element of the list is a reply message that would be obtained if the argument were sent as a message command to the dmh_SQLsrv's command mailbox. In other words, since most of the SQL/message commands return a single reply message, most of the return values will be formatted similar to "{//c 0 COMMAND: success}".

SQL/Message Command Reference

In this section, the commands that are given as arguments to the Tcl SQL command, or to the Tcl execute command are presented.  These are also the commands that are processed when sent as messages to the command mailbox of the dmhSQLsrv, or specified on the dmh_SQLsrv command line.

The dmhSQLsrv retains reply format compatibility with the SCC Data Server program was developed by SEMATECH. In general, a reply is formatted as "//c rc COMMAND: text". In this format, a return code, rc, of 0 means that the command completed successfully. A non-zero code implies an error or warning condition. If you execute an SQL statement that is not supported, or execute some other unrecognized statement, the reply is:

//c 2 <UNKNOWN>:  Unrecognized command, enter "help" for command list.

For ordinary SQL statement types,  the reply formats are:

//c 0 <cmd>: 
//c 5 <cmd>: not connected to database
//c 13 <cmd>: SQL error - <sql error text>

The dmh_SQLsrv accepts and processes any of the following command types.  Entries in the table that are not linked to individual explanations are standard SQL statement types with ordinary reply formats as described above.  Refer to your database vendor's documentation for detailed reference information on these commands.  Also, you may find the basic SQL syntax described in the Datahub document useful.  Entries in the table that are hyperlinked, are not standard SQL, or have different reply formats.  These commands are described in aphabetical order below.
 
 

#	ALTER	COMMIT	CONNECT	CREATE	DELETE
DISCONNECT	DROP	DUMP	EOF	EVAL	EXIT
GRANT	HELP	INCLUDE	INSERT	LOCK	PING
PROCESS	QUERY	REM	REVOKE	ROLLBACK	SELECT
SET	SOURCE	TELECT	UPDATE

#	comment
Syntax	# comment text
Examples	# These statements build version 2 tables # Send change requests to flipper@ocean.umiami.edu
Description	This command lets you lets you add comments to files of SQL commands that will be processed using the SQL include command. The # command and the rem command are equivalent.
Replies	none

CONNECT	connect to a database
Syntax	CONNECT database user password
Examples	connect cim5 scott tiger connect spc2 DBA SQL
Description	You must connect to a database in order to issue SQL commands.  See the odbc connect command for more detail. User and password items are usually case sensitive.  Note that the order of the arguments is changed from the certain earlier versions to be consistent with the odbc command.
Replies	//c 0 CONNECT: connected to database //c 13 CONNECT: connect failure - error message

DISCONNECT	disconnect from a database
Syntax	DISCONNECT
Examples	disconnect
Description	The Oracle POSIX version does not support disconnecting and reconnecting.  End the process gracefully using commit and exit; and start a new dmh_SQLsrv process instead of re-connecting.  See the odbc disconnect command for more detail.
Replies	//c 0 DISCONNECT:

DUMP	Obtain status information
Syntax	DUMP
Examples	dump
Description	The dump command replies with 5 messages indicating the connected database name (or "None") and the last SQL statement executed and the statement's reply.
Replies	server = server, by Hume Integration, Austin TX connected database = { name | None } last command was "stmt" last reply was "reply" //c 0 DUMP: complete

EOF	initialize message system communication
Syntax	EOF
Examples	dmh_SQLsrv "set group=apc" eof &
Description	Executing this command causes the dmh_SQLsrv to initialize message system communication, and thereafter to process messages received in the server mailbox as SQL commands.
Replies	//c 0 EOF:  Message system initialized, command mailbox is \   "SERVER@GROUP"

EVAL	evaluate Tcl code
Syntax	EVAL tclcode
Examples	eval user_login jsmith rx7fan eval exec xterm -display atlas:0.0 & eval SQL "update checking set balance=12 where acct='smith'" ; SQL "update savings set balance=24 where acct='smith' eval proc hello {a} {puts $a} ; hello world
Description	This command provides a means for message system clients to access the Tcl functionality of the server. As the examples show, it can be used to execute Tcl procedures, or even to execute more than 1 SQL command in a single message.
Replies	The reply message is exactly the result you would see if the same tclcode were executed at a Tcl/Tk prompt.

EXIT	shutdown the process
Syntax	EXIT
Examples	exit
Description	This command causes the process to exit.  The reply message is sent and the Tcl exit command is called after the procedure returns.
Replies	//c 0 EXIT:

 

HELP	display a list of available commands
Syntax	HELP
Examples	help
Description	This command returns a list of  the possible commands.  There is not much detail on SQL statements since the SQL syntax can be complex and vendor-specific.
Replies	command synopsis

 

INCLUDE	process a file of SQL commands
Syntax	INCLUDE pathname
Examples	include c:/users/tonka/xtc.sql include ../lib/report1.sql SQL "include ../lib/report1.sql"
Description	This reads and processes a file of SQL commands. The logic reads each text line in the file as a new SQL command.  In contrast to the Datahub, the dmh_SQLsrv implementation is simple and does not work properly if the commands span multiple lines, such as character data with embedded newline characters.   It is recommended that you do not use the dmh_SQLsrv include command unless you are sure that it is compatible with your requirements. Relative pathnames are with respect to the current working directory of the process. The current working directory of the process can be changed using the Tcl cd command, or by setting the CWD parameter. Even on Windows NT platforms, the pathname is specified using Unix style slashes. The INCLUDE command may be nested inside of included files to arbitrary depths.
Replies	all non-blank replies for the included commands   followed by: //c 0 INCLUDE: count included results //c 1 INCLUDE:  Unable to open file "pathname"

PING	verify online responsiveness
Syntax	PING
Examples	ping
Description	This command exists for compatibility with the older versions.
Replies	//c 0 PING:  SERVER date time (POSIX_seconds) Id //c 0 PING:  ENGDB 1999-10-07 16:57:38 (939333458) ENGDB

PROCESS	process the data of a select query using specified Tcl code
Syntax	process select_clause with tcl_code
Examples	process * from my_table where a>0 with puts process [join $cols ,] from big_table with [list my_proc $cols]
Description	This command 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. The <select_clause> is any SQL select statement supported by the database vendor 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 instance of the program 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 Tcl Reply Format. 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.
Replies	//c 5 PROCESS: not connected to database //c 13 PROCESS: SQL error - description //c 0 PROCESS: process {} {} {} N_rows {} {}

QUERY	display the value of configuration parameters
Syntax	QUERY [parameter]
Examples	query group
Description	The dmh_SQLsrv has a handful of configurable data parameters. Each is described under the SET command. This command lets you obtain the current value(s). With no argument, all of the values are returned, otherwise the value of parameter is returned. For compatibility with SEMATECH style servers, the parameter = value reply is spaced differently depending on whether you requested a specific parameter or not.
Replies	one or more: parameter = value followed by //c 0 QUERY:  Complete. //c 1 QUERY:  Variable "name" not found. //c 2 QUERY:  QUERY [variable]

REM	comment
Syntax	REM comment text
Examples	rem uncomment the following statements for option B
Description	This command lets you lets you add comments to files of SQL commands that will be processed using the SQL include command. The # command and the rem command are equivalent.
Replies	none

 

SELECT	query table data
Syntax	see vendor documentation (also Datahub select)
Examples	select help_text from help_config where lot_id='any lot' and step='etch' select * from capt_log where sdate<='1996-01-01' SELECT: capt_log event='Klingons near chimney' sdate='1992-12-25' SELECT: capt_log event='read Dr. Spock''s book' sdate='1994-04-21' //c 0 SELECT: 2 rows selected SQL "select * from spc_datatype"
Description	select is used routinely to lookup stored table data.
Replies	If select is executed in Tcl code from the SQL command, you obtain the Tcl Reply Format which is described with the odbc command. The format described below is obtained when the select statement is sent to a dmh_SQLsrv as a message command, or executed in Tcl code from the execute command. One or more messages for each row, formatted as SELECT: [column=value]+ where value is enclosed in single quotes for character data, and then a final message, //c 0 SELECT:  N row[s] selected //c 13 SELECT:  SQL error - description

 

SET	configure a dmh_SQLsrv parameter
Syntax	SET parameter=value
Examples	set server=DB
Description	The dmh_SQLsrv has a small set of configurable parameters that can be assigned values using this command.    AUTO_COMMIT When this parameter is non-zero, the server executes a commit action after each data manipulation statement.  The application should set this flag to 1 (true) if there is no other provision for commit and rollback logic.  The default value is 1 (true).  See the discussion. CWD current working directory. New code should use the Tcl cd command. GROUP DMH message group. Once the message system interface has been initialized using eof this parameter cannot be changed. See the mbx document for a discussion of message groups. The value of this parameter defaults to mbx. NULL_NUMBER_MAP This parameter is used to 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, up to 40 bytes in length.  The value need not be numeric.  For example, it could be set to the word NULL.  NULL_STRING_MAP This parameter is used to 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. SERVER the command mailbox. Once the message system interface has been initialized using eof, the server reads messages from this mailbox and processes them as SQL commands. The value of this parameter defaults to ENGDB. STRIP_RHS_WHITE This parameter is available for the Oracle database software on POSIX platforms.  It is used as a boolean flag to indicate whether stripping of trailing space characters in char and varchar data selection results should be performed.  As of 11/20/2002, the default setting is 0, which means that trailing spaces are not removed.  This parameter needs to be set after a database connection is established because its value is saved as a property of the connection.
Replies	//c 0 SET:  Variable "parameter" set to "value". //c 1 SET:  Variable "parameter" not found. //c 3 SET:  Unable to set variable "parameter", error message

 

SOURCE	source a file of Tcl code
Syntax	SOURCE pathname
Examples	source c:/users/doe/zapp.tcl source ../library/util.tcl
Description	This command is used to explicitly load Tcl code such as procedure definitions into the Datahub. The functionality is equivalent to the Tcl command source. You use this command repeatedly during development to reload a file of source code that you are editing. However, for production, many applications will take advantage of the interpreter's auto_path mechanism that loads unknown Tcl code only if it is actually executed. To use this mechanism, the developer uses the auto_mkindex command to build a tclIndex file, and adds the directories where the source code files are found to the auto_path list.
Replies	The reply is an empty string or a single error message.

TELECT	an alias for the select command
Syntax	see select
Examples	telect * from menu_data
Description	This command exists to provide the Tcl Reply Format for message system clients. In the name of compatibility, the reply to select statements received through the command mailbox is the older, one message per row style. Tcl applications can use "telect" instead of "select" to obtain the new format which is designed for ease of parsing in Tcl code.
Replies	The Tcl Reply Format

Document Version

This document covers the Hume Integration Software developed dmh_SQLsrv that is available for the Tcl 8.5/Tk 8.5 environment on the Windows NT and UNIX/POSIX platforms.

Leave this document and go to the Tcl/Tk Resource Index