Tivoli Service Desk 6.0 Developer's Toolkit Script Language Reference
Return to Main Page
Begins a transaction.
FUNCTION SQLBeginWork: INTEGER;
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.
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 Code | Description |
1 | Successful completion |
(other) | See the Tivoli Service Desk 6.0 Developer's Toolkit Script Programming Guide |
Closes open cursors and prepared statements and disconnects open DBMS connections.
FUNCTION SQLCloseAll: INTEGER;
Although not required, you should call this routine before exiting the main .kb file. This commits any transactions in progress.
KNOWLEDGEBASE Example;
--PUBLIC ROUTINES
FUNCTION Test:INTEGER;
PRIVATE ROUTINES
FUNCTION Test:INTEGER IS ACTIONS (* perform some SQL operations... *)
SQLCloseAll; END;
Return Codes | Description |
1 | Successful completion |
(other) | See the Tivoli Service Desk 6.0 Developer's Toolkit Script Programming Guide |
Closes a cursor previously opened by a call to SQLSelect.
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 Name | Description |
cursor | This is the parameter of type SQLCursor that was set by a previous call to SQLSelect. |
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.
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 Code | Description |
1 | The cursor was successfully closed |
(other) | See the Tivoli Service Desk 6.0 Developer's Toolkit Script Programming Guidee |
Closes a statement previously opened (prepared) by SQLPrepare.
FUNCTION SQLCloseStatement(statement: SQLSTATEMENT): INTEGER;
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 Name | Description |
statement | The statement parameter is initialized by a call to SQLPrepare |
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.
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 Code | Description |
1 | Successful completion |
(other) | See the Tivoli Service Desk 6.0 Developer's Toolkit Script Programming Guide |
Issues implementation-dependent, non-SQL commands to the SQL interpreter.
FUNCTION SQLCommand(sub_command: STRING): INTEGER;
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. |
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). |
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.
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 Code | Description |
1 | Successful completion |
(other) | See the Tivoli Service Desk 6.0 Developer's Toolkit Script Programming Guide |
See the Tivoli Service Desk 6.0 Developer's Toolkit Script Programming Guide for information on the following:
Issues an SQL Commit Work command to the SQL interpreter, thereby accepting all changes made during this transaction.
FUNCTION SQLCommit: INTEGER;
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.
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.
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 Code | Description |
1 | Successful completion |
(other) | See the Tivoli Service Desk 6.0 Developer's Toolkit Script Programming Guide |
Given a record whose fields contain search criteria, SQLCreateSearchString creates a valid SQL WHERE clause (without the WHERE).
FUNCTION SQLCreateSearchString(VAL tableName: STRING, REF searchString: STRING, VAL searchRec: any record or list of any record): INTEGER;
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 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 |
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:
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. |
_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.
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).
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 Code | Description |
1 | Successful completion |
(other) | See the Tivoli Service Desk 6.0 Developer's Toolkit Script Programming Guide |
Deletes a specified row from a table.
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 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), ... |
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');
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 Code | Description |
1 | Successful completion |
(other) | See the Tivoli Service Desk 6.0 Developer's Toolkit Script Programming Guide |
Deletes the row at the current cursor position.
SQLDeleteCurrent(cursor: SQLCURSOR): INTEGER;
Argument Name | Description |
cursor | The cursor must be previously opened by a call to SQLSelect |
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 *.
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 Code | Description |
1 | Successful completion |
(other) | See the Tivoli Service Desk 6.0 Developer's Toolkit Script Programming Guide |
Executes a prepared statement.
SQLExecute(statement: STATEMENT [,expressionList...]): INTEGER;
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 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), ... |
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.
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 Code | Description |
1 | Successful completion |
(other) | See the Tivoli Service Desk 6.0 Developer's Toolkit Script Programming Guide |
Executes an SQL statement directly from its string form.
SQLExecuteImmediate(sqlCommand: STRING [,expressionList...]): INTEGER;
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), ... |
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);
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 Code | Description |
1 | Successful completion |
(other) | See the Tivoli Service Desk 6.0 Developer's Toolkit Script Programming Guide |
See your SQL documentation for additional information.
Fetches the next row of the cursor into the specified parameters.
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 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). |
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.
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.
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.
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 Code | Description |
1 | Successful completion |
(other) | See the Tivoli Service Desk 6.0 Developer's Toolkit Script Programming Guide |
Converts a data value to a string in the appropriate data format for the DBMS to which you are currently connected.
SQLFormat(value: SIMPLE EXPRESSION): STRING;
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 Name | Description |
value | This must be a simple type (such as DATE or STRING) |
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:
The formatted string is returned. If the value passed in is $Unknown, the string 'NULL' is returned.
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 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.
Gets the actual cursor name used internally by the SQL Interpreter for the specified cursor.
SQLGetCursorName(cursor: SQLCURSOR, cursorName: STRING):
INTEGER;
Argument Name | Description |
cursor | The cursor must be previously opened by a call to SQLSelect |
cursorName | The name of the cursor |
SQLGetCursorName is supported only in the DB2 CLI driver.
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 Code | Description |
1 | Successful completion |
(other) | See the Tivoli Service Desk 6.0 Developer's Toolkit Script Programming Guide |
Retrieves the value of a configuration or context state option.
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 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. |
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 Code | Description |
1 | Successful completion |
(other) | See the Tivoli Service Desk 6.0 Developer's Toolkit Script Programming Guide |
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;
Inserts a new row into the specified table.
SQLInsert(tableName: STRING [,parameters...]): INTEGER;
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). |
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.
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 Code | Description |
1 | Successful completion |
(other) | See the Tivoli Service Desk 6.0 Developer's Toolkit Script Programming Guide |
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.
SQLPrepare(statement: SQLSTATEMENT, sqlCommand: STRING): INTEGER;
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 Name | Description |
statement | The statement handle that is used in the subsequent SQLExecute statement |
sqlCommand | This is a complete, syntactically correct SQL statement |
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.
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 Code | Description |
1 | Successful completion |
(other) | See the Tivoli Service Desk 6.0 Developer's Toolkit Script Programming Guide |
For additional information, see your SQL documentation.
Issues a ROLLBACK WORK command to the SQL interpreter, thereby reversing/undoing all changes made during this transaction.
FUNCTION SQLRollBack: INTEGER;
Caution: A rollback causes all open cursors and prepared statements to close automatically.
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.
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 Code | Description |
1 | Successful completion |
(other) | See the Tivoli Service Desk 6.0 Developer's Toolkit Script Programming Guide |
Opens a cursor based on the rows specified for later fetching by SQLFetch.
SQLSelect(cursor: SQLCURSOR, queryString: STRING [,expressionList...]): INTEGER;
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 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), ... |
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');
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 Code | Description |
1 | Successful completion |
(other) | See the Tivoli Service Desk 6.0 Developer's Toolkit Script Programming Guide |
Selects and fetches the specified row into the parameters (column variables).
SQLSelectInto(queryString: STRING [,parameters...]): INTEGER;
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. |
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.
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.
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 Code | Description |
1 | Successful completion |
(other) | See the Tivoli Service Desk 6.0 Developer's Toolkit Script Programming Guide |
Sets the value of a configuration or context state option.
SQLSetOption(VAL option: STRING, VAL optionValue:<ANY SIMPLE VARIABLE> [,VAL srcOrConn : <INTEGER OR STRING EXPRESSION> ) : INTEGER;
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 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. |
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).
KNOWLEDGEBASE Example;
--PUBLIC ROUTINES
PROCEDURE Example;
PRIVATE ROUTINES PROCEDURE Example IS VARIABLES retCd : INTEGER; ACTIONS retCd := SQLSetOption('CASE CONVERSION', 2); END;
Return Code | Description |
1 | Successful completion |
(other) | See the Tivoli Service Desk 6.0 Developer's Toolkit Script Programming Guide |
Updates the specified row with the values contained in the parameters (column variables).
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 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. |
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.
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.
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 Code | Description |
1 | Successful completion |
(other) | See the Tivoli Service Desk 6.0 Developer's Toolkit Script Programming Guide |
Updates the currently positioned row of the cursor with the values in the parameters (column variables).
SQLUpdateCurrent(cursor: SQLCURSOR, parameters): INTEGER;
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. |
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.
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 Code | Description |
1 | Successful completion |
(other) | See the Tivoli Service Desk 6.0 Developer's Toolkit Script Programming Guide |
Tivoli Service Desk 6.0 Developer's Toolkit Script Language Reference