AdabasTcl is a Tcl extension providing access to an Adabas D Database Server. It is loadable as package and consists of a collection of Tcl commands and a Tcl global array. Each AdabasTcl command generally invokes several Adabas D library functions.
The first of the following four sections will cover all procedures of the AdabasTcl call interface. The second section give hints, how to use the built in Tcl commands to load AdabasTcl into the Tcl interpreter. After that comes a description of the adabas command, which provides a lower level interface to the database and a section about sqlsh, a set of commands, that are automatically defined, when AdabasTcl is loaded into an interactive session.
adalogon connect-str ?option ...?
Connect to an Adabas D server using connect-str. The connect string should be a string in one of the following forms:
If name and password are given (comma separated), both are translated into upper case before they are used to connect to the database.
If a userkey is given (with a leading comma), the data for name and password are extracted from the xuser record. If only a comma is given, it stands for the DEFAULT xuser entry.
The special connect string -noconnect signals, that only a low level connection to the database server is established, but no connection at the user level is made. This connection can be used with the adaspecial command.
A logon handle is returned and should be used for all other AdabasTcl commands using this connection that require a logon handle. Multiple connections to the same or different servers are allowed. Adalogon raises a Tcl error if the connection is not made for any reason (login or password incorrect, network unavailable, etc.).
Option may be any of the following.
If a serverdb is given, the connection is made to this serverdb. Else the value of the environment variable SERVERDB, which resides in the global Tcl variable env(SERVERDB), is used as the name of the server. If SERVERDB isn't set, the environment variable DBNAME is tested also.
The name of a serverdb may be prefixed by a colon separated hostname. An example call of adalogon would be:
adalogon demo,adabas -serverdb mycomp:mydb
If the serverdb isn't prefixes by a hostname, the hostname can be given as separate option -servernode. An example for this:
adalogon demo,adabas -serverdb mydb -servernode mycomp
If a sqlmode is given, the connection is made in this sqlmode. Sqlmode can be any of adabas, ansi or oracle. Default is sqlmode adabas.
If an isolation is given, the connection is made with this isolation level. Isolation can be any of 0, 1, 2, 3 10, 15 or 20.
If a service is given, the connection is made for the given service. Service may be any of the following:
You can create multiple connections to the database server by calling adalogon multiple times. The limit is set by the configuration of the database or by the amount of available memory, not by AdabasTcl. Since you can create multiple cursors out of one logon handle, it only makes sence to have multiple logons with different users on each connection.
The returned logon handle for the first connection is the name of the serverdb, for the next connections a string in the form serverdb #n with n replaced by an increasing number. No programs should depend on this; instead you should store the returned logon handle into a variable:
set logon [adalogon demo,adabas]
adalogoff logon-handle
Logoff from the Adabas D server connection associated with logon-handle. Logon-handle must be a valid handle previously opened with adalogon. Adalogoff returns a null string. Adalogoff raises a Tcl error if the logon handle specified is not open.
adaopen logon-handle
Open a SQL cursor to the server. Adaopen returns a cursor to be used on subsequent AdabasTcl commands that require a cursor handle. Logon-handle must be a valid handle previously opened with adalogon. Multiple cursors can be opened through the same or different logon handles. Adaopen raises a Tcl error if the logon handle specified is not open.
The returned cursor handle is a string in the form cursorn, with n replaced by an increasing number. No programs should depend on this; instead you should store the returned cursor handle into a variable:
set cursor [adaopen $logon]
adaclose cursor-handle
Closes the cursor associated with cursor-handle. Adaclose raises a Tcl error if the cursor handle specified is not open.
adasql cursor-handle ?-command? sql-statement ?option ...?
Send the Adabas D SQL statement sql-statement to the server. Cursor-handle must be a valid handle previously opened with adaopen. The argument -command can be omitted. Adasql will return the numeric return code 0 on successful execution of the sql statement. The adamsg array index rc is set with the return code; the rows index is set to the number of rows affected by the SQL statement in the case of insert, update, or delete.
Only a single SQL statement may be specified in sql-statement. Adafetch allows retrieval of return rows generated.
Option may be any of the following.
If sqlmode is specified, then the sql-statement will be parsed and executed in this sqlmode, and not in the session wide sqlmode determined by adalogon. Note, that it may be neccesary to give the same -sqlmode option, when calling adafetch.
If resulttable is specified, this will become the name of the resulttable of the SELECT statement. For any other statement kind (e.g. UPDATE or CREATE) the -resulttable option will be ignored.
You have to give an explicit resulttable name, if you want to fetch from more than one cursor at once. The resulttable names can be arbitrary (max. 18 characters long), but should be distinct between all cursors of one logon handle, you want to fetch from.
Adasql performs an implicit adacancel, if any results are still pending from the last execution of adasql. Adasql raises a Tcl error if the cursor handle specified is not open, if the SQL statement is syntactically incorrect or if no data was found for a SELECT or no row was affected by an UPDATE or DELETE command.
If the given sql-statement denotes a select into, the selected values are stored directly into the mentioned parameters. The parameter names may denote ordinary variables or arrays. E.g.
adasql $cursor {SELECT DATE, TIME INTO :res(date), :res(time) FROM dual}
After the above call of adasql the current date and time is stored into the array variable res with the indexes date and time.
There exist some alternative forms of the adasql command, where the second argument is a specification of the kind, the statement is handled:
adasql cursor-handle -parameter sql-statement ?option ...?
In this case the sql-statement is executed in three steps: First it is parsed by the database server and a so called parsId is returned. This parsId is then immediately given for execution together with the values of all mentioned parameters. At last the parsId is dropped from the database catalogue. Note that this way there are three communication steps instead of one.
The good side of this is, that the sql-statement can contain parameter specifications.
adasql $cursor -parameter "SELECT * FROM tab
WHERE numb >= :limit
AND name BETWEEN :lower AND :upper
There are no string delimiter around :lower or :upper, since the substitution with the current values and its conversion are done by the database server and not by the Tcl interpreter.
There exist options to do each of the three above mentioned steps separate:
adasql cursor-handle -parse sql-statement ?option ...?
adasql cursor-handle -execute parsId
adasql cursor-handle -drop parsId
adafetch cursor-handle ?option...?
option may be any of the following.
Return the next row from the last SQL statement executed with adasql as a Tcl list. Cursor-handle must be a valid handle previously opened with adaopen. Adafetch raises a Tcl error if the cursor handle specified is not open. All returned columns are converted to character strings. (This is not completely true for Tcl starting at version 8, since there any numeric values (FIXED or FLOAT) is returned as number object. But you shouldn't notice any difference from the script level.)
An empty list is returned if there are no more rows in the current set of results. The Tcl list that is returned by adafetch contains the values of the selected columns in the order specified by SELECT.
If the option -array specifies a true value (e.g. 1 or on), the resulting list will be in a format, that can be given as last argument to an array set command. An example session follows.
% adasql $c {SELECT USER "my_name" FROM dual}
% array set x [adafetch $c -array 1]
% set x(my_name)
krischan
The cursor can be moved in any direction by means of the -position option. Its associated value can have any of the forms first, last, next, prev or it can be a number. The textual variants specify the direction, in which the cursor should be moved. A direction of e.g. first rewinds the cursor to the start of the result set. A number denotes the rownum of the result row to fetch; the first row has the rownum 1. Default position is next.
It may be nessecary, to give the same -sqlmode option, as you give to adasql, since some mode dependend computations (e.g. date format) are delayed to the call of adafetch.
By means of the optional -command argument adafetch can repeatedly fetch rows and execute commands as a tcl script for each row. Substitutions are made on commands before passing it to Tcl_Eval() for each row. Adafetch interprets @n in commands as a result column specification. For example, @1, @2, @3 refer to the first, second, and third columns in the result. @0 refers to the entire result row, as a Tcl list. Substitution columns may appear in any order, or more than once in the same command. Substituted columns are inserted into the commands string as proper list elements, i.e., one space will be added before and after the substitution and column values with embedded spaces are enclosed by braces if needed.
A Tcl error is raised if a column substitution number is greater than the number of columns in the results. If the commands execute break, adafetch execution is interrupted and returns with TCL_OK. Remaining rows may be fetched with a subsequent adafetch command. If the commands execute return or continue, the remaining commands are skipped and adafetch execution continues with the next row. Adafetch will raise a Tcl error if the commands return an error. Commands should be enclosed in double quotes or braces.
Adatcl performs conversions for all data types. For long data only a long descriptor is returned, which can be used as argument for a subsequent call of adareadlong with its -descriptor option.
The adamsg array index rc is set with the return code of the fetch. 0 indicates the row was fetched successfully; 100 indicates the end of data was reached.
The adamsg array index nullvalue can be set to specify the value returned when a column is null. The default is an empty string for values of all datatypes.
The adamsg array index specialnull can be set to specify the value returned when a column is the special null value. The default is the string ??? for values of all datatypes.
adacols cursor-handle
Return the names of the columns from the last adasql or adafetch command as a Tcl list.
As a side effect of this command the global array adamsg is updated with some additional information about the selected columns. The adamsg array index collengths is set to a Tcl list corresponding to the lengths of the columns; index coltypes is set to a Tcl list corresponding to the types of the columns; index colprecs is set to a Tcl list corresponding to the precision of the numeric columns, other corresponding non-numeric columns got its length as precision; index colscales is set to a Tcl list corresponding to the scale of the numeric columns, other corresponding non-numeric columns are 0; Adacols raises a Tcl error if the cursor handle specified is not open.
adacancel cursor-handle
Cancels any pending results from a prior adasql command that use a cursor opened through the connection specified by cursor-handle. Cursor-handle must be a valid handle previously opened with adaopen. Adacancel raises a Tcl error if the cursor handle specified is not open.
Note, that this command doesn't cancel any long running sql statemant (though it should).
adacommit logon-handle
Commit any pending transactions from prior adasql commands that use a cursor opened through the connection specified by logon-handle. Logon-handle must be a valid handle previously opened with adalogon. Adacommit raises a Tcl error if the logon handle specified is not open.
adarollback logon-handle
Rollback any pending transactions from prior adasql commands that use a cursor opened through the connection specified by logon-handle. Logon-handle must be a valid handle previously opened with adalogon. Adarollback raises a Tcl error if the logon handle specified is not open.
adaautocom logon-handle on-off
Enables or disables automatic commit of SQL data manipulation statements using a cursor opened through the connection specified by logon handle. Logon-handle must be a valid handle previously opened with adalogon. on-off must be a valid Tcl boolean value (0, 1, false, true, no, yes, on, off or abbreviations thereof). Adaautocom raises a Tcl error if the logon handle specified is not open.
adareadlong cursor-handle ?option ...?
Option may be any of the following.
Read the contents of a LONG column and returns the result, or writes it into a file, if the optional parameter filename is specified. Cursor-handle must be a valid handle previously opened with adaopen.
The switches can be given in any order, but there must be present either the -descriptor switch or all of -table, -column and (most probably) -where switches.
With -table, -column and -where the column to be read can be specified, as if a select in the following form was specified:
SELECT column-name FROM table-name WHERE where-condition
The given column must be of data type LONG and the where-condition must limit the resultcount of the above select statement to 1.
Long-descriptor is the Adabas long descriptor of a recently fetched row, as returned by adafetch.
Here are two examples, how to use the two variants of this command:
adasql $cursor "SELECT longval FROM tab WHERE keyval=1" set row [adafetch $cursor] set val1 [adareadlong $cursor -descriptor [lindex $row 0]] set val2 [adareadlong $cursor -table tab -column longval -where "keyval=1"]
Filename is the name of a file in which to write the LONG data.
If called with optional parameter filename, adareadlong returns upon successful completion the number of bytes read from the LONG column.
The resulting string will be decoded, since it may contain characters, that are not printable, or even null characters. You can specify the encoding kind with the -encoding option. Encoding must be one out of the following list.
image create photo -data \
[adareadlong $cursor -table people -column picture \
-where "name='Krischan'" -encoding base64]
Adareadlong raises a Tcl error if the cursor handle specified is not open, -descriptor is given and either the long descriptor specified is invalid or no call of adafetch precedes the call of adareadlong, or if the where-condition doesn't limit to a single result.
adawritelong cursor-handle ?option ...?
Option may be any of the following.
Read the contents of a file or the given string and store into a LONG column. Cursor-handle must be a valid handle previously opened with adaopen.
The switches can be given in any order, but there must be present either the -value or -filename switch and all of -table, -column and -where switches.
With -table, -column and -where the column to be read must be specified, as if an update in the following form was specified:
UPDATE table-name SET column-name = 'value-to-be-inserted' WHERE where-condition
The given column must be of data type LONG and the where-condition must limit the resultcount of the above update statement to 1.
Filename is the name of a file out of which to read the LONG data. Long-value is the LONG data itself.
The string to insert will be decoded, since only this way it is possible to insert LONG columns containing characters, that are not printable, or even null characters. You can specify the encoding kind with the -encoding option. Encoding must be one out of the list, you can find above by the adareadlong command.
Adawritelong raises a Tcl error if the cursor handle specified is not open or if the where-condition doesn't limit to a single result.
adaspecial logon-handle command ?params...?
Logon-handle must be a handle returned by a previous call of adalogon. Adaspecial is the only command, where logon handles created with the -noconnect option can be given.
Command and params may be any of the following.
All these commands except hello are only available, if a slow database kernel is started, and are mainly for debugging of the database server. The hello command can be used to avoid a timeout fot the given connection.
adausage logon-handle usage-kind ?option ...?
Usage-kind must be on, off or add. A call of adausage with usage kind on will inform the database kernel, that subsequent calls of adasql with the -parse option should be analyzed for usage relations. A call of adausage with usage kind off will fire the ddl triggers of Adabas D, which will update the data dictionary.
option can be -objecttype (maximal eight character) or -parameters (a list of up to 3 identifiers).
As example here are the calls of adausage and adasql to store a SQL statement as stored query command:
adausage $logon on -objecttype QUERYCOM -parameters [list $name] adasql $cursor -parse $sqlStatement adausage $logon off
adautil logon-handle utility-command
Send the Adabas D utility command utility-command to the server. Logon-handle must be a valid handle previously opened with adalogon with the session specified as utility. The return value depends heavily on the specified command.
AdabasTcl creates and maintains a Tcl global array to provide feedback of Adabas D server messages, named adamsg. Adamsg is also used to communicate with the AdabasTcl interface routines to specify null and specialnull return values. In all cases except for nullvalue, specialnull and version, the contents of each element may be changed upon invocation of any AdabasTcl command, and any element affected by the command is set. The adamsg array is shared among all open AdabasTcl handles. Adamsg should be defined with the global statement in any Tcl procedure needing access to adamsg. The following list defines all indexes of adamsg.
Typical values are:
As the introduction already said, AdabasTcl is an extension to Tcl. It is available as package with the name Adabastcl, following the Tcl conventions with capitalized package names.
On Unix systems with AdabasTcl installed there exist at least the following four interpreters:
On a Windows system there are no interpreters with the AdabasTcl package included, so there exist only tclsh and wish.
In every case there is a shared library on Unix systems (with the extension .so or .sl) or a dynamic link library on Windows systems (with the extension .dll) waiting in the lib subdirectory of $DBROOT. You can load it into your current interpreter with a platform independent command like this:
load [file join $env(DBROOT) lib Adabastcl[info sharedlibextension]]
If you have an interpreter program with AdabasTcl already included (e.g. adabaswish), you can call it with an empty first argument and the package name as additional second argument, like this:
load {} Adabastcl
Although this seems to be useless on the first look, it is useful, if you are working with multiple interpreter.
The most elegant way to get the AdabasTcl commands known by the interpreter is not to use the load command at all, but the package command. It works the same, if AdabasTcl is built in or not: First you append the subdirectory lib of $DBROOT to your auto_path and then simply call package require like this:
lappend auto_path [file join $env(DBROOT) lib] package require AdabasTcl
All the Tk applications in $DBROOT/bin (e.g. tkquery) starts with a procedure loadAdabastcl, which is responsible to make the AdabasTcl extension available in the current interpreter. You are invited to look and improve.
The adabas command provides a low level interface to the Adabas D database server. In deed, it is so low level, that you must know some internals about the order interface between applications and kernel, to get the most of the command variants functional.
In theory, it is possible to rebuild all the above given commands of the AdabasTcl call interface as purely Tcl commands by means of this command. But its performance is very bad, since all data at this level is binary, and Tcl isn't very good in handling strings with null characters in it.
Note, that most subcommands of this command are likely to deminish in future versions of AdabasTcl.
If adabastclsh was called without a filename to source, and therefore tcl_interactive is set to 1, it reads at startup time a file called ~/.adabastclshrc. In this procedure you can e.g. set your prompt.
If AdabasTcl is loaded into an interactive Tcl interpreter (as static package or via the load command), a bunch of convinience commands, that have the names of all the Adabas SQL commands, are defined.
The Tcl commands rename and update are accessible as tcl_rename and tcl_update. The switch statement does its best to determine, if the Adabas SWITCH statement or the Tcl switch procedure is meant.
The connect opens a connection to the database, that keeps established, until a session timeout occured or a commit or rollback with the release option was given.
After a select statement, that returns no error and contains no into clause, all results are fetched in portions of 25 lines. At the prompt the user can quit the fetch by typing q, can scroll the remaining lines without further prompting by typing n or can see the next lines by typing any other key.
All other commands are just send to the database kernel and the resulting errormessage, if any, is returned as error.
So an example session could look like the following.
connect krischan geheim select date, time into :x, :y from dual select * from order where order_date = '$x' delete from account where account_date <> '$x' commit work release
Beside the above given SQL commands there are two more commands defined: utility to connect as control user on the utility service and util, to perform some commands with this connection. Again an example:
utility control control util state util diagnose tabid krischan.adresse util commit release