Tivoli Service Desk 6.0 Developer's Toolkit Script Programming Guide

Chapter 8: Database Management Facilities

Back to Table of Contents


Introduction

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 overview

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.

Supported databases

The following are databases (also known as sources) supported by Developer's Toolkit:

SQL Support in Developer's Toolkit

Developer's Toolkit and the TSD Script language support various SQL statements and SQL databases used to develop client/server applications.

SQL statements not supported with direct drivers

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.

Data Source Attributes and Descriptions

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.

Database source definition

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.

Source attributes

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.

CASE_CONVERSION

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.

CASE_SENSITIVE

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.

COLUMN_DEFINITION_TABLE

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.

COMMIT_SELECTS

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

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.

Specifying the day

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

Specifying the month

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

Specifying the year

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

Separators

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

DBMS

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:

DEFAULT

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.

DRV

This is the DBMS driver name, and is required when connecting.

DSN

This attribute lists the data source name for the SQL configuration file (ODBC only).

MANUAL_COMMITS

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.

MULTIPLE_CONNECT

This entry is set to TRUE if the DBMS supports multiple, simultaneous connections.

MULTIPLE_CONNECT_REQUIRED

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.

QUAL

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.

SYSQUAL

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).

TABLE_DEFINITION_TABLE

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 Default

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)

TIME_FORMAT

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.


Military time

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

Case-Insensitive time formats

The format specifier is case insensitive. For example hh:mm:ss is equivalent to HH:MM:SS

Trimming leading zeros

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.

Specifying 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

Separators

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

UPPERCASE_FUNCTION

This entry contains the DBMS-specific function name used to convert columns in a query to uppercase (used by SQLCreateSearchString). The default values are:

Connecting Your Application to a Database

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.

SQL configuration file name

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).

Other locations for configuration instructions

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.

Database configuration example

To add a database client for its initial use, follow these steps:

  1. From the Developer's Toolkit program group, choose the SQL Configuration Editor.
    Result: The SQL Configuration Editor dialog box appears.

The following components are found in the SQL Configuration Editor dialog box:

  1. If it is not already entered in the File text box, enter the drive, path, and filename for the sai_sql.cfg file (the default location is c:\sai\cfg\sai_sql.cfg) and choose Add.
    Result: You created a configuration file and added the first data source. The Driver Selection dialog box appears.
  2. Select the database driver that you want to use from the radio button list and choose OK.
    Result: The Source Setup dialog box appears.
  3. Enter information relevant to your database client in the appropriate text boxes and choose either OK or Advanced.

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.

  1. Choose Add.
    Result: The Option Selection dialog box appears.
  2. Select the option you want to configure and choose Select.
    Result: The Option Value Entry dialog box appears.
  3. In the Option Value Entry dialog box, enter the attribute option and the attribute value for the source you are configuring. If you need more information, choose Help.
  4. Choose OK and exit from the SQL Configuration Editor.

Testing the database client connection

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:

  1. From the Developer's Toolkit program group, choose the SQL Configuration Editor.
  2. Select the data source you want to test and choose Test Connect. If the Test Connect function returns an error message, please check the parameters entered in the Source Setup Dialog box or test the connectivity.

Connection information in the SQL configuration file

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).

Eliminating the SQL configuration file

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='); 

An Example Database

Database tables and views

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

Retrieving Data with SQLSelectInto

SQLSelectInto

The SQLSelectInto statement provides an easy way to get information from a SQL table or view. It takes a variable number of arguments.

First argument

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 

Subsequent arguments

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.

Using a Record Variable

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);


Record types

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); 

Using TSD Script Import Statements

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

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.

Connection string

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.

Parsing with the /S option

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.)

Using keywords

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.

IMPORT statement examples

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);

Incorrect example

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!*)

Data types

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.


Multiple Row Retrieval

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.

Process

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.

Example

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.

Qualifiers

Using qualifiers

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.

Qualification substitution

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:

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'); 

Quotes

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';

Concatenating strings with quotes

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.

Exceptions

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);

Data Formatting with the SQLFormat Statement

Using SQLFormat

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:

Example

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.

Name Overrides

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

Retrieving data without matching names

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.

Updating Data

Introduction

This section describes how to insert data into a SQL database, delete data from a SQL database, and modify data in a SQL database.

Inserting data

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); 

Deleting data

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''');

Modifying data

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:

Passive concurrency

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.

Transaction-Oriented Processing

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.

Description

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:

Example

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; 

Unknown Values in SQL Columns

Using null values

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.

Mapping unknown values

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.

Error Handling

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:

Detecting error messages

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);

Filtering error messages

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.

Using Commands for Multiple Executions

Preparing SQL looping commands

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.

Substituting parameter markers

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).

Nested SQL statements

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

Back to Table of Contents

Copyright