isCOBOL Evolve : Language Reference : Embedded SQL Statements : DECLARE
DECLARE
The DECLARE statement associates a cursor name with a SELECT statement or a CALL statement. It also allows you to define tables and database entities.
Format 1
EXEC SQL [ AT Database ]
 
  DECLARE Cursor-Name [ { SENSITIVE   } SCROLL ] CURSOR 
                        { INSENSITIVE }
      [ WITH [NO] HOLD ]
      [ { WITHOUT ROWSET POSITIONING } ]
        { WITH ROWSET POSITIONING    } 
                                                           FOR { Prepared-Statement }
                                                               { Select-Statement   }
                                                               { Call-Statement     }
 
END-EXEC
Format 2
EXEC SQL [ AT Database ]
 
  DECLARE Prepared-Statement STATEMENT
 
END-EXEC
Format 3
EXEC SQL [ AT Database ]
 
  DECLARE Table-Name TABLE
 
END-EXEC
Format 4
EXEC SQL
 
  DECLARE Database DATABASE
 
END-EXEC
Format 5
EXEC SQL [ AT Database ]
 
  DECLARE GLOBAL TEMPORARY TABLE Table-Name
 
END-EXEC
Syntax rules
1. Cursor-Name, Database, Procedure-Name and Prepared-Statement are Nonnumeric Literals, as defined in the Definitions section of the Preface of this document.
2. Select-Statement is a complete SELECT statement.
3. Call-Statement is a CALL statement without the INTO clause.
General rules
1. The DECLARE statement must appear in the source before any other statement referencing Cursor-Name, Prepared-Statement, Table-Name and Database.
2. The DECLARE statement can appear in either the working-storage section or in the procedure division.
3. The DECLARE statement doesn’t set SQLCA entries.
4. Prepared-Statement must be previously defined by a PREPARE statement.
5. Call-Statement must not use the INTO clause. The INTO clause must be used on OPEN or FETCH of the cursor.
6. The behavior of the WITH clause is database dependent.
a. When the NO phrase is not specified, the cursor may be closed as a consequence of a commit operation.
b. When the NO phrase is specified, the cursor is closed as a consequence of a commit operation.
7. Format 3 is supported for compatibility and is treated as commentary.
8. Format 4 defines a named connection. It should be used before a Format 2 CONNECT statement.
9. Database identifies the active connection that will execute the query and must be previously defined using a Format 4 DECLARE statement.
10. A Format 5 DECLARE statement is treated as comment if it’s found in the Working-Storage Section. If it appears in the Procedure Division, it’s passed as is to the database.
11. Cursor-Name and Prepared-Statement are bound to the program object instance.
A cursor or a statement declared in a standard program is available only inside the program.
In object oriented programming, a cursor or a statement declared in an Object is shared between all the methods of the Object. The same doesn’t apply to Factory; cursors and statements cannot be shared between Factory methods, they must be declared and used in the same method.
12. The SCROLL clause overrides the iscobol.jdbc.cursor.type * configuration setting.
SENSITIVE SCROLL allows the cursor to move forward and backwards through the data. Changes made while the cursor is open are immediately available. It provides a dynamic view of the underlying data to which the cursor refers.
INSENSITIVE SCROLL allows the cursor to move forward and backward through the data. Changes made while the cursor is open are ignored. It provides a static view of the underlying data to which the cursor refers.
13. WITHOUT ROWSET POSITIONING specifies that the cursor can be used only with row-positioned FETCH statements. The cursor is to return a single row for each FETCH statement and the FOR n ROWS clause cannot be specified on a FETCH statement for this cursor.
WITH ROWSET POSITIONING specifies that the cursor can be used with either row-positioned or rowset-positioned FETCH statements. This cursor can be used to return either a single row or multiple rows, as a rowset, with a single FETCH statement.
Examples
Format 1 - Declare, open and fetch a cursor
exec sql 
   declare cust_cur  cursor for select * from customers
end-exec               
 
exec sql 
   open cust_cur
end-exec     
 
perform until 1 = 2
   exec 
      sql fetch next cust_cur into :ws-cust-code, :ws-cust-name
   end-exec
 
   display "code: " ws-cust-code " name: " ws-cust-name
   if sqlcode = 100
      exit perform
   end-if
 
end-perform
 
exec sql 
   close cust_cur
end-exec
Format 1 - Declare a cursor to intercept the result of a stored procedure that returns a resultset. Refer to the snippet above for information on how to read the content of the cursor
exec sql
   declare cities cursor for call locateStores(:userState)
end-exec