Introduction to PowerBuilder

Stored Procedures as Data Source

A stored procedure is essentially a set of SQL statements that are stored in the database. When you create a stored procedure, the back-end database parses and converts it into internal object-ids and stores them in the database. It would be faster when you execute it since it is already parsed.

Also, since a stored procedure is stored in the database, the client application only needs to send the stored procedure name and any associated parameters. This can drastically reduce network traffic and also help to speed up execution.

We'll look at stored procedures in depth in the later session on embedded SQL, but for now let us create a DataWindow with stored procedure as the data source.

Invoke the DataWindow painter and select FreeForm and Stored Procedure. PowerBuilder lists all the stored procedures available in the connected database as shown in the following picture.

Once you select the stored procedure, PowerBuilder displays the stored procedure definition at the bottom of the window. PowerBuilder automatically detects the result set of the stored procedures and defines them. Just in case, to define the result set manually, select the 'Manual Result' set option.

Note that the stored procedure option is available only if the database to which you are connected to supports it.

Select dbo.sp_tables stored procedure and click the OK button. This will take you to the design view. When you comeback to the data source by clicking on the icon, you see the result set of the stored procedure, as shown in the picture below. Click on the More button to see more details. Here, you see the actual SQL statement that PowerBuilder sends to the connected database.

Note that a stored procedure can return multiple result sets. By default, PowerBuilder brings the first result set and the result set definition also reflects to the first result set. To retrieve another result set, set it in the Result Set option.

execute dbo.sp_tables;0 @table_name = :table_name, @table_owner = :table_owner, @table_qualifier = :table_qualifier, @table_type = :table_type

You can group any number of stored procedures and give them the same name. When you do that you need to assign a number for each group member. While executing if the group number is not specified, Sybase automatically executes the first member in the group. To execute a specific group member, specify the group no. after the stored procedure name separated by a semicolon, similar to the above statement.

Home	Previous Lesson: External Data Source Next Lesson: DataWindow Presentation Styles