Using Stored Procedure
A stored procedure is a set of precompiled and preoptimized SQL statements that performs some
database operation. Stored procedures reside where the database
resides, and you can access them as needed.
Defining data using a stored procedure
You can specify a stored procedure as the data source for
a DataWindow object if your DBMS supports stored procedures.
For information on support for stored procedures,
see your database documentation.
If the Stored Procedure icon is not displayed The icon for the Stored Procedure data source displays in
the Choose Data Source dialog box in the DataWindow object wizards only
if the database to which you are connected supports stored procedures.
To define the data using Stored Procedure:
-
Select Stored Procedure in the Choose Data
Source dialog box in the wizard and click Next.The Select Stored Procedure dialog box displays a list of
the stored procedures in the current database. -
Select a stored procedure from the list.
To list system procedures, select the System Procedure check
box.The syntax of the selected stored procedure displays below
the list of stored procedures. -
Specify how you want the result set description
built:- To build the result set description
automatically, clear the Manual Result Set check box and click Next.
PowerBuilder executes the stored procedure and builds the result
set description for you. - To define the result set description manually, select
the Manual Result Set check box and click Next.
In the Define Stored Procedure Result Set dialog box:- Enter the name and type of
the first column in the result set. - To add additional columns, click Add.
- Enter the name and type of
Your preference is saved PowerBuilder records your preference for building result set
descriptions for stored procedure DataWindow objects in the variable Stored_Procedure_Build in
the PowerBuilder initialization file. If this variable is set to 1, PowerBuilder will
automatically build the result set; if the variable is set to 0,
you are prompted to define the result set description. - To build the result set description
-
Continue in the DataWindow wizard as needed for
the presentation style you are using.When you have finished interacting with the wizard, you go
to the DataWindow painter with the columns specified in the result set placed
in the DataWindow object.For information about defining retrieval arguments
for DataWindow objects, see Chapter 19, “Enhancing DataWindow Objects .”For information about using a stored procedure to update the
database, see “Using stored procedures
to update the database”.
Editing a result set description
After you create a result set that uses a stored procedure,
you can edit the result set description from the DataWindow painter.
To edit the result set description:
-
Select Design>Data Source from
the menu bar.This displays the Column Specification view if it is not already
displayed. -
Select Stored Procedure from the Column Specification
view’s pop-up menu.The Modify Stored Procedure dialog box displays.
-
Edit the Execute statement, select another stored
procedure, or add arguments.The syntax is:
1execute<i> sp_procname</i>;<i>num</i> <i>arg1</i> = :<i>arg1</i>, <i>arg2</i> = :<i>arg2</i>..., <i>argn</i> =:<i>argn</i>where
sp_procname is the name of the stored procedure, num is
the stored procedure group suffix, and arg1, arg2,
and argn are the stored procedure’s
arguments.The group suffix is an optional integer used in some DBMSs
to group procedures of the same name so that they can be dropped
together with a single DROP PROCEDURE statement.
For other DBMSs the number is ignored. -
When you have defined the entire result set, click
OK.You return to the DataWindow painter with the columns specified in
the result set placed in the DataWindow object.For information about defining retrieval arguments
for DataWindow objects, see Chapter 19, “Enhancing DataWindow Objects .”