Advanced PowerBuilder

Retrieving Multiple Result Sets

Before talking about Multiple result sets, let's be clear about a result set. It means, the rows returned by one SELECT statement. In SQL Server, you can have as many SELECT statements as you want, and each of those SELECT statements can return rows. The columns that are returned by each SELECT statement could be different. For example:

Declare ProcDummy AS /* Result set 1, returns everything from product_master */ select * from product_master /* Set 2, returns everything from trans table */ select * from trans /* Set 3, returns the no. of rows for the above SELECT */ select @@rowcount /* Not a result set, because, we are using SELECT statement to assign a value to a variable.*/ select @dummy = 100 /* Set 4, returns everything from units table */ select * from units return 0

While executing multiple result set stored procedures in PowerBuilder, you can retrieve only one set at a time. When the script fetches the first result set, SQLCODE is populated with the 100 return code. From then onwards, the values of the second result set are available to the script.

For example, the following script would execute sp_help Stored Procedure. If you supply a table name as parameter, it would give five result sets. The script listed below fetches the first two result sets:

// This script assumes there are 2 DataWindow controls in // the window with appropriate columns and data types. String lObjectName, lOwner, lObjectType, lDataSegment Long lNewRow DateTime lCreationTime lObjectName = "sysobjects" DECLARE sp_help_proc PROCEDURE FOR dbo.sp_help @objname = :lObjectName ; EXECUTE sp_help_proc ; If SQLCA.SQLcode <> 0 Then MessageBox( "Error", SQLCA.SQLErrorText ) Close sp_help_proc ; Return End If dwc_1.Title = "Result Set: #1" Do While True Fetch sp_help_proc INTO :lObjectName, :lOwner, :lObjectType ; If SQLCA.SQLcode = 100 Then Exit lNewRow = dwc_1.InsertRow(0) dwc_1.SetItem( lNewRow, 1, lObjectName ) dwc_1.SetItem( lNewRow, 2, lOwner ) dwc_1.SetItem( lNewRow, 3, lObjectType ) Loop dwc_2.Title = "Result Set: #2" Do While True FETCH sp_help_proc INTO :lDataSegment, :lCreationTime ; If SQLCA.SQLCOde = 100 or SQLCA.SQLCode = -1 Then Exit lNewRow = dwc_2.InsertRow(0) dwc_2.SetItem( lNewRow, 1, lDataSegment ) dwc_2.SetItem( lNewRow, 2, lCreationTime ) Loop Close sp_help_proc ;

Home	Previous Lesson: Executing a Remote Stored Procedure Next Lesson: Retrieving the Value Of OUTPUT Variable