Tivoli Service Desk 6.0 Developer's Toolkit Script Language Reference

SQL Manipulation

Return to Main Page


SQLBeginWork

Description

Begins a transaction.

Syntax

FUNCTION SQLBeginWork: INTEGER;

Notes

TSD Script normally commits every SQL statement upon its successful completion. However, there are times when you want a series of SQL statements to be committed or rolled back together. SQLBeginWork pauses automatic commits of every SQL statement until you make a call to SQLCommit or SQLRollBack.

After a commit or rollback, the transaction terminates and TSD Script resumes automatic committing. SQLBeginWork must be called again to begin another transaction.

Example

KNOWLEDGEBASE Example;
--PUBLIC
ROUTINES
FUNCTION Test:INTEGER;
PRIVATE
ROUTINES
FUNCTION Test:INTEGER IS
VARIABLES
ok : BOOLEAN;
ACTIONS
(* start the transaction (transaction) ==> this disables the
 normal
 automatic committing after every SQL operation *)
SQLBeginWork;
(* do your work here ok := .... *)
IF ok THEN
 SQLCommit; (* save changes since SQLBeginWork *)
ELSE
 SQLRollBack; (* reverse changes since SQLBeginWork *)
END;
(* After a commit or rollback the transaction will be
 terminated. *)
(* Call SQLBeginWork again to start the next transaction if
 you don't *)
(* want the automatic committing *)
END; --Test--

Return Codes

Return Code Description
1 Successful completion
(other) See the Tivoli Service Desk 6.0 Developer's Toolkit Script Programming Guide

See Also


SQLCloseAll

Description

Closes open cursors and prepared statements and disconnects open DBMS connections.

Syntax

FUNCTION SQLCloseAll: INTEGER;

Notes

Although not required, you should call this routine before exiting the main .kb file. This commits any transactions in progress.

Example

KNOWLEDGEBASE Example;
--PUBLIC
ROUTINES
FUNCTION Test:INTEGER;
PRIVATE
ROUTINES
FUNCTION Test:INTEGER IS
ACTIONS
(* perform some SQL operations... *)
SQLCloseAll;
END;

Return Codes

Return Codes Description
1 Successful completion
(other) See the Tivoli Service Desk 6.0 Developer's Toolkit Script Programming Guide

See Also


SQLCloseCursor

Description

Closes a cursor previously opened by a call to SQLSelect.

Syntax

FUNCTION SQLCloseCursor(cursor: SQLCURSOR): INTEGER;

Caution: A rollback operation closes all cursors (see SQLRollBack).

Most drivers do not allow you to specify cursors that may span units of work (for instance, declare them WITH HOLD). An exception is the DB2 CLI driver. After a commit operation all cursors are closed. Therefore, if you perform SQL operations (such as updates) in a fetch loop, you must disable the automatic commit after every SQL statement.

Note: You should close all cursors before performing a commit or rollback operation.

Argument Notes

Argument Name Description
cursor This is the parameter of type SQLCursor that was set by a previous call to SQLSelect.

Notes

Upon completion, SQLCloseCursor sets the cursor to $Unknown. A call to SQLCloseCursor with a previously closed cursor exits with an error. No error message is displayed.

Example

KNOWLEDGEBASE Example;
--PUBLIC
ROUTINES
FUNCTION Test:INTEGER;
PRIVATE
ROUTINES
FUNCTION Test:INTEGER IS
VARIABLES
(* columns *)
name: STRING;
(* misc *)
cursor: SQLCursor;
retCd: INTEGER;
ACTIONS
(* select all employees *)
retCd := SQLSelect(cursor,'* from emp');
IF (retCd < 0) THEN
 EXIT retCd;
END;
 retCd := SQLFetch(cursor,name);
 WHILE (retCd > 0) DO
 (* perform some operations based on this row...
 .
 .
 .
 *)
 (* get the next record *)
 retCd := SQLFetch(cursor);
END;
(* now close the cursor *)
SQLCloseCursor(cursor);
END;

Return Codes

Return Code Description
1 The cursor was successfully closed
(other) See the Tivoli Service Desk 6.0 Developer's Toolkit Script Programming Guidee

See Also


SQLCloseStatement

Description

Closes a statement previously opened (prepared) by SQLPrepare.

Syntax

FUNCTION SQLCloseStatement(statement: SQLSTATEMENT): INTEGER;

Cautions

A rollback operation destroys all prepared statements. A commit operation closes all prepared statements that do not reference an open cursor.

Note: You should manually close all affected statements with SQLCloseStatement before performing a commit or rollback.

Argument Notes

Argument Name Description
statement The statement parameter is initialized by a call to SQLPrepare

Notes

The SQLCloseStatement is supported by all drivers. Upon completion, SQLCloseStatement sets the statement to $Unknown.

A call to SQLCloseStatement with a previously closed statement exits with an error. No error message is displayed.

Example

KNOWLEDGEBASE Example;
--PUBLIC
ROUTINES
FUNCTION Test(REF users: LIST OF STRING):INTEGER;
PRIVATE
ROUTINES
FUNCTION Test(REF users: LIST OF STRING):INTEGER IS
VARIABLES
stmt: SQLStatement;
retCd: INTEGER;
ACTIONS
SQLBeginWork
 (* insert the user names passed in *)
 retCd := SQLPrepare(stmt,'INSERT INTO USERS VALUES (?)');
 IF (retCd < 0) THEN
 SQLRollBack; (* terminate the transaction *)
 EXIT retCd;
 END;
 FOR users DO
 retCd := SQLExecute(stmt,users[$current]);
 IF retCd < 0 THEN
 SQLCloseStatement(stmt);
 SQLRollBack; (* reverse changes and release locks *)
 EXIT retCd;
 END;
 END; (* for *)
 (* close the prepared statement *)
 SQLCloseStatement(stmt);
 SQLCommit; (* save changes and release locks *)
END;

Return Codes

Return Code Description
1 Successful completion
(other) See the Tivoli Service Desk 6.0 Developer's Toolkit Script Programming Guide

See Also


SQLCommand

Description

Issues implementation-dependent, non-SQL commands to the SQL interpreter.

Syntax

FUNCTION SQLCommand(sub_command: STRING): INTEGER;

Argument Notes

Argument Name Description
sub_command The command string is composed of two parts: the first part is the database manager subcommand; the second part contains any parameters for this command. The command is case-insensitive. All parameters are separated by white space. See the following section for the available subcommands.

Notes

SQLCommand Subcommands

As shown in the table, there are numerous subcommands for the SQLCommand statement.

Several subcommands require a connection_handle_string. To specify the currently selected source database, you can pass in $Current. Alternatively, if you do not specify anything, the currently selected source database is used.

To get a connection handle string, call SQLCommand with the subcommand GET CURRENT CONNECTION. The integer return value can then be assigned to a string. This string is the connection_handle_string.

There are also several subcommands that specify a source_name. (Source name parameters are always optional.) To specify the source to which you are currently connected, you can pass in $Current. To specify the default data source, you can pass $Default. If no source name is specified, $Current is assumed.

The following table shows subcommands for SQLCommand ('<sub_command>') as well as their parameters and descriptions. The values in the Parameter column are supplied by the user.

Subcommand Parameter Description
CONNECT connect_string Opens a connection with your DBMS. This command must be called before any SQL operation. Usually it is one of the first statements called by your application. If connect_string is not supplied, a connection is made to the default data source. After successfully connecting, this becomes the currently selected connection.
DISCONNECT connection_handle_string Disconnects from the connection referred to by connection_handle_string. The connection handle is an integer that may be obtained by calling SQLCommand (GET CURRENT CONNECTION) immediately after connecting.
GET CASE CONVERSION source_name Returns the case conversion mode for object names (not for data):
0 = none
1 = upper
2 = lower
A negative return code indicates an error.
GET CASE SENSITIVITY source_name Informs Tivoli Service Desk Developer's Toolkit whether the DBMS has case sensitive object names (such as table names, column names, and so on). This does not apply to data (although with some DBMS's, such as SQLServer, case sensitive object names and case sensitive data co-exist).
The possible return values are:
0 = case insensitive
1 = case sensitive
GET CATALOG CASE CONVERSION source_name Returns the current case conversion mode for data in the system catalog tables:
0 = none
1 = upper
2 = lower
A negative return value indicates an error.
GET CHECK DRIVER source_name Returns TRUE if (at connect time) a check is performed to determine whether the driver (for example, CDSS06.DLL) is one of the unsupported drivers; otherwise it returns FALSE.
GET CURRENT CONNECTION source_name Returns the integer connection handle for the currently-selected connection. If you receive a return code less than zero, see the page SQL Error Code Definitions for more information.
GET CURRENT ODBC HENV   Returns the actual ODBC environment handle used by Tivoli Service Desk Developer's Toolkit. This only applies when using ODBC drivers. A negative return value indicates an error.
GET CURRENT ODBC HDBC   Returns the actual ODBC connection handle for Tivoli Service Desk Developer's Toolkit's current primary connection. This only applies when using ODBC drivers. A negative return value indicates an error.
GET DATE FORMAT source_name Returns the current date format for the DBMS.
GET DBMS source_name Returns the integer code for the DBMS of the source named source_name. The possible return values are: 0 or 2 (DB2/); 1 (Oracle); 3, 23, or 24 (SQLServer); 4 or 12 (SYBASE); and 16 or 17 (INFORMIX).
GET IN TRANSACTION connection_handle Returns 1 if a transaction is in progress (in other words, SQLBeginWork has been called and SQLCommit or SQLRollback has not yet been called). A return code of 0 indicates that no transaction is in progress. A negative return code indicates an error.
GET MODULE   Returns the integer code for the SQL library you are using. This provides for backward compatibility only. The possible values of the module codes are: 2 = (Multi-platform - X/OPEN and ODBC).
GET MODULE TYPE   Returns the current module type. This provides for backward compatibility only. Possible values are: 5 = X/OPEN
Note: This is similar to the GET MODULE subcommand, except that GET MODULE maps ODBC to Q+E version 2 for backwards compatibility.
GET MULTI CONNECT source_name Returns 1 if the source named source_name supports multiple, simultaneous connections.
GET SHOW WARNINGS source Returns TRUE if message boxes for warnings are enabled; otherwise it returns FALSE.
GET TIME FORMAT source_name Returns the current time format for the DBMS.
GET UPDATE LOCK STYLE source_name Returns the current lock style:
0 = none
1 = select for update
2 = update
A negative return code indicates an error.
GET UPDATE MODE source_name Returns the passive concurrency update mode. Possible values are:
0 = none
1 = select
2 = DBMS optimistic
A negative return code indicates an error.
RESTORE CURRENT QUALIFIER   Fetches the value of the most recently saved qualifier on the internal stack and makes it the current qualifier for the current connection.
RESTORE CURRENT SYSQUALIFIER   Fetches the value of the most recently saved system table qualifier on the internal stack and makes it the current system qualifier for the current connection.
SAVE CURRENT QUALIFIER   Stores the value of the current table qualifier for the current connection on an internal stack.
SAVE CURRENT SYSQUALIFIER   Stores the value of the current system table qualifier for the current connection on an internal stack.
SET CASE CONVERSION conversion Sets the case conversion mode for object names (not for data). Valid values for conversion are: NONE, UPPER, and LOWER.
SET CASE SENSITIVITY true | false Informs Tivoli Service Desk Developer's Toolkit whether the current DBMS has case-sensitive object names (such as table names, column names, and so on). This does not apply to data (although with some DBMSs, such as SQLServer, case sensitive object names and case-sensitive data go together).
SET CATALOG CASE CONVERSION conversion Sets the current case conversion mode for data in the system catalog tables. Valid values for conversion are UPPER, LOWER, and NONE.
SET CHECK DRIVER true | false Sets the current state for performing the driver validation check.
SET CONNECTION connection_handle_string Sets the currently selected connection to the handle referred to by connection_handle_string.
SET CURRENT QUALIFIER qualifier Sets the table qualifier to qualifier for the currently selected connection.
SET CURRENT SYSQUALIFIER qualifier Sets the system table qualifier to qualifier for the currently selected connection.
SET DBMS dbms Allows you to override the currently recognized DBMS. (See GET DBMS for a list of DBMS values.)
SET SHOW WARNINGS true | false Sets the current state for showing warning message boxes.
SET UPDATE LOCK STYLE style Allows you to set the current lock style locally. Valid values for style are: NONE, SELECT FOR UPDATE, and UPDATE.
SET UPDATE MODE none | select | dbms_optimistic Informs Tivoli Service Desk Developer's Toolkit how the passive concurrency should be performed:
NONE - the original record is not compared to the database. SELECT -performs an SQL select of the record and compares it to the original record used in the passive concurrency mechanisms to detect whether the DBMS version of the record has changed.
DBMS_OPTIMISTIC - attempts to use the DBMS specific passive concurrency mechanisms to detect whether the DBMS version of the record has changed.
START USING DATABASE database_name This command switches the currently active database to database_name.
TRACE ALERT DESTINATION file name Sets the destination file for any alert messages generated by a tracing timer.
If used, information is appended to the specified file. By default, this file is named sql_trc.alr.
Note: The information is appended to the existing file. This is different from the TRACE FILE command, which truncates information to the specified file.
TRACE AUTO FLUSH true | false If TRACE AUTO FLUSH is set to TRUE, the output to the trace file is flushed (written to disk ) every time it is written. Normally, output to the log is written to disk only when the tracing is completed or when the operating system decides to flush a buffer.
An operating system failure can cause some or all of the trace information to be lost. The TRACE AUTO FLUSH command forces the information to the file, ensuring that an operating system failure does not result in lost output. The default value is TRUE.
Note: Performance may suffer from the frequency of file access.
TRACE ENABLED true | false Determines whether tracing is enabled or disabled. If TRACE ENABLED is not present, the default value is FALSE.
TRACE FILE file name Places the trace file output into the specified file. This file is truncated each time you start tracing unless appending has been specified (see TRACE APPEND). If the TRACE_FILE parameter is missing, the default trace file, sql_trc.log, is created in the current directory.
TRACE FILE APPEND true | false When TRUE is passed, any existing trace file is appended; if FALSE is passed, any existing file is overwritten. In either case, the trace file is created if it does not already exist. The default is FALSE.
TRACE INDENT INCR integer Controls the number of spaces used to indent nested blocks. The default is four spaces.
TRACE MAX LINE LENGTH integer Sets the maximum line length in the output tracing file before wrapping occurs. The default is 2000000000, which effectively disables wrapping.
TRACE MAX NUM FETCHES integer Sets the number of result set rows that are recorded in the log file. This is also the number of executes that are logged when using the SQLPrepare/SQLExecute combination. The default is 3.
TRACE MAX PARAMETER SIZE integer | ALL | NONE Sets the amount of data, in bytes per column, that is output to the log file for results returned from SQL commands. If you specify ALL, the entire statement is always written. If you specify NONE, columns data tracing is disabled and no results data are logged. If you specify 0, the column names appear in the log, but no data is recorded.
The default value for TRACE MAX PARAMETER SIZE is 256 bytes.
TRACE MAX STATEMENT SIZE integer | ALL Sets the limit on the size of output logged in the log file. If you specify ALL, the entire statement is logged without truncation. The default is 1024 bytes.
TRACE MSG Message Text Places the message text into the trace log file.
TRACE SEPARATOR string Allows a user-specified string to be used instead of the default "==>". Use this to locate a block in the tracing output quickly.
TRACE START TIMER timer name [/i=indent_spaces] [/a=milliseconds] [/q] Starts a timer with the unique identifier supplied. This is displayed in the log file to give a point of reference. The /i parameter controls the Indent Level (number of spaces) for logging events during the time interval. The default for /i is 0.
The /a parameter (optional) allows an upper limit on the time interval prior to giving an alert.
The /q parameter (optional) is used for quiet mode output with no log file output. When /q is absent, output is logged to a file normally.
Note: An implicit TRACE_START_TIMER is called with SQL initialization.
TRACE STATEMENTS   This is the list of statement types to be logged, each separated by a plus (+) sign. To effectively disable logging, set the flag to NONE. The default is ALL.
TRACE STOP TIMER timer name Terminates the timing session initiated with the matching call to TRACE_START_TIMER. (The unique timer name is matched without case-sensitivity.)
When the timing session ends, the indent level is reset and the timing results are logged unless /q (quiet) was used (in TRACE START TIMER) to suppress output to the log file.
Note: An implicit TRACE STOP TIMER is called with the SQL de-initialization (SQLCloseAll or program termination).
WRITE LAST ERROR MSG file_name Writes the last DBMS error message to file_name. This does not write error messages generated by Tivoli Service Desk Developer's Toolkit (for example, INVALID CURSOR HANDLE).

Environment-configurable SQL Tracing Parameters

You can set a subset of the tracing options with environment variables SAISQLTRCFILE and SAISQLTRCENABLED.

SAISQLTRCFILE sets the name of the trace file. For example, to set this environment variable from the command line, you could type:

SET SAISQLTRCFILE=c:\trc\trc.log.

SAISQLTRCENABLED enables or disables SQL tracing. For example, to set this environment variable from the command line, you could type:

SET SAISQLTRCENABLED=TRUE.

Example

KNOWLEDGEBASE Example;
--PUBLIC
ROUTINES
FUNCTION Test:INTEGER IS
VARIABLES
connectionHandle, retCd, dbms, module, caseSensitive,
 caseConvert: INTEGER;
ACTIONS
(*connect to the data source*)
retCd := SQLCommand('CONNECT SOURCE=ADV_ORACLE;QUAL=
 EXAV;UID=TIM;');
IF (retCd < 0) THEN
 EXIT retCd;
END;
(* get the current connection handle*)
connectionHandle := SQLCommand ('GET CURRENT CONNECTION');
IF (retCd < 0) THEN
 EXIT retCd;
END;
(* set the current connection*)
ret := SQLCommand ('SET CONNECTION' & connectionHandle);
IF (retCd < 0) THEN
 EXIT retCd;
END;
(*disconnect from the current connection*)
retCd := SQLCommand ('DISCONNECT');
IF (retCd < 0) THEN
 EXIT retCd;
END;
(*disconnect from a specified connection*)
retCd := SQLCommand ('DISCONNECT' & connectionHandle);
IF (retCd < 0) THEN
 EXIT retCd;
END;
(* get the current DBMS type*)
dbms := SQLCommand ('GET DBMS');
IF (retCd < 0) THEN
 EXIT retCd;
END;
(* get the DBMS type for a specific data source *)
dbms := SQLCommand ('GET DBMS TEST');
IF (retCd < 0) THEN
 EXIT retCd;
END;
(* get SAI_SQL.DLL module type *)
module := SQLCommand ('GET MODULE');
IF (retCd < 0) THEN
 EXIT retCd;
END;
(* get whether or not the DBMS is case-sensitive *)
caseSensitive := SQLCommand ('GET CASE SENSITIVITY');
IF (retCd < 0) THEN
 EXIT retCd;
END;
(* get the current case conversion mode *)
caseConvert := SQLCommand ('GET CASE CONVERSION');
IF (retCd < 0) THEN
 EXIT retCd;
END;
(* set the current case conversion mode for the current connection *)
ret := SQLCommand ('SET CASE CONVERSION upper');
IF (retCd < 0) THEN
 EXIT retCd;
END;
(* save the current qualifier for the current connection *)
ret := SQLCommand ('SAVE CURRENT QUALIFIER');
IF (retCd < 0) THEN
 EXIT retCd;
END;
(* get the date format for the named ADVISOR *)
ret := SQLCommand('GET DATE FORMAT ADVISOR');
IF (retCd < 0) THEN
 EXIT retCd;
END;
(* get the time format for the source to which I am currently connected *)
ret := SQLCommand('GET TIME FORMAT');
IF (retCd < 0) THEN
 EXIT retCd;
END;
(* set the current qualifier for the current connection *)
ret := SQLCommand('SET CURRENT QUALIFIER Fred');
IF (retCd < 0) THEN
 EXIT retCd;
END;
(* restore the current qualifier for the current connection *)
ret := SQLCommand('RESTORE CURRENT QUALIFIER');
IF (retCd < 0) THEN
 EXIT retCd;
END;
(* switch databases *)
ret := SQLCommand('START USING DATABASE advisor');
IF (retCd < 0) THEN
 EXIT retCd;
END;
END; --Test--

Return Codes

Return Code Description
1 Successful completion
(other) See the Tivoli Service Desk 6.0 Developer's Toolkit Script Programming Guide

See Also

See the Tivoli Service Desk 6.0 Developer's Toolkit Script Programming Guide for information on the following:


SQLCommit

Description

Issues an SQL Commit Work command to the SQL interpreter, thereby accepting all changes made during this transaction.

Syntax

FUNCTION SQLCommit: INTEGER;

Cautions

If a prepared statement does not reference an open cursor, a commit causes that statement to close. You should manually close any prepared statements before performing a commit.

Most drivers do not allow you to specify cursors that may span units of work (for instance, declare them WITH HOLD). An exception is the DB2 CLI driver. After a commit operation, all cursors are closed. Therefore, if you perform SQL operations (such as updates) in a fetch loop, you must disable the automatic committing after every SQL statement.

Note: You should close all cursors before performing a commit or rollback operation.

Notes

TSD Script normally commits every SQL statement when it completes. When running a group of SQL statements inside a transaction, however, you must commit or rollback the transaction manually with SQLCommit or SQLRollBack.

After a call to SQLCommit (or SQLRollBack), the current transaction is terminated and automatic committing resumes. You must make another call to SQLBeginWork to start another transaction.

Example

KNOWLEDGEBASE Example;
--PUBLIC
ROUTINES
FUNCTION Test(REF age: INTEGER):INTEGER;
PRIVATE
ROUTINES
FUNCTION Test(REF age: INTEGER):INTEGER IS
VARIABLES
 (* misc *)
 retCd: INTEGER;
ACTIONS
 (* begin a transaction *)
 SQLBeginWork;
 (* delete all employees whose age is > age passed in *)
 retCd := SQLDelete('emp','age > ' & age);
 IF retCd < 0 THEN
 SQLRollBack; (* terminate this transaction *)
 EXIT retCd;
 END;
(* commit changes and release locks *)
 SQLCommit;
END;

Return Codes

Return Code Description
1 Successful completion
(other) See the Tivoli Service Desk 6.0 Developer's Toolkit Script Programming Guide

See Also


SQLCreateSearchString

Description

Given a record whose fields contain search criteria, SQLCreateSearchString creates a valid SQL WHERE clause (without the WHERE).

Syntax

FUNCTION SQLCreateSearchString(VAL tableName: STRING,
                               REF searchString: STRING,
                               VAL searchRec: any record or
                               list of any record): INTEGER;

Cautions

When performing a search that is not case-sensitive, TSD Script automatically calls the appropriate Upper function. In most cases, the search does not use an index. To use indices in searches, you should specify a case-sensitive operator.

Argument Notes

Argument Name Description
tableName The table or view that you search
searchString This contains the search string that SQLCreateSearchString generates
searchRec This parameter is either a single record or a list of records that contain the search criteria

Notes

SQLCreateSearchString is used to create a WHERE clause based on the contents of the record, or list of records, passed in. If a list of records is passed in, the clauses resulting from each list element are combined with the OR logical operator. Within a record, the clauses resulting from each field are combined with the AND logical operator.

Note the following about the SQLCreateSearchString:


Valid Values for Operation Fields

Value Description
(none) If no operator is specified on the Inquiry screen, a case-sensitive search is performed. This allows for the use of indexes.
= Case-insensitive equal (used if the operator field does not exist or is $Unknown:database:querying).
> Case-insensitive greater than.
< Case-insensitive less than.
>= Case-insensitive greater than or equal to.
<= Case-insensitive less than or equal to.
<> Case-insensitive not equal to.
== Case-sensitive equal to.
>> Case-sensitive greater than.
<< Case-sensitive less than.
>= Case-sensitive greater than or equal to.
<= Case-sensitive less than or equal to.
<<>> Case-sensitive not equal to.
IS NULL TRUE if the column is currently NULL. When you use the IS NULL operator, the value of the corresponding column field is irrelevant. However, the field corresponding to the column must be present.
IS NOT NULL TRUE if the column is not currently NULL. When you use the IS NOT NULL operator, the actual value of the corresponding column field is irrelevant. However, the field corresponding to the column must be present.

Field Range Values

_LO and _HI suffixes indicate the low and high range values for the field. For example, if age is a field corresponding to a column of the table, and age_lo is equal to 21 and age_hi is equal to 65, the following search criterion is generated:

'(age >= 21) AND (age <= 65)'. s:

When you use the _LO and _HI range operators, the value of the corresponding column field is irrelevant. However, the field corresponding to the column must be present.

If either the _LO or _HI range operator field does not exist or is $Unknown, the range feature is disabled and the value of the corresponding column field is used. If an _OP operator field exists and its value is <>, the range operators are > and <. Otherwise the defaults (>= and <=) are used.

Wildcard Searches

If the column is a string type, you can perform a wildcard search using the * and ? wildcard characters:

Internally, the * and ? wildcard characters are converted to the SQL LIKE clause compatible to % and _. If an _OP operator field exists and its value is <>, then the rows not matching the search criteria are selected (that is, a NOT LIKE clause is generated).

Example

KNOWLEDGEBASE Example;
--PUBLIC
ROUTINES
FUNCTION Test:INTEGER;
PRIVATE
ROUTINES
FUNCTION Test:INTEGER IS
TYPES
SearchRecord IS RECORD
 ssn: INTEGER;
 title: STRING;
 title_op: STRING;
 salary: REAL;
 age_lo:integer;
 age_hi:integer;
 age_op:string;
 name: STRING;
 name_lo:string;
 name_hi:string;
 name_op:string;
 age: INTEGER;
 married: BOOLEAN;
 MARRIED_OP:STRING;
 rating: REAL;
 addr: STRING;
 addr_OP: STRING;
 bDate: DATE;
 bTime: TIME;
 sysDT: STRING;
 sysDT_op: STRING;
END; (* SearchRecord *)
VARIABLES
r : SearchRecord;
searchString : STRING;
cursor : SQLCursor;
ret : INTEGER;
ACTIONS
 (* These values would normally come from a custom dialog box. *)
 r.title_op := '<>';
 r.title := 'PROGRAMMER'; (* ( (title <> 'PROGRAMMER) AND *)
 r.ssn := 315687890; (* (ssn = 316700056) AND *)
 r.name := 'Pamela*'; (* (name LIKE 'Pamela%') AND *)
 r.name_op := '=='; (* perform a case-sensitive search *)
 r.age := 30; (* ((age >= 13) AND (age <= 49)) AND *)
 r.age_lo := 13;
 r.age_hi := 49;
 r.age_op := '=';
 r.MARRIED_OP := 'is NOT null'; (* (married IS NOT NULL) AND *)
 r.rating := 43.457; (* (rating = 43.457) *)
 r.addr := '5051 ?. Morris *'; (* (addr NOT LIKE '5050 _. Morris %') AND *)
 r.bTime := {14,09,16}:TIME; (* (bTime = '14:09:16') AND *)
 r.bDate := {06,26,1985}:DATE; (* (bDate= '06/26/1985') AND *)
 r.sysDT := '1961-10-10-14.09.16.00'; (* (sysDT >= '1961-10-10-14.09.16.00)) *)
 r.sysDT_op := '>=';
 SQLCreateSearchString('emps',searchString,r);
 ret := SQLSelect(cursor, 'emps',searchString);
 IF ret < 0 THEN
 Exit ret;
 END;
 ret := SQLFetch(cursor,r);
 WHILE ret > 0 DO
 (* process r... *)
 ret := SQLFetch(cursor);
END;
 SQLCloseCursor(cursor);
END;

Return Codes

Return Code Description
1 Successful completion
(other) See the Tivoli Service Desk 6.0 Developer's Toolkit Script Programming Guide

SQLDelete

Description

Deletes a specified row from a table.

Syntax

SQLDelete(tableName: STRING,
          queryString: STRING [,expressionList...]):
          INTEGER;

Caution: Since one call to SQLDelete can delete many rows, it is important to specify your WHERE clause carefully.

Argument Notes

Argument Name Description
tableName The name of the table from which to delete.
queryString The WHERE clause.
expressionList This a list of zero or more expressions, each separated by commas. For example:
3.4, age + 100, name, 'Fred', Sin(45), ...

Notes

The WHERE keyword in the query string is optional.

SQLDelete allows for parameter marker substitution. To use parameter markers, insert a question mark (?) in the statement string. This serves as a placeholder for a value to be substituted later.

The value comes from the optional expression list following the statement string. The number of expressions must match the number of parameter markers (?), and the order of the expressions must match that of the markers.

Tivoli Service Desk Developer's Toolkit is unable to provide robust type checking with parameter markers. For example:

retCd = SQLDelete('emp','WHERE name = ?',name);

The string contained in the variable name does not have to be single quoted. You do not need to use variables. For example:

retCd = SQLDelete('emp','WHERE name = ?','Smith');

Example

KNOWLEDGEBASE Example;
--PUBLIC
ROUTINES
FUNCTION Test(REF AGE: INTEGER):INTEGER;
PRIVATE
ROUTINES
FUNCTION Test(REF AGE: INTEGER):INTEGER IS
VARIABLES
retCd: INTEGER;
ACTIONS
(* delete all employees from Indiana, and whose age is greater than *)
(* the age passed into this function. *)
retCd := SQLDelete('emp','(state = ''IN'') AND (age >> ' & age & ')');
IF (retCd < 0) THEN
 EXIT retCd;
END;
END;

Return Codes

Return Code Description
1 Successful completion
(other) See the Tivoli Service Desk 6.0 Developer's Toolkit Script Programming Guide

See Also


SQLDeleteCurrent

Description

Deletes the row at the current cursor position.

Syntax

SQLDeleteCurrent(cursor: SQLCURSOR): INTEGER;

Argument Notes

Argument Name Description
cursor The cursor must be previously opened by a call to SQLSelect

Notes

SQLDeleteCurrent is only supported in the DB2 CLI driver.

Even though you do not update, you should use the FOR UPDATE OF clause in SQLSelect when you delete a row. The SQL interpreter then applies locks appropriately. If no FOR UPDATE OF clause is given, the select is treated as read-only.

Unlike SQLUpdateCurrent, where each updated column must be specified in the FOR UPDATE OF clause, it is sufficient to specify only one column when using SQLDeleteCurrent. You must specify at least one valid column; you cannot use *.

Example

KNOWLEDGEBASE Example;
--PUBLIC
ROUTINES
FUNCTION Test(REF age: INTEGER):INTEGER;
PRIVATE
ROUTINES
FUNCTION Test(REF age: INTEGER):INTEGER IS
VARIABLES
(* columns *)
name: STRING;
(* misc *)
cursor: SQLCursor;
retCd: INTEGER;
ACTIONS
SQLBeginWork
(* select all employees from Indiana, and whose age is greater than *)
(* the age passed into this function. *)
retCd := SQLSelect(cursor,'name from emp WHERE (state =
 ''IN'') AND ' & '(age > ' & age & ')
 FOR UPDATE OF NAME');
IF (retCd < 0) THEN
 SQLRollBack; (* terminate this transaction *)
 EXIT retCd;
END;
retCd := SQLFetch(cursor,name);
WHILE (retCd > 0) DO
 (* possibly perform some operation(s) based on "name" *)
 (* now delete this row *)
 retCd := SQLDeleteCurrent(cursor);
 IF (retCd < 0) THEN
 SQLCloseCursor(cursor);
 SQLRollback; (* reverse any changes, and release locks *)
 EXIT retCd;
 END;
 (* get the next record *)
 retCd := SQLFetch(cursor);
END;
 SQLCloseCursor(cursor);
 SQLCommit; (* accept changes and release locks *)
END;

Return Codes

Return Code Description
1 Successful completion
(other) See the Tivoli Service Desk 6.0 Developer's Toolkit Script Programming Guide

See Also

SQLDelete


SQLExecute

Description

Executes a prepared statement.

Syntax

SQLExecute(statement: STATEMENT [,expressionList...]): INTEGER;

Cautions

A rollback operation automatically closes all open (prepared) statements and cursors (see SQLRollBack). A commit operation automatically closes all open statements that do not reference an open cursor.

Note: You are limited to 20 open statements at any time.

Argument Notes

Argument Name Description
statement This is the statement in its prepared form, obtained from SQLPrepare.
expressionList This a list of zero or more expressions, each separated by commas. For example: 3.4, age + 100, name, 'Fred', $Sin(45), ...

Notes

If you execute a command many times in a loop, it may be quicker to prepare the command once using SQLPrepare and then execute the prepared version in the loop using SQLExecute.

SQLExecute allows for parameter marker substitution. To use parameter markers, insert a question mark (?) in the statement string.

This serves as a placeholder for a value to be substituted later. The value comes from the optional expression list following the statement string. The number of expressions must match the number of parameter markers (?), and the order of the expressions must match that of the markers.

Tivoli Service Desk Developer's Toolkit is unable to provide robust type checking with parameter markers. For example:

retCd = SQLPrepare(stmt, 'UPDATE emp SET name= ? WHERE ssn=?);

The string contained in the variable name does not have to be single quoted. You do not need to use variables. For example:

retCd = SQLExecute (stmt, 'Smith', 317689630);

Parameter markers enable you to perform certain operations (such as this update) in tight loops using the efficient SQLPrepare/SQLExecute combination instead of SQLUpdate, SQLExecuteImmediate, and so forth.

Example

KNOWLEDGEBASE Example;
--PUBLIC
ROUTINES
FUNCTION Test(REF users: LIST OF STRING):INTEGER;
PRIVATE
ROUTINES
FUNCTION Test(REF users: LIST OF STRING):INTEGER IS
VARIABLES
stmt: SQLStatement;
retCd: INTEGER;
ACTIONS
SQLBeginWork; (* start a transaction *)
(* insert the user names passed in *)
retCd := SQLPrepare(stmt,'INSERT INTO USERS VALUES (?)');
IF (retCd < 0) THEN
 SQLRollBack; (* terminate this transaction *)
 EXIT retCd;
END;
FOR users DO
 retCd := SQLExecute(stmt,users[$current]);
 IF (retCd < 0) THEN
 SQLRollBack; (* reverse changes and release locks *)
 EXIT retCd;
 END;
 END; (* for *)
 SQLCloseStatement(stmt);
 SQLCommit; (* accept changes and release locks *)
END;

Return Codes

Return Code Description
1 Successful completion
(other) See the Tivoli Service Desk 6.0 Developer's Toolkit Script Programming Guide

See Also


SQLExecuteImmediate

Description

Executes an SQL statement directly from its string form.

Syntax

SQLExecuteImmediate(sqlCommand: STRING [,expressionList...]): INTEGER;

Argument Notes

Argument Name Description
sqlCommand This must be a complete, syntactically correct SQL command (statement) that can be prepared dynamically and that is not a SELECT command.
expressionList This a list of zero or more expressions, each separated by commas. For example: 3.4, age + 100, name, 'Fred', $Sin(45), ...

Notes

SQLExecuteImmediate allows you to execute almost any SQL command, except SELECT commands and commands that can not be prepared dynamically.

If you execute a command many times in a loop, it may be quicker to prepare the command once and then execute the prepared version in the loop.

You may use Tivoli Service Desk Developer's Toolkit's built-in string handling functions to help you build the command string.

Tivoli Service Desk Developer's Toolkit does not perform qualification expansion in SQLExecuteImmediate. You can still use $QUAL and $SYSQUAL.

Note: SQLExecuteImmediate allows for parameter marker substitution.

To use parameter markers, insert a question mark (?) in the statement string. This serves as a placeholder for a value to be substituted later. The value comes from the optional expression list following the statement string. The number of expressions must match the number of parameter markers (?), and the order of the expressions must match that of the markers.

Tivoli Service Desk Developer's Toolkit is unable to provide robust type checking with parameter markers. For example:

retCd = SQLExecuteImmediate('UPDATE emp SET name = ? WHERE ssn = ?',name,ssn);

The string contained in the variable name does not have to be single quoted. You do not need to use variables. For example:

retCd =SQLExecuteImmediate('UPDATE emp SET name = ? WHERE ssn?','Smith', 316798965);

Example

KNOWLEDGEBASE Example;
--PUBLIC
ROUTINES
FUNCTION Test:INTEGER;
PRIVATE
ROUTINES
FUNCTION Test:INTEGER IS
VARIABLES
(* table columns of emp *)
sDate: DATE; (* 'integer' *)
name: STRING; (* 'char(15)' *)
age: INTEGER; (* 'smallint' *)
ssn: INTEGER; (* 'integer' *)
married: BOOLEAN; (* 'smallint' *)
salary: REAL; (* 'float' *)
rating: REAL; (* 'decimal(8,3)' *)
addr: STRING; (* 'varchar(81)' *)
sp: SPACE; (* 'varchar(2500) *)
lsp: SPACE; (* 'long varchar for bit data *)
bDate: DATE; (* 'date' *)
bTime: TIME; (* 'time' *)
sTime: TIME; (* 'integer' *)
sysDT: STRING; (* 'timestamp' *)
(* misc *)
retCd: INTEGER;
s: STRING;
ACTIONS
 SQLExecuteImmediate('DROP TABLE tsql');
 s := 'CREATE TABLE tsql (name CHAR(15), ' &
 'age SMALLINT, ' &
 'married SMALLINT, ' &
 'ssn INTEGER, ' &
 'salary FLOAT, ' &
 'rating DECIMAL(8,3), ' &
 'addr VARCHAR(81), ' &
 'sp VARCHAR(2500), ' &
 'lsp LONG VARCHAR FOR BIT DATA, ' &
 'bdate DATE, ' &
 'btime TIME, ' &
 'sdate INTEGER, ' &
 'stime INTEGER, ' &
 'sysDT TIMESTAMP) ' &
 'PRIMARY KEY(ssn))';
 retCd := SQLExecuteImmediate(s);
 EXIT retCd;
END;

Return Codes

Return Code Description
1 Successful completion
(other) See the Tivoli Service Desk 6.0 Developer's Toolkit Script Programming Guide

See Also

See your SQL documentation for additional information.


SQLFetch

Description

Fetches the next row of the cursor into the specified parameters.

Syntax

SQLFetch(cursor: SQLCURSOR [,parameterList...]): INTEGER;

Caution: If you use the preinitialized fetch optimization, you should ensure that the parameters (column variables) listed in the first (initializing) fetch are still visible when the subsequent (initialized) fetches are performed. This is always the case when the initializing fetch, the parameters, and the initialized fetches are all in the same function.

Note: A rollback closes all cursors. If a rollback occurs in the middle of a fetch loop, the next call to SQLFetch fails.

Argument Notes

Argument Name Description
cursor The cursor must be previously opened by a call to SQLSelect.
parameters This is a comma delimited list of zero or more parameters (column variables) whose names must match the corresponding column names (see the Notes for this statement).

Notes

SQLFetch has two forms for optimization. The first form has all of the parameters (column variables) listed after the cursor; the second form has only the cursor. The first form is referred to as an initializing fetch since it initializes Tivoli Service Desk Developer's Toolkit to allow for rapid insertions into the parameters. The second form assumes that the parameters to be fetched are the same as those specified in the most recent call to a fetch of the first form. This second form is called the initialized or preinitialized fetch.

After Tivoli Service Desk Developer's Toolkit is aware of the fetching parameters, it is considerably faster if you do not specify the parameters again. (This causes Tivoli Service Desk Developer's Toolkit to reinitialize the parameters unnecessarily.)

Note: For more information on using preinitialized fetches, see the Cautions for this statement.

Matching Parameter Names with Column Names

The parameter names must match the respective column names. Any parameter whose name does not match a column name is ignored. An exception occurs if the column name begins with SQLColumn_ and ends with the column number that corresponds to the position in the select list. With this method, you can access the results of column (aggregate) functions.
For example, if the select string is

SELECT count(*) FROM emp

and you declare SQLColumn_1 as a variable of type INTEGER, you could fetch the count with:

SQLFetch(cursor,$SQLColumn_1);

The order of the $SQLColumn_xx parameters does not matter within the parameter list.

For example, if name is a character column, and age is a numeric column, and your select string is:

SELECT name,age FROM emp

and you declared the $SQLColumn_xx variables as

$SQLColumn_1: STRING; $SQLColumn_2: INTEGER;

then the following fetches name into $SQLColumn_1, and age into $SQLColumn_2:

SQLFetch(cursor,$SQLColumn_1,$SQLColumn_2);

and so would:

SQLFetch(cursor,$SQLColumn_2,$SQLColumn_1);

Although this example uses STRING and INTEGER, you can define any valid TSD Script data type for $SQLColumn except LIST or WINDOWS.

Passing Records as Parameters

You may pass a record as a parameter. In this case, the field names must match the table's column names or they are ignored.

Example

KNOWLEDGEBASE Example;
--PUBLIC
ROUTINES
FUNCTION Test(REF age: INTEGER):INTEGER;
PRIVATE
ROUTINES
FUNCTION Test(REF age: INTEGER):INTEGER IS
VARIABLES
VARIABLES
(* columns *)
name: STRING;
(* misc *)
cursor: SQLCursor;
retCd: INTEGER;
ACTIONS
SQLBeginWork; (* start a transaction *)
(* select all employees from Indiana, and whose age is greater *)
(* than the age passed into this function. *)
retCd := SQLSelect(cursor,'name from emp WHERE (state = ''IN'') AND ' &
 '(age > ' & age & ') FOR UPDATE OF NAME');
IF (retCd < 0) THEN
 SQLRollBack; (* terminate the transaction *)
 EXIT retCd;
END;
retCd := SQLFetch(cursor,name);
WHILE (retCd > 0) DO
 (* possibly perform some operations based on "name"
 .
 .
 .
 *)
 (* set name to uppercase *)
 name := StrUpper(name);
 (* now update this row *)
 retCd := SQLUpdateCurrent(cursor,name);
 IF (retCd < 0) THEN
 SQLCloseCursor(cursor);
 SQLRollback; (* reverse any changes, and release locks *)
 EXIT retCd;
 END;
 (* get the next record *)
 retCd := SQLFetch(cursor);
END;
SQLCloseCursor(cursor);
SQLCommit; (* accept changes and release locks *)
END;

Return Codes

Return Code Description
1 Successful completion
(other) See the Tivoli Service Desk 6.0 Developer's Toolkit Script Programming Guide

See Also


SQLFormat

Description

Converts a data value to a string in the appropriate data format for the DBMS to which you are currently connected.

Syntax

SQLFormat(value: SIMPLE EXPRESSION): STRING;

Cautions

The date and time formats required by your DBMS may differ from the date and time formats that are used for display. For example, the default Oracle date format is DD-MON-YY, while the default Tivoli Service Desk Developer's Toolkit date format on a United States workstation is MM/DD/YYYY.

Argument Notes

Argument Name Description
value This must be a simple type (such as DATE or STRING)

Notes

SQLFormat returns a formatted string, not a return code, that indicates whether an operation was successful. If the value passed in is $Unknown, the string 'NULL' is returned. The specific formatting of the string depends on the type of the string:

Return Codes

The formatted string is returned. If the value passed in is $Unknown, the string 'NULL' is returned.

Example

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;

See Also

For more information on the date and time formats, and the integer and real formats, see "Data Type Format Flags" in Chapter 3.

For more information on the sai_sql.cfg file, see the Tivoli Service Desk 6.0 Developer's Toolkit Script Programming Guide.


SQLGetCursorName

Description

Gets the actual cursor name used internally by the SQL Interpreter for the specified cursor.

Syntax

SQLGetCursorName(cursor: SQLCURSOR, cursorName: STRING):
 INTEGER;

Argument Notes

Argument Name Description
cursor The cursor must be previously opened by a call to SQLSelect
cursorName The name of the cursor

Notes

SQLGetCursorName is supported only in the DB2 CLI driver.

Example

KNOWLEDGEBASE Example;
--PUBLIC
ROUTINES
FUNCTION Test(REF age: INTEGER):INTEGER;
PRIVATE
ROUTINES
FUNCTION Test(REF age: INTEGER):INTEGER IS
VARIABLES
(* columns *)
name: STRING;
(* misc *)
cursor: SQLCursor;
cursorName: STRING;
retCd: INTEGER;
ACTIONS
(* select all employees from Indiana, and whose age is
 greater *)
(* than the age passed into this function. *)
retCd := SQLSelect(cursor,'name from emp WHERE (state =
 ''IN'') AND ' & '(age > ' & age & ')
 FOR UPDATE OF NAME');
IF (retCd < 0) THEN
 EXIT retCd;
END;
 SQLGetCursorName(cursor,cursorName);
 retCd := SQLFetch(cursor,name);
WHILE (retCd > 0) DO
 (* possibly perform some operations based on "name" and "cursorName"
 .
 .
 .
 *)
 (* get the next record *)
 retCd := SQLFetch(cursor);
END;
SQLCloseCursor(cursor);
END;

Return Codes

Return Code Description
1 Successful completion
(other) See the Tivoli Service Desk 6.0 Developer's Toolkit Script Programming Guide

See Also


SQLGetOption

Description

Retrieves the value of a configuration or context state option.

Syntax

SQLGetOption(VAL option: STRING,
             REF optionValue : <ANY SIMPLE VARIABLE>
             [, VAL srcOrConn : <INTEGER OR STRING EXPRESSION>])
             : INTEGER;

Caution: Unlike the options in the SQL configuration file (sai_sql.cfg), the option names passed to SQLGetOption use spaces, not underscores, for separation.

Note: Most of the options that you can get with SQLGetOption can be set with SQLSetOption.

Argument Notes

Argument Name Description
option The name of the option (like: 'DATE FORMAT'). See the Notes section for a complete list.
optionValue The variable that is set to the value of the option;
srcOrConn This argument is optional, and if provided must contain either a source name (STRING) or a connection handle (INTEGER). If this argument is not passed, then $CURRENT is assumed.

Notes

The function returns a success or error code. The most common error codes are: INVALID_OPTION and NOT_CONNECTED (for those options that require you to be connected, which is the case for most options).

This function incorporates previous functionality in SQLCommand ('GET ... '); with the advantage of being able to return STRING data.

The following are options that can be specified with this command:

Option Description
'BIND PARAMETER BUFFER SIZE' Returns the buffer size used for parameter binding.
'BIND PARAMETER FILL OUTPUT NULL VALS' Returns TRUE if output values that are NULL are set to zero.
'BIND PARAMETER PAD OUTPUT STRINGS' Returns TRUE if the output parameter strings are blank padded to the specified precision.
'BOOLEAN FORMAT' Returns the format used to store Boolean values in the DBMS. Refer to Chapter 3 in this guide for more information about data formatting.
'BOOLEAN PARAMETER PRECISION' Returns the precision used for binding Boolean values.
'BOOLEAN PARAMETER TYPE' Returns the X/Open data type used for binding Boolean values.
'BOOLEAN STORAGE TYPE' Returns the storage type used to represent Booleans. Possible values are:
1 - INTEGER
2 - STRING
3 - BOOLEAN
'CASE CONVERSION' Returns the case conversion used by Tivoli Service Desk Developer's Toolkit for object names (does not apply to data). Possible values are:
1 - Upper
2 - Lower
3 - None
'CASE SENSITIVITY' Returns TRUE if the database has case-sensitive object names (does not apply to the case-sensitivity of DATA).
'CATALOG CASE CONVERSION' Returns the case conversion used by Tivoli Service Desk Developer's Toolkit for data in the catalog views (SAI_SYSCOLUMNS and SAI_SYSTABLES). Possible values are:
1 - Upper
2 - Lower
3 - None
'CHECK DRIVER' Returns TRUE if Tivoli Service Desk Developer's Toolkit performs a check to see if the driver is listed as unsupported.
'COLUMN CATALOG METHOD' Returns the method used by Tivoli Service Desk Developer's Toolkit to obtain the column catalog values. Possible values are:
1 - Use standard method to query column
catalog.
2 - Use a DBMS specific query to
colum catalog.
3 - Use a ODBC SQLColumn() function.
'COMMIT SELECTS' Applicable if manual commits is TRUE. Returns whether or not commits are performed after read-only units of work.
'CONFIG FILE' Returns the name of the configuration file used by Tivoli Service Desk Developer's Toolkit when connecting. You do not have to be connected to get this value.
'CONNECTION' Returns the connection handle. You can get this option with SQLGetOption but you cannot set it with SQLSetOption.
'CURRENT COL BUFFER' Returns the size of the current column buffer.
'CURRENT ROW BUFFER' Returns the size of the current row buffer.
'DATE FORMAT' Returns the DBMS's date format. Refer to Chapter 3 in this guide for more information about data formatting.
'DATE PARAMETER PRECISION' Returns the precision used for binding date values.
'DATE PARAMETER TYPE' Returns the X/Open data type used for binding date values.
'DATE STORAGE TYPE' Returns the storage type used to represent dates. Possible values are:
1 - INTEGER
2 - STRING
6 - DATE
'DBMS' Returns the DBMS (number). Currently recognized DBMSs are:
0 - DB2/2, DB2/6000, DB2/NT, DB2/UX
1 - ORACLE
2 - DB2
3 - SQL SERVER 4.x
4 - SYBASE
5 - NETWARE SQL
6 - GUPTAN SQLBASE
7 - XDB
8 - DBASE
9 - BTRIEVE
10 - ASCII TEXT
11 - EXCEL
12 - SYBASE 10 (and above)
13 - PARADOX
14 - INGRES3
15 - INGRES4
16 - INFORMIX4
17 - INFORMIX5
18 - TANDEM
19 - PROGRESS
20 - HP ALLBASE
21 - WATCOM
22 - FULCRUM
23 - MS SQL SERVER 6.x
24 - (OTHER)
'DBMS STRING' Returns the following database names:
DB2, ORACLE, SQLSERVER, SYBASE, or DBMS_OTHER.
'DSN' Returns the current ODBC data source name (only if connecting to an ODBC data source).
'HAS TRANSACTIONS' Returns TRUE if the DBMS supports transactions
'IN TRANSACTION' Returns TRUE if a transaction has started with SQLBeginWork and has not committed or rolled back. You can get this option with SQLGetOption but it cannot be set with SQLSetOption.
'INTEGER FORMAT' Returns the DBMS's integer format. Refer to Chapter 3 in this guide for more information about data formatting.
'INTEGER PARAMETER PRECISION' Returns the precision used for binding integer values.
'INTEGER PARAMETER TYPE' Returns the X/Open data type used for binding integer values.
'INTEGER STORAGE TYPE' Returns the storage type used to represent integers. Possible values are:
1 - INTEGER
2 - STRING
'LAST ERROR MSG' Returns the last DBMS error message text.
'MANUAL COMMITS' Returns TRUE if SAI_SQL (instead of the driver) controls commit behavior.
'MAX LITERAL LEN' Returns the value of the maximum string literal length after which Tivoli Service Desk Developer's Toolkit performs parameter binding on insert and update.
'MODULE' For backward compatibility only. Returns the module type. The only possible value for Tivoli Service Desk Developer's Toolkit 5.0 is 5 (X/Open).
'MODULE TYPE' For backward compatibility only. Returns the module type. The only possible value for Tivoli Service Desk Developer's Toolkit 5.0 is 5 (X/Open).
'MULTI CONNECT' Returns TRUE if the DBMS supports multiple connections from a client process.
'MULTI CONNECT REQUIRED' Returns TRUE if the DBMS requires multiple connections in order to service multiple, simultaneous ("nested") SQL statements.
'NEEDS CODEPAGE TRANSLATION' Returns TRUE if Tivoli Service Desk Developer's Toolkit is to perform ANSI-to- OEM and OEM-to-ANSI code page translations (Windows only).
'PROCESS RESULTS ON FREESTMT' Returns TRUE if Tivoli Service Desk Developer's Toolkit ensures complete result set flushing by fetching until no more rows are returned.
'QUAL' Returns the qualifier (usually the creator or owner) used to access your data tables.
'REAL FORMAT' Returns the DBMS's real number format. Refer to Chapter 3 in this guide for more information about data formatting.
'REAL PARAMETER PRECISION' Returns the precision used for binding real values.
'REAL PARAMETER SCALE' Returns the scale used for binding real values.
'REAL PARAMETER TYPE' Returns the X/Open data type used for binding real values.
'REAL STORAGE TYPE' Returns the storage type used to represent reals. Possible values are:
2 - STRING
3 - REAL
'SHOW WARNINGS' Returns TRUE if warning message boxes are displayed.
'SOURCE' Returns the name of the currently connected SAI_SQL data source.
'STRING FORMAT' Returns the DBMS's string format. Refer to Chapter 3 in this guide for more information about data formatting.
'STRING PARAMETER PRECISION' Returns the precision used for binding string values.
'STRING PARAMETER TYPE' Returns the X/Open data type used for binding string values.
'SYSQUAL' Returns the qualifier required to access the system catalog tables.
Note: Tivoli Service Desk Developer's Toolkit versions 4.1.0 and above use SAI-created wrapper views for catalog information where SYSQUAL is used to create views (e.g., EXAV), not the normal system qualifier (e.g., SYSIBM).
'TABLE CATALOG METHOD' Returns the method used by Tivoli Service Desk Developer's Toolkit to obtain the table catalog values. Possible values are:
1 - Use standard method to query column
catalog.
2 - Use a DBMS specific query to column
catalog.
3 - Use a ODBC SQLTable() function.
'TIME FORMAT' Returns the DBMS's time format. Refer to Chapter 3 in this guide for more information about data formatting.
'TIME PARAMETER PRECISION' Returns the precision used for binding time values.
'TIME PARAMETER TYPE' Returns the X/Open data type used for binding time values.
'TIME STORAGE TYPE' Returns the storage type used to represent times. Possible values are:
1 - INTEGER
2 - STRING
5 - TIME
'TRACE ALERT DESTINATION' Returns the file name of the alert file (if alerts are being used).
'TRACE APPEND' Returns TRUE if the trace file is to be appended to (instead of overwritten) each time.
'TRACE AUTO FLUSH' Returns TRUE if the trace output is flushed to the trace file after every write.
'TRACE ENABLED' Returns TRUE if tracing has been enabled.
'TRACE FILE' Returns the name of the trace output file.
'TRACE INDENT INCR' Returns the indent increment being used.
'TRACE MAX LINE LENGTH' Returns the maximum line length after which wrapping occurs.
'TRACE NUM EXECUTES' Returns the maximum number of loggedSQLFetch and SQLExecute statements.
'TRACE PARAM SIZE' Returns the maximum length for parameters that are written to the file.
'TRACE SEPARATOR' Returns the trace output separator being used.
'TRACE STATEMENT SIZE' Returns the maximum length for statements that are written to the file.
'TRACE STATEMENTS' Returns the bitmask of statements that are currently being traced.
'TRANSLATE FUNC' Returns the name of the upper case function for your DBMS.
'UPDATE LOCK COLUMN' Returns the column (name) used for the passive concurrency lock (usually MODIFY_DATETIME).
'UPDATE LOCK ERROR FILTER' Returns the error filter level used while the passive concurrency update lock is performed. Possible values are:
0 - Fatal (the default)
1 - Error
2 - Warning
3 - Informational
'UPDATE LOCK STYLE' Returns the locking style used for passive concurrency updates. Possible values are:
1 - SELECT FOR UPDATE
2 - UPDATE
3 - UPDATE CONDITIONAL
'UPDATE MODE' Returns the passive concurrency update mode (algorithm). Possible values are:
1 - SELECT
2 - DBMS OPTIMISTIC
3 - NONE

Return Codes

Return Code Description
1 Successful completion
(other) See the Tivoli Service Desk 6.0 Developer's Toolkit Script Programming Guide

Examples

 KNOWLEDGEBASE Example;
 --PUBLIC
 ROUTINES
 PROCEDURE Example;
 PRIVATE
 ROUTINES
 PROCEDURE Example IS
 VARIABLES
 retCd : INTEGER;
 dateFormat : INTEGER;
 lastErrorMsg : STRING;
 hConn : INTEGER;
 ACTIONS
 retCd := SQLGetOption('LAST ERROR MSG' lastErrorMsg);
 retCd := SQLGetOption('DATE FORMAT', dateFormat,
 'ADVISOR');
 retCd := SQLGetOption('CONNECTION', hConn);
 retCd := SQLGetOption('DATE FORMAT', dateFormat, hConn);
 END;

See Also


SQLInsert

Description

Inserts a new row into the specified table.

Syntax

SQLInsert(tableName: STRING [,parameters...]): INTEGER;

Argument Notes

Argument Name Description
tableName The name of the table into which you are inserting.
parameters This is a comma-delimited list of zero or more parameters (column variables) whose names must match the corresponding column names (see the Notes for this statement for more information).

Notes

The parameters (column variables) passed into SQLInsert must have the same name as the actual columns of the table. If a parameter name does not match any column name, the parameter is ignored.

Note: You do not need to pass in parameters for all columns of the table, but only for the ones created as NOT NULL.

Do not try to insert an Tivoli Service Desk Developer's Toolkit parameter whose value is $Unknown into a table where the corresponding column was created with the NOT NULL option. You can pass a record as a parameter. In this case, the field names must match the table's column names, or they are ignored.

Example

KNOWLEDGEBASE Example;
--PUBLIC
ROUTINES
FUNCTION Test:INTEGER;
PRIVATE
ROUTINES
FUNCTION Test:INTEGER IS
VARIABLES
(* column names *)
name: STRING;
ssn: INTEGER;
(* misc *)
retCd: INTEGER;
ACTIONS
(* set the column values *)
name := 'Fred';
ssn := 316768990;
(* insert new row *)
retCd := SQLInsert('emp',name,ssn);
IF (retCd < 0) THEN
 EXIT retCd;
END;
END;

Return Codes

Return Code Description
1 Successful completion
(other) See the Tivoli Service Desk 6.0 Developer's Toolkit Script Programming Guide

See Also


SQLPrepare

Description

SQLPrepare prepares an SQL command string for later execution. This command string, once prepared, is called a prepared statement. If the SQLPrepare operation is successful, the variable you pass in as the statement parameter contains a valid statement handle.

Syntax

SQLPrepare(statement: SQLSTATEMENT, sqlCommand: STRING):
           INTEGER;

Cautions

A rollback operation automatically closes all open (prepared) statements and cursors. A commit operation automatically closes all open statements that do not reference an open cursor declared WITH HOLD (see SQLCommit).

Argument Notes

Argument Name Description
statement The statement handle that is used in the subsequent SQLExecute statement
sqlCommand This is a complete, syntactically correct SQL statement

Notes

SQLPrepare allows you to prepare almost any SQL command, with the exception of SELECT commands and commands that cannot be prepared dynamically.

If you execute a command (such as SQLDelete) many times in a loop, it is quicker to prepare the command once and execute the prepared version with SQLExecute than to execute the command directly each time.

Note: You can use Tivoli Service Desk Developer's Toolkit's built-in string handling functions to help you build the command string to be prepared.

Tivoli Service Desk Developer's Toolkit does not perform qualification expansion in SQLPrepare. However, you can still use $QUAL and $SYSQUAL.

Example

KNOWLEDGEBASE Example;
--PUBLIC
ROUTINES
FUNCTION Test(REF users: LIST OF STRING):INTEGER;
PRIVATE
ROUTINES
FUNCTION Test(REF users: LIST OF STRING):INTEGER IS
VARIABLES
stmt: SQLStatement;
retCd: INTEGER;
ACTIONS
SQLBeginWork; (* start a transaction *)
 (* insert the user names passed in *)
retCd := SQLPrepare(stmt,'INSERT INTO USERS VALUES (?)');
IF (retCd < 0) THEN
 SQLRollBack; (* terminate the transaction *)
 EXIT retCd;
END;
 FOR users DO
 retCd := SQLExecute(stmt,users[$current]);
 IF (retCd < 0) THEN
 SQLRollBack; (* reverse changes and release locks *)
 EXIT retCd;
 END;
END; (* for *)
 SQLCloseStatement(stmt);
SQLCommit; (* accept changes and release locks *)
END;

Return Codes

Return Code Description
1 Successful completion
(other) See the Tivoli Service Desk 6.0 Developer's Toolkit Script Programming Guide

See Also

For additional information, see your SQL documentation.


SQLRollBack

Description

Issues a ROLLBACK WORK command to the SQL interpreter, thereby reversing/undoing all changes made during this transaction.

Syntax

FUNCTION SQLRollBack: INTEGER;

Caution: A rollback causes all open cursors and prepared statements to close automatically.

Notes

SQLRollBack terminates the current transaction. You must call SQLBeginWork again if you want another transaction started; otherwise, TSD Script resumes the automatic committing of each SQL statement.

Use SQLRollBack to reverse small, failed transactions that do not involve user input. Avoid using a rollback as a generic undo for user interface operations.

Example

KNOWLEDGEBASE Example;
--PUBLIC
ROUTINES
FUNCTION Test(REF ssnList: LIST OF INTEGER):INTEGER;
PRIVATE
ROUTINES
FUNCTION Test(REF ssnList: LIST OF INTEGER):INTEGER IS
VARIABLES
(* misc *)
retCd: INTEGER;
ACTIONS
(* Start a logical transaction *)
SQLBeginWork;
(* delete all employees whose ssn is in the ssn list passed in *)
FOR ssnList DO
 retCd := SQLDelete('emp','ssn = ' & ssnList[$CURRENT]);
 IF retCd < 0 THEN
 SQLRollBack; (* reverse the changes and release locks *)
 EXIT retCd;
 END;
END;
(* commit changes and release locks *)
SQLCommit;
END;

Return Codes

Return Code Description
1 Successful completion
(other) See the Tivoli Service Desk 6.0 Developer's Toolkit Script Programming Guide

See Also


SQLSelect

Description

Opens a cursor based on the rows specified for later fetching by SQLFetch.

Syntax

SQLSelect(cursor: SQLCURSOR,
 queryString: STRING [,expressionList...]): INTEGER;

Cautions

You are limited to 20 open cursors at any time.

A rollback operation automatically closes all cursors.

Most drivers do not allow you to specify cursors that may span units of work (i.e., declare them WITH HOLD). An exception is the DB2 CLI driver. After a commit operation all cursors are closed. Therefore, if you perform SQL operations (such as updates) in a fetch loop, you must disable the automatic committing after every SQL statement.

Note: You should close all cursors before performing a commit or rollback operation.

Argument Notes

Argument Name Description
cursor The cursor handle for the query.
queryString This is the select string. It must be complete and syntactically correct.
expressionList... This a list of zero or more expressions, each separated by commas. For example:
3.4, age + 100, name, 'Fred', $Sin(45), ...

Notes

If you update or delete any fetched rows, you should use a FOR UPDATE OF clause to make your intentions clear to the SQL interpreter. This is required if you want to do an SQLUpdateCurrent later.

It is also good practice if you use SQLDeleteCurrent. If you do not use a FOR UPDATE OF clause, the select is treated as read-only.

Note: For simple select strings (like the example), the SELECT keyword is optional.

SQLSelect allows for parameter marker substitution. To use parameter markers, insert a question mark (?) in the statement string. This serves as a placeholder for a value to be substituted later. The value comes from the optional expression list following the statement string. The number of expressions must match the number of parameter markers (?), and the order of the expressions must match that of the markers.

Tivoli Service Desk Developer's Toolkit is unable to provide robust type checking with parameter markers. For example:

retCd = SQLSelect(cursor,'SELECT * FROM emp WHERE name = ?',name);

The string contained in the variable name does not have to use single quotes. You do not need to use variables. For example:

retCd = SQLSelect(cursor,'SELECT * FROM emp WHERE name = ?', 'Smith');

Example

KNOWLEDGEBASE Example;
--PUBLIC
ROUTINES
FUNCTION Test(REF age: INTEGER):INTEGER;
PRIVATE
ROUTINES
FUNCTION Test(REF age: INTEGER):INTEGER IS
VARIABLES
(* columns *)
name: STRING;
(* misc *)
cursor: SQLCursor;
retCd: INTEGER;
ACTIONS
SQLBeginTran; (* start a transaction *)
(* select all employees from Indiana, and whose age is *)
(* greater than the age passed into this function. *)
retCd := SQLSelect(cursor,'SELECT name from emp WHERE
 (state = ''IN'') AND ' & '(age > ' & age & ') FOR UPDATE OF NAME');
IF (retCd < 0) THEN
 SQLRollBack; (* terminate the transaction *)
 EXIT retCd;
END;
retCd := SQLFetch(cursor,name);
WHILE (retCd > 0) DO
 (* possibly perform some operations based on "name"
 .
 .
 .
 *)
 (* set name to uppercase *)
 name := StrUpper(name);
 (* now update this row *)
 retCd := SQLUpdateCurrent(cursor,name);
 IF (retCd < 0) THEN
 SQLCloseCursor(cursor);
 SQLRollBack; (* reverse changes and release locks *)
 EXIT retCd;
 END;
 (* get the next record *)
 retCd := SQLFetch(cursor);
END;
SQLCloseCursor(cursor);
SQLCommit; (* accept changes and release locks *)
END;

Return Codes

Return Code Description
1 Successful completion
(other) See the Tivoli Service Desk 6.0 Developer's Toolkit Script Programming Guide

See Also


SQLSelectInto

Description

Selects and fetches the specified row into the parameters (column variables).

Syntax

SQLSelectInto(queryString: STRING [,parameters...]):
INTEGER;

Argument Notes

Argument Name Description
queryString This is the select string. It must be complete and syntactically correct.
parameters This is a comma-delimited list of zero or more parameters (column variables) whose names must match the corresponding column names.
See the Notes section for additional information.

Notes

SQLSelectInto is equivalent to the following code sequence:

SQLSelect SQLFetch SQLCloseCursor.

Therefore, if the selection query specifies more than one row, only the first row is fetched.

If you update or delete any fetched rows, use a FOR UPDATE OF clause to make your intentions clear to the SQL interpreter. If you do not use a FOR UPDATE OF clause, the select is treated as read-only.

Matching Parameter Names with Column Names

The parameter names must match the respective column names. Any parameter whose name does not match a column name is ignored. An exception to occurs if the column name begins with SQLColumn_ and ends with the column number that corresponds to the position in the select list. With this method, you can access the results of column (aggregate) functions.

For example, if you declare SQLColumn_1 as a variable (integer), you could fetch the count with

SQLSelectInto('SELECT count(*) from emp',$SQLColumn_1);

The order of the $SQLColumn_xx parameters does not matter within the parameter list. For example, if name is a character column, and age is a numeric column, and you declared the $SQLColumn_xx variables as:

$SQLColumn_1: STRING; $SQLColumn_2: INTEGER;

then the following would fetch name into $SQLColumn_1, and age into $SQLColumn_2:

SQLSelectInto('name,age FROM emp',$SQLColumn_1,$SQLColumn_2);

and so would:

SQLSelectInto('name,age FROM emp',$SQLColumn_2,$SQLColumn_1);


For simple select strings, like the example, the SELECT keyword is optional. You may pass a record as a parameter; in this case, the field names must match the table's column names, or they are ignored.

If you pass two records of the same type, SQLSelectInto fills both. This saves an assignment step when using passive concurrency.

Example

KNOWLEDGEBASE Example;
--PUBLIC
ROUTINES
FUNCTION Test:INTEGER;
PRIVATE
ROUTINES
FUNCTION Test:INTEGER IS
VARIABLES
(* columns *)
name: STRING;
ssn: INTEGER;
age: INTEGER;
(* misc *)
retCd: INTEGER;
ACTIONS
(* select all employees from Indiana *)
retCd := SQLSelectInto('SELECT * from emp WHERE state = ''IN''',
 name, age, ssn);
IF (retCd < 0) THEN
 EXIT retCd;
END;
END;

Return Codes

Return Code Description
1 Successful completion
(other) See the Tivoli Service Desk 6.0 Developer's Toolkit Script Programming Guide

See Also


SQLSetOption

Description

Sets the value of a configuration or context state option.

Syntax

SQLSetOption(VAL option: STRING,
             VAL optionValue:<ANY SIMPLE VARIABLE>
             [,VAL srcOrConn : <INTEGER OR STRING
             EXPRESSION>
             ) : INTEGER;

Cautions

The option names passed to SQLGetOption use spaces, not underscores, for separation, unlike the options in the SQL configuration file (sai_sql.cfg).

All options with SQLGetOption can not be set with SQLSetOption. See the entry for SQLGetOption for a complete list of valid options and a specification of whether they can be changed with SQLSetOption.

Argument Notes

Argument Name Description
option The name of the option, such as 'DATE FORMAT'
optionValue The option value to set.
srcOrConn This argument is optional, and if provided, must contain a source name (STRING) or a connection handle (INTEGER). $CURRENT is assumed if this argument is not passed.

Note: Unlike SQLGetOption, Tivoli Service Desk Developer's Toolkit currently does not support this argument; if one is provided, it is ignored.

Notes

The function returns a success or error code. The most common error codes are: INVALID_OPTION and NOT_CONNECTED (for those options that require you to be connected, which most do).

This function incorporates most of the functionality previously found in SQLCommand('SET ... '). Additional information on each parameter (including defaults) is found by invoking help for the parameter inside the SQL configuration editor. With a few exceptions, parameters with SQLGetOption can be set with SQLSetOption. Refer to SQLGetOption for a list of available options (options that cannot be set are covered there as well).

Example

KNOWLEDGEBASE Example;
 --PUBLIC
 ROUTINES
 PROCEDURE Example;
 PRIVATE
 ROUTINES
 PROCEDURE Example IS
 VARIABLES
 retCd : INTEGER;
 ACTIONS
 retCd := SQLSetOption('CASE CONVERSION', 2);
 END;

Return Codes

Return Code Description
1 Successful completion
(other) See the Tivoli Service Desk 6.0 Developer's Toolkit Script Programming Guide

See Also


SQLUpdate

Description

Updates the specified row with the values contained in the parameters (column variables).

Syntax

SQLUpdate(tableName: STRING, queryString: STRING,
 parameters...]
 [,originalParameters...]): INTEGER;

Caution: Since one call to SQLUpdate can update many rows, it is important to specify your query condition carefully.

Argument Notes

Argument Name Description
tableName The name of the table or view to be updated.
queryString The WHERE clause.
parameter This is a comma delimited list of one or more parameters (column variables) whose names must match the corresponding column names.
See the Notes for this statement for additional information.
originalParameters This is an optional set of parameters that are identical to the first set. These parameters are used for passive concurrency checking.
See the Notes for this statement for additional information.

Notes

The WHERE keyword in the query string is optional. The parameters (column variables) passed into SQLUpdate must have the same name as the actual columns of the table. If a parameter name does not match any column name, the parameter is ignored.

Do not try to update a column of a table that was created with the NOT NULL option, with a Tivoli Service Desk Developer's Toolkit parameter that is $Unknown. You may pass a record as a parameter; in this case, the field names must match the table's column names, or they are ignored.

Passive Concurrency

Tivoli Service Desk Developer's Toolkit provides some built-in support for a concurrency mechanism known as passive concurrency. Passive concurrency is a simple but powerful way to allow multiple users to access a table without locking the table. It is passive in the sense that no locks are actively applied in the normal course of accessing the record.

Passive concurrency works in the following manner:

Note: If only one record is passed, SQLUpdate does not perform any passive concurrency checking.

You do not have to use records as parameters. If you use passive concurrency, you should ensure that the order of the two sets of parameters are the same.

Do not try to update a column of a table that was created with the NOT NULL option with a Tivoli Service Desk Developer's Toolkit parameter that is $Unknown.

Example

KNOWLEDGEBASE Example;
(* This example shows an update without passive concurrency. *)
--PUBLIC
ROUTINES
FUNCTION Test:INTEGER;
PRIVATE
ROUTINES
FUNCTION Test:INTEGER IS
VARIABLES
(* table columns *)
age: INTEGER;
(* misc *)
retCd: INTEGER;
ACTIONS
age := 29;
(* Set all female employee's ages to 29 if they are 30 or over *)
retCd := SQLUpdate('emp','age > 29',age);
IF (retCd < 0) THEN
 EXIT retCd;
END;
END;

Return Codes

Return Code Description
1 Successful completion
(other) See the Tivoli Service Desk 6.0 Developer's Toolkit Script Programming Guide

See Also


SQLUpdateCurrent

Description

Updates the currently positioned row of the cursor with the values in the parameters (column variables).

Syntax

SQLUpdateCurrent(cursor: SQLCURSOR, parameters): INTEGER;

Argument Notes

Argument Name Description
cursor The cursor must be previously opened by a call to SQLSelect.
parameters This is a comma-delimited list of one or more parameters (column variables) whose names must match the corresponding column names.
See the Notes section for more information.

Notes

SQLUpdateCurrent is supported only in the DB2 CLI driver. Each column that is to be updated must be specified in the FOR UPDATE OF clause in the selection string (See the SQLSelect statement).

The parameters (column variables) passed into SQLUpdateCurrent must have the same names as the actual columns of the table. If a parameter name does not match any column names, the parameter is ignored. You may pass a record as a parameter. In this case, the field names must match the table's column names or they are ignored.

Note: You must use the FOR UPDATE OF... clause on every column that you update.

Do not try to update a column of a table that was created with the NOT NULL option with a Tivoli Service Desk Developer's Toolkit parameter that is $Unknown.

Example

KNOWLEDGEBASE Example;
--PUBLIC
ROUTINES
FUNCTION Test(REF age: INTEGER):INTEGER;
PRIVATE
ROUTINES
FUNCTION Test(REF age: INTEGER):INTEGER IS
VARIABLES
(* columns *)
name: STRING;
(* misc *)
cursor: SQLCursor;
retCd: INTEGER;
ACTIONS
SQLBeginWork; (* start a transaction *)
(* select all employees from Indiana, and whose age is
 greater *)
(* than the age passed into this function. *)
retCd := SQLSelect(cursor,'name from emp WHERE (state =
 ''IN'') AND ' & '(age > ' & age & ') FOR UPDATE OF NAME');
IF (retCd < 0) THEN
 SQLRollBack; (* must terminate the transaction *)
 EXIT retCd;
END;
retCd := SQLFetch(cursor,name);
WHILE (retCd > 0) DO
 (* possibly perform some operations based on "name"
 .
 .
 .
 *)
 (* set name to uppercase *)
 name := StrUpper(name);
 (* now update this row *)
 retCd := SQLUpdateCurrent(cursor,name);
 IF (retCd < 0) THEN
 SQLCloseCursor(cursor);
 SQLRollback; (* reverse any changes, and release locks *)
 EXIT retCd;
 END;
 (* get the next record *)
 retCd := SQLFetch(cursor);
END;
SQLCloseCursor(cursor);
SQLCommit; (* commit changes and release locks *)
END;

Return Codes

Return Code Description
1 Successful completion
(other) See the Tivoli Service Desk 6.0 Developer's Toolkit Script Programming Guide

See Also


Tivoli Service Desk 6.0 Developer's Toolkit Script Language Reference

Return to Main Page

Copyright