Dealing with multiple result sets Professional

The stored procedure described in the previous chapter returns more than one result set, but only one is accessible at a time. The setting ODBCSSN_MORE_RESULTS is responsible how to retrieve the result set of interest.

The following example shows how to retrieve one specific result set. Note if the selected result set does not exist or does not deliver columns, the resulting workarea does not provide fields, but might be able to deliver RecCount(), that is, the number of affected rows of this operation.

// retrieve the 2nd result set only 
oSession:setProperty(ODBCSSN_MORE_RESULTS, 2) 
USE ("EXECUTE select_multiple;") 
? DbStruct() 
CLOSE 

The following example shows how to retrieve the first result set that actually delivers data. Note that a simple INSERT, UPDATE or other statements always creates a result set in the sense of SQL, but the result set might not contain any columns.

// retrieve 1st result set having columns 
oSession:setProperty(ODBCSSN_MORE_RESULTS, ODBC_MORE_RESULT_AUTO) 
USE ("EXECUTE select_multiple;") 
? DbStruct() 
CLOSE 

The following sample demonstrates how to retrieve all result sets subsequently. The developer can call DbInfo(ODBCDBO_MORE_RESULTS, ODBC_MORE_RESULT_NEWAREA) anytime on the current result set to abandon processing it and to get the next result set in a new workarea. Note that once the next result set has been set active, the previous result set cannot perform any operations or navigation any longer, only the last record and status information can be queried. All workareas must be closed afterwards.

USE ("EXECUTE select_multiple;") ALIAS "gettest" 
lMoreResults := .T. 
DO WHILE lMoreResults 
   ? Alias() 
   DO WHILE !EOF() 
      AEval(aStr, {|e, i| Qout(aStr[i][1], FieldGet(i))}) 
      SKIP 
   ENDDO 
   DbInfo(ODBCDBO_MORE_RESULTS, ODBC_MORE_RESULT_NEWAREA) 
   nInfo := DbInfo(ODBCDBO_MORE_RESULTS) 
   lMoreResults := nInfo != ODBC_MORE_RESULT_NOMORE 
ENDDO 
CLOSE ALL