Summary

A stored procedure in a Microsoft SQL Server or Sybase database can return multiple resultsets. 

Using the {MULTISET} keyword, Connect SQL rules report back all resultsets and update counts.

Suggested Approach

If your application uses Connect SQL rules to execute a stored procedure that performs two or more SELECT statements, multiple resultsets may be returned. The {multiset} keyword causes the resultsets to appear on the clipboard nested in a two-level Page List structure.

For SQL operations that don't return a resultset (including INSERT, UPDATE and DELETE statements), the clipboard contains an update count conveying the number of rows affected.

To use this feature, follow these steps::

1. If multiple result sets are expected, include the tag "{multiset}" (case and location do not matter) in the Connect SQL rule:
   
2. After the connector completes,the pxResults property is of class Code-Pega-List . This Page List contains embedded pages with the resultsets.
3. Update the activity which handles Connect-SQL instances to iterate through the embedded pages for the resultsets, instead of using the main page for results.  The main page contains one embedded page for each resultset.

Example

A stored procedure that does the following:

insert into some_table values ('a')
select * from table_with_one_row
update another_table set the_column = 'affect_five_rows'
select * from table_with_two_rows

If a developer runs this query using an interactive SQL tool, results are similar to the following:

1 row(s) affected
<< data for table_with_one_row >>
5 row(s) affected
<< data for table_with_two_rows>>

If you execute this stored procedure with a Connect SQL rule, the response contains:

YourResultPage (Code-Pega-List)
+- pxResults
+- pxResults(1) (Code-Pega-List)
+- pxResultCount: 1
+- pxResults /* this is empty */
+- pxResults(2) (Code-Pega-List)
+- pxResultCount: 1
+- pxResults
+- pxResults(1) (Your-Target-Class) /* First ResultSet, Row 1 of 1 */
+- pxResults(3) (Code-Pega-List)
+- pxResultCount: 5
+- pxResults /* this is empty */
+- pxResults(4) (Code-Pega-List)
+- pxResultCount: 2
+- pxResults
+- pxResults(1) (Your-Target-Class) /* Second ResultSet, Row 1 of 2 */
+- pxResults(2) (Your-Target-Class) /* Second ResultSet, Row 2 or 2 */
+- pxResultCount: 4

Beginning with V 4.1, two boolean properties present on the requestor page:

• pxRequestor.pyRDBSuppressUpdateCounts — If this property is set to true , and the {multiset} directive is in the Connect SQL request, then responses from the JDBC driver that contain only update counts are ignored.
• .pyRDBSingleMultisetResultSet — If this property is set to true , and the {multiset} directive is in the Connect SQL request, it is assumed that there will only be one result set response from the JDBC driver, and the nested pages normally produced by the {multiset} directive are not created.

If .pyRDBSingleMultisetResultSet is set to true, it is assumed that .pyRDBSuppressUpdateCounts is also true, since without the nested structure of pages, there is no place to save the count data.  Process Commander creates a single Code-Pega-List page with one level of results.

If this option is selected, and the stored procedure called returns multiple result sets, the requestor receives the first result set and a status that indicates a database problem, described in the pxRDBError property of the top-level Code-Pega-List page.  The error message is:

There was a problem performing a database query: Database-General multiple RDB results found from a stored procedure when the pxRequestor.pyRDBSingleMultisetResultSet option was set to true.