{\rtf1\ansi\ansicpg1252\deff0\deflang3081{\fonttbl{\f0\fswiss\fprq2\fcharset0 Arial;}{\f1\fswiss\fcharset0 Arial;}{\f2\fmodern\fprq1\fcharset0 Courier New;}{\f3\fswiss\fcharset0 Verdana;}{\f4\fnil\fcharset2 Symbol;}}
{\colortbl ;\red0\green0\blue255;}
\viewkind4\uc1\pard\qc\b\f0\fs36 CL4 OpenAccess ODBC Database Server\par
\fs32 Implementation Notes\par
\pard\b0\fs20\par
\b\fs32 Introduction\par
\fs24\par
\pard\li284 Release\par
\pard\li568\tx3408\b0\fs20 CL4 ODBC server release:\tab\cf1\b 1.6b\cf0\b0\par
OpenAccess release:\tab\cf1\b 4.80.8\cf0\b0\par
Document ID:\tab $Id: Implemnt.wri,v 1.10 2002/10/30 05:01:35 planetary Exp oaccess $\par
\pard\par
This document covers several aspects pertaining to the implementation of CL4 services for use through an ODBC SQL interface. Note that this is not intended to be a guide to SQL, but more a reference for functions provided by the CL4 ODBC Server. Areas covered by this reference include:\par
\par
\pard{\pntext\f4\'B7\tab}{\*\pn\pnlvlblt\pnf4\pnindent0{\pntxtb\'B7}}\fi-294\li710\b Schema Object Access\b0 , a short description of the concept of SQL objects employed in the implementation of the CL4 Server;\par
\b{\pntext\f4\'B7\tab}Implementing Stored Procedures\b0 , describing the implementation and usage of CL4 stored procedures;\par
\b{\pntext\f4\'B7\tab}Database Control Language\b0  (DCL), which covers the commands that have been defined for maintaining SQL user logins and roles, and the additional tables which can be used to examine the defined users and permissions;\par
\b{\pntext\f4\'B7\tab}Permissioning\b0  describes the use of object and system privileges which can be set up to restrict access to information and facilities in your schema environment;\par
\b{\pntext\f4\'B7\tab}SQL Function Reference\b0 , where the supported SQL functions are described,\par
\b{\pntext\f4\'B7\tab}SQL Reserved Words\b0  Reference, which lists the SQL words that must be avoided when creating new databases or using preexisting databases, tables and column names; and\par
\b{\pntext\f4\'B7\tab}OAISQL Program Quick Reference. \b0 Describes the usage of the \i oaisql\i0  utility program.\par
\pard\par
The current document relates to facilities provided in the abovementioned release of the CL4 ODBC Database Server.\par
\par
\b\fs28 The SQL Environment\fs24\par
\pard\li284\b0\fs20\par
The SQL environment of the CL4 ODBC Server is defined within the realm of a self-contained namespace called a \b schema\b0 . Within the schema is defined a number of \b objects\b0  which are owned by object \b owners\b0 . These objects can be database tables, views, or stored procedures. A fully qualified name for each object within a schema contains three components, namely \i schema\i0 , \i owner\i0 , and \i object name\i0 . Therefore the full qualification for each object is:\par
\par
\pard\li1420\i <schema>\b .\b0 <owner>\b .\b0 <object>\par
\pard\li284\i0\par
for example,\par
\par
\pard\li1420\b cl4_conn.DEMGL.periods\b0\par
\pard\li284\par
Access to a given schema object may be made by fully qualified reference ("\b <schema>.<owner>.<object>\b0 "), by owner reference ("\b <owner>.<object>\b0 "), or by simple object reference ("\b <object>\b0 "). As the schema is fully self-contained, the \i <schema>\i0  component may be omitted without danger of confusion.\par
\par
\b\fs24 Schema\b0\fs20\par
\par
As has been mentioned above, a \b schema\b0  defines an SQL namespace which is distinct and inaccessible from other schema. In the CL4 SQL Server, the schema name is equivalent to the current \b connection\b0  being accessed. The schema name can also be SYSTEM for predefined system objects, and these cannot be modified or dropped.\par
\par
\b\fs24 Owner\b0\fs20\par
\par
The \b owner\b0  refers to the owner of the object within the schema. In the CL4 SQL Server, the schema uses a link to pre-existing databases in order to present them for SQL use, rather than embedding the database within the schema itself. The convention for the CL4 Server is that the database \i name\i0  is used as the \i owner\i0  of its database tables. It is possible to log into the schema using the database name as the username, which would thus make the current user the owner of the database objects. In such a case (once DDL commands have been implemented) further tables could be created or dropped in the database. A regular user cannot create a table object.\par
\par
\b\fs24 Object\b0\fs20\par
\par
An \b object\b0  within the schema is owned by an \b owner\b0 . As outlined above, a database "owns" its tables. In the case of \i views\i0 , a view may be created and thus owned by any user (whether a regular user or a database user). Likewise a \i stored procedure\i0  is owned by its creator.\par
\par
For each database linked to a schema there is a corresponding \i sequence order\i0  which is defined at schema creation time (see the description of \b schemaint\b0  in the CL4 Server Readme.wri). This sequence number is used when searching for a table referenced without the \i <owner>\i0  component. It is possible that a table of a given name may exist in more than one of the included databases. In such a case the table from the \i first \i0 database, by sequence order, satisfying the table name is returned. Thus in most cases, the database name \f1 - the \i <owner>\i0  - in an object specification may also be omitted. When there is confusion, or if a table from the last database in the sequence is required, then include the \i <owner>\i0  in the object specification.\par
\par
For example the statement \b SELECT * FROM employees;\b0  issued from within the \b cl4_conn\b0  sample schema is sufficient to access the \i employees\i0  table in the TIMEBILL database, as there is no such table in the DEMGL database (which is the first in the sequence of databases in this example).\par
\f0\par
In the case of other objects like \b procedures\b0\i  \i0 or \b views\b0 , these are by default created under the name of the current user. For views, \i their\i0  underlying tables access database table objects in the same precedence as previously described. It is good practise to qualify a view's tables using their respective \i <owner>\i0  components, when creating the view.\par
\pard\par
\b\fs28 Stored Procedures\par
\pard\li710\b0\fs20\par
\pard\li284\i Stored procedures \i0 provide a mechanism to allow remote (on the server end, at the location of the database) invocation of native language CL4 \i clenter\i0  commands which affect the schema's databases. Rather than storing the procedure scripts within the database itself (as is traditional in most SQL database systems), the CL4 Server implements procedures externally, that is by invoking an external \i clenter\i0  script in the Unix environment. In order to use a \i clenter\i0  script from within the SQL environment it needs to be first "declared" to the schema using the \i CreateProc()\i0  inbuilt procedure. Such a procedure definition, once created, provides the SQL environment access to your external \i clenter\i0  script which can then perform actions on the database tables defined within the schema. You can therefore write any \i clenter\i0  script, then invoke it and pass it parameters from the CL4 Server in order to perform some brief self-contained and non-interactive action against schema databases.\par
\par
In order to access these \i clenter\i0  scripts from SQL however, it is necessary to "declare" them and their expected parameters to the schema. Stored Procedures are declared using the \i CALL CreateProc()\i0  procedure and removed using the \i CALL DropProc()\i0  procedure. Such operations do not affect the external scripts themselves in any way.\par
\par
\b\fs24 Implementation\b0\par
\fs20\par
Stored procedures are implemented in two parts:\par
\par
\pard\li852 1)\tab The physical \i clenter\i0  script, and\par
2)\tab The procedure reference definition stored in the Schema.\par
\pard\li284\par
Once an appropriate \i clenter\i0  script has been written to perform some action on one or more databases referenced in the schema, and then placed in the correct subdirectory to be accessible through the schema, a definition for this script must then be made in the SQL environment in order to make the script known to the server as a \i procedure\i0 , as well as what parameters it should expect before it calls the external script.\par
\par
Such a \i clenter\i0  script should be stored in a directory underneath the location of the database to which it refers. Create a subdirectory named \i <database>\b .prc\b0\i0  in the same location as the database. In this subdirectory you should place all related \i clenter\i0  scripts which will be called as procedures within the SQL environment. All scripts should already be \i compiled\i0 , as this does not happen "on-the-fly" in the clserver environment.\par
\par
It should be noted that a given \i clenter\i0  script, although stored in a location suggestive of access to \i one \i0 database, is allowed to access \i all\i0  databases within the defined schema. For example, if your schema defines three databases in completely different locations, there should exist (although it's not mandatory) a procedures subdirectory (named \i <database>\b .prc\b0\i0 ) for each database. Any procedure which accesses these databases may be placed in any of the three relevant procedure subdirectories.\par
\par
Once you have a set of procedures you must then declare them for use using the \i Interactive SQL\i0  \b odbcisql\b0  utility, or some other SQL-based link to the clserver (eg execute an SQL command in Microsoft Access on a PC connected to the server). The name of the procedure \i must\i0  be the same as the physical \i clenter\i0  file (without the \i .enc\i0 ).\par
\par
\b\fs24 Writing a \i clenter \i0 Procedure Script\par
\b0\fs20\par
\pard\li568\b The Nature of a \i clenter\i0  Script\b0\par
\pard\li852 Stored procedure scripts can support any non-interactive database operation. When designing a \i clenter\i0  script to be used with the SQL schema it is important to keep in mind that due to its internal nature, it cannot be user-interactive insofar as accepting form data from the user. Instead, use the cgi facilities of \i clenter\i0  to obtain the parameters passed to it on invocation (ie, when it is \i CALL\i0 ed), and send Result Set data back to the CL4 Server by printing it to standard output (stdout). Therefore scripts should perform some database insert or update or validation as is relevant to your application, or some kind of selective data retrieval which requires on-the-fly calculations which may not be easy or possible to be done with SQL.\par
\par
A prerequisite for running user procedure scripts with the CL Server is to have the CL4 development suite installed on your system, and accessible by $PATH from the \i oaccess\i0  user from where the Server was installed. Specifically, you will need \i clcomp\i0  to compile your scripts, and \i clenter\i0  for the server to invoke them.\par
\par
A script, \i <filename>\b .ent\b0\i0 , should be precompiled to its equivalent \b\i .enc\b0\i0  form prior to its use with the CL4 Server. The server will not perform an implicit compile if the source is out of date, as a standalone invocation of \i clenter\i0  will. Use \i clcomp\i0  to compile your ent file before using. Note that this ent file would normally reside in one of the database's \i <database>\b .prc\b0\i0  subdirectories to be accessible by the server.\par
\par
As a specific use, it is feasible to convert and utilise some legacy CL scripts which may already be in use in your organisation, to provide their functionality to SQL/ODBC users. Alternatively, specific functions which would be difficult or impossible to perform from within SQL can be implemented as \i clenter\i0  scripts.\par
\pard\li284\b\par
\pard\li568 Database references\b0\par
\pard\li852 All CL4 databases defined within the schema will be made available to the script on invocation. You can therefore refer to the databases without specifying path information to resolve their specific locations.\par
\par
When compiling the script with \i clcomp\i0  however it is often necessary to specify synonyms for your databases to temporarily specify the path information so they can be compiled. Use \i clcomp\i0 's -r<DB>=<DB> parameter to let \i clcomp\i0  know where to find the databases you need to compile against. For example, compile the example script \i DEMGL.prc/ins_trans.ent\i0  using the following command:\par
\pard\li284\par
\pard\li1278\b $ cd data/cl4/DEMGL.prc\par
$ clcomp -rDEMGL=../DEMGL ins_trans\b0\par
\pard\li284\b\par
\pard\li568 Obtaining parameters\b0\par
\pard\li852 Parameters passed by the CL Server to your \i clenter\i0  script can be obtained using the \i getcgi()\i0  function call. For example, a parameter defined as "Account varchar(12)" in the procedure definition should be retrieved into a \i clenter\i0  variable \b define\b0 d as a \b c12\b0  and retrieved using the command:\par
\pard\li284\par
\pard\li1278\b define tAccount,c12\par
tAccount = getcgi('Account')\b0\par
\pard\li284\b\par
\pard\li568 Returning a Result Set\b0\par
\pard\li852 A set of data can be returned by the script back to the SQL environment if the procedure has been declared (in the SQL environment) with a \i RESULT SET\i0  clause. The result set declaration specifies the number and datatypes of the columns the script is expected to return.\par
\par
Data to be returned should be in "data dump format" and sent to the standard output (stdout), with the fields in the same order as expected by the CL Server in the \i RESULT SET\i0  definition. This data should be ASCII text data (regardless of internal datatype), the fields separated with the tilde ("~") character, and each new row is delimited by a newline character (ie, one row per line). Any embedded tildes in your field data should be escaped with the backslash ("\\") character. Each field thus returned is put into successive SQL procedure result set columns by the CL Server as defined in the \i RESULT SET \i0 clause.\par
\par
If insufficient fields are provided in the data output by your\i  clenter\i0  script, then the CL Server will assign subsequent columns to NULL in the result set, up to the end of the row. If every row omits the same column(s), then such additional columns will be suppressed entirely from the result set. Thus a CL procedure can return varying amounts of data to the CL Server, and the columns will be filled from left to right.\par
\par
If too many fields are returned to the server, then these extra fields are written to the server's log file as extranneous data. This mechanism may therefore be used as a debugging aid for your procedure scripts, if server logging is turned on (either set the Trace flag and restart the server, or \i CALL Trace('proc');\i0  - see below). If the procedure declaration contains no \i RESULT SET\i0  clause then all data appearing on stdout from the \i clenter\i0  script is written to the log file.\par
\par
If there is no output from the procedure call and you are expecting output, then it is likely that there was some execution error (usually that the source .ent file needs to be recompiled) and the error message will appear in the server log file (if tracing is on).\par
\par
For example, for a procedure declared as:\par
\par
\pard\li1278\b CALL CreateProc('MyProc(infield1 varchar(20)) RESULT SET (SEQ integer, TXTVAL varchar(40))', 'Sample procedure');\b0\par
\pard\li852\par
and invoked as:\par
\par
\pard\li1278\b CALL Trace('proc');\par
CALL MyProc('Hello world');\par
CALL Trace('noproc');\b0\par
\pard\li852\par
which returns text as follows to it's standard output:\par
\par
\pard\li1278\b 10~Here's data for line 1\par
20~Line 2 data\par
30~The third line~This is extra text sent to the log file\par
40~Fourth line~data~data~more data\par
50~Your parameter was "Hello world"~Last line of data here\line\pard\li852\b0\par
the following data is seen as the result set from SQL:\line\par
\pard\li1278\tx1846\b SEQ\tab TXTVAL  \par
10\tab Here's data for line 1  \par
20\tab Line 2 data     \par
30\tab The third line  \par
40\tab Fourth line     \par
50\tab Your parameter was "Hello world"     \par
\pard\li1278 Rows selected = 5\par
Elapsed time 0 ms.\par
\pard\li852\b0\par
and the following text appears in the server's log file:\par
\par
\pard\li1278\b SQLDRV:  [TID: 4],cl4_procDML.c:285: loadOutput(): Procedure "MyProc" stdout: "This is extra text sent to the log file"\par
SQLDRV:  [TID: 4],cl4_procDML.c:285: loadOutput(): Procedure "MyProc" stdout: "data~data~more data"\par
SQLDRV:  [TID: 4],cl4_procDML.c:285: loadOutput(): Procedure "MyProc" stdout: "Last line of data here"\par
\pard\li284\b0\par
\pard\li852 As an aside, if the CL procedure code had only returned the SEQ field (ie, no text or extra data), then the server will completely suppress the TXTVAL column in the output:\par
\par
\pard\li1278\tx1846\b SEQ\par
10\par
20\par
30\par
40\par
50\par
\pard\li1278 Rows selected = 5\par
Elapsed time 0 ms.\b0\par
\pard\li284\b\fs24\par
Invoking Stored Procedures\b0\par
\fs20\par
To call a stored procedure, use the \b CALL\b0  SQL command thus:\par
\par
\pard\li852\b\i CALL \b0 <procName>\b (\b0 [<parameter> [\b ,\b0  <parameter>]\b );\b0\i0\par
\pard\li284\par
The called procedure will be searched for in the \b .prc\b0  subdirectories along the paths of the databases defined in the schema. Any returned output which does not fit into the result set columns (if defined) is written to the server's log file if logging (tracing) is turned on.\par
\par
\b\fs24 Predefined System Procedures\b0\par
\fs20\par
There are several built-in SQL procedures which are provided to allow the database administrator to perform such operations as creating user procedures and examining tables.\par
\par
\b Procedure CreateProc()\b0\par
\par
\pard\li568 Usage:\par
\pard\fi-1704\li2556\b\i CALL CreateProc('\b0 <procName>\b (\b0 [<paramName> <datatype>[\b (\b0 <size>\b ,\b0 <precision>\b )\b0 ] [\b , \b0 <param> <type>...]\b ) \b0 [\b RESULT SET (\b0 <paramName> <datatype>\b  \b0 [\b ,\b0  <param> <type>...]\b )\b0 ]\b ',\line\pard\li852\tab\tab       \b0 <remarks>\b );\b0\i0\par
\par
\pard\li568 Description:\par
\pard\li852 This procedure creates a user procedure by creating a reference to a \i clenter\i0  script and defining the parameters and associated datatypes required by the \i clenter\i0  process. It will first check to see if the script, named "\i <procName>\b .enc\b0\i0 ", exists in any of the databases' \b .prc\b0  subdirectories (see above).\par
\par
An optional RESULT SET clause, with data parameters, provides a way to return data from the CL procedure to the calling application. The CL program can output data fields in the same number and order as defined.\par
\par
\pard\li568 Parameters:\par
\pard\li852\tx2698\i <procName>\i0\tab The name of the \i clenter\i0  script, without the \i .enc\i0 .\par
\par
\pard\fi-1846\li2698\tx2698\i <paramName>\i0\tab A name used to denote the parameter. These are accessible from within the \i clenter\i0  script using the \b\i getcgi(\b0 '<paramName>'\b )\b0\i0  function.\par
\par
\i <datatype>\i0\tab Is a valid SQL data type. Currently defined datatypes are:\par
\pard\fi-1846\li4828\tx4828 CHAR\tab Fixed length string. Takes an additional \b (\b0 <size>\b )\b0  specifier.\par
VARCHAR\tab Variable length string. Also takes a \b (\b0 <size>\b )\b0  specifier.\par
NUMERIC\tab Numeric data. Takes additional \b (\b0 <size>,<precision>\b )\b0  specifiers.\par
DECIMAL\tab Synonym for NUMERIC.\par
NUMBER\tab Synonym for NUMERIC.\par
INTEGER\tab Integer numeric data. Takes a \b (\b0 <size>\b )\b0  specifier.\par
REAL\tab Double-precision floating point numeric. Takes additional \b (\b0 <size>,<precision>\b )\b0  specifiers.\par
FLOAT\tab Floating point numeric. Takes additional \b (\b0 <size>,<precision>\b )\b0  specifiers.\par
DOUBLE\tab Double-precision floating point numeric. Takes additional \b (\b0 <size>,<precision>\b )\b0  specifiers.\par
DATE\tab Date string.\par
TIMESTAMP\tab Date and time string.\par
LONGVARBINARY\tab Not yet implemented.\par
\par
\pard\fi-1846\li2698\tx2698\b RESULT SET\b0\tab Introduces an optional result set of data which will be returned by the function (see above).\par
\par
\i <remarks>\i0\tab Any text which you can use to describe this function.\par
\pard\li710\par
\pard\li568 Examples:\par
\pard\li852 The following examples assume using the \i cl4_conn\i0  schema provided as a demonstration connection in your distribution.\par
\pard\li568\par
\pard\fi-852\li1704\i CALL CreateProc('MyProc()', 'Sample procedure');\line\i0 Defines a procedure reference to a CL program named "MyProc.enc" which resides in the DEMGL.prc subdirectory. It requires no parameters (and returns no data), and therefore may be invoked as follows:\line\pard\fi-994\li1704\tab CALL MyProc();\par
\par
\pard\fi-1562\li2414\i CALL CreateProc('ins_trans(Account varchar(12), Period integer, TDate date, Source varchar(6), Type varchar(6), Ref varchar(6), Desc varchar(30), TAmount numeric(10,2))','Insert transaction into database with unique transaction number');\i0\par
\pard\fi-852\li1704\tx1704\tab This procedure is provided as a sample in the DEMGL.prc subdirectory. It takes the shown parameters and will insert a record in the \i Trans\i0  table according to the supplied information. Each parameter is retrieved in the script with a \i getcgi()\i0  function call. It can be invoked as follows:\line\tab CALL ins_trans('400', 199901, '10-18-99', 'gl', 'sql', 'simon', 'Test clsql transaction', 100.00);\par
\par
\pard\fi-1562\li2414\tx1704\i CALL CreateProc('gettrans(Account varchar(12), Period integer, TDate date) RESULT SET (Source Varchar(6), Type varchar(6), Ref varchar(6))', 'Retrieve transaction details');\i0\par
\pard\fi-852\li1704\tx1704\tab This is a sample of how a result set can be returned from a transaction call. The procedure invoked, \i gettrans\i0 , can perform any manipulations on the data in native CL code prior to returning the result set to the clserver.\line\tab CALL gettrans('400', 199901, '10-18-99');\par
\pard\fi-994\li1704\par
\pard\li284\b Procedure DropProc()\par
\pard\li568\par
\b0 Usage:\par
\pard\li852\b\i CALL DropProc('\b0 <procName>[\b ()\b0 ]\b ');\b0\i0\par
\pard\li710\par
\pard\li568 Description:\par
\pard\li852 This procedure deletes a reference to a \i clenter\i0  procedure and all its parameters.\par
\par
\pard\li568 Parameter:\par
\pard\li1562\tx3408\i <procName>\i0\tab The name of the \i clenter\i0  script, without the \i .enc\i0 .\par
\pard\li710\par
\pard\li568 Example:\par
\pard\fi-852\li1704\i CALL DropProc('MyProc');\par
\line\pard\li284\b\i0 Procedure Trace()\b0\par
\par
\pard\li568 Usage:\par
\pard\li852\b\i CALL Trace('\b0 [<function>|<module>\b :\b0 <hexVal>]\b ');\b0\i0\par
\par
\pard\li568 Description:\par
\pard\li852 This procedure controls tracing levels from within the SQL environment. With this function you can selectively activate certain tracing operations. All trace output is sent to the server's log file, which can be examined using the \b L\b0 og function from within the \i srv\i0  server control program. The \i Trace()\i0  procedure affects tracing only within the current session. Thus, if you invoke the \i Trace()\i0  command separately from the command you wanted to trace you will not get the desired output. With the server itself in \i Notrace\i0  mode (from \i srv\i0 ) it is therefore a lot more convenient to manipulate tracing using the \i Trace()\i0  procedure only for those statements you wish to examine (see the example above, in \b\i Returning a Result Set\b0\i0 ).\par
\par
\pard\li568 Parameters:\par
\pard\fi-1846\li2698\tx2698\i <function>\i0\tab Specifies the tracing function to perform:\par
\par
\pard\fi-1278\li4402\tx4402 ON\tab Turns on all tracing (case is not critical). Equivalent function codes are: "ALL" and "1". Note that this can create \i really\i0  voluminous output!\par
\par
OFF\tab Turns off all tracing. Equivalent function codes are "NOALL", "0" and "" (empty).\par
\par
PROC\tab Turns on procedure output tracing. This allows output from a procedure \i CALL\i0  to be written to the log file. If there is no \i RESULT SET\i0  clause defined for a procedure then all text output from the procedure is logged. Otherwise, any excess column data other than can be used in the result set is sent to the log file. See the above section \i "Returning a Result Set"\i0  for more information about procedure tracing.\par
\par
NOPROC\tab Turns off procedure output tracing.\par
\pard\li852\tx2698\par
\pard\fi-1846\li2698\tx2698\i <module>\b :\b0 <hexVal>\i0\tab If none of the above code strings are matched, the \i Trace()\i0  function will attempt to convert the code to a module and hexadecimal tracel level value. This method can be used to more precisely control the level of tracing which the server should perform. Currently the codes defined are a bitwise mask of the following (hexadecimal) values:\par
\par
\pard\fi-3124\li6248\tx4402\tx6248 001\tab FATAL\tab Indicates fatal errors, such as cannot get memory, or invalid addresses.\par
002\tab SNO\tab Specifies "should not occur" type errors.\par
004\tab PARM\tab Specifies that a bad or missing parameter was passed.\par
008\tab ERRORS\tab Specifies general errors.\par
010\tab MAJOR_EV\tab Indicates a major application event.\par
020\tab MINOR_EV\tab Indicates minor or secondary application events to give more detail.\par
040\tab INFO\tab Specifies general information to give details about events.\par
080\tab F_TRACE\tab Specifies a function call trace (program flow).\par
100\tab TRIVIA\tab Specifies trivial information of value only to the implementation.\par
\pard\fi-1846\li2698\tx2698\par
\pard\li2698\tx2698 The internal modules relevant for tracing are as follows:\par
\pard\fi-3124\li6248\tx4402\tx6248 ALL\tab All modules\tab This gives all of the available modules, and turns out to be as verbose as setting server trace ON using the \i srv\i0  command and restarting the server. Care should be used for this level as the log file will rapidly fill up and response will be quite slow.\par
SQLDRV\tab SQL driver\tab This is the CL level driver module. It provides all interface functions between the SQL language and the CL database. This module is set when the "ON" function is used, above.\par
\pard\li2698\tx2698\par
There are a number of other modules which can be traced: AE, AL, PRES, RASE RDA, RTP, SACF, SAO, SESS, SQLS, SUR and USER. Most of these relate to the internal mechanics of the middleware and provide no really useful function for the purposes of tracking down SQL problems.\par
\pard\b\fs24\par
\pard\li284\fs20 Procedure Describe()\b0\par
\par
\pard\li568 Usage:\par
\pard\li852\b\i CALL Describe('\b0 <tableName>\b ');\b0\i0\par
\par
\pard\li568 Description:\par
\pard\li852 This procedure can be used to describe the structure of a CL4 table. The structure of the columns (fields) in the table is presented in SQL format as a CREATE TABLE statement. The primary index columns follow as a UNIQUE INDEX statement. Following the primary table structure, any alternate key indexes are presented as a series of CREATE INDEX statements.\par
\par
Note that this procedure currently does not support SYSTEM tables or VIEWs. It should also be noted that although the returned information is given as CREATE TABLE and CREATE INDEX statements, the CL Server does not currently support the DDL (Data Definition Language) commands necessary to create tables and indexes. The output from the \i Describe()\i0  procedure can neverthless aid in saving or documenting your schema configuration.\par
\par
\pard\li568 Parameters:\par
\pard\fi-1846\li2698\tx2698\i <tableName>\i0\tab Specifies the CL4 table name to describe.\par
\pard\li852\par
\pard\fi-284\li852 Result set:\line This function returns the following result set columns:\par
\par
\pard\fi-1846\li2698\tx2698\i SEQ\i0\tab Sequential ordering for the result statement lines.\par
\i STATEMENT\i0\tab Statement text.\par
\pard\b\fs24\par
\pard\li284\fs20 Procedure DBConnect()\b0\par
\par
\pard\li568 Usage:\par
\pard\li852\b\i CALL DBConnect('\b0 <DbName>\b ', '\b0 <DbPath>\b ', \b0 <Seq>\b , '\b0 <Remarks>\b ');\b0\i0\par
\par
\pard\li568 Description:\par
\pard\li852 The DBConnect procedure provides an SQL mechanism by which CL4 databases can be associated with the current schema. This is equivalent to using the external \i schemaint\i0  Database Maintenance program to set up the databases to be used within the schema, but allows this operation to be performed wholly from within the SQL Schema environment.\par
\par
Databases to be connected are still location-limited by the top-level path specified in the \i CONNECT_STRING\i0  for the connection, from within the \i rdaadmin\i0  utility (see \i Readme.wri\i0 ). If a path has been given in the \i CONNECT_STRING\i0 , then any databases which can be connected to must be \b at\b0  or \b below\b0  this directory. In this case, the \i <DbPath>\i0  field can specify a relative path \i below\i0  the top-level directory, or be an empty string to indicate a database in the top-level directory.\par
\par
In this manner, a site administrator (usually the sole administrator of the \i oaccess\i0  Unix account) may specify the top-level path using \i rdaadmin\i0 , then delegate schema-specific database administration to a designated DBA who has superuser privileges in the SQL Schema environment, but otherwise cannot login to \i oaccess\i0  and access unauthorised databases by other means. This provides an extra level of administration security for the CL Server environment.\par
\par
If there is no top-level path given in the \i CONNECT_STRING\i0  then the \i <DbPath>\i0  may access any absolute path within the Unix filesystem.\par
\par
NOTE: Extreme care should be exercised while using the \i DBConnect()\i0  function. In particular it is strongly advised that no other users be connected to the schema while issuing this statement.\par
\par
In addition, use of this function could produce internal inconsistencies in the administration user's \i currently running instance \i0 of the server. That is, the user issuing the statement from \i oaisql\i0  or an equivalent utility should detach from their session soon afterwards. Attached databases may not be available to the current user until they disconnect from the current session (ie, issue the \i disconnect\i0  command) then reconnect again. It is neither necessary for the user to log off Unix, nor is it a requirement that the server itself need be restarted. After disconnecting the user can reconnect to the schema and continue working with the newly connected CL4 databases.\par
\par
Once created (and after reconnecting), the database administrator can then associate a password with the database using the \i CREATE USER\i0  statement, as the database becomes just another user in the Schema environment.\par
\par
Superuser permissions (user \i sa\i0  or equivalent) are required to perform this function.\par
\par
\pard\li568 Parameters:\par
\pard\fi-1846\li2698\tx2698\i <DbName>\i0\tab Specifies the CL4 database name to connect into the current Schema. This database must exist in the specified location prior to executing this procedure. This database must also be readable \b\i and writable\b0\i0  by the \i oaccess\i0  user (the user under which the CL server is running), even if it is to be used as a read-only database.\par
\par
\i <DbPath>\i0\tab Specifies the absolute or relative path where the database may be found. A relative path must be given if there is a top-level path string defined in the \i CONNECT_STRING\i0  specified in the \i rdaadmin\i0  utility (see \i Readme.wri\i0 ). This relative path must be at or below this top-level path, ie specifying "../<path>" will not work.\par
\par
\i <Seq>\i0\tab Indicates the sequence number of the database. A unique sequence number should be given for each database defined in the system. This value specifies the search order for tables within the schema. Should the same named table be present in more than one database connected to the schema, then the first table matched in Sequence order will be returned. To override this ordering, the Owner (database) name can be given in the table accessing statement in order to explicitly name the database to which it belongs.\line\line For example, assuming the schema contains three databases with the same internal table structures, named GLCURRENT (Seq=1), GLLASTMTH (Seq=2) and GLLASTYR (Seq=3). Then to access the last year's \i accounts\i0  table issue:\line\pard\fi-1846\li2698\tx2698\tx4544\tab SELECT Acc_Description, Profit_Balance\line\tab    FROM GLLASTYR.accounts\line\tab WHERE Account = '400';\par
\par
\i <Remarks>\i0\tab This is a free-form text string which can be used to identify the use of the database.\par
\pard\b\fs24\par
\pard\li284\fs20 Procedure DBDisconnect()\b0\par
\par
\pard\li568 Usage:\par
\pard\li852\b\i CALL DBDisconnect('\b0 <DbName>\b ');\b0\i0\par
\par
\pard\li568 Description:\par
\pard\li852 The \i DBDisconnect() \i0 procedure allows removal of the database connection reference within the current SQL schema. As for the \i DBConnect()\i0  procedure, care should be taken that no other users are connected while issuing this statement, and that the current user should also reconnect to the schema to avoid inconsistencies in the user's current SQL environment.\par
\par
Superuser permissions (user \i sa\i0  or equivalent) are required to perform this function.\par
\par
\pard\li568 Parameters:\par
\pard\fi-1846\li2698\tx2698\i <DbName>\i0\tab Specifies the CL4 database name to disconnect from the current Schema.\par
\pard\b\fs28\par
\pard\li284\fs20 Procedure Hier()\b0\par
\par
\pard\li568 Usage:\par
\pard\li852\b\i CALL Hier('\b0 [on|off|default[:default]]\b ');\b0\i0\par
\par
\pard\li568 Description:\par
\pard\li852 The \i Hier() \i0 procedure provides control over the referential integrity facilities inherent in the CL4 database. With this function turned on (\i CALL Hier('on');\i0 ), deletion of table rows in a "master" table will recursively cascade down all of it's detail or "Set" tables, thereby automatically maintaining database integrity.\par
\par
For example, if an order header table has a record with order number "199293", and an order detail table has several records with the same parent record key, then deleting order number "199293" in the header (master) table will also delete all order detail records corresponding to this order, without the need to explicitly maintain these as well.\par
\par
Each schema has a global default hierarchy mode, which can be changed using the \i :default\i0  option. Superuser permission (user \i sa\i0  or equivalent) is required to set the default hierarchy mode.\par
\par
\pard\li568 Parameters:\par
\pard\fi-1846\li2698\tx2698\i 'on'\i0\tab Turns on Hierarchical referential integrity mode.\par
\i 'off'\i0\tab Turns off Hierarchical referential integrity mode.\par
\i 'on'\i0\tab Resets the Hierarchy mode to the schema's default.\par
\i 'on:default'\i0  or\par
\i 'off:default'\i0\tab Sets the default Hierarchy mode for the schema (superuser only).\par
\pard\b\fs28\par
Views\par
\b0\fs20\par
\pard\li284 Views can be used to selectively extract data from a table, or facilitate access of data from joined tables by predefining the join characteristics ahead of time.\par
\pard\par
\pard\li994 CREATE VIEW smith.perview AS\par
\pard\fi-852\li2698\tx2698 SELECT\tab Period, Starting_Date, Report_Date\par
   FROM\tab DEMGL.Periods\par
WHERE\tab Period >= 9000;\par
\pard\li1846\par
\pard\li994 CREATE VIEW DEMGL.Accinfo AS\par
\pard\fi-852\li2698\tx2698 SELECT\tab ac.Account, ac.Acc_Description, ac.Profit_Balance, ac.Period, pd.Starting_Date, pd.Ending_Date\par
   FROM\tab DEMGL.Accounts ac, DEMGL.Periods pd\par
WHERE\tab pd.Period = ac.Period;\par
\pard\li994\par
\pard\li284\b\fs24 System Views\b0\par
\fs20\par
Several system views have been created to facilitate access to table and view information.\par
\par
\pard\fi-2272\li2840\tx2840\b ALL_TABLES\b0\tab Displays all tables in the system. This view is equivalent to the OA_TABLES table except that it returns only a listing of tables (excluding views).\par
\b SCHEMA_TABLES\b0\tab Lists all non-system user-defined schema tables, that is tables found in databases only.\par
\b USER_TABLES\b0\tab This view displays the current user's tables only. Unless the current user is a database-user (like "DEMGL") this will always return an empty list (as users cannot currently create tables in their own name).\par
\par
\b ALL_VIEWS\b0\tab Lists all the views defined in the system.\par
\b SCHEMA_VIEWS\b0\tab Lists all non-system (user-defined) views in the schema.\par
\b USER_VIEWS\b0\tab Limits the views to the current user's views only.\par
\par
\b ALL_PROCS\b0\tab Displays all procedures in the schema including system procedures. This is equivalent to the contents of the CL_PROCS table.\par
\b SCHEMA_PROCS\b0\tab Lists all user-defined procedures in the schema.\par
\b USER_PROCS\b0\tab Lists all procedures defined by the current user only.\par
\pard\li284\par
\b\fs24 View Permissions\b0\par
\fs20\par
Granting a permission on a view and not on it's underlying table can provide a way to hide sensitive data. See the subsection \i View Permissions\i0 , below in section \i Permissions\i0  for information about how views may be used to limit access to tables.\par
\par
\pard\b\fs28 Database Control Language (DCL)\par
\b0\fs20\par
\pard\li284\b\fs24 Commands\b0\par
\fs20\par
Although functions have been created within \i srv\i0  to support the creation and deletion of users, a more complete interface into this level of schema management has been provided from within the SQL environment. Using an SQL access utility like \b odbcisql\b0 , a DBA (Database Administrator) can maintain the list of users, passwords, roles and privileges for which they are to be granted access to schema objects. The DCL commands which have been implemented are as follows.\par
\par
\pard\li568\b CREATE USER\b0\par
\pard\li284\par
\pard\li852 Usage:\par
\pard\li1136\b\i CREATE USER \b0 <userName> [\b IDENTIFIED BY \b0 <password> | \b IDENTIFIED EXTERNALLY\b0 ]] [\b USERDATA '\b0 <description>\b '\b0 ] \b ;\b0\i0\par
\pard\li852\par
This command causes a user to be created or redefined \i for the current schema only\i0 . The \i <userName>\i0  cannot be the same as any existing role name, or a database owner name (which is effectively just another user anyway). User names do not necessarily need to be existing Unix logon names.\par
\par
Use the \b\i IDENTIFIED BY\b0  <password>\i0  clause to set a password for the user. Note that the password is \i not\i0  to be enclosed in quotes, therefore it must be a one word password.\par
\par
If the user already has an external Unix login account on the server's system, then \b\i IDENTIFIED EXTERNALLY\b0\i0  can be used to allow the user to login to the CL4 Server using \b odbcisql\b0  without specifiying a password, provided he or she is already logged into the Unix system and is accessing the server from their login account. Note that this implies that such a user cannot access their SQL login from a remote PC-based client. Use a conventional SQL login/password to allow access in this case.\par
\par
If the \b\i IDENTIFIED BY\i0  \b0 clause is not present, then the user's password will be deleted, allowing the user to login without having to specify a password.\par
\par
The \b\i USERDATA\b0\i0  clause can be used to attach some descriptive text to the user account for easy identification.\par
\par
If the user already exists in the SQL schema when this command is issued, any objects the user owns or permissions the user has are left untouched. This command may therefore be used to safely change a user's password, or identifying description.\par
\par
The SQL \i admin\i0  (superuser) identifier cannot be set using this command (see User Maintenance, under \b schemaint\b0 ). The \b sa\b0  user, or whoever you designate to be the superuser, has carte blanche to access and change all system operations and permissions, regardless of the permissions assigned to this superuser. Use \b schemaint\b0  User Maintenance to set this identifier externally.\par
\par
The designated \i <userName>\i0  may also be one of the defined \b databases\b0  within the schema. This allows a database owner's password and identifying remarks to be changed.\par
\par
\pard\li568\b DROP USER\b0\par
\pard\li284\par
\pard\li852 Usage:\par
\pard\li1136\b\i DROP USER \b0 <userName>\b ;\b0\i0\par
\pard\li852\par
This command may be used to remove a defined user from the Schema. This does not remove a Unix user, but simply removes an SQL user's name from the schema environment.\par
\par
Note that a user's set of permissions, views and procedures will not be removed automatically if the user is dropped. This provides a means whereby the DBA can temporarily suspend the account of a user. If a user is to be removed completely from the system, the DBA must revoke \i all\i0  privileges which have been granted to the user, and drop any tables, views and procedures which the user has created. It is permissible to remove permissions and objects belonging to a user \i after \i0 the user has been dropped.\par
\par
A database owner name cannot be dropped, as this name provides access to the database in the schema.\par
\pard\li284\par
\pard\li568\b CREATE ROLE\b0\par
\pard\li284\par
\pard\li852 Usage:\par
\pard\li1136\b\i CREATE ROLE \b0 <roleName>\b ;\b0\i0\par
\pard\li852\par
This command creates a role in a Schema. The \i <roleName>\i0  specified cannot be the same as any existing user name or database owner name.\par
\par
Defining roles allow sets of privileges (see below) to be assigned collectively to individual users, rather than having to assign each privilege separately. For example, you could define a role \i accounts\i0  which has associated privileges granted allowing access to only the required accounting tables in a database. Once the \i accounts\i0  role has been defined and set up with the required privileges, you can assign the role to a group of users. The users thus take on the privileges granted to the role. Users can have more than one role assigned to them if desired. A DBA could also set up a role called \i dba\i0  to allow developers to control access to tables, views and procedures they create in the system.\par
\pard\li568\b\par
DROP ROLE\b0\par
\pard\li284\par
\pard\li852 Usage:\par
\pard\li1136\b\i DROP ROLE \b0 <roleName>\b ;\b0\i0\par
\pard\li852\par
This command removes a defined role from the Schema. All privileges associated with the role are \i not\i0  implicitly removed with this command. Privileges associated with a role must be separately revoked. Note that it is permissible to revoke privileges on a role even \i after \i0 the role has been dropped. Any users which have been granted the role however will no longer have its associated privileges.\par
\par
\pard\li568\b GRANT (Role)\b0\par
\par
\pard\li852 Usage:\par
\pard\li1136\b\i GRANT\b0  <roleName> \b TO\b0  \{ <userName> | <roleName> | \b PUBLIC\b0  [\b ,\b0  ...] \} \b ;\b0\i0\par
\pard\li852\par
This command associates a user with a predefined role. All privileges which have been assigned to the role are now available for the given user. Roles can also be associated with other roles. The when such a nested role is assigned to a user, the user takes on all capabilities of the nested roles as well.\par
\pard\li284\par
\b\fs24 OA SQL Support Tables\par
\pard\li568\b0\fs20\par
The SQL System schema contains several tables which can be examined to identify objects defined in the Schema. These tables are read-only and describe the tables, columns, procedures and such within this schema environment. The tables can be examined using a command like SELECT * FROM OA_TABLES;    The following is a brief list of the available tables.\par
\pard\par
\pard{\pntext\f4\'B7\tab}{\*\pn\pnlvlblt\pnf4\pnindent0{\pntxtb\'B7}}\fi-436\li1288\tx3976\b OA_TABLES\b0\tab Description of all the tables in the schema\par
\b{\pntext\f4\'B7\tab}OA_COLUMNS\b0\tab Descriptions of columns for all the tables in the schema\par
\b{\pntext\f4\'B7\tab}OA_TYPES\b0\tab Data types supported by the system\par
\b{\pntext\f4\'B7\tab}OA_INFO\b0\tab A variety of information about the support and implementation of the server\par
\b{\pntext\f4\'B7\tab}OA_STATISTICS\b0\tab Index information of all tables in the schema\par
\b{\pntext\f4\'B7\tab}OA_FKEYS\b0\tab Foreign Key information of all tables in the schema\par
\b{\pntext\f4\'B7\tab}OA_PROC\b0\tab Description of all the procedure names in the schema\par
\b{\pntext\f4\'B7\tab}OA_PROCCOLUMNS\b0\tab Descriptions of input and output parameters and columns that make up result\line\tab set for specified procedures.\par
\pard\li284\b\par
\fs24 CL SQL Support Tables\b0\par
\fs20\par
\pard\li568 There are a few additional \i read-only\i0  schema tables (aside from the OA_* tables) which have been provided to facilitate database administration. These tables enable the DBA to examine the users, roles and privileges which have already been defined in the system. The tables can be examined using a command like:\line\pard\li568\tx3976\tab SELECT * FROM CL_USERS;\par
\pard\li568 The CL function tables are:\par
\par
\pard{\pntext\f4\'B7\tab}{\*\pn\pnlvlblt\pnf4\pnindent0{\pntxtb\'B7}}\fi-436\li1288\tx3976\b CL_USERS\b0\tab Description of all the users defined within the schema\par
\b{\pntext\f4\'B7\tab}CL_ROLES\b0\tab Gives a list of the user roles defined in the schema\par
\b{\pntext\f4\'B7\tab}CL_PRIVS\b0\tab A list of all privileges which have been defined in the schema\par
\b{\pntext\f4\'B7\tab}CL_DATABASES\b0\tab Gives a list of all databases defined for use within the schema\par
\b{\pntext\f4\'B7\tab}CL_PROCS\b0\tab Lists all procedures, system and user, defined for use in the schema\par
\pard\par
\pard\li568\b\i CL_USERS\b0\i0  - User table\par
\pard\li284\par
\pard\li852 Columns:\par
\pard\li1136\tx2698\b USERNAME\b0\tab User name\par
\b ADMIN\b0\tab Administrator flag. Usually 0, this will be 1 if the user is the defined SuperUser for the schema.\par
\b PASSWORD\b0\tab The user's encrypted password.\par
\b DESCRIP\b0\tab The descriptive comments associated with the user.\par
\pard\li852\par
The \b CL_USERS\b0  table lists the users defined in the current schema. All database "owners" are also listed here. Note that SQL users are independant from both Unix usernames or Windows workstation usernames. In addition, the set of users defined in one schema is independent from those in other schemas. This allows greater flexibility for the system administrator to control the security of user access to each of the given schemas over which the CL Server presides.\par
\par
\pard\li568\b\i CL_ROLES\b0\i0  - Roles table\par
\pard\li284\par
\pard\li852 Columns:\par
\pard\li1136\tx2698\b ROLENAME\b0\tab The name of the role\par
\pard\li852\par
The \b CL_ROLES\b0  table lists the roles defined in the schema. Once defined, privileges can be assigned to these roles rather than individually to users. A user may then be associated with a role, thereby immediately adopting all privileges assigned against the role.\par
\par
\pard\li568\b\i CL_PRIVS\b0\i0  - Privileges table\par
\pard\li284\par
\pard\li852 Columns:\par
\pard\fi-1562\li2698\tx2698\b NAME\b0\tab User or role name to which this privilege refers. This can also be \i PUBLIC\i0  for public privileges\par
\b TYPE\b0\tab The type of the name: \i USER\i0  or \i ROLE\i0 .\par
\b OBJECT\b0\tab The object associated with this privilege. This is only applicable to object and role privileges (not system privileges).\par
\b PRIVTYPE\b0\tab The type of the privilege being applied to the \b OBJECT\b0 . Can be \i TABLE\i0 , \i PROC\i0 , \i ROLE\i0  or \i SYSTEM\i0  privilege.\par
\b STATEMENT\b0\tab An SQL statement which can be used to regenerate this privilege.\par
\pard\li852\par
The \b CL_PRIVS\b0  table lists all the privileges which have been granted within the schema. There are three types of entry. They can be assigned to both \i USERs\i0  and \i ROLEs\i0 . The schema's database administrator (usually \i sa\i0 ) has blanket privileges across the schema, and therefore does not appear in this list.\line\par
\pard{\pntext\f4\'B7\tab}{\*\pn\pnlvlblt\pnf4\pnindent0{\pntxtb\'B7}}\fi-436\li1572\b System \b0 privileges have no associated object.\par
\b{\pntext\f4\'B7\tab}Table\b0 , \b view \b0 and \b procedure \b0 privileges will specify the table/view/procedure name in the \b OBJECT\b0  field.\par
\b{\pntext\f4\'B7\tab}Role \b0 privileges identify that the specified \i USER\i0  or \i ROLE\i0  has been granted this Role. Thus a role can be assigned to another role in the Schema.\par
\pard\li852\par
\pard\li568\b\i CL_DATABASES\b0\i0  - Database table\par
\pard\li284\par
\pard\li852 Columns:\par
\pard\li1136\tx2698\b SEQ\b0\tab Sequence number of the database. This is the order of databases for which a particular table will be searched.\par
\b DBNAME\b0\tab Database file name.\par
\b DBPATH\b0\tab Path where the database is located.\par
\pard\li852\par
The \b CL_DATABASES\b0  table lists all databases defined in the schema.\par
\par
\pard\li568\b\i CL_PROCS\b0\i0  - Procedures table\par
\pard\li284\par
\pard\li852 Columns:\par
\pard\li1136\tx2698\b QUALIFIER\b0\tab The current schema name.\par
\b OWNER\b0\tab The owner of the procedure. System procedures are identified with the owner "SYSTEM".\par
\b NAME\b0\tab Name of the procedure.\par
\b DEFINITION\b0\tab Definition of the procedure.\par
\pard\li852\par
The \b CL_PROCS\b0  table lists all system (built-in) and user procedures which have been defined in the schema. The list of procedures can be saved to a file using the following statement:\par
\par
\pard\li2698 RESFILE proclist.sql\par
SELECT CONCAT(CONCAT(CONCAT(CONCAT('CALL CreateProc(''', Definition), ''', '''), Remarks), ''');')\par
   FROM cl_procs\par
WHERE Owner != 'SYSTEM';\par
RESFILE\par
\pard\li852 or alternatively:\par
\pard\li2698 RESFILE proclist.sql\par
SELECT 'CALL CreateProc(''', Owner, '.', Definition, ''', ', CONCAT(CONCAT('''',Remarks),''''), ');'\par
   FROM cl_procs\par
WHERE Owner <> 'SYSTEM';\par
RESFILE\par
\pard\li852\par
The output is a series of lines with "CALL CreateProc()" statements in it. Such a file can then be used as a backup which can be later run in a script to regenerate all user procedures in the system (or even applied to another schema with a similar configuration):\par
\par
\pard\li2698 CMDFILE proclist.sql\par
\pard\li852\par
\pard\b\fs28 Permissioning\par
\pard\li284\b0\fs20\par
The permissioning module is provided to permit restrictions on access to objects within the SQL environment to be defined. With a newly created schema, only system objects (system tables like OA_* and CL_*) can be accessed by an ordinary user. Permissions must be granted for the user to access tables in databases which have been defined for the schema.\par
\par
In addition a user has implicit permissions to use or drop any object (like a view or a procedure) which they own, although the user must have permission to create the object in the first place (eg, GRANT CREATE PROCEDURE TO <user>).\par
\par
It should be noted that these commands largely follow the standard SQL syntax, and are chiefly provided here as a summary and to explain differences where they apply. In the following descriptions, permissions as granted may be revoked using the appropriate \b REVOKE\b0  command. Replace GRANT with REVOKE, and TO with FROM. For example:\par
\par
\pard\li852 REVOKE CREATE ROLE FROM <user>;\par
\pard\li284\b\par
\fs24 Table Permissions\b0\par
\fs20\par
\pard\li568 A table is owned by a database. At present tables cannot be created or dropped from within the SQL environment (even though the commands to do so are recognised by the server), but a database \i owner \i0 must grant permissions for others to read or modify its contents. Logged in as the database owner, this user can access its own tables freely.\par
\par
The command which effects access to database tables is:\par
\par
\pard\li1136\tx1988\b\i GRANT\b0\tab\{ \b SELECT\b0  | \b INSERT\b0  | \b UPDATE\b0  | \b DELETE\b0  | \b ALL\b0  [\b PRIVILEGES\b0 ] [\b ,\b0  ...] \}\line\pard\li1136\tx1420\tx1988\tab  \b ON\b0\tab\{ [<owner>\b .\b0 ]<tablename> [\b ,\b0  ...] \}\par
\tab  \b TO\b0\tab\{ <user> | <role> | \b PUBLIC\b0  [\b ,\b0  ...] \} \b ;\b0\par
\pard\li568\i0\par
These can be used to selectively allow access to one or more tables to one or more users, roles, or to the general public.\par
\par
For example,\par
\pard\li1136 GRANT SELECT, UPDATE ON DEMGL.periods, DEMGL.journals TO scott, dbarole;\line\pard\li568\par
It should be noted that the user issuing this command needs to have permission to \i grant \i0 the privileges. This user must be one of the following:\par
\pard{\pntext\f4\'B7\tab}{\*\pn\pnlvlblt\pnf4\pnindent0{\pntxtb\'B7}}\fi-294\li1288 The owner of the table(s) being granted (i.e., the \i DEMGL\i0  user),\par
{\pntext\f4\'B7\tab}The Superuser (eg, \i sa\i0 ),\par
{\pntext\f4\'B7\tab}Have GRANT ANY PRIVILEGE permission (see \i System Privileges\i0 , below), or\par
{\pntext\f4\'B7\tab}Belong to a role which has GRANT ANY PRIVILEGE permission.\par
\pard\fi-720\li1288\par
\pard\li568 Note that if the user satisfies any of the last three of these conditions, then the user has the ability to grant privileges for tables which he/she does not necessarily own.\par
\pard\fi-720\li1288\par
Any given user will have permission to access a table under any of the following conditions:\par
\pard{\pntext\f4\'B7\tab}{\*\pn\pnlvlblt\pnf4\pnindent0{\pntxtb\'B7}}\fi-294\li1288 The owner of the table (or someone with the above authorities) has issued an explicit GRANT for the desired operation (eg SELECT) to the given user,\par
{\pntext\f4\'B7\tab}The user has a System Privilege which covers the desired operation (eg GRANT SELECT ANY TABLE),\par
{\pntext\f4\'B7\tab}The user belongs to a Role which has the required permission,\par
{\pntext\f4\'B7\tab}PUBLIC has permission to perform the desired operation,\par
{\pntext\f4\'B7\tab}The user is the Superuser, or\par
{\pntext\f4\'B7\tab}The user is the owner of the table.\par
\pard\fi-720\li1288\par
\pard\li284\b\fs24 View Permissions\b0\par
\fs20\par
\pard\li568 Views follow the same rules as tables. It is important to note however that although tables cannot currently be created or dropped, a view \i can\i0  be created by a given user, and thereby be owned \i by\i0  that user. It is up to the view owner therefore to grant others permission to access the view.\par
\par
Note that a user cannot \i create\i0  the view unless he/she has either of the following permissions:\par
\pard{\pntext\f4\'B7\tab}{\*\pn\pnlvlblt\pnf4\pnindent0{\pntxtb\'B7}}\fi-294\li1288 GRANT CREATE VIEW TO <user>, or\par
{\pntext\f4\'B7\tab}GRANT CREATE ANY VIEW TO <user>\line\pard\li568\par
In the second such case, the user could potentially create a view on behalf of another user. For example if the user is logged in as \i scott\i0  and has CREATE ANY VIEW privilege, then the user can issue the command:\par
\pard\li994\par
CREATE VIEW smith.perview AS\par
\pard\li1846 SELECT period, starting_date, report_date FROM DEMGL.periods WHERE period >= 9000;\par
\pard\li568\par
Implementation Note: Currently the \i GRANT CREATE VIEW\i0  statement is not supported by the server. If a user is to be allowed to create views then the database administrator should grant them \i CREATE ANY VIEW\i0  permissions for now.\par
\par
The use of this view, however, depends on the permissions on the view. Judicious use of permissions on views can provide access to tables (or even parts of tables) for which a user would otherwise not have access to, without having explicit permissions to access the base table. A table owner (ie, a database, or \i sa\i0  on behalf of the database) could create a view over a table, then grant permission to PUBLIC (for example) to access the view. Even if the underlying table has no permissions on it a user can access it's information using the view, thereby exposing table data in a controlled manner through the view.\par
\par
\pard\li994 GRANT SELECT ON smith.perview TO PUBLIC;\par
\pard\li568\par
For example granting the above permission only on the view \i perview\i0  (and none on the \i periods\i0  table), would allow users access to only the \i period\i0 , \i starting_date\i0  and \i report_date\i0  columns of the table, and even then only on periods "9000" and above.\par
\pard\li284\par
\b\fs24 Procedure Permissions\b0\par
\fs20\par
\pard\li568 A user can create a procedure (and thus becomes it's owner) and must grant permissions for others to use the procedure.\par
\par
\pard\li1136\tx1988\b\i GRANT\b0\tab\b EXECUTE\b0\line\pard\li1136\tx1420\tx1988\tab  \b ON\b0\tab\{ [<owner>\b .\b0 ]<procedureName>[\b ,\b0  ...] \}\par
\tab  \b TO\b0\tab\{ <user> | <role> | \b PUBLIC\b0  [\b ,\b0  ...] \} \b ;\b0\par
\pard\li568\i0\par
As for tables and views, a user can issue this statement only if they satisfy one of the authorities required.\par
\pard\li284\par
\b\fs24 System Permissions\b0\par
\fs20\par
\pard\li568 System permissions allow restrictions to be placed on the control of the SQL environment schema-wide. These permissions affect authorities to create users, roles, to allow access to objects in the system and so on.\par
\par
\b DCL Permissions\par
\b0\par
\pard\li1136\b\i GRANT CREATE\b0  [\b ANY\b0 ] \b USER TO\b0  \{ <user> | <role> | \b PUBLIC\b0  [\b ,\b0  ...] \} \b ;\b0\par
\pard\li1704\i0 Allows a user to create and maintain usernames.\par
\pard\li1136\par
\b\i GRANT DROP ANY USER TO\b0  \{ <user> | <role> | \b PUBLIC\b0  [\b ,\b0  ...] \} \b ;\b0\par
\pard\li1704\i0 Allows a user to delete a username.\par
\pard\li1136\par
\b\i GRANT CREATE\b0  [\b ANY\b0 ] \b ROLE TO\b0  \{ <user> | <role> | \b PUBLIC\b0  [\b ,\b0  ...] \} \b ;\b0\par
\pard\li1704\i0 Allows the user to create a rolename.\par
\pard\li1136\par
\b\i GRANT DROP ANY ROLE TO\b0  \{ <user> | <role> | \b PUBLIC\b0  [\b ,\b0  ...] \} \b ;\b0\par
\pard\li1704\i0 Allows the user to delete a rolename.\par
\pard\li1136\par
\b\i GRANT GRANT ANY PRIVILEGE TO\b0  \{ <user> | <role> | \b PUBLIC\b0  [\b ,\b0  ...] \} \b ;\b0\par
\pard\li1704\i0 Allows the user to grant and revoke any privilege (not role) to themselves or others.\par
\pard\li1136\par
\b\i GRANT GRANT ANY ROLE TO\b0  \{ <user> | <role> | \b PUBLIC\b0  [\b ,\b0  ...] \} \b ;\b0\par
\pard\li1704\i0 Allows a user to grant a role to another user.\par
\pard\li1136\par
\b\i GRANT\b0  <role> \b TO\b0  \{ <user> | <role> | \b PUBLIC\b0  [\b ,\b0  ...] \} \b ;\b0\i0\par
\pard\li1704 Assigns a role to a user or another role. Thus a set of permissions can be granted to a given user. Note that this command implies that roles can also be nested (ie one role can contain one or more others).\par
\pard\li568\par
\b DDL Permissions\b0\par
\par
\pard\li852 The following commands affect the manipulation of data objects in the system. It should be noted that with the current release, table DDL commands have not as yet been implemented, and the permissions relevant to these operations although recognised are ineffective at present.\par
\pard\li568\par
\pard\li1136\b\i GRANT CREATE\b0  \b TABLE TO\b0  \{ <user> | <role> | \b PUBLIC\b0  [\b ,\b0  ...] \} \b ;\b0\i0\par
\pard\li1704 This permission allows a given user, role or all users to create a table in their own name. Note that table DDL commands have not yet been implemented. Also note that only database owners may create additional tables, that is you need to be logged in as the database to create a table.\par
\pard\li1136\par
\b\i GRANT \b0\{\b  CREATE | ALTER\b0  | \b DROP | SELECT | INSERT | UPDATE | DELETE \b0\} \b ANY\b0  \b TABLE\par
\tab   TO\b0  \{ <user> | <role> | \b PUBLIC\b0  [\b ,\b0  ...] \} \b ;\b0\i0\par
\pard\li1704 Allows a user to access or manipulate any table (belonging to another owner) in the schema. Note that table DDL commands are not implemented - therefore the CREATE, ALTER and DROP clauses have no use at present. This command can however be used to afford access and update capabilities (SELECT, INSERT, UPDATE and DELETE) for a given user.\par
\pard\li1136\par
\b\i GRANT \b0\{ \b CREATE | ALTER | DROP\b0  \} \b ANY INDEX TO\b0  \{ <user> | <role> | \b PUBLIC\b0  [\b ,\b0  ...] \} \b ;\b0\i0\par
\pard\li1704 This command will allow a user to manipulate an index on any table in the schema. The indexing DDL commands are not currently implemented.\par
\pard\li1136\par
\b\i GRANT CREATE\b0  \b VIEW TO\b0  \{ <user> | <role> | \b PUBLIC\b0  [\b ,\b0  ...] \} \b ;\b0\i0\par
\pard\li1704 This command allows a user to create a new view under their own name. Note that a user can drop his/her own views even if they do not have this permission.\par
\pard\li1136\par
\b\i GRANT \b0\{\b  CREATE | DROP \b0\} \b ANY\b0  \b VIEW TO\b0  \{ <user> | <role> | \b PUBLIC\b0  [\b ,\b0  ...] \} \b ;\b0\i0\par
\pard\li1704 This command allows a user to create or drop a view on behalf of any user (or database owner).\par
\pard\li1136\par
\b\i GRANT CREATE\b0  \b PROCEDURE TO\b0  \{ <user> | <role> | \b PUBLIC\b0  [\b ,\b0  ...] \} \b ;\b0\i0\par
\pard\li1704 This command allows a user to create a new procedure under their own name. Note that a user can drop his/her own procedures even if they do not have this permission.\par
\pard\li1136\par
\b\i GRANT \b0\{\b  CREATE | DROP | EXECUTE \b0\} \b ANY\b0  \b PROCEDURE TO\b0  \{ <user> | <role> | \b PUBLIC\b0  [\b ,\b0  ...] \} \b ;\b0\i0\par
\pard\li1704 This command allows a user to create or drop a procedure on behalf of any user or database owner.\par
\pard\li568\par
\par
\b Object Permissions\b0\par
\pard\li284\par
\pard\li852 These permissions affect access to a specific object (table, view or procedure) in the system.\par
\par
\pard\li1136\b\i GRANT\b0  \{ \b ALTER | INDEX\b0  \} \b ON\b0  \{ [<owner>\b .\b0 ]<table> [\b ,\b0  ...] \} \b TO\b0  \{ <user> | <role> | \b PUBLIC\b0  [\b ,\b0  ...] \} \b ;\b0\i0\par
\pard\li1704 Allows a user to manipulate indexes on a given table. Note that table DDL commands have not yet been implemented, and this command has no use at present.\par
\pard\li1136\par
\b\i GRANT\b0  \{ \b SELECT | INSERT | UPDATE | DELETE\b0  \} \b ON\b0  \{ [<owner>\b .\b0 ]<table> [\b ,\b0  ...] \} \b TO\b0  \{ <user> | <role> | \b PUBLIC\b0  [\b ,\b0  ...] \} \b ;\b0\i0\par
\pard\li1704 Grants a user access to read or manipulate a table. The granting user must own the table unless he/she has GRANT ANY PRIVILEGE permission or is the superuser.\par
\pard\li1136\par
\b\i GRANT\b0  \b SELECT ON\b0  \{ [<owner>\b .\b0 ]<view> [\b ,\b0  ...] \} \b TO\b0  \{ <user> | <role> | \b PUBLIC\b0  [\b ,\b0  ...] \} \b ;\b0\i0\par
\pard\li1704 Grants a user access to retrieve data from a view. The granting user must own the view unless he/she has GRANT ANY PRIVILEGE permission or is the superuser.\par
\pard\li1136\par
\b\i GRANT\b0  \b EXECUTE\b0  \b ON\b0  \{ [<owner>\b .\b0 ]<procedure> [\b ,\b0  ...] \} \b TO\b0  \{ <user> | <role> | \b PUBLIC\b0  [\b ,\b0  ...] \} \b ;\b0\i0\par
\pard\li1704 Grants a user permission to execute a procedure. The granting user must own the procedure unless he/she has GRANT ANY PRIVILEGE permission or is the superuser.\par
\pard\li852\par
\pard\li284\b\fs24 The Superuser\b0\par
\fs20\par
\pard\li568 The Superuser is a special user who is designated as the master DBA for the schema. There may be only one superuser in any given schema; usually this is the user named \i sa\i0 . This user does not require any permissions in order to access any user or system function. The Superuser is identified with a "1" in the ADMIN flag in the CL_USERS table. As this table cannot be inserted into or updated, there is no way to elect this superuser from within SQL. Use the \b schemaint\b0  User Maintenance utility to select the Superuser for your schema.\par
\pard\par
\b\fs24\par
\fs28 SQL Function Reference\par
\b0\fs20\par
\pard\li284\b\fs24 Standard SQL Functions\par
\b0\fs20\par
\pard\li568 The following standard SQL functions are supported:\par
\par
\pard\li994\b String Functions\b0\par
\pard{\pntext\f4\'B7\tab}{\*\pn\pnlvlblt\pnf4\pnindent0{\pntxtb\'B7}}\fi-294\li1562 ASCII(<char>)\par
{\pntext\f4\'B7\tab}CHAR(<num>)\par
{\pntext\f4\'B7\tab}CONCAT(<string1>, <string2>)\par
\cf1{\pntext\f4\'B7\tab}INSTR(<sourceString>, <matchString> [, <start>, <num>])\cf0\par
{\pntext\f4\'B7\tab}LCASE(<string>)\par
{\pntext\f4\'B7\tab}LOWER(<string>)\par
{\pntext\f4\'B7\tab}LEFT(<string>, <num>)\par
{\pntext\f4\'B7\tab}LENGTH(<string>)\par
{\pntext\f4\'B7\tab}LOCATE(<substring>, <string>, <start>)\par
{\pntext\f4\'B7\tab}LTRIM(<string>)\par
{\pntext\f4\'B7\tab}REPEAT(<string>, <count>)\par
\cf0{\pntext\f4\'B7\tab}RIGHT(<string>, <num>)\par
\cf0{\pntext\f4\'B7\tab}RTRIM(<string>)\par
\cf1{\pntext\f4\'B7\tab}SUBSTR(<sourceString> [, <start>, <num>])\cf0\par
\cf1{\pntext\f4\'B7\tab}TRANSLATE(<sourceString> [, <fromString>, <toString>])\cf0\par
{\pntext\f4\'B7\tab}UCASE(<string>)\par
{\pntext\f4\'B7\tab}UPPER(<string>)\par
\pard\li568\b\par
\pard\li994 Date and Time Functions\b0\par
\pard{\pntext\f4\'B7\tab}{\*\pn\pnlvlblt\pnf4\pnindent0{\pntxtb\'B7}}\fi-294\li1562 CURDATE()\par
{\pntext\f4\'B7\tab}CURTIME()\par
{\pntext\f4\'B7\tab}DAYNAME(<timestamp>)\par
{\pntext\f4\'B7\tab}DAYOFMONTH(<timestamp>)\par
{\pntext\f4\'B7\tab}HOUR(<timestamp>)\par
{\pntext\f4\'B7\tab}MINUTE(<timestamp>)\par
{\pntext\f4\'B7\tab}MONTH(<timestamp>)\par
{\pntext\f4\'B7\tab}MONTHNAME(<timestamp>)\par
{\pntext\f4\'B7\tab}NOW()\par
{\pntext\f4\'B7\tab}SECOND(<timestamp>)\par
{\pntext\f4\'B7\tab}YEAR(<timestamp>)\par
\pard\li568\b\par
\pard\li994 Numeric Functions\b0\par
\pard{\pntext\f4\'B7\tab}{\*\pn\pnlvlblt\pnf4\pnindent0{\pntxtb\'B7}}\fi-294\li1562 MOD(<num1>, <num2>)\par
\pard\li568\b\par
\b0\par
\pard\li284\b\fs24 CLserver Specific Functions\b0\par
\fs20\par
\pard\li568 Several additional functions are provided for use in SQL statements.\par
\par
\b USER()\b0\par
\pard\li284\par
\pard\li852 Usage example:\par
\pard\li1136\b\i SELECT DISTINCT USER() FROM \b0 <table>\b ;\b0\i0\par
\pard\li852\par
The \i User()\i0  function returns the current username accessing this connection. Note that is is the server username, not the Unix user or the name of the user logged into Windows (if access is being made via ODBC from a Windows PC).\par
\pard\par
\pard\li568\b SCHEMA()\b0\par
\pard\li284\par
\pard\li852 Usage example:\par
\pard\li1136\b\i SELECT DISTINCT SCHEMA() FROM \b0 <table>\b ;\b0\i0\par
\pard\li852\par
This function returns the name of the current schema being accessed.\par
\pard\par
\par
\b\fs28 SQL Command Reference Summary\par
\b0\fs20\par
\pard\li284 This section lists the SQL commands which are available in the CL4 ODBC Server. The function of the supported statements is beyond the scope of this guide, and any number of SQL reference manuals may be referred to for a full description of these functions.\par
\par
In general, all standard DML statements are supported as would be expected in any reasonable SQL environment. As mentioned above, the DDL commands like CREATE TABLE and CREATE INDEX are not currently supported in this release of the CL4 ODBC server.\par
\par
\b\fs24 DML Statements\par
\pard\tx3692\b0\i\fs20\par
\pard\li710\tx3692 <statement>\i0  ::=\tab\i <selectStmt>\i0\par
\pard\tx3692\tab | \i <insertStmt>\i0\par
\tab | \i <updateStmt>\i0\par
\tab | \i <deleteStmt>\i0\par
\tab | \i <procedureCallStmt>\i0\par
\pard\li284\par
\pard\li710\b SELECT\b0\par
\pard\li1136\b\fs18 SELECT\b0\fs20  \i <query>\i0  [\b\fs18 UNION\b0  \fs20 [\b\fs18 ALL\b0\fs20 ] \i <query>\i0 ] [\i <orderByClause>\i0 ];\par
where\par
\pard\li1420\i <query>\i0  ::=\tab\tab [\b\fs18 ALL\b0\fs20 |\b\fs18 DISTINCT\b0\fs20 ] \i <selectList>\i0  \b\fs18 FROM\b0  \i\fs20 <tableRefList>\par
\pard\li3550\i0 [\i <whereClause>\i0 ] [\i <groupByClause>\i0  | \b\fs18 FOR\b0  \b UPDATE\b0  \fs20 [\b\fs18 NOWAIT\b0\fs20 ]]\par
\pard\li1420\par
\i <selectList>\i0  ::=\tab\tab\b\fs24 *\b0\fs20  | \i <selectItem>\i0  [\b\fs24 ,\b0\fs20  \i <selectItem>\i0 ...]\par
\tab\i <selectItem>\i0  ::=\tab\i <tableName>\b\i0 .*\b0  | \i <valueExpression>\i0  [\b AS\b0  \i <columnName>\i0 ]\par
\pard\li1988\par
\pard\li1420\i <tableRefList>\i0  ::=\tab\i <tableRef>\i0  [\b ,\b0  \i <tableRef>\i0 ]\par
\par
\i <tableRef>\i0  ::=\tab\tab\i <tableName>\i0  | \i <outerJoin>\i0\par
\i <tableName>\i0  ::=\tab\i <baseTable>\i0  [\b AS\b0  \i <identifier>\i0 ]\par
\pard\fi-2130\li3550\tx4402\i\par
<outerJoin>\i0  ::=\tab\b (\b0\i <tableName>\i0  \i <outerJoinType>\i0  \i <tableName>\i0 |\i <outerJoin>\i0  \b ON\b0  \i <searchCondition>\b\i0 )\b0  [\i <outerJoinType>\i0  \i <tableName>\i0  \b ON\b0  \i <searchCondition>\i0 ...]\par
\pard\li1420\i <outerJoinType>\i0  ::=\tab [\b LEFT\b0 |\b RIGHT\b0 |\b FULL\b0 ] [\b OUTER\b0 ] \b JOIN\b0  | \b INNER JOIN\b0\par
\par
\i <whereClause>\i0  ::=\tab\b WHERE\b0  \i <searchCondition>\i0\par
\par
\i <groupByClause>\i0  ::=\tab\b GROUP BY \b0\i <columnName>\i0  [\b ,\b0  \i <columnName>\i0 ]\par
\pard\li284\tx2272\par
\pard\li1420\i <havingClause>\i0  ::=\tab\b HAVING\b0  \i <searchCondition>\i0\par
\par
\i <orderByClause>\i0  ::=\tab\b ORDER BY\b0  \i <sortSpec>\i0  [\b ,\b0  \i <sortSpec>\i0 ]\par
\i <sortSpec>\i0  ::=\tab\tab\i <sortKey>\i0  [\b ASC\b0  | \b DESC\b0 ]\par
\i <sortKey>\i0  ::=\tab\tab\i <columnName>\i0  | \i <unsignedInteger>\i0\par
\pard\tx3692\par
\pard\li1420\i <searchCondition>\i0  ::=\tab\i <booleanTerm>\i0\par
\pard\tx3692\tab | \i <searchCondition>\i0  \b OR\b0  \i <booleanTerm>\i0\par
\pard\li1420\i <booleanTerm>\i0  ::=\tab\i <booleanFactor>\i0\par
\pard\tx3692\tab | \i <booleanTerm>\i0  \b AND\b0  \i <booleanFactor>\i0\par
\pard\li1420\i <booleanFactor>\i0  ::=\tab [\b NOT\b0 ] \i <booleanPrimary>\i0\par
\i <booleanPrimary>\i0  ::=\tab\i <predicate>\i0\par
\pard\tx3692\tab | \b (\b0\i <searchCondition>\b\i0 )\b0\par
\pard\li1420\i <predicate>\i0  ::=\tab\tab\i <comparisonPredicate>\i0\par
\pard\tx3692\tab | \i <betweenPredicate>\i0\par
\tab | \i <inPredicate>\i0\par
\tab | \i <likePredicate>\i0\par
\tab | \i <nullPredicate>\i0\par
\pard\li1420\i <comparisonPredicate>\i0  ::=\line\pard\li1420\tx3692\tab\i <rowValueConstructor> <compareOperator> <rowValueConstructor>\i0\par
\pard\li1420\i <compareOperator>\i0  ::=\tab\b >\b0  | \b >=\b0  | \b <\b0  | \b <=\b0  | \b =\b0  | \b !=\b0  | \b <>\b0\par
\i <betweenPredicate>\i0  ::=\tab\i <rowValueConstructor>\i0  [\b NOT\b0 ] \b BETWEEN\b0\par
\pard\tx3692\tab\i <rowValueConstructor>\i0\par
\pard\li1420\i <inPredicate>\i0  ::=\tab\i <rowValueConstructor>\i0  [\b NOT\b0 ] \b IN \b0\i <inValueList>\i0\par
\pard\tx3692\tab | \i <rowValueConstructor>\i0  [\b NOT\b0 ] \b IN \b0\i <subquery>\i0\par
\pard\li1420\i <inValueList>\i0  ::=\tab\i <valueExpression>\i0  [\b ,\b0  \i <valueExpression>\i0 ]\par
\i <likePredicate>\i0  ::=\tab\i <rowValueConstructor>\i0  [\b NOT\b0 ] \b LIKE \b0\i <patternValue> <escapeClause>\i0\par
\i <nullPredicate>\i0  ::=\tab\i <rowValueConstructor>\i0  [\b NOT\b0 ] \b NULL\b0\par
\i <patternValue>\i0  ::=\tab\i <characterLiteral>\i0\par
\pard\tx3692\tab | \b ?\b0\par
\pard\li1420\i <escapeClause>\i0  ::=\tab\b ESCAPE\b0  \i <characterLiteral>\i0\par
\i <rowValueConstructor>\i0  ::= \i <valueExpression>\i0  | \b NULL\b0\par
\par
\i <subquery>\i0  ::=\tab\tab\b (\b0  \i <queryExpression>\i0  \b )\b0\par
\i <valueExpression>\i0  ::=\tab\i <numericValueExpression>\i0\par
\pard\tx3692\tab | \i <stringValueExpression>\i0\par
\tab | \i <datetimeValueExpression>\i0\par
\tab | \i <scalarFunction>\i0\par
\pard\li1420\i <numericValueExpression>\i0  ::=\line\pard\li1420\tx3692\tab\i <term>\i0\par
\pard\tx3692\tab | \i <numericValueExpression>\i0  \b\fs24 +\b0\fs20  \i <term>\i0\par
\tab | \i <numericValueExpression>\i0  \b\fs24 -\b0\fs20  \i <term>\i0\par
\pard\li1420\i <term>\i0  ::=\tab\tab\i <factor>\i0\par
\pard\tx3692\tab | \i <term>\i0  \b\fs24 *\b0\fs20  \i <factor>\i0\par
\tab | \i <term>\i0  \b\fs24 /\b0\fs20  \i <factor>\i0\par
\pard\li1420\i <factor>\i0  ::=\tab\tab\i <numericPrimary>\i0\par
\i <numericPrimary>\i0  ::=\tab\i <numericLiteral>\i0\par
\pard\tx3692\tab | \i <columnName>\i0\par
\tab | \i <setFunctionSpec>\i0\par
\tab | \b ?\b0\par
\tab | \i <subquery>\i0\par
\tab | \b (\b0  \i <valueExpression>\i0  \b )\b0\par
\pard\li1420\i <stringValueExpression>\i0  ::=\tab\i <characterLiteral>\i0\par
\i <datetimeValueExpression>\i0  ::=\tab\b\fs24 '\b0\i\fs20 <dateTimeString>\b\i0\fs24 '\b0\fs20\par
\i <scalarFunction>\i0  ::=\tab\i <scalarFunctionName>\i0  \b ( )\b0\par
\pard\tx3692\tab | \i <scalarFunctionName>\i0  \b (\b0  \i <valueExpressionList>\i0  \b )\b0\par
\pard\li1420\i <valueExpressionList>\i0  ::=\tab\i <valueExpression>\i0  [\b\fs24 ,\b0\fs20  \i <valueExpression>\i0 ]\par
\i <setFunctionSpec>\i0  ::=\tab\b COUNT\b0  \b (*)\b0\par
\pard\tx3692\tab | \b AVG\b0  \b (\b0  [\b ALL\b0  | \b DISTINCT\b0 ] \i <valueExpression>\i0  \b )\b0\par
\tab | \b MIN\b0  \b (\b0  [\b ALL\b0  | \b DISTINCT\b0 ] \i <valueExpression>\i0  \b )\b0\par
\tab | \b MAX\b0  \b (\b0  [\b ALL\b0  | \b DISTINCT\b0 ] \i <valueExpression>\i0  \b )\b0\par
\tab | \b SUM\b0  \b (\b0  [\b ALL\b0  | \b DISTINCT\b0 ] \i <valueExpression>\i0  \b )\b0\par
\tab | \b COUNT\b0  \b (\b0  [\b ALL\b0  | \b DISTINCT\b0 ] \i <valueExpression>\i0  \b )\b0\par
\pard\li1420\par
\i <columnElementList>\i0  ::=\tab\i <columnElement>\i0  [\b\fs24 ,\b0\fs20  \i <columnElement>\i0 ]\par
\i <columnElement>\i0  ::=\tab\tab\i <columnName>\i0  \i <dataType>\i0  [\i <columnConstraintList>\i0 ]\par
\i <dataType>\i0  ::=\tab\tab\b INTEGER\b0  | \b SMALLINT\b0\par
\pard\tx3692\tab | \b DECIMAL\b0  \b (\b0\i <integerLiteral>\i0  [\b\fs24 ,\b0\fs20  \i <integerLiteral>\i0 ]\b )\b0\par
\tab | \b NUMERIC\b0  \b (\b0 <integerLiteral> [\b\fs24 , \b0\i\fs20 <integerLiteral>\i0 ]\b )\b0\par
\tab | \b FLOAT\b0  | \b REAL\b0  | \b DOUBLE\b0\par
\tab | \b CHAR (\b0  \i <integerLiteral>\i0  \b )\b0\par
\tab | \b VARCHAR\b0  \b (\b0  \i <integerLiteral>\i0  \b )\b0\par
\tab | \b TIMESTAMP\b0  | \b LONGVARCHAR\b0\par
\tab | \b BINARY\b0  | \b VARBINARY\b0  | \b LONGVARBINARY\b0\par
\pard\li1420\i <columnConstraintList>\i0  ::=\tab\i <columnConstraint>\i0  [\b\fs24 ,\b0\fs20  \i <columnConstraint>\i0 ]\par
\i <columnConstraint>\i0  ::=\tab\b NOT NULL\b0\par
\pard\tx3692\tab | \b UNIQUE KEY\b0\par
\tab | \b PRIMARY KEY\b0\par
\tab | \b REFERENCES\b0  \i <tableName> <columnName>\i0\par
\tab | \b USERDATA\b0  \i <characterLiteral>\i0\par
\pard\li284\tx2272\par
\pard\li710\b INSERT\b0\par
\pard\li1136\b\fs18 INSERT INTO\b0\fs20  \i <tableName>\i0  [\i <columnList>\i0 ] \i <queryOrValues>\b\i0 ;\b0\par
where\par
\pard\li1420   \i <columnList>\i0  ::=\tab\i <columnName>\i0  [\b\fs24 ,\b0\fs20  \i <columnName>\i0 ]\par
  <\i queryOrValues\i0 > ::=\tab <\i querySpecification\i0 >\i\par
\pard\tx3692\i0\tab | \b VALUES\b0  \b (\b0\i <insertValueRow>\b\i0 )\b0\par
\tab | \b VALUES\b0  \b (\b0\i <insertValueRowList>\b\i0 )\b0\par
\pard\li1420\i <insertValueRowList>\i0  ::= \i <insertValueRow>\i0  [\b\fs24 ,\fs20  \b0\i <insertValueRow>\i0 ]\i\par
<insertValueRow>\i0  ::=\tab\i <insertValue>\i0  [\b\fs24 ,\b0\fs20  \i <insertValue>\i0 ]\i\par
<insertValue>\i0  ::=\tab\i <literal>\i0  | \b NULL\b0  | \b ?\b0  | \i <scalarFunction>\par
\pard\tx3692\i0\par
\pard\li710\b UPDATE\b0\par
\pard\li1136\b\fs18 UPDATE\b0\fs20  \i <tableName>\i0  \b SET\b0  \i <setValueList>\i0  [\i <whereClause>\i0 ]\b ;\b0\par
where\par
\pard\li1420\i <setValueList>\i0  ::=\tab\i <setValueItem>\i0  [\b\fs24 ,\b0\fs20  \i <setValueItem>\i0 ]\par
\i <setValueItem>\i0  ::=\tab\i <columnName>\i0  \b = NULL\par
\pard\tx3692\b0\tab | \i <columnName>\i0  \b =\b0  \i <valueExpression>\i0\par
\pard\li710\par
\b DELETE\b0\par
\pard\li1136\b\fs18 DELETE FROM\b0\fs20  \i <tableName>\i0  [\i <whereClause>\i0 ]\b ;\b0\par
\pard\li284\par
\b\fs24 DML Statements\par
\b0\fs20 There are currently only a few of these type of statements:\par
\par
\b Views\par
\pard\li852\fs18 CREATE VIEW\b0\fs20  \i <viewName>\i0  \b\fs18 AS\b0\fs20  \i <selectStmt>\b\i0 ;\par
\pard\li284\par
Procedures\b0\par
\pard\li852\b\fs18 CALL \b0\i\fs20 <procName>\i0  [ \b (\b0 [\i <parameter>\i0  [\b ,\b0  \i <parameter>\i0 ]\b )\b0  ]\b ;\b0\par
\pard\li1136 where\par
\pard\li1420\i <parameter>\i0  ::=\tab\i <literal>\i0  | \b ?\b0  | \b NULL\b0\par
\pard\fi-1704\li2556\b\fs18 CALL CreateProc\fs20 ('\b0\i <procName>\b\i0 (\b0 [\i <paramName> <datatype>\i0 [\b (\b0\i <size>\b\i0 ,\b0\i <precision>\b\i0 )\b0 ] [\b , \b0\i <param> <type>\i0 ...]\b ) \b0 [\b\fs18 RESULT SET \fs20 (\b0\i <paramName> <datatype>\b\i0  \b0 [\b ,\b0  \i <param> <type>\i0 ...]\b )\b0 ]\b ',\i\line\pard\li852\tab\tab       \b0 <remarks>\b\i0 );\par
\fs18 CALL DropProc\fs20 ('\b0\i <procName>\i0 [\b ()\b0 ]\b ');\b0\par
\b\fs18 CALL Trace\fs20 ('\b0 [\i <function>|<hexVal>\i0 ]\b ');\b0\par
\b\fs18 CALL Describe\fs20 ('\b0\i <tableName>\b\i0 ');\b0\par
\b\fs18 CALL DBConnect\fs20 ('\b0\i <DbName>\b\i0 ', '\b0\i <DbPath>\b\i0 ', \b0\i <Seq>\b\i0 , '\b0\i <Remarks>\b\i0 ');\b0\par
\b\fs18 CALL DBDisconnect\fs20 ('\b0\i <DbName>\b\i0 ');\b0\fs24\par
\pard\li284\fs20\par
\b\fs24 DCL Statements\par
\b0\fs20 These are fully described above.\par
\par
\pard\li710\b User\par
\pard\li1136\fs18 CREATE USER \b0\i\fs20 <userName>\i0  [\b\fs18 IDENTIFIED BY \b0\i\fs20 <password>\i0  | \b\fs18 IDENTIFIED EXTERNALLY\b0\fs20 ]]\line\tab [\b\fs18 USERDATA \fs20 '\b0\i <description>\b\i0 '\b0 ] \b ;\i\par
\i0\fs18 DROP USER \b0\i\fs20 <userName>\b\i0 ;\b0\par
\b\fs18 CREATE ROLE \b0\i\fs20 <roleName>\b\i0 ;\b0\par
\b\fs18 DROP ROLE \b0\i\fs20 <roleName>\b\i0 ;\b0\par
\par
\pard\li710\b Permissioning\b0\par
\pard\li1136\par
\pard\fi-1704\li2840\tx2840\b\fs18 GRANT|REVOKE\b0\fs20\tab\b\fs18 SELECT\b0  \fs20 | \b\fs18 INSERT\b0  \fs20 | \b\fs18 UPDATE\b0  \fs20 | \b\fs18 DELETE\b0  \fs20 | \b\fs18 ALL\b0  \fs20 [\b\fs18 PRIVILEGES\b0\fs20 ] [\b ,\b0  ...]\i\line\pard\li1136\tx2272\tx2840\b\fs18\tab\i0 ON\b0\fs20\tab [\i <owner>\b\i0 .\b0 ]\i <tablename>\i0  [\b ,\b0  ...]\line\b\fs18\tab TO\b0\fs20\tab\i <user>\i0  | \i <role>\i0  | \b\fs18 PUBLIC\b0  \fs20 [\b ,\b0  ...]\b ;\b0\par
\pard\li1136\tx2840\b\i\fs18 GRANT|REVOKE\tab\i0 SELECT\fs20 |\fs18 INSERT\fs20 |\fs18 UPDATE\fs20 |\fs18 DELETE\b0\fs20  \b\fs18 ANY\b0  \b TABLE\fs20\par
\pard\li1136\tx2272\tx2840\tab\fs18 TO\b0\fs20\tab\i <user>\i0  | \i <role>\i0  | \b\fs18 PUBLIC\b0  \fs20 [\b ,\b0  ...]\b ;\b0\par
\pard\li1136\b\i\par
\i0\fs18 GRANT\fs20 |\fs18 REVOKE CREATE\b0  \b VIEW TO\b0  \i\fs20 <user> | <role>\i0  | \b\fs18 PUBLIC\b0  \fs20 [\b ,\b0  ...]\b ;\b0\par
\b\fs18 GRANT|REVOKE \b0\fs20\{\b  \fs18 CREATE \fs20 | \fs18 DROP \b0\fs20\} \b\fs18 ANY\b0  \b VIEW TO\b0  \i\fs20 <user> | <role>\i0  | \b\fs18 PUBLIC\b0  \fs20 [\b ,\b0  ...]\b ;\b0\par
\b\fs18 GRANT|REVOKE\b0  \b SELECT ON\b0  \fs20 [\i <owner>\b\i0 .\b0 ]\i <view>\i0  [\b ,\b0  ...] \b\fs18 TO\b0  \i\fs20 <user> | <role>\i0  | \b\fs18 PUBLIC\b0  \fs20 [\b ,\b0  ...]\b ;\b0\par
\b\par
\fs18 GRANT|REVOKE CREATE\b0  \fs20 [\b\fs18 ANY\b0\fs20 ] \b\fs18 USER TO\b0  \i\fs20 <user> | <role>\i0  | \b\fs18 PUBLIC\b0  \fs20 [\b ,\b0  ...]\b ;\b0\i\par
\b\i0\fs18 GRANT|REVOKE DROP ANY USER TO\b0  \i\fs20 <user> | <role>\i0  | \b\fs18 PUBLIC\b0  \fs20 [\b ,\b0  ...]\b ;\b0\i\par
\b\fs18\par
\i0 GRANT|REVOKE\b0  \i\fs20 <roleName>\i0  \b\fs18 TO\b0  \i\fs20 <userName> | <roleName>\i0  | \b\fs18 PUBLIC\b0\fs20  [\b ,\b0  ...]\b ;\b0\par
\b\fs18 GRANT\fs20 |\fs18 REVOKE CREATE\b0  \fs20 [\b\fs18 ANY\b0\fs20 ] \b\fs18 ROLE TO\b0  \i\fs20 <user> | <role>\i0  | \b\fs18 PUBLIC\b0  \fs20 [\b ,\b0  ...]\b ;\b0\i\par
\b\i0\fs18 GRANT\fs20 |\fs18 REVOKE GRANT ANY ROLE TO\b0  \fs20 <\i user> | <role>\i0  | \b\fs18 PUBLIC\b0  \fs20 [\b ,\b0  ...]\b ;\b0\par
\b\fs18 GRANT\fs20 |\fs18 REVOKE DROP ANY ROLE TO\b0  \i\fs20 <user> | <role>\i0  | \b\fs18 PUBLIC\b0  \fs20 [\b ,\b0  ...]\b ;\b0\i\par
\b\par
\i0\fs18 GRANT\fs20 |\fs18 REVOKE CREATE\b0  \b PROCEDURE TO\b0  \i\fs20 <user> | <role>\i0  | \b\fs18 PUBLIC\b0  \fs20 [\b ,\b0  ...]\b ;\b0\par
\b\fs18 GRANT\fs20 |\fs18 REVOKE CREATE\fs20 |\fs18 DROP\fs20 |\fs18 EXECUTE\b0\fs20  \b\fs18 ANY\b0  \b PROCEDURE TO\b0  \i\fs20 <user> | <role>\i0  | \b\fs18 PUBLIC\b0  \fs20 [\b ,\b0  ...]\b ;\b0\par
\b\fs18 GRANT\fs20 |\fs18 REVOKE\b0  \b EXECUTE\b0  \b ON\b0  \fs20 [\i <owner>\b\i0 .\b0 ]\i <procedure>\i0  [\b ,\b0  ...] \b\fs18 TO\b0  \i\fs20 <user> | <role>\i0  | \b\fs18 PUBLIC\b0  \fs20 [\b ,\b0  ...]\b ;\par
\i\par
\i0\fs18 GRANT\fs20 |\fs18 REVOKE GRANT ANY PRIVILEGE TO\b0  \i\fs20 <user> | <role>\i0  | \b\fs18 PUBLIC\b0  \fs20 [\b ,\b0  ...]\b ;\b0\par
\pard\li284\par
\pard\b\fs24\par
\fs28 SQL Reserved Words\par
\b0\fs20\par
\pard\li284 The following lists the reserved words used by SQL. Care should be taken that table, column and view names do not use any of these words.\par
\par
\pard\li1136\tx3408\tx5680\tx7952 CALL\tab GRANT\tab SELECT\tab VIEW\par
CREATE\tab INSERT\tab TABLE\par
DELETE\tab REVOKE\tab UPDATE\par
DROP\tab ROLE\tab USER\par
\pard\li1136\tx3408\tx5680\par
\pard\li1136\tx3408\tx5680\tx7952 ALL\tab FOR\tab MAX\tab RESTRICT\par
AND\tab FROM\tab MIN\tab RIGHT\par
AS\tab FULL\tab NOT\tab SET\par
ASC\tab GROUP\tab NOWAIT\tab SUM\par
AVG\tab IN\tab NULL\tab UNION\par
BETWEEN\tab INDEX\tab ON\tab UNIQUE\par
BY\tab INTO\tab OR\tab UPDATE\par
CASCADE\tab JOIN\tab ORDER\tab USERDATA\par
COUNT\tab KEY\tab OUTER\tab VALUES\par
DESC\tab LEFT\tab PRIMARY\tab WHERE\par
DISTINCT\tab LIKE\tab REFERENCES\par
\par
BINARY\tab FLOAT\tab NUMERIC\tab TIMESTAMP\par
CHAR\tab INTEGER\tab REAL\tab VARBINARY\par
DECIMAL\tab LONGVARBINARY\tab SMALLINT\tab VARCHAR\par
DOUBLE\tab LONGVARCHAR\par
\par
ASCII\tab HOUR\tab MINUTE\tab SECOND\par
CHAR\tab\cf1 INSTR\cf0\tab MOD\tab\cf1 SUBSTR\cf0\par
CONCAT\tab LCASE\tab MONTH\tab\cf1 TRANSLATE\cf0\par
CURDATE\tab LEFT\tab MONTHNAME\tab UCASE\par
CURTIME\tab LENGTH\tab NOW\tab UPPER\par
DATABASE\tab LOCATE\tab REPEAT\tab USER\par
DAYNAME\tab LOWER\tab RTRIM\tab YEAR\par
DAYOFMONTH\tab LTRIM\tab SCHEMA\par
\pard\li284\par
The following reserved words are used in the \i oaisql\i0  and \i odbcisql\i0  programs as control verbs and should also be avoided as identifier names:\par
\par
\pard\li1136\tx3408\tx5680\tx7952 ATTACH\tab END\tab HELP\tab START\par
CMDFILE\tab ERRFILE\tab QUIT\tab STOP\par
CONNECT\tab EXEC\tab REPEAT\tab TIMER\par
DISCONNECT\tab EXIT\tab RESFILE\tab WAIT\par
ECHO\tab FETCH\tab SHOW\par
\pard\li284\par
It is possible (although not recommended) that under certain circumstances some database fields could retain their original names where they conflict with the above list. This may however cause unexpected errors, and in particular may cause the server to behave differently whether the data is being accessed from one of the command line utilites (see below) or from a remote ODBC data source from a Windows platform. In extreme cases there may be conflicts with reserved words under specific utilities like Visual Basic or Microsoft Access.\par
\par
\pard\b\fs28 OAISQL and ODBCISQL Program Quick Reference\par
\pard\li284\b0\fs20\par
The \i oaisql\i0  and \i odbcisql\i0  programs are interactive SQL client programs (otherwise known as \i Interactive SQL\i0 ). They are text line oriented utilities which can be used to connect to and access schemas from within the Unix or Windows environment. In order to use these utilities under Unix, a user must first have the OpenAccess environment properly set up from within their \i .profile\i0 :\par
\par
\pard\li1136\f2 $ . ~oaccess/config/setenv.sh\par
\pard\li284\f0\par
The \i oaccess\i0  user should already have this command in the \i .profile\i0  file, set up manually after the installation procedure.\par
\par
\i Oaisql\i0  and \i odbcisql\i0  are equivalent programs. The former connects directly to the currently running server, whereas the latter connects through the ODBC interface. In essence however, their operation is identical.\par
\par
They both support the following command line interface:\par
\par
\pard\li1136\b $ oaisql [-i \b0\i configfile\b\i0 ] [-c \b0\i cmdfile\b\i0 ] [-v] [-e] [-h] [-b \b0\i count\b\i0 ] [-q]\par
\par
\pard\li284\b0 Command line options are as follows:\par
\par
\pard\li1136\tx2414\tx7952\b Option\tab Description\tab Example\par
\b0 -i \i configfile\i0\tab Configuration file to be used. OAISQL will use this file\tab -i openrda.ini\line\tab instead of using the file pointed to by the OPENRDA_INI\line\tab environment variable.\par
\par
-c \i cmdfile\i0\tab Execute the commands in the cmdfile. The cmdfile can\tab -c test.sql\line\tab contain multiple commands.\par
\par
-v\tab Print version information.\tab -v\par
\par
-e\tab Echo the command being executed to the standard out.\tab -e\line\tab This option can be used when OAISQL is set up to run\line\tab automated test scripts so that the output file contains\line\tab the command being executed.\par
\par
-h\tab Display command line usage.\tab -h\par
\par
-b \i count\i0\tab Benchmark mode in which printing of results from\tab -b 1000\line\tab queries is turned off. Only the number of rows fetched is\line\tab printed after every \i count \i0 records are read. Use this to\line\tab time the execution of large queries without having to\line\tab wait for the results to be printed.\par
\par
-q\tab Enables quiet mode in which all output such as the start\tab -q\line\tab up message and time to complete the operation are not\line\tab sent to the standard output.\par
\pard\li284\par
\b\i\fs24 Oaisql/Odbcisql\i0  Command Reference\b0\par
\pard\li710\fs20\par
\f3 This section details the commands available to the ISQL user. Note that commands to \i oaisql\i0  and \i odbcisql\i0  are \b not\b0  terminated by a semicolon (";"). All SQL commands must however be terminated with a semicolon. SQL commands may therefore occupy more than one line of input before being terminated with the semicolon.\par
\f0\par
\b\f3 CMDFILE file_name\par
\pard\li1136\b0 Reads the commands from the given command file.\par
\pard\li710\par
\b CONNECT userID[*password]@schema\par
\pard\li1136\b0 Connects to the given data source. The \i userID\i0  is always required. The \i *password\i0  text is required if the user has an associated password. The program supports only one connection at any instance. If you are connected to another server or schema, that connection will be closed before establishing the new connection.\par
\pard\li710\par
\b DISCONNECT\par
\pard\li1136\b0 Disconnects from the currently connected data source. No further server SQL commands can be issued until another CONNECT has been given.\par
\pard\li710\b\par
ECHO information\par
\pard\li1136\b0 Echoes the given \i information\i0 .\par
\pard\li710\par
\b ERRFILE file_name\par
\pard\li1136\b0 Errors are written to the \i file_name \i0 file.\par
\pard\li710\par
\b # [comment]\par
\pard\li1136\b0 Ignores the text following the hash sign. This is useful when \i oaisql\i0  is set up to run automated test scripts that can be documented with comment lines. Note that if the \b -e\b0  option is enabled, the comment will be echoed to the standard out.\par
\pard\li710\par
\b EXEC sql_stmt;\par
\pard\li1136 or\par
\pard\li710 !sql_stmt;\par
\pard\li1136\b0 Executes the given SQL statement. The SQL statement can be entered on multiple lines and needs to be terminated by a semicolon.\par
\pard\li710\par
\b sql_smt;\par
\pard\li1136\b0 Executes only the supported set of SQL statements. This command supports the following DML statements (SELECT, INSERT, UPDATE, DELETE, CALL) . The SQL statement can be entered on multiple lines and needs to be terminated by a semicolon.\par
\pard\li710\par
\b EXIT\par
\pard\li1136 or\par
\pard\li710 QUIT\par
\pard\li1136\b0 Quits the interactive SQL session after closing any active connection.\par
\pard\li710\par
\b HELP\par
\pard\li1136\b0 Provides on-line help of the supported commands.\par
\pard\li710\par
\b REPEAT number\par
command 1\par
\'85\par
command n\par
END\par
\pard\li1136\b0 Repeats the given commands specified number of times. Any supported command can be repeated, except another \i repeat \i0 command.\par
\pard\li710\par
\b TIMER START\par
\pard\li1136\b0 Starts the timer. There is only one timer in the program, so any previous timer will be reset.\par
\pard\li710\par
\b TIMER STOP\par
\pard\li1136\b0 Stops the recently started timer and reports the number of seconds elapsed.\par
\pard\li710\par
\b RESFILE \{file_name\}\par
\pard\li1136\b0 Logs the results to the given file. If the filename is not specified, it logs the results to standard output.\par
\pard\li710\par
\b WAIT num_of_secs\par
\pard\li1136\b0 Waits for the given number of seconds.\f0\par
\pard\li284\par
}