ODBC DECLARE and EXECUTE with PBNewSPInvocation
PowerBuilder requires a declarative statement to identify the
database stored procedure that is being used and to specify a logical name
for the procedure. The logical name is used to reference the procedure in
subsequent SQL statements.
The general syntax for declaring a procedure is:
|
1 2 3 4 5 |
DECLARE logical_procedure_name PROCEDURE FOR procedure_name @param1 = value, @param2 = value2, @PARAM3 = VALUE3 OUTPUT {USING transaction_object}; |
where logical_procedure_name can be any valid PowerScript identifier
and procedure_name is the name of a stored procedure in the database. Use
the OUT or OUTPUT keyword to obtain the value of the output
parameter.
The parameter references can take the form of any valid parameter
string the database accepts. PowerBuilder inspects the parameter list
format only for variable substitution. The USING clause is required only
if you are using a transaction object other than the default transaction
object (SQLCA).
You must set the PBNewSPInvocation database parameter to “Yes” to
use this method to invoke a stored procedure. The behavior of the
PowerBuilder ODBC driver when this DBParm is set is consistent with the
default behavior of the OLE DB drivers.
If PBNewSPInvocation is set to “Yes”, this method is used when you
retrieve data into a DataWindow object that uses a stored procedure. This
DBParm has no effect when you use RPC to invoke a stored procedure.
If PBNewSPInvocation is set to “No”, use the syntax described in
ODBC DECLARE and
EXECUTE.
Example 1
Assume a stored procedure named proc1 is defined on the server
as:
|
1 2 |
CREATE PROCEDURE proc1 AS SELECT emp_name FROM employee |
To declare proc1 for processing within PowerBuilder, enter:
|
1 |
DECLARE emp_proc PROCEDURE FOR proc1; |
The procedure declaration is a nonexecutable statement, just like a
cursor declaration. However, where cursors have an OPEN statement,
procedures have an EXECUTE statement.
When an EXECUTE statement executes, the procedure is invoked. The
EXECUTE refers to the logical procedure name, in this example
emp_proc:
|
1 |
EXECUTE emp_proc; |
Example 2
To declare a procedure with input and output parameters,
enter:
|
1 2 3 4 |
DECLARE sp_duration PROCEDURE FOR pr_date_diff_prd_ken @var_date_1 = :ad_start, @var_date_2 = :ad_end, @rtn_diff_prd = :ls_duration OUTPUT; |
If the stored procedure contains result sets, you must fetch the
result sets first. If the stored procedure has a return value and you want
to obtain it, use the format RC=procedure_name:
|
1 2 3 4 |
DECLARE sp_duration PROCEDURE FORRC=pr_date_diff_prd_ken @var_date_1 = :ad_start, @var_date_2 = :ad_end, @rtn_diff_prd = :ls_duration OUTPUT; |