ODBC DECLARE and EXECUTE – PB Docs 2021

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:

where logical_procedure_name can be any valid PowerScript identifier
and procedure_name is the name of a stored procedure in the
database.

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).

Output parameters might not be returned when you use an embedded SQL
command to call a stored procedure. You can set the PBNewSPInvocation
database parameter to “Yes” to use an alternative 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 and
JDBC drivers.

If PBNewSPInvocation is set to “Yes,” the alternative method is used
when you retrieve data into a DataWindow object that uses a stored
procedure. See ODBC DECLARE
and EXECUTE with PBNewSPInvocation.

Example

Assume a stored procedure named proc1 is defined on the server. To
declare proc1 for processing within PowerBuilder, enter:

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:

See also

ODBC EXECUTE

ODBC DECLARE and EXECUTE
with PBNewSPInvocation