Programming Guide

DECLARE CURSOR

Declares a named cursor for a DBMS SQL statement

Synopsis

DBMS [ WITH CONNECTION connection ] DECLARE cursor CURSOR 
	FOR SQLstatement

Arguments

WITH CONNECTION connection
Name of connection to associate with the cursor. If the clause is not used, Prolifics opens the cursor on the default connection.

cursor
Name of cursor to be created.

SQLstatement
SQL statement to be performed when the cursor is executed.

Description

Use DBMS DECLARE CURSOR to create or redeclare a named cursor.

If the application has not already declared cursor, Prolifics allocates a new cursor structure and adds its name to the list of declared cursors.

If a structure already exists for cursor and the connection is the same, Prolifics reinitializes the structure. Reinitialization clears any information on SELECT columns and binding parameters. It does not clear any attributes assigned to the cursor with the statements:

Prolifics uses these settings if the cursor is redeclared with a SELECT statement. It ignores the attributes if the cursor is redeclared with an INSERT, UPDATE, or DELETE statement. To redeclare the cursor with a new (empty) structure, close the cursor with DBMS CLOSE CURSOR before executing the new declaration.

If a cursor is redeclared on another connection, Prolifics automatically closes the cursor and declares a new structure.

The cursor remains open until it is explicitly closed with the DBMS CLOSE CURSOR command. Closing a connection also closes all cursors on the connection.

There are few restrictions on valid cursor names. However, avoid using any DBMS, JDB, or Prolifics keywords as a cursor name. Prolifics is case sensitive regarding cursor names; for example, it interprets cursor c1 as distinct from cursor C1.

For information on the format of parameters in the SQL statement, refer to Chapter 30, "Writing Information to the Database," in Application Development Guide and refer to "Using Database Cursors" in Application Development Guide for information about declaring cursors.

Example

// When the following statement is executed, it fetches 
// a list of actors in the specified video.
 
proc s_entry
 
DBMS WITH CONNECTION c1 DECLARE act_cursor CURSOR FOR \ 
	SELECT actors.first_name, actors.last_name, roles.role \ 
	FROM actors, roles \ 
	WHERE actors.actor_id = roles.actor_id \ 
	AND roles.title_id = ::film_code
 


proc exec1
 
DBMS WITH CURSOR t_cursor EXECUTE USING film_code
 
return

See Also

DBMS CLOSE CURSOR, DBMS EXECUTE, DBMS WITH CURSOR, dm_is_cursor