Dynamic SQL Format 3
Description
Use this format to execute a SQL statement that produces a result
set in which the input parameters and result set columns are known at
compile time.
Syntax
|
1 2 3 4 5 6 7 8 9 10 11 12 |
DECLARE Cursor | Procedure DYNAMIC CURSOR | PROCEDURE FOR DynamicStagingArea ; PREPARE DynamicStagingArea FROM SQLStatement {USING TransactionObject} ; OPEN DYNAMIC Cursor {USING ParameterList} ; EXECUTE DYNAMIC Procedure {USING ParameterList} ; FETCH Cursor | Procedure INTO HostVariableList ; CLOSE Cursor | Procedure ; |
|
Parameter |
Description |
|---|---|
|
Cursor or Procedure |
The name of the cursor or procedure you want to |
|
DynamicStagingArea |
The name of the DynamicStagingArea (usually If you need a DynamicStagingArea variable |
|
SQLStatement |
A string containing a valid SQL SELECT statement Enter a question mark (?) for each |
|
TransactionObject (optional) |
The name of the transaction object that identifies |
|
ParameterList (optional) |
A comma-separated list of PowerScript variables. |
|
HostVariableList |
The list of PowerScript variables into which the |
Usage
To specify a null value, use the SetNull function.
The DECLARE statement is not executable and can be declared
globally.
If your DBMS supports formats of FETCH other than the customary
(and default) FETCH NEXT, you can specify FETCH FIRST, FETCH PRIOR, or
FETCH LAST.
The FETCH and CLOSE statements in Format 3 are the same as in
standard embedded SQL.
To declare a local cursor or procedure, open the script in the
Script view and select Paste SQL from the PainterBar or the
Edit>Paste Special menu. To declare a global, instance, or shared
cursor or procedure, select Declare from the first drop-down list in the
Script view, and select Global Variables, Instance Variables, or Shared
Variables from the second drop-down list. Then, select Paste SQL.
For information about global, instance, shared, and local scope,
see Where to declare
variables.
Examples
Example 1
These statements associate a cursor named my_cursor with SQLSA,
prepare a SELECT statement in SQLSA, open the cursor, and return the
employee ID in the current row into the PowerScript variable
Emp_id_var:
|
1 2 3 4 5 6 |
integer Emp_id_var DECLARE my_cursor DYNAMIC CURSOR FOR SQLSA ; PREPARE SQLSA FROM "SELECT emp_id FROM employee" ; OPEN DYNAMIC my_cursor ; FETCH my_cursor INTO :Emp_id_var ; CLOSE my_cursor ; |
You can loop through the cursor as you can in embedded static
SQL.
Example 2
These statements associate a cursor named my_cursor with SQLSA,
prepare a SELECT statement with one parameter in SQLSA, open the cursor,
and substitute the value of the variable Emp_state_var for the parameter
in the SELECT statement. The employee ID in the active row is returned
into the PowerBuilder variable Emp_id_var:
|
1 2 3 4 5 6 7 8 9 10 11 |
DECLARE my_cursor DYNAMIC CURSOR FOR SQLSA ; integer Emp_id_var string Emp_state_var = "MA" string sqlstatement sqlstatement = "SELECT emp_id FROM employee "& +"WHERE state = ?" PREPARE SQLSA FROM :sqlstatement ; OPEN DYNAMIC my_cursor using :Emp_state_var ; FETCH my_cursor INTO :Emp_id_var ; CLOSE my_cursor ; |
Example 3
These statements perform the same processing as the preceding
example but use a database stored procedure called Emp_select:
|
1 2 3 4 5 6 7 8 9 10 11 12 |
// The syntax of emp_select is: // create procedure emp_select (@stateparm char(2)) as // SELECT emp_id FROM employee WHERE state=@stateparm. DECLARE my_proc DYNAMIC PROCEDURE FOR SQLSA ; integer Emp_id_var string Emp_state_var PREPARE SQLSA FROM "execute emp_select @stateparm=?" ; Emp_state_var = "MA" EXECUTE DYNAMIC my_proc USING :Emp_state_var ; FETCH my_proc INTO :Emp_id_var ; CLOSE my_proc ; |
Example 4
These statements are for a stored procedure with a return value
for a SQL Native Client (SNC) connection:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
integer var1, ReturnVal string var2 PREPARE SQLSA FROM "execute @rc = myproc @parm1=?, @parm2=? OUTPUT "; DECLARE my_proc DYNAMIC PROCEDURE FOR SQLSA ; EXECUTE DYNAMIC my_proc USING :var1, :var2 ; //fetch result set . . . //fetch return value and output parameter FETCH my_proc INTO : ReturnVal, :var2; CLOSE my_proc ; |