To execute a command and process a result set you should do the following:

For example, let's fetch rows from the table EMPLOYEES (NAME CHAR(25), AGE INTEGER).

Initialize SELECT Command

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

SACommand cmd(
    &con, 
    _TSA("SELECT NAME, AGE FROM EMPLOYEES WHERE AGE > :1"));

The line above creates a command object cmd based on previously created and connected connection object con (for initializing con object see Connect to database). Second parameter is the command text. This command will select all rows from the table EMPLOYEES where the value of field AGE more than some input value.

Bind Input Parameters

To associate a value with input variable use SACommand::operator <<:

cmd << 25L;

This line sets the input parameter marked by :1 to value 25 .

The library provides several ways for binding input variables. To get more details see Bind input parameters.

Execute the Command

To execute a command use SACommand::Execute method:

cmd.Execute();

This command selects all rows from the table where field AGE value is greater than 25L. The result of command execution is a set of rows corresponding to the request.

To check whether a result set exists after the command execution use SACommand::isResultSet method:

bool is_result = cmd.isResultSet();

Process the Result Set

After the command execution a set of SAField objects is created implicitly. Each SAField object represents a column in the result set. To get the number of columns in the result set call SACommand::FieldCount method:

int col_count = cmd.FieldCount();

Each field can be referenced by its name or position. To get a field information (name, type, size, etc.) directly after the command execution you can call SACommand::Field method:

SAField& field = cmd.Field(_TSA("age"));

The line above associates the column AGE in the result set with field variable. We can do the same getting column AGE by its position in the result set:

SAField& field = cmd.Field(2);

A field value is updated only after row fetching. To fetch row by row from the result set use SACommand::FetchNext method:

while(cmd.FetchNext())
    printf("Name: %s, age: %d \n",
            cmd.Field(_TSA("name")).asString().GetMultiByteChars(),
            cmd.Field(_TSA("age")).asInt32());

In example above we obtained a field value accessing appropriated SAField object. Another way to get a field value is to use SACommand::operator []:

while(cmd.FetchNext())
    printf("Name: %s, age: %d \n",
            cmd[1].asString().GetMultiByteChars(),
            cmd[2].asInt32());

You can use SAField () operators to quickly access values. In that case you can perform fetching as shown below:

while(cmd.FetchNext())
{
    SAString sName = cmd.Field(_TSA("name"));
    int nAge = cmd.Field(_TSA("age"));
    printf("Name: %s, age: %d \n", sName.GetMultiByteChars(), nAge);
}

Or using field indexes:

while(cmd.FetchNext())
{
    SAString sName = cmd[1];
    int nAge = cmd[2];
    printf("Name: %s, age: %d \n", sName.GetMultiByteChars(), nAge);
}

Processing Long, BLob and CLob data can have some differences compared to processing other data types. See Handle Long/CLob/BLob to get more information.

Handle Multiple Result Sets

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

// Process first result set
while(cmd.FetchNext())
{
    // do something special with first result set
    SAString s = cmd[_TSA("fstr")];
    printf("fstr: %s\n", s.GetMultiByteChars());
}
                    
// Process second result set
while(cmd.FetchNext())
{
    // do something special with second result set
    int n = cmd[_TSA("fnum")];
    printf("fnum: %d\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.GetMultiByteChars());
    }
}