Using dynamic SQL DataWindow object property
General information
Because database applications usually perform a specific activity,
you usually know the complete SQL statement
when you write and compile the script. When PowerBuilder does not
support the statement in embedded SQL (as
with a DDL statement) or when the parameters or the format of the
statements are unknown at compile time, the application must build
the SQL statements at runtime.
This is called dynamic SQL.
The parameters used in dynamic SQL statements
can change each time the program is executed.
Using SQL Anywhere
For information about using dynamic SQL with
SQL Anywhere®,
see the SQL Anywhere documentation.
Four formats
PowerBuilder has four dynamic SQL formats.
Each format handles one of the following situations at compile time:
|
Format |
When used |
|---|---|
|
Format 1 |
Non-result-set statements with no input |
|
Format 2 |
Non-result-set statements with input |
|
Format 3 |
Result-set statements in which the input |
|
Format 4 |
Result-set statements in which the input |
To handle these situations, you use:
-
The PowerBuilder dynamic SQL statements
-
The dynamic versions of CLOSE, DECLARE, FETCH, OPEN,
and EXECUTE -
The PowerBuilder datatypes DynamicStagingArea and DynamicDescriptionArea
About the examples
The examples assume that the default transaction object (SQLCA) has been assigned valid
values and that a successful CONNECT has been
executed. Although the examples do not show error checking, you
should check the SQLCode after
each SQL statement.
Dynamic SQL statements
The PowerBuilder dynamic SQL statements
are:
|
1 |
DESCRIBE <span>DynamicStagingArea</span><br> INTO <span>DynamicDescriptionArea</span> ; |
|
1 |
EXECUTE {IMMEDIATE} <span>SQLStatement</span><br> {USING <span>TransactionObject</span>} ; |
|
1 |
EXECUTE <span>DynamicStagingArea</span><br> USING <span>ParameterList</span> ; |
|
1 |
EXECUTE DYNAMIC <span>Cursor</span> | <span>Procedure</span><br> USING <span>ParameterList</span> ; |
|
1 |
OPEN DYNAMIC <span>Cursor</span> | <span>Procedure</span><br> USING <span>ParameterList</span> ; |
|
1 |
EXECUTE DYNAMIC <span>Cursor</span> | <span>Procedure</span><br> USING DESCRIPTOR <span>DynamicDescriptionArea</span> ; |
|
1 |
OPEN DYNAMIC Cursor | Procedure<br> USING DESCRIPTOR <span>DynamicDescriptionArea</span> ; |
|
1 |
PREPARE <span>DynamicStagingArea</span><br> FROM <span>SQLStatement</span> {USING <span>TransactionObject</span>} ; |
Two datatypes
DynamicStagingArea
DynamicStagingArea is a PowerBuilder datatype. PowerBuilder
uses a variable of this type to store information for use in subsequent
statements.
The DynamicStagingArea is the only connection between the
execution of a statement and a transaction object and is used internally
by PowerBuilder; you cannot access information in the DynamicStagingArea.
PowerBuilder provides a global DynamicStagingArea variable
named SQLSA that you can
use when you need a DynamicStagingArea variable.
If necessary, you can declare and create additional object
variables of the type DynamicStagingArea. These statements declare
and create the variable, which must be done before referring to
it in a dynamic SQL statement:
|
1 |
DynamicStagingArea dsa_stage1 |
|
1 |
dsa_stage1 = CREATE DynamicStagingArea |
After the EXECUTE statement is completed, SQLSA is no longer referenced.
DynamicDescriptionArea
DynamicDescriptionArea is a PowerBuilder datatype. PowerBuilder
uses a variable of this type to store information about the input
and output parameters used in Format 4 of dynamic SQL.
PowerBuilder provides a global DynamicDescriptionArea named SQLDA that you can use when you
need a DynamicDescriptionArea variable.
If necessary, you can declare and create additional object
variables of the type DynamicDescriptionArea. These statements declare
and create the variable, which must be done before referring to
it in a dynamic SQL statement:
|
1 |
DynamicDescriptionArea dda_desc1 |
|
1 |
dsa_desc1 = CREATE DynamicDescriptionArea |
For more information about SQLDA, see Dynamic SQL Format 4 .
Preparing to use dynamic SQL
When you use dynamic SQL,
you must:
-
Prepare the DynamicStagingArea
in all formats except Format 1 -
Describe the DynamicDescriptionArea in Format 4
-
Execute the statements in the appropriate order
Preparing and describing the datatypes
Since the SQLSA staging
area is the only connection between the execution of a SQL statement and a transaction object,
an execution error will occur if you do not prepare the SQL statement correctly.
In addition to SQLSA and SQLDA, you can declare other
variables of the DynamicStagingArea and DynamicDescriptionArea datatypes.
However, this is required only when your script requires simultaneous
access to two or more dynamically prepared statements.
This is a valid dynamic cursor:
|
1 |
DECLARE my_cursor DYNAMIC CURSOR FOR SQLSA ; |
|
1 |
PREPARE SQLSA FROM "SELECT emp_id FROM employee" ; |
|
1 |
OPEN DYNAMIC my_cursor ; |
This is an invalid dynamic cursor. There
is no PREPARE, and therefore an execution error
will occur:
|
1 |
DECLARE my_cursor DYNAMIC CURSOR FOR SQLSA ; |
|
1 |
OPEN DYNAMIC my_cursor ; |
Statement order
Where you place the dynamic SQL statements
in your scripts is unimportant, but the order of execution is important
in Formats 2, 3, and 4. You must execute:
-
The DECLARE and the PREPARE before
you execute any other dynamic SQL statements -
The OPEN in Formats 3 and 4 before
the FETCH -
The CLOSE at the end
If you have multiple PREPARE statements,
the order affects the contents of SQLSA.
These statements illustrate the correct ordering:
|
1 |
DECLARE my_cursor DYNAMIC CURSOR FOR SQLSA |
|
1 |
string sql1, sql2 |
|
1 |
sql1 = "SELECT emp_id FROM department "& |
|
1 |
WHERE salary > 90000" |
|
1 |
sql2 = "SELECT emp_id FROM department "& |
|
1 |
WHERE salary > 20000" |
|
1 |
|
1 |
IF deptId = 200 then |
|
1 |
PREPARE SQLSA FROM :sql1 USING SQLCA ; |
|
1 |
ELSE |
|
1 |
PREPARE SQLSA FROM :sql2 USING SQLCA ; |
|
1 |
END IF |
|
1 |
OPEN DYNAMIC my_cursor ; // my_cursor maps to the |
|
1 |
// SELECT that has been |
|
1 |
// prepared. |
Declaring a procedure with the SQL Native Client
database interface
When you connect to Microsoft SQL Server using the PowerBuilder
SQL Native Client (SNC) database interface, the syntax for declaring
a procedure is:
|
1 |
DECLARE <span>logical_procedure_name</span> PROCEDURE FOR <br>   [@rc=]<span>procedure_name</span><br>   {@<span>param1</span> = <span>value1</span> [OUTPUT], @<span>param2</span> = <span>value2</span> [OUTPUT], ...}<br>   {USING <span>transaction_object</span>}; |
[@rc=] indicates that
you want to get the procedure’s return value.
Use the keyword OUTPUT or OUT to
indicate an output parameter if you want to get the output parameter’s
value.
If the BindSPInput database parameter is 0, value1, value2,… can
be either PowerBuilder script variables or literal values. If BindSPInput
is 1, value1, value2,… must
be PowerBuilder script variables. If you specify literal values, the
SNC interface returns a runtime error.
When you declare a dynamic SQL statement
with a procedure, enter a question mark (?) for each IN/OUT
parameter in the statement. Value substitution is positional. For
examples, see Dynamic SQL Format
3 and 4.