Fetching multiple result sets

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 line 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.
It 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);
    }
}

Problems and Questions

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