It is possible to process more than one result set returned from a batch or stored procedure using SQLAPI++. To execute a command and process multiple result sets you should do the following:

  1. Create a command object and set a command text
  2. Bind input parameters (if needed)
  3. Execute the command
  4. Process result sets

For example, let's fetch rows from this sample query (batch):

Select 'aaa' union select 'bbb'
Select 1 union select 2 union select 3

Step 1. Creating a command object and setting a command text

To execute a command we need two objects: SAConnection (connection object) and SACommand (command object).

SACommand cmd(&Connection,
    "Select 'aaa' as fstr union select 'bbb'\n"
    "Select 1 as fnum union select 2 union select 3");

The code above creates a command object cmd based on previously created and connected connection object Connection (for creating and connection Connection object see Connecting to databases). Second parameter is our sample command text.

Step 2. Binding input parameters

The Library provides several ways for binding input variables. To get more details see Binding input parameters. There is nothing to bind in our example.

Step 3. Execute the command

To execute a command use SACommand::Execute method:

cmd.Execute();

Step 4. Processing the result sets

Now we have to process all result sets returned.

// Process first result set
while(cmd.FetchNext())
{
    // do something special with first result set
   SAString s = cmd["fstr"];
   printf("fstr: %s\n", s);
}

// Process second result set
while(cmd.FetchNext())
{
   // do something special with second result set
   long n = cmd["fnum"];
   printf("fnum: %s\n", n);
}

If number of result sets is not known at compile time we can process result sets while SACommand::isResultSet returns true:

int nResulSets = 0;
while(cmd.isResultSet())
{
   printf("Processing result set #%d", ++nResulSets);
   while(cmd.FetchNext())
   {
       // do something special with this result set
       SAString s = cmd[1].asString();
       printf("field value: %s\n", s);
   }
}

If you haven't found the answer to your questions or have some problems using the Library, please, send an e-mail to howto@sqlapi.com

Last modified: 19 October 2019
webmaster@sqlapi.com