Tivoli Service Desk 6.0 Developer's Toolkit Script Programming Guide
Developer's Toolkit was designed specifically to simplify the process of building
client/server database applications. For that reason, the TSD Script language contains a
set of statements devoted to interaction with SQL databases.
The TSD Script SQL interface is an intrinsic part of the language. The SQL interface
leverages the flexibility of the TSD Script RECORD and LIST types. In addition, the TSD
Script SQL statements offer built-in passive concurrency for the development of reliable
multi-user applications.
Another feature that is particularly useful is the ability to use arrayed fetches with
Oracle. Arrayed fetching significantly improves the time required to query a database.
SQL, or Structured Query Language, is an industry-standard language used to:
Because this book does not intend to be a SQL tutorial, it is helpful to be familiar with SQL syntax before you read this chapter.
The following are databases (also known as sources) supported by Developer's Toolkit:
Developer's Toolkit and the TSD Script language support various SQL statements and SQL databases used to develop client/server applications.
Some SQL statements are not supported by Oracle, Sybase/SQLServer, and Informix direct drivers. The statements are listed in the table that follows.
This statement... | Is supported only in... |
SQLDeleteCurrent | DB2/2 |
SQLGetCursorName | DB2/2 |
SQLUpdateCurrent | DB2/2 |
Note: SQLUpdateCurrent and SQLDeleteCurrent require the use of nested statements and should be used as infrequently as possible. The statements are generally considered to be obsolete, and are not widely supported.
This section describes the attributes that are available when you configure a data source. Attributes control the behavior of a database layer in Developer's Toolkit.
Note: These attributes are not necessary when you use the SQL Configuration Editor to configure your data source. Please see the section Connecting Your Application to a Database that appears later in this chapter.
A database source is a collection of information identified by a specific name. It is used to define the database and the database connection.
Note: The choice for the source name is yours. TSD Script does not require, nor does it recognize, any predefined names. TSD Script does require that data source names not begin with a number. However, applications written with TSD Script may expect certain source names. For example, Tivoli Problem Management expects "ADVISOR" as the data source name. Check the application's documentation for any such requirements.
When a data source is configured, the attributes, or characteristics, for that data source are listed by using the ATTRIB=VALUE syntax. The possible attributes are:
The following attributes are not normally used:
TSD Script automatically uses the defaults for each DBMS it supports. These attributes are used only to override defaults in special cases such as an instance of case sensitivity.
In most cases, you do not need to override the default. Many of these attributes affect advanced features in a database and should only be changed by your database administrator.
If you use a database with case-sensitive object names, you should code the queries with the proper case. If you create all the objects of your database in the same case, (either all uppercase or all lowercase) TSD Script can convert all the object names to that case for you. The possible values for this attribute entry are: UPPER, LOWER, or NONE. The default value, if this entry is not present, is NONE.
Note: TSD Script always preserves the case of quoted string literals within a query.
This entry refers to the way your DBMS treats case relative to database objects. Database objects are table names, column names, and so on. If the value is TRUE, then TSD Script preserves the case of all object names (unless the CASE_CONVERSION entry is set). If the CASE_SENSITIVE entry is not present, the default value is FALSE.
The following entries can be used to override the column names expected by TSD Script in the default column catalog view.
Column | Default |
COL_NAME_COLUMN | NAME |
COL_REMARKS_COLUMN | REMARKS |
COL_LENGTH_COLUMN | LENGTH |
COL_SCALE_COLUMN | SCALE |
COL_TYPE_COLUMN | COLTYPE |
COL_NULLS_COLUMN | NULLS |
COL_TBNAME_COLUMN | TBNAME |
COL_TBCREATOR_COLUMN | TBCREATOR |
Note: TSD Script automatically uses the defaults for each DBMS it supports. Therefore, this entry is not used under normal circumstances. This entry would be used only to override the default in special cases. This entry contains the column dictionary/catalog table or view name. Because Tivoli products provide a "wrapper" view for consistency, the default value is always SAI_SYSCOLUMNS.
This entry is available only if MANUAL_COMMITS is TRUE. If applicable, the default
COMMIT_SELECTS setting is TRUE.
Set the COMMIT_SELECTS entry to TRUE if you want automatic committing after selects.
DATE_FORMAT is the expected date format for the data source (DBMS). For most systems, the default date format is: mm/dd/yyyy. The default date format for Oracle is dd-MON-yy.
You customize a date format by specifying the format of the day, month, year, and separator. While most combinations of these items are supported, there are a few exceptions that are not supported. For example, yyyymmdd is supported, but yyyyddmm is not.
You can trim leading zeros from both days and months. To trim leading zeros on days, specify only one "d" in the date.
Note: If you trim leading zeros from the days, you must also trim them from the months. For example, mm/d/yyyy is not allowed.
Example | Result |
mm/dd/yyyy | 07/08/1999 |
m/d/yyyy | 7/8/1999 |
You may specify the month by either the number of the month in the calendar year
(1=January) or by the name of the month. To specify the month by numbers, use
"MM." To trim leading zeros in month numbers, use "M." To specify the
month by name, or with an abbreviation, use "Month" or "Mon."
Month name specifiers (both Month and Mon) are case-sensitive. Month number specifiers (MM
and M) are not case-sensitive.
Note: If you trim leading zeros from the months, you must also trim them from the days.
Example | Result |
MM | 08 |
M | 8 |
Month | August |
Mon | Aug |
MONTH | AUGUST |
MON | AUG |
When specifying the year, you can specify either the full year or the last two digits of the year.
Caution: Tivoli advises that, for year 2000 dates, you use the full four-digit numbers.
Example | Result |
mm/dd/yyyy | 07/08/1999 |
mm/dd/yy | 07/08/99 |
You can use the following separators to format dates:
Separator Type | Example | Result |
none | yyyymmdd | 19990626 |
periods | mm.dd.yyyy | 06.26.1999 |
dashes | dd-mm-yyyy | 26-06-1999 |
forward slashes | mm/dd/yyyy | 06/26/1999 |
For ODBC drivers, a DBMS listing is required in the SQL configuration file. The possible databases that can be listed as DBMS= entries are as follows:
If the value is TRUE, this data source is considered the default data source when connecting. Only one DEFAULT entry is allowed per SQL configuration file. The default value if this entry is not present is FALSE.
This is the DBMS driver name, and is required when connecting.
This attribute lists the data source name for the SQL configuration file (ODBC only).
This entry is available for direct interfaces; it should be set to TRUE if you want transaction control to be handled at the TSD Developer's Toolkit level.
This entry is set to TRUE if the DBMS supports multiple, simultaneous connections.
Set this entry to TRUE if the DBMS requires multiple connections in order to execute more than one open statement at a time (for example, nested select/fetch loops). The default value for Sybase and SQLServer is TRUE. It is FALSE for all other databases.
Note: When needed, TSD Script opens the additional connections for you.
This is the qualifier used for table access. SQL stores the creator/owner of the table in the table definition. This creator/owner name is referred to as the table's qualifier. A combination of the qualifier and table name must be used to uniquely identify a table within a database.
This entry contains the name of the qualifier needed to access the system catalog tables/views. Since all Tivoli products provide a "wrapper" view for consistency, the default value is the same as the current qualifier (in the default connect string).
This entry contains the table dictionary/catalog table or view name. Since all Tivoli products provide a "wrapper" view for consistency, the default value is always SAI_SYSCOLUMNS.
The following five entries can be used to override the column names expected by TSD Script in the default table catalog view:
TAB_NAME_COLUMN | NAME |
TAB_REMARKS_COLUMN | REMARKS |
TAB_TYPE_COLUMN | TYPE |
TAB_CREATOR_COLUMN | CREATOR |
TAB_COLCOUNT_COLUMN | COLCOUNT (for DB2/2 and SQLBase) |
This entry is the expected time format for the data source. The default for all DBMS entries is hh:mm:ss. While most time format specifiers are valid, some are not. For example, hh:mm:ss is supported, but mm:hh:ss is not.
See the section in the TSD 6.0 Script Language Reference on the TimeFormat statement for more information on valid time formats.
By default, the time is specified in military format (24-hour clock). To specify time in a 12-hour clock, include the AM_PM suffix:
Example | Result |
hh:mmAM_PM | 09:08 PM |
hh:mm | 21:08 |
The format specifier is case insensitive. For example hh:mm:ss is equivalent to HH:MM:SS
To trim leading zeros, specify only one h, m, and s. For example:
Example | Result |
hh:mm:ss | 08:35:09 |
h:m:s | 8:35:9 |
Note: If you choose to trim leading zeros, you must trim them from the hours, minutes, and seconds.
To specify seconds, include the ss designation. You can optionally omit seconds.
Example | Result |
hh:mm:ss | 08:35:09 |
h:m | 8:35 |
You may specify either colons or periods to format the time specifier:
Separator Type | Example | Result |
none | hhmmss | 121604 |
periods | hh.mm.ss | 12.16.04 |
colons | hh:mm:ss | 12:16:04 |
This entry contains the DBMS-specific function name used to convert columns in a query to uppercase (used by SQLCreateSearchString). The default values are:
Attaching or connecting to a database is common to all SQL DBMSs. The means for connection vary relative to the database used. For example:
To mask these differences, TSD Script provides a set of connection services that use the SQLCommand statement. SQLCommand allows for external commands to be used for the DBMS-specific connection information in a SQL configuration file. This simplifies your application code and makes it portable.
The default name of the Developer's Toolkit SQL configuration file is sai_sql.cfg. Developer's Toolkit looks for a file called sai_sql.cfg in the CFG subdirectory of the SAI root directory.
You may override the file name that TSD Script looks for by setting an environment variable called SAISQLCFG to contain a different file name. The value in the SAISQLCFG environment variable takes precedence over the path derived from SAI_ROOT if both are set. An error is returned if the file is not found.
The SQL configuration file is divided into sections that describe a data source. Consider a data source as the DBMS with which you are working. Normally, data sources correspond to physical database servers, although the idea can be extended to include logical sources within a server (a source per database, for example).
Developer's Toolkit provides a number of direct database interfaces that you can use, based on your database requirements. These direct database interfaces are the default method of database connectivity for Developer's Toolkit.
If you want to use ODBC, rather than the direct databases interfaces, you can find further information about ODBC drivers in Appendix A "ODBC Configurations" in the Tivoli Service Desk 6.0 Installation Guide.
If you use the default Developer's Toolkit direct database interfaces, please see "Configuration Process" in the Tivoli Service Desk 6.0 Installation Guide for complete configuration and testing instructions.
To add a database client for its initial use, follow these steps:
The following components are found in the SQL Configuration Editor dialog box:
- File - Text box for the name of the configuration file used to set up a database client connection. This field normally contains the file name that you use.
- Sources - List box that contains the names of data sources.
- Options - List box that contains the options for the selected data source.
- Configure - Button used to configure the options for the selected data source.
- Add - Button used to create a new data source entry.
- Delete - Button used to delete a selected data source entry.
- Copy - Button used to copy the contents of the selected data source. When you copy a data source, it contains the same data as the original, but has a new name.
- Rename - Button used to rename the selected data source. This function renames an existing data source without copying it.
- Make Default - Button used to make the selected data source the default data source.
- Test Connect - Button used to test the configured database connection that returns either a success or error message.
- Trace Setup - Button used to trace database activity to provide report information for debugging.
- Password Security -
- Qualifier - Because SQL allows multiple tables of the same name within a database, SQL stores the creator/owner of the table in the table definition. This creator/owner name is referred to as the table's qualifier. For example, EXAV can be used as the qualifier in the Source Setup dialog box.
- Server - The name of the server to which client requests are sent.
- Database - The name of the database you use at your site.
- Driver - The name of the driver you use to connect to the database.
- ODBC DSN - If you chose the ODBC driver option in the Driver Selection dialog box, you need to enter the ODBC Data Source Name here. If you select the ODBC option, please refer to Appendix A: ODBC Configuration in the Tivoli Service Desk 6.0 Installation Guide for complete instructions.
- ID - The user ID used to access the database. (optional)
- PWD - The password used to access the database. (optional)
- Default check box - Check this box if the entries you make will be used as the default configuration for your database connection.
Result: If you chose OK, you are finished with the procedure. If you chose Advanced from the Source Setup dialog box, the Advanced Option Setup dialog box appears.
After you add a new database connection, you should test the connection to ensure that it works properly. Complete these steps to test a database connection:
There is a potential security problem if you maintain user IDs and passwords in the SQL
configuration file. There may also be difficulty if you maintain connection-specific
information for more than one user since the file is readily accessible by all users and
stored passwords are not encrypted by default.
The user must select encryption using the SQL Configuration Editor. See Database configuration example in this guide
for more information.
TSD Script provides a simple method of providing connection-specific information in the connect statement itself:
ret := SQLCommand('CONNECT SOURCE=DB2_TEST; UID=FRED; PWD=DERF;');
When TSD Script encounters this type of connect request, the following occurs:
The default connect string for DB2_TEST is located (DB2_TEST must be a source in the SQL configuration file). If a SOURCE= entry is not found, then TSD Script uses the default.
Note: When there are other attributes present, you need to put the SOURCE= before the data source name.
A new connect string is formed by overlaying the remaining ATTRIBUTE=VALUE pairs onto the corresponding entries in the default connect string, and by inserting the new pairs that are not in the default connect string.
You can override any entry of the default connect string except the driver (DRV=). This allows you to have default connection information (database, table qualifier, and so on) that may be overridden as needed. You may also get some of this effect by creating new data sources (a source per database, for example).
It is possible to provide all connection information to SQLCommand and eliminate the
SQL configuration file. However, you must be willing to accept all of the default source
values (such as MULTI_CONNECT, and so on).
Externalizing the DBMS-specific information is normally a good thing to do. However, there
may be times when you need to eliminate the SQL configuration file. To handle these cases,
TSD Script allows you to connect to and register a new source not found in a SQL
configuration file:
ret := SQLCommand('CONNECT NEWSOURCE=MYAPP; DRV=XOORA;SRVR=X:ORASERV; UID=MARY; PWD=');
The remainder of this chapter refers to an example database called COMPANY. This database consists of the following tables and views. Column attributes are listed under each table or view name.
Table DEPARTMENT
Table EMPLOYEE
Table MANAGER
The SQLSelectInto statement provides an easy way to get information from a SQL table or view. It takes a variable number of arguments.
The first argument is always a string expression that represents a SQLSelect statement such as:
SELECT * FROM EMPLOYEE WHERE EMPLOYEE_ID='305-83-3811'.
In most cases, SQLSelectInto is used to retrieve a unique row from a table. The select expression passed as the first argument generally contains a clause in the format:
WHERE <primary-key>=<value>
In general, the format of a select statement is:
SELECT <column-list> FROM <table-or-view name> WHERE <condition>.
<column-list> is a list of one or more column names separated by commas (such as "FIRST_NAME, LAST_NAME"). If you want to get all of the columns from a table, you can use the asterisk (*) instead.
<condition> is a TRUE/FALSE condition usually formed by using a column name, a relational operator (=, >, <, >=, <=), and a value (such as 'SMITH').
Examples of valid select statements are shown below:
SELECT EMPLOYEE_ID,LAST_NAME FROM EMPLOYEE WHERE DEPARTMENT_ID='SALES' SELECT FIRST_NAME,LAST_NAME,PHONE FROM EMPLOYEE SELECT * FROM MANAGER
The second and subsequent arguments to SQLSelectInto are TSD Script variables. The names of these variables must match the names of columns in the SQL table or view being accessed, as shown in this example:
VARIABLES last_name, first_name: String; ACTIONS SQLSelectInto('SELECT LAST_NAME,FIRST_NAME FROM EMPLOYEE WHERE EMPLOYEE_ID=123', last_name,first_name);
Case sensitivity of the TSD Script variables is not important to processing in these examples. The value from the LAST_NAME column is read into the last_name string variable. The value from the FIRST_NAME column is read into the first_name column.
An easier way to extract information from SQL tables is to use TSD Script record variables. You define a record type where the fields match column names in a SQL table. You can then pass a record of that type to SQLSelectInto as shown below:
TYPES EmployeeRec IS RECORD employee_ID: Integer; last_name: String; first_name: String; birth_date: Date; salary: Real; END; VARIABLES r: EmployeeRec; SQLSelectInto('SELECT LAST_NAME,FIRST_NAME FROM EMPLOYEE WHERE EMPLOYEE_ID=123',r);
In general, you declare a record type whose name is the same for every table or view in a database. Using TSD Script's name binding functions allows you to extract information from or insert information into the database using variables of that record type.
In the preceding example, the EmployeeRec record type is declared so that field names and types match the names and types of columns in the EMPLOYEE SQL table. When you pass a record (r) of this type to SQLSelectInto, Developer's Toolkit automatically copies information from the requested row into the matching fields. After the SQLSelectInto executes, r has the following value:
r.employee_ID = $Unknown r.last_name = 'Brown' r.first_name = 'Robert' r.birth_date = $Unknown r.salary = $Unknown
Notice that only the r.LAST_NAME and r.FIRST_NAME columns have values. All fields in a record start out with the value $Unknown. Because the SQLSelectInto statement selected the LAST_NAME and FIRST_NAME columns, only r.LAST_NAME and r.FIRST_NAME were assigned values. All the r fields would have been completed if the following statement had been used:
SQLSelectInto('SELECT * FROM EMPLOYEE WHERE EMPLOYEE_ID=123',r);
The mapping between TSD Script record types and SQL tables is an intuitive process. TSD Script allows you to create an automatic association between a table, a view, and a record type by means of import statements.
This means that you do not have to manually declare a record field for every column of a table or view. Look at the following example:
TYPES EmployeeRec IS RECORD IMPORT('EMPLOYEE'); END;
Import statements can be used inside a record declaration to create field declarations
directly from the names and types of a SQL table or view.
If the table or view is changed, the record declaration automatically tracks the change.
That is, if you added a new ADDRESS column to the EMPLOYEE table, you
wouldn't have to add a new
address: String;
field to EmployeeRec. IMPORT is a parse-time statement. Any time a module containing an IMPORT statement is rebuilt (parsed), the indicated SQL table or view is read and its columns used to create field declarations.
Import statements take a connection string that is specified along with the table or view name. This connection string replaces the default connect string specified in the sai_sql.cfg file. For the purpose of backward compatibility, you may specify the database name in the format, 'DATABASE xxx', where xxx is the name of the database.
When you parse, you should pass the /S option to the TSD Script Parser to override any hard-coded database or connection information in the import statement.
The /S option allows you to specify a database at parse time, so that there is no need to hard code the data source name. It also allows you to specify the user ID and password when you run the TSD Script Parser from the command prompt. These values are substituted for the defaults specified in the sai_sql.cfg file.
The preferred method for using the /S option is to specify all values except the user ID and password in the sai_sql.cfg file. Then, when you parse, these values can be passed in. For example:
/S"UID=UserId;PWD=Password"
(where UserId is the user ID and Password is the user's unique password.)
When you use IMPORT, you can use optional keywords to clarify the ordering of the contents of IMPORT. The keywords are:
If you do not specify any keywords, the order of the import string is assumed to be the table name first, followed by the connection string.
Use white space and/or commas to separate the table name and connection information.
Note: You may use either the TABLE or VIEW keyword to identify table or view names. They are treated the same.
In this book, keywords appear in all capital letters and they are not case sensitive. However, the table names and connection information may be case-sensitive, depending on your DBMS installation. Check with either a LAN administrator or a database administrator for this information. You may also consult the manuals that accompanied your DBMS to ascertain whether case sensitivity is an issue.
There are many ways to construct a valid import string. The following examples are
designed to give you a variety of choices. However, it is recommended that you choose one
style and use it consistently.
The preferred method of using IMPORT is shown below:
Import('ALARMS');
The following syntax was supported in SA-ASE 4.1:
Import('DATABASE ADVISOR TABLE ALARMS');
For SA-ASE 4.2 and later, this syntax is:
'CONNECT DB=ADVISOR'
The following examples are also equivalent to the previous example:
Import ('CONNECT DB=ADVISOR TABLE ALARMS');
Import('TABLE ALARMS CONNECT DB=ADVISOR');
Import('ALARMS, CONNECT');
Import('TABLE ALARMS');
Import('VIEW PROBLEM_VIEW');
In the following example, notice the lack of spaces inside the connect string:
Import('ALARMS, SRVR=X:ORASRV;DB=ADVISOR');
The following is also correct:
Import('TABLE ALARMS, CONNECT SRVR=X:ORASRV; DB=ADVISOR);
In the following example, the "'" is optional.
Import('TABLE ALARMS CONNECT SRVR=X:ORASRV; DB=ADVISOR');
The following is also correct:
Import('CONNECT SOURCE=ADVTEST;SRVR=X:ORASRV; DB=ADVISOR, TABLE ALARMS);
Note that the following syntax is incorrect, because the string, 'ADVISOR' is not a valid connect string ('DB=ADVISOR' is a valid connect string).
Import('ALARMS,ADVISOR'); (*this is WRONG!*)
TSD Script has six simple data types:
The IMPORT statement takes a generic approach to mapping columns to fields based on their data type.
Column | Maps to... |
CHAR, VARCHAR, and LONG VARCHAR | TSD Script STRINGS |
INTEGER | TSD Script INTEGERS |
DATE | TSD Script DATES |
TIME | TSD Script TIMES |
SQL types containing decimal points (either fixed or floating) | TSD Script REALS |
The BOOLEAN type is not supported in most SQL implementations. However, if you use Oracle or IBM's DB2/2, you can include $ASETYPE=BOOLEAN in the comment for a column to generate a Boolean field with the IMPORT statement. For SQLServer, Developer's Toolkit creates a user-defined BOOLEAN type.
Developer's Toolkit creates a type table that links into SYSCOLUMNS view for Informix.
SQLSelectInto is an easy way to retrieve a unique row from a table or
view. SQLSelectInto can be used to find one row, but cannot scan a table.
TSD Script provides a cursor variable for retrieving multiple items from a database within
a loop. The cursor keeps track of the current position in a retrieved list.
The basic process for retrieving multiple rows follows:
To permit the retrieval of multiple rows, TSD Script provides three statements:
The TSD 6.0 Developer's Toolkit Script Language Reference provides additional information about the preceding statements.
The statements are used in the following example:
VARIABLES cursor: SQLCursor; r: EmployeeRec; ACTIONS IF SQLSelect(cursor,'SELECT * FROM EMPLOYEE WHERE SALARY>>0000.00') > 0 THEN WHILE SQLFetch(cursor,r) > 0 DO ProcessEmployee(r); END; SQLCloseCursor(cursor); END;
This example does the following:
The SQLCloseCursor operation is very important. When an SQL cursor is open, most SQL implementations lock all the rows in the result table. These rows are not unlocked until the SQLCloseCursor statement is processed.
Note: In general, you do not want to perform any user interface operations between the SQLSelect and SQLCloseCursor statements. Additionally, TSD Script provides a limited number of simultaneous cursors. If you forget to close cursors, you eventually exceed this limit.
SQL allows multiple tables of the same name within a database. To distinguish between them, SQL stores the creator/owner of the table in the table definition. This creator/owner name is referred to as the table's qualifier. A combination of the qualifier and table name must be used to uniquely identify a table within a database. For example:
mary.address
Most SQL database managers use the current user's ID as the qualifier if none is provided. If you are not logged on as the user who created the database and you don't provide a qualifier some SQL implementations continue to search the database using the database owner as the qualifier. In other SQL implementations (such as DB2/2), you are required to provide the qualifier each time.
TSD Script performs appropriate qualification substitutions as long as you provide a
QUAL= entry in the connection string.
TSD Script performs the substitution in all statements except:
SQLPrepare SQLExecuteImmediate
If you use complex queries containing correlation names, the substitution may not be
performed correctly. To handle these cases, TSD Script provides a pseudo-qualifier, $QUAL,
that is substituted for the current qualifier (that is, the one set with the QUAL= entry
of the connect string).
For example, to avoid hard-coding the qualifier, the following code fragment uses $QUAL as
the qualifier:
ret := SQLExecuteImmediate('DROP TABLE $QUAL.ADDRESS');
In TSD Script, all strings are surrounded by single quotes (or apostrophes). In SQL, character, date, and time literals must also be surrounded by single quotes. For example, to issue the SQL query,
SELECT * FROM EMPLOYEE WHERE LAST_NAME='BROWN'
you would use the following TSD Script statement:
SQLSelectInto('SELECT * FROM EMPLOYEE WHERE LAST_NAME=''BROWN''',r);
Looking at the previous SQLSelectInto example, you can see that the two single quotes to the left of the "B" in "BROWN" results in one single quote. There are two single quotes following the "N" in "BROWN." This causes one single quote to be inserted at this position. The third single quote after the "N" in "BROWN" is used to terminate the string.
In order to include a single quote in a TSD Script string literal, you must include two single quotes. For example, to assign the string "Tom's Place" to a TSD Script variable, you must use the following:
s := 'Tom''s Place';
When you concatenate strings that use quotes, it can become more complicated. In this example, the actual last name you want to look for is stored in a sting variable called last_name:
last_name := 'BROWN';
SQLSelectInto('SELECT * FROM EMPLOYEE WHERE LAST_NAME=''' & last_name & '''',r);
When you create a select string using the TSD Script concatenation operator (&), be careful to position the single quotes on either side of the literal value for which you want to search. The resulting query looks like this:
SELECT * FROM EMPLOYEE WHERE LAST_NAME = 'BROWN'
The single quote to the left of the last name value is accomplished via the three single quotes following "LAST_NAME=''.
The first two of these are used to insert the needed single quote while the third simply terminates the first string constant, 'SELECT * ... ='.
The second necessary single quote is accomplished by concatenating the literal '''' onto the end of the entire expression.
When you create select expressions where the value being sought is a string, date, or time, you need to include three quotes before the variable holding the search value and four quotes after.
Quotes are not needed when searching for other types of data, as shown below:
SQLSelectInto('SELECT * FROM EMPLOYEE WHERE AGE=30',r);
You do not need quotes around the 30 since this is an integer value. If the 30 had been held in an integer variable, your statement might look like this:
age := 30; SQLSelectInto('SELECT * FROM EMPLOYEE WHERE AGE=' & age,r);
The SQLFormat statement is a TSD Script statement used in SQL manipulation. SQLFormat converts a data value to a string that matches the data format for the DBMS that you are currently using.
The SQLFormat statement is especially useful for date and time variables and uses the following syntax:
SQLFormat(value: SIMPLE EXPRESSION): STRING;
Note: Your DBMS may require date and time formats that differ from the default formats used for display by Developer's Toolkit. For example, the default Oracle date format is DD-MON-YY, while the default Developer's Toolkit date format is MM/DD/YYYY.
The value argument SQLFormat takes must be a simple type such as DATE or STRING. SQLFormat returns the formatted string, not a return code, that indicates whether the operation was successful. If the value passed is $Unknown, the string 'NULL' is returned. The specific formatting of the string depends on the type of the string:
An example knowledgebase using SQLFormat is shown below:
KNOWLEDGEBASE Example;
--PUBLIC ROUTINES FUNCTION GetCount(VAL d: DATE): INTEGER;
PRIVATE ROUTINES FUNCTION GetCount(VAL d: DATE): INTEGER IS VARIABLES retCd : INTEGER; cmd : STRING; $SQLColumn_1 : INTEGER; ACTIONS cmd := 'SELECT COUNT(*) FROM COMPANIES WHERE name = ' & SQLFormat('Joe''s place') & ' AND founded_date='& SQLFormat(d); retCd := SQLSelectInto(cmd, $SQLColumn_1); IF retCd < 0 THEN Exit( retCd ); ELSE Exit( $SQLColumn_1 ); END; END;
For more information on TSD Script statements used in data formatting, see the TSD 6.0 Developer's Toolkit Script Language Reference.
TSD Script uses the equivalency of variable names and column names to make data retrieval simple. There are times, however, when name-based mapping is inconvenient or cannot be used.
Consider the following SQL query that could be used to determine the number of employees (rows) in the EMPLOYEE table:
SELECT COUNT(*) FROM EMPLOYEE
TSD Script provides a simple method to retrieve data from an SQL column without relying on matching names. All you have to do is declare a variable named $SQLCOLUMN_n where "n" is the number of the column you wish to retrieve. The type of this variable must map to the column being retrieved.
To retrieve the number of employees (rows) in the EMPLOYEE table, you could use the following code:
VARIABLES $SQLCOLUMN_1: INTEGER; ACTIONS SQLSelectInto('SELECT COUNT(*) FROM EMPLOYEE',$SQLCOLUMN_1);
$SQLCOLUMN_1 is a normal variable. You can assign it, test it, use it in expressions, and so on.
This section describes how to insert data into a SQL database, delete data from a SQL database, and modify data in a SQL database.
The SQLInsert statement can be used to insert new rows into a table. All you need to do is provide a table name followed by the variable(s) holding the data you wish to insert. Like the retrieval function, TSD Script performs a mapping between TSD Script variable names and SQL column names.
In the following example, values are assigned to the various fields of a variable of type EmployeeRecord. To insert this information into the EMPLOYEE table, SQLInsert is called with the name of the destination table and the variable.
VARIABLE r: EmployeeRecord; ACTIONS r.employee_ID:'312-34-3444'; r.last_name:'Brown' r.first_name:'Robert' r.birth_date:'08/05/1964'; r.salary: $Unknown SQLInsert('EMPLOYEE',r);
SQLInsert, like SQLSelectInto and SQLSelect, takes a variable number of arguments. The first argument is the name of the table. The remaining arguments represent values to put into each column of the new row. You can pass either a single record or a number of simple variables, as shown in the following example.
VARIABLE employee_ID: STRING; last_name: STRING; first_name: STRING; birth_date: DATE; salary: REAL; ACTIONS employee_ID:'312-34-3444'; last_name:'Brown'; first_name:'Robert'; birth_date:'08/05/1964'; salary:$Unknown; SQLInsert('EMPLOYEE',employee_ID,last_name, first_name,birth_date,salary);
SQLDelete can be used to delete one or more rows from a table. It
takes two arguments: the name of the table and a SQL WHERE clause identifying the rows to
be deleted. The word WHERE in this clause is optional.
This example deletes every employee whose last name is "Smith."
SQLDelete('EMPLOYEE','LAST_NAME=''Smith''');
The following example deletes an employee with a specific employee ID number.
SQLDelete('EMPLOYEE','EMPLOYEE_ID=''123-45- 6789''');
SQLUpdate can be used to change the information stored in existing rows of a table. This statement takes the following arguments:
For instance, suppose your company has an employee with an ID of 345-67-8901 who received a 10% raise. You would update the database to reflect that fact, as shown in this example:
VARIABLES salary: REAL; ACTIONS IF SQLSelectInto('SELECT SALARY FROM EMPLOYEE WHERE EMPLOYEE_ID= ''345-67-8901''', salary) > 0 THEN salary := salary * 1.10; SQLUpdate('EMPLOYEE','EMPLOYEE_ID=' '345-67- 8901''',salary); END;
In the next example, the company has decided to grant a 10% raise to everyone. This example shows how to update the database with that information:
VARIABLES salary: REAL; cursor: SQLCursor; ACTIONS IF SQLSelect(cursor, 'SELECT SALARY FROM EMPLOYEE ') > 0 THEN WHILE SQLFetch(cursor,salary) > 0 DO salary := salary * 1.10; SQLUpdateCurrent(cursor,salary); END; SQLCloseCursor(cursor); END;
SQLUpdateCurrent is a special version of SQLUpdate that can be used to update the most recently fetched row of a cursor. It takes the cursor variable and the values to be updated as arguments.
Finally, SQLUpdate (and SQLUpdateCurrent) can take a record instead of a sequence of a few simple variables. The next example assumes that:
VARIABLE r: EmployeeRecord; ACTIONS r.employee_ID: = '312-34-3444'; IF SQLSelectInto('SELECT * FROM EMPLOYEE WHERE EMPLOYEE_ID=''' & r.employee_ID & '''',r) > 0 THEN EditEmployee(r); SQLUpdate('EMPLOYEE', 'EMPLOYEE_ID=''' & r.employee_ID & '''',r); END;
In summary, the following steps are performed in this example:
You may have noticed in the examples that records have not been locked, nor have any steps been taken to prevent multiple users from accessing the same records.
Developer's Toolkit's passive concurrency feature handles the typical concurrency problems faced in multi-user applications, as summarized in the following list:
The error message has a negative error code. In some situations, you may wish to test this error code and perform different actions depending upon its value.
For example, if a section of code depends on the success of an insert operation, you should surround that code with a test for a positive return from SQLInsert.
Developer's Toolkit generates an error message to the second user who attempts a deletion.
Note: To implement passive concurrency checking include an additional argument to SQLUpdate.
Consider the following example:
VARIABLE r, oldR: EmployeeRecord; ACTIONS r.employee_ID: = '312-34-3444'; IF SQLSelectInto('SELECT * FROM EMPLOYEE WHERE EMPLOYEE_ID=''' & r.employee_ID & '''', r, oldR) > 0 THEN EditEmployee(r); SQLUpdate('EMPLOYEE', 'EMPLOYEE_ID=''' & r.employee_ID & '''',r,oldR); END;
In this example the following events occurred:
When TSD Script begins to process the SQLUpdate statement, it detects the extra record variable, oldR. This causes it to:
If any differences are detected, it means that someone else has already changed that row and the update is canceled. SQLUpdate returns a negative error code and a user-level error message is displayed on screen. Additionally, the current value of the row is placed in oldR (so that the user does not have to retrieve the current version of the record).
Passive concurrency represents an easy and effective means of ensuring proper operation in a multi-user environment. There is slight overhead because there is a second retrieval of the row to be updated. However, the additional protection provided is worthwhile.
Note: In general, you should always use the passive-concurrency form of SQLUpdate unless you are sure that there is no possibility of lost updates.
Most SQL database engines offer a capability that allows you to group SQL operations together into units of work. You can then use built-in facilities to ensure that each unit of work succeeds as a whole or fails as a whole.
In normal processing, all changes are committed to the database as soon as they occur. In transaction-oriented processing, changes to data are handled differently.
The SQLBeginWork statement indicates the start of a unit of work. SQLBeginWork indicates to the SQL database engine that it should call neither SQLCommit nor SQLRollback until you tell it to do so.
The SQLCommit statement indicates that all changes to the database since the last SQLBeginWork should be applied to the database and made permanent.
The SQLRollback operation occurs when one of the processes defined for the unit of work failed. A SQLRollback indicates that all changes made to the database since the last SQLBeginWork should be undone.
For example, in a banking application, a transfer may be made from a customer savings account to a checking account. Such a transfer might involve reducing the balance column in one table (SAVINGS_ACCOUNTS) and it in another table (CHECKING_ACCOUNTS). This would be a problem if one operation succeeded while the other failed. To prevent this possibility, a transaction-oriented application would do the following:
The code fragment that follows illustrates the principles discussed previously. As indicated, this is a case where several SQL operations must succeed before committing the changes to the database. Specifically, you do not want to commit after the first update since the customer will be left with a lower savings balance and no compensatory increase in checking balance if the second update fails.
PROCEDURE SavingsToCheckingTransfer( VAL savings_ID: STRING, VAL checking_ID: STRING, VAL amount: REAL) IS VARIABLES debited, credited: BOOLEAN; balance: REAL; ACTIONS SQLBeginWork; debited := FALSE; IF SQLSelectInto('SELECT BALANCE FROM SAVINGS WHERE ACCOUNT_ID=''' & savings_ID & '''' ,balance) > 0 THEN balance := balance - amount; IF SQLUpdate('SAVINGS', 'ACCOUNT_ID=''' & savings_ID & '''', balance) > 0 THEN debited := TRUE; END; END; IF debited THEN credited := FALSE; IF SQLSelectInto('SELECT BALANCE FROM CHECKING WHERE ACCOUNT_ID=''' & checking_ID & '''',balance) > 0 THEN balance := balance + amount; IF SQLUpdate('CHECKING', 'ACCOUNT_ID=''' & checking_ID & '''' , balance) > 0 THEN credited := TRUE; END; END; IF credited THEN SQLCommit; ELSE SQLRollback; END; END; END;
When you define a database table, you can specify whether each column can have null values. Declaring that a column can contain null values means that the column does not have to contain a value. (This is different than having an empty string value.)
Note: SQL's NULL maps well onto TSD Script's support for unknown values. In TSD Script, every variable starts with the value $Unknown. An exception to this is record variables: all fields of a record variable begin with the value $Unknown.
As values are exchanged between TSD Script and SQL, the mapping between $Unknown and NULL is preserved. For instance, consider this example:
SQLInsert('EMPLOYEE',r);
In this example, if r.salary is $Unknown, then the SALARY column for the inserted row would contain a NULL value.
Likewise, if the PHONE column were NULL for a given row, then retrieving that row into a record variable with SQLSelectInto or SQLSelect/SQLFetch would cause the phone field of that variable to be $Unknown.
In the Developer's Toolkit form system, null values are also supported. If a user leaves a dialog field blank, the corresponding record field is set to $Unknown.
Note: You receive an error if you attempt to insert an $Unknown value into a column that was not declared to accept null values. This usually occurs only when form fields are left blank.
In business applications, large amounts of code are written to handle error conditions. Once code is written, it must be tested. Often, this means evaluating the return code from each statement.
Developer's Toolkit includes an automatic error handling mechanism that reduces the error handling burden. This means that:
By default, Developer's Toolkit detects unsuccessful SQL operations and displays error messages to end users.
Developer's Toolkit displays as much contextual information as possible to identify the cause of the error (duplicate column error, I/O error, disk full, and so on). If the success or failure of an operation has no significance, it is not necessary to check the return value. In this case, a new employee is inserted into the EMPLOYEE table:
SQLInsert('EMPLOYEE',r);
There may be times when you wish to temporarily disable the automatic error message facility. $ErrorFilter is a system integer function that allows you to specify an error level threshold. Errors with levels below the specified threshold do not generate error messages.
The following severity levels generate automatic error code messages:
Error Severity Code | Description |
0 | Fatal error |
1 | Non-fatal error |
2 | Warning message |
3 | Informational message |
For example, setting $ErrorFilter(3) means that only errors and warnings with a severity code level of three or lower generate automatic messages. Errors with a higher severity code level do not cause error messages to appear, and are discernible only through examination of the statement return codes.
Severity code levels decrease with increasing severity. Therefore, you can call ErrorFilter(0) to disable all but fatal error messages.
If you plan to execute an SQL command multiple times in a loop, it may be more efficient to prepare (precompile) the command once outside of the loop and then execute the prepared form in the loop.
Developer's Toolkit allows you to prepare most SQL commands with the exception of Select statements and commands that cannot be prepared dynamically. See your SQL documentation for information about specific commands that cannot be prepared dynamically.
Developer's Toolkit provides two commands for preparing an SQL command:
Note: SQLPrepare and SQLExecute are supported only in DB2/2, DB2/6000, and Oracle.
Using SQLPrepare and SQLExecute is like using SQLExecuteImmediate except that the execution is deferred until you need it. The following example shows how to insert a list of new users into a USER table with the SQLPrepare/SQLExecute combination:
FUNCTION InsertNewUsers(REF users: LIST OF STRING):INTEGER IS VARIABLES stmt: SQLStatement; retCd: INTEGER; ACTIONS (* insert the user names passed in *) retCd := SQLPrepare(stmt,'INSERT INTO USERS VALUES (?)'); IF retCd <= 0 THEN EXIT retCd; END;
(* perform the insertions *) FOR users DO retCd := SQLExecute(stmt,users[$current]); IF retCd <= 0 THEN SQLRollback; (* reverse changes and release locks *) EXIT retCd; END; END; (* for *)
(* release the resources used by this Prepared statement *) SQLCloseStatement(stmt); END;
Note: Since TSD Script has limited resources for simultaneous, prepared statements, you need to ensure that you close the prepared statement by calling SQLCloseStatement.
Parameter marker substitution is integral to the process of using prepared statements. Parameter markers, represented by question marks (?), are placeholders for values that are substituted later.
For each value, a question mark is inserted in the statement string (supplied to SQLPrepare). The guidelines for these values follow:
Note: TSD Script is unable to provide equally robust type conversions with parameter markers as it does with normal parameters (those used in SQLInsert, SQLUpdate, SQLSelectInto, and SQLFetch).
Note: These statements apply only to Sybase and Microsoft SQLServer.
Some DBMSs support only one active SQL statement at a time on a connection. For example, putting a SQLSelectInto statement in the body of a SQLFetch loop requires two simultaneous SQL statements with SQLServer. Developer's Toolkit compensates for this by cloning connections. Cloned or "secondary" connections have their own transaction space.
Developer's Toolkit's transaction model causes all the connections to behave as if they belong to the same transaction. Although this is somewhat unorthodox, (no two-phase commit protocol is used) it works reliably in practice. There is an existing requirement that defers the implicit "auto" commits until the outer cursor is closed for environments where open cursors and prepared statements are closed on commits or rollbacks.
In earlier versions of Developer's Toolkit, all nested or simultaneous SQL operations were performed inside a transaction. In those versions, SQLBeginWork is called before the outermost cursor was opened, and a commit (or rollback) had to be performed when this outermost cursor was closed.
If you wanted the statements to execute in their own transaction spaces, you had to open additional primary connections with SQLCommand for the statements.
Developer's Toolkit 6.0 no longer requires transaction wrapping for simultaneous statement execution in "manual" commit mode. However, transaction wrapping is still required when using "auto" commit mode if the DBMS does not allow cursors to span transactions.
Because of the complexities and side-effects involved with nested SQL statements (such
as connection overhead, the deadlock potential when accessing the same table from two
connections in different transaction spaces, and so on), we strongly recommend that you
avoid them wherever possible.
It is almost always possible to split the SQL operations into two parts:
Beside avoiding many of the side-effects of nested statements, this approach has the advantage of increased concurrency because you are not forced to perform the operations inside a transaction. Depending on the application, however, you may still need a transaction for the second part.
Tivoli Service Desk 6.0 Developer's Toolkit Script Programming Guide