Dynamic SQL Format 3 SQL statement
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 |
DECLARE <span>Cursor</span> | <span>Procedure</span> <br> DYNAMIC CURSOR | PROCEDURE <br> FOR <span>DynamicStagingArea</span> ; |
|
1 |
PREPARE <span>DynamicStagingArea</span> FROM <span>SQLStatement</span> <br> {USING <span>TransactionObject</span>} ; |
|
1 |
OPEN DYNAMIC <span>Cursor</span> <br> {USING <span>ParameterList</span>} ; |
|
1 |
EXECUTE DYNAMIC <span>Procedure</span><br> {USING <span>ParameterList</span>} ; |
|
1 |
FETCH <span>Cursor</span> | <span>Procedure<br> </span>INTO <span>HostVariableList</span> ; |
|
1 |
CLOSE <span>Cursor</span> | <span>Procedure</span> ; |
|
Parameter |
Description |
|---|---|
|
Cursor or Procedure |
The name of the cursor or procedure you |
|
DynamicStagingArea |
The name of the DynamicStagingArea (usually SQLSA). If you need a DynamicStagingArea variable other than SQLSA, you must declare it and |
|
SQLStatement |
A string containing a valid SQL SELECT statement The Enter a question mark (?) for each parameter in the statement. |
|
TransactionObject (optional) |
The name of the transaction object that |
|
ParameterList |
A comma-separated list of PowerScript |
|
HostVariableList |
The list of PowerScript variables into |
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
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 |
integer Emp_id_var |
|
1 |
DECLARE my_cursor DYNAMIC CURSOR FOR SQLSA ; |
|
1 |
PREPARE SQLSA FROM "SELECT emp_id FROM employee" ; |
|
1 |
OPEN DYNAMIC my_cursor ; |
|
1 |
FETCH my_cursor INTO :Emp_id_var ; |
|
1 |
CLOSE my_cursor ; |
You can loop through the cursor as you can in embedded static SQL.
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 |
DECLARE my_cursor DYNAMIC CURSOR FOR SQLSA ; |
|
1 |
integer Emp_id_var |
|
1 |
string Emp_state_var = "MA" |
|
1 |
string sqlstatement |
|
1 |
|
1 |
sqlstatement = "SELECT emp_id FROM employee "& |
|
1 |
+"WHERE state = ?" |
|
1 |
PREPARE SQLSA FROM :sqlstatement ; |
|
1 |
OPEN DYNAMIC my_cursor using :Emp_state_var ; |
|
1 |
FETCH my_cursor INTO :Emp_id_var ; |
|
1 |
CLOSE my_cursor ; |
These statements perform the same processing as the preceding example
but use a database stored procedure called Emp_select:
|
1 |
// The syntax of emp_select is: |
|
1 |
// create procedure emp_select (@stateparm char(2)) as |
|
1 |
// SELECT emp_id FROM employee WHERE state=@stateparm. |
|
1 |
DECLARE my_proc DYNAMIC PROCEDURE FOR SQLSA ; |
|
1 |
integer Emp_id_var |
|
1 |
string Emp_state_var |
|
1 |
|
1 |
PREPARE SQLSA FROM "execute emp_select @stateparm=?" ; |
|
1 |
Emp_state_var = "MA" |
|
1 |
EXECUTE DYNAMIC my_proc USING :Emp_state_var ; |
|
1 |
FETCH my_proc INTO :Emp_id_var ; |
|
1 |
CLOSE my_proc ; |
These statements are for a stored procedure with a return
value for a SQL Native Client (SNC) connection:
|
1 |
integer var1, ReturnVal<br>string var2<br> <br>PREPARE SQLSA FROM "execute @rc = myproc @parm1=?, @parm2=? OUTPUT ";<br>DECLARE my_proc DYNAMIC PROCEDURE FOR SQLSA ;<br> <br>EXECUTE DYNAMIC my_proc USING :var1, :var2 ;<br> <br>//fetch result set<br>. . .<br> <br>//fetch return value and output parameter<br>FETCH my_proc INTO : ReturnVal, :var2;<br> <br>CLOSE my_proc ; |