Programming Guide

EXECUTE

Executes the DBMS SQL statement declared for a named cursor

Synopsis

DBMS WITH CURSOR cursor EXECUTE [ USING args ]

Arguments

WITH CURSOR cursor
Name of declared SELECT cursor. If the clause is not used, Prolifics uses the default SELECT cursor.

args
Prolifics variables containing parameter values.

Description

Use DBMS EXECUTE to execute the statement associated with a declared cursor.

DBMS EXECUTE does not support the WITH CONNECTION clause. Prolifics uses the database engine that was specified either by name or by default when the cursor was declared. The only way to change the cursor's engine or connection is to redeclare the cursor.

If an application is executing a similar statement many times, it is often more efficient to declare a cursor for the statement. Usually the database engine saves the parsed statement, executing it when the application executes the cursor. It is not necessary to redeclare the cursor to supply new data for a WHERE or VALUES clause. Instead, the application can declare the cursor and use a substitution parameter for each value that the application supplies when it executes the cursor. Substitution parameters begin with a double colon (::). For example: 

DBMS DECLARE c1 CURSOR FOR \ 
	SELECT * FROM titles WHERE name LIKE ::name_parm

name_parm is a place holder for the value that will be supplied when the cursor is executed. For example: 

DBMS WITH CURSOR c1 EXECUTE USING "St%"

This command fetches rows where name begins with the characters "St." The application could execute the cursor repeatedly, each time with a new value. It can use the value of a field to supply a value. For example: 

DBMS WITH CURSOR c1 EXECUTE USING aname

Since aname is not quoted, Prolifics assumes it is a Prolifics variable. If an argument in the USING clause is a widget or LDB variable that has date/time, currency, null field, or type property specifications, Prolifics formats the variable's value before passing it to the database engine. Refer to Chapter 30, "Writing Information to the Database," in Application Development Guide for details of this topic.

Example

DBMS DECLARE x CURSOR FOR \ 
	SELECT * FROM tapes WHERE title_id=::p1 AND copy_num=::p2
 
// bind by position:
 
DBMS WITH CURSOR x EXECUTE USING code, copy_id
 
// or bind by name:
 
DBMS WITH CURSOR x EXECUTE \ 
	USING p1 = code, p2 = copy_id

See Also

DBMS DECLARE CURSOR, DBMS CLOSE CURSOR, DBMS WITH CURSOR