SQL Cursors

A cursor is a pointer to data that allows an Embedded SQL program to perform an operation on the record pointed to. By using a cursor, Embedded SQL can iterate through a result set. Embedded SQL can use a cursor to execute a query that returns data from multiple records. Embedded SQL can also use a cursor to update or delete multiple records.

You must first DECLARE an SQL cursor, giving it a name. In the DECLARE statement you supply a SELECT statement that identifies which records the cursor will point to. You then supply this cursor name to the OPEN cursor statement. You then repeatedly issue the FETCH cursor statement to iterate through the SELECT result set. You then issue a CLOSE cursor statement; it is impreative that you close the cursor before exiting the ObjectScript method that contains Embedded SQL.

A cursor-based query uses DECLARE cursorname CURSOR FOR SELECT to select records and (optionally) return select column values into output host variables. The FETCH statement iterates through the result set, using these variables to return selected column values.
A cursor-based DELETE or UPDATE uses DECLARE cursorname CURSOR FOR SELECT to select records for the operation. No output host variables are specified. The FETCH statement iterates through the result set. The DELETE or UPDATE statement contains a WHERE CURRENT OF clause to identify the current cursor position in order to perform the operation on the selected record. For further details on cursor-based DELETE and UPDATE, see WHERE CURRENT OF.

Note that a cursor cannot span methods. Therefore, you must declare, open, fetch, and close a cursor within the same class method. It is important to consider this with all code that generates classes and methods, such as classes generated from a .CSP file.

The following example, uses a cursor to execute a query and display the results to the principal device:

 &sql(DECLARE C1 CURSOR FOR
    SELECT %ID,Name
    INTO :id, :name
    FROM Sample.Person
    WHERE Name %STARTSWITH 'A'
    ORDER BY Name
 )

 &sql(OPEN C1)
      QUIT:(SQLCODE'=0)
 &sql(FETCH C1)

 While (SQLCODE = 0) {
     Write id, ":  ", name,!        
    &sql(FETCH C1)
 }
    
 &sql(CLOSE C1)

This example does the following:

It declares a cursor, C1, that returns a set of Person rows ordered by Name.
It opens the cursor.
It calls FETCH on the cursor until it reaches the end of the data. After each call to FETCH, the SQLCODE variable will be set to 0 if there is more data to fetch. After each call to FETCH, the values returned are copied into the host variables specified by the INTO clause of the DECLARE statement.
It closes the cursor.

The DECLARE Cursor Statement

The DECLARE statement specifies both the cursor name and the SQL SELECT statement that defines the cursor. The DECLARE statement must occur within a routine before any statements that use the cursor.

A cursor name is case-sensitive.

A cursor name must be unique within a class or routine. For this reason, a routine that is called recursively cannot contain a cursor declaration. In this situation, it may be preferable to use Dynamic SQL.

The following example declares a cursor named MyCursor:

 &sql(DECLARE MyCursor CURSOR FOR
    SELECT Name, DOB
    FROM Sample.Person
    WHERE Home_State = :state
    ORDER BY Name
    )

A DECLARE statement may include an optional INTO clause that specifies the names of the local host variables that will receive data as the cursor is traversed. For example, we can add an INTO clause to the previous example:

 &sql(DECLARE MyCursor CURSOR FOR
    SELECT Name, DOB
    INTO :name, :dob
    FROM Sample.Person
    WHERE Home_State = :state
    ORDER BY Name
    )

The INTO clause may contain a comma-separated list of host variables, a single host variable array, or a combination of both. If specified as a comma-separated list, the number of INTO clause host variables must exactly match the number of columns within the cursor’s SELECT list or you will receive a “Cardinality Mismatch” error when the statement is compiled.

If the DECLARE statement does not include an INTO clause, then the INTO clause must appear within the FETCH statement. A small performance improvement may result from specifying the INTO clause in the DECLARE statement, rather than in the FETCH statement.

Because DECLARE is a declaration, not an executed statement, it does not set or kill the SQLCODE variable.

If a specified cursor has already been declared, compilation fails with a SQLCODE -52 error, Cursor name already declared.

Executing a DECLARE statement does not compile the SELECT statement. The SELECT statement is compiled the first time the OPEN statement is executed. Embedded SQL is not compiled at routine compile time, but at SQL execution time (runtime).

The OPEN Cursor Statement

The OPEN statement prepares a cursor for subsequent execution:

 &sql(OPEN MyCursor)

Executing the OPEN statement compiles the Embedded SQL code found in the DECLARE statement, creates an optimized query plan, and generates a cached query. Error involving missing resources (such as an undefined table or field) are issued when the OPEN is executed (at SQL runtime).

Upon a successful call to OPEN, the SQLCODE variable will be set to 0.

You cannot FETCH data from a cursor without first calling OPEN.

The FETCH Cursor Statement

The FETCH statement fetches the data for the next row of the cursor (as defined by the cursor query):

 &sql(FETCH MyCursor)

You must DECLARE and OPEN a cursor before you can call FETCH on it.

A FETCH statement may contain an INTO clause that specifies the names of the local host variables that will receive data as the cursor is traversed. For example, we can add an INTO clause to the previous example:

 &sql(FETCH MyCursor INTO :a, :b)

The INTO clause may contain a comma-separated list of host variables, a single host variable array, or a combination of both. If specified as a comma-separated list, the number of INTO clause host variables must exactly match the number of columns within the cursor’s SELECT list or you will receive an SQLCODE -76 “Cardinality Mismatch” error when the statement is compiled.

Commonly, the INTO clause is specified in the DECLARE statement, not the FETCH statement. If both the SELECT query in the DECLARE statement and the FETCH statement contain an INTO clause, only the host variables specified by the DECLARE statement are set. If only the FETCH statement contain an INTO clause, the host variables specified by the FETCH statement are set.

If FETCH retrieves data, the SQLCODE variable is set to 0; if there is no data (or no more data) to FETCH, SQLCODE is set to 100 (No more data). Host variable values should only be used when SQLCODE=0.

Depending on the query, the first call to FETCH may perform additional tasks (such as sorting values within a temporary data structure).

The CLOSE Cursor Statement

The CLOSE statement terminates the execution of a cursor:

 &sql(CLOSE MyCursor)

The CLOSE statement cleans up any temporary storage used by the execution of a query. Programs that fail to call CLOSE will experience resource leaks (such as unneeded increase of the IRISTEMP temporary database).

Upon a successful call to CLOSE, the SQLCODE variable is set to 0. Therefore, before closing a cursor you should check whether the final FETCH set SQLCODE to 0 or 100.

Embedded SQL Variables

Host Variables