Home How To Online Documentation Support Download Order

 

 

Connecting to databases
Transactions
Executing an SQL command
Executing stored procedures
Binding input parameters
Fetching result set
Fetching multiple result sets
Optimizing Database Performance
Returning output parameters
Working with Long or Lob(CLob, BLob) Data
Working with date/time values
Error handling
Using native API
Query examples


Fetching result set

To execute a command and process a result set 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 the result set.

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

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 name, age from employees where age>:1");

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 the command text. This command will select all rows from the table EMPLOYEES where the value of field AGE more than some input value.

Step 2. Binding input parameters.

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

cmd << 25;

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 Binding input parameters.

Step 3. 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 more than 25. The result of command execution is the result set - a set of rows corresponding with the request. 

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

bool is_result = cmd.isResultSet();

Step 4. Processing the result set.

After the command execution a set of SAFieldobjects 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:

long col_count = cmd.FieldCount();

Each field can be reached 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("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("name").asString(),
              cmd.Field("age").asLong());

In example above we reached 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(),
              cmd[2].asLong());


 

You can use SAField operators for quick accessing values. In this case you can organize a fetching process as shown below:

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

or another variant:

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

Processing Long, BLob and CLob data can have some differences from processing other data types. See Working with Long or Lob(CLob, BLob) Data to get more information.

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.