Copyright © Cloud Software Group, Inc. All Rights Reserved

Statements Supported by the SQL Preprocessor

The following subset of SQL statements is supported by the SQL preprocessor. All these statements are written in the usual format. After each SQL statement, you should check for errors. Refer to Error Checking for more information.

SQL Statements

 

Statement	Usage
CLOSE CURSOR	You must close all the cursors before the end of the program. COMMIT and ROLLBACK also close all cursors.
COMMIT	Commits changes and closes all cursors.
DECLARE CURSOR	The DECLARE CURSOR statement accesses data from multiple tables or from a single table. Operation on a join type cursor (a cursor that accesses multiple tables) is limited to a fetch. The FOR UPDATE OF and ORDER BY clauses are not allowed on join type cursors.
DELETE	Delete all table occurrences satisfying the search condition. If no search condition is specified, all table occurrences are deleted. Both positioned delete (using WHERE CURRENT OF cursor-name) and searched delete are supported.
FETCH	You can have only one cursor open on a given table.
INSERT	Insert occurrence values into a table. The values are either those of a derived table of a query-specification or values specified directly: If the statement contains a query-specification, one row of values is inserted into the base table for each of the rows of the searched table that satisfy the search condition of the query-specification. If no search condition is stated, all the values of all the rows of the table in the query-specification are copied into the base table. The insertion is done in the order specified (that is, the value of the nth column of a row in the searched table, or the nth insert-value in the VALUES list, is assigned to the nth column of the base table).
OPEN CURSOR	All values for selection are determined at this point. WHENEVER is supported.
ROLLBACK	Rolls back changes and closes all cursors.
SELECT	Assign values from the specified table to host variables. The SELECT statement is supported only within the DECLARE CURSOR statement. Refer to Supported Keywords and Clauses for the SELECT Statement for the keywords and clauses that are supported for the SELECT statement.
UPDATE	This statement updates zero or more occurrences of the specified table. It behaves as follows: Both positioned update (which uses WHERE CURRENT OF cursor-name) and searched update are supported. If the WHERE clause is specified, only those occurrences satisfying the conditions are updated, otherwise all occurrences of the specified table are updated. Any expressions are evaluated in COBOL. Parameter information is taken from the CURSOR, not the table specification in UPDATE. If the SET clause changes the primary key, TIBCO Object Service Broker ignores the cursor. If the primary key exists, that occurrence is updated. If the primary key does not exist, the REPLACEFAIL exception is raised.
WHENEVER	Used in the Procedure Division of the COBOL program to enable or disable subsequent SQL statements from transferring control if they produce exceptions. It behaves as follows: The SQLERROR clause traps all errors except TABLEEND, which is trapped by the NOT FOUND clause. The WHENEVER statement affects those SQL statements that follow it. The keyword GOTO indicates the applicable SQL statements that follow should transfer control to the specified host-label. The keyword CONTINUE specifies no transfer of control in the SQL statements that follow it: this is typically used to cancel the redirection of a previous WHENEVER statement.

Supported Keywords and Clauses for the SELECT Statement

The keywords and clauses supported by the SELECT statement are listed in the following table:

Keyword or Clause	Usage
ALL	DISTINCT is not supported.
FROM clause	The table specifications in the FROM clause can have parameters.
INTO clause	The specific host variables to retrieve values into.
ORDERED BY clause	You can specify ASC (ascending order) or DESC (descending order), and you can refer to the fields by number. If the SELECT statement cannot find an occurrence that meets the specified conditions, it raises a TABLEEND exception and sets the applicable status variables with the appropriate codes. If you used the WHENEVER statement, the exception is trapped in the COBOL routine associated with the exception. If the SELECT statement finds an occurrence, it checks to see if there are any more occurrences that can be retrieved, and if so, it reports an error (Sqlstate = 21000). This is the desired behavior according to the various SQL standards. If there is only one occurrence meeting the conditions, the retrieved values are assigned to the corresponding host variables.
WHERE clause	All search conditions are supported. You can use either SQL or TIBCO Object Service Broker relational operators. BETWEEN, AND, OR, and NOT are supported. NOT BETWEEN is not supported. With LIKE, you can use SQL wildcards (% and _) or TIBCO Object Service Broker wildcards (* and ?).

Copyright © Cloud Software Group, Inc. All Rights Reserved