Step 3: Retrieving Data from the Database

This step shows how to retrieve the output from the database. In Step 2, a simple query to create a new table was sent to the RDBMS, and records were inserted in this table. Now, you can read the data from the table.

First, a select statement is executed. This is done as follows:

const char* selectStr = "select * from ATABLE";

cout << "Retrieving all rows : " << selectStr << endl;

if (!request->execute(selectStr))

IldDisplayError("Select failed : ", request);

Note The value of rowCount passed to execute is 0 for “select” queries. This is logical—counting the rows before they have all been accessed would impose an unnecessary performance penalty on the application.

When execute is successful, a description of the results set can be accessed. The description includes the number, name, data type, and size of each column. These are accessed by using the following methods:

• IldUShort IldRequest::getColCount - Gets the number of columns in the results set.

• const char* getColName(IldUShort index) - Gets the name of the column defined by the index position in the results set.

• IldColumnType IldRequest::getColType(IldUShort index) - Gets the type of the given column.

• IldUInt IldRequest::getColSize(IldUShort index) - Gets the size of the column (number of bytes required to store a value of the column).

In the sample, you know exactly what results set to retrieve. However, these methods are used to display the results set when the query is known only at run time. This is Dynamic SQL; the application can process queries provided by an end user at run time.

The information is processed as follows:

// Print selected item names.

IldUShort i ;

IldUShort nbCols = request->getColCount() ;

const char* colName1 = request->getColName(0) ;

const char* colName2 = request->getColName(1) ;

cout << "\t ATABLE" << endl ;

cout << " " << colName1 << "\t\t" << colName(2) << endl ;

The information is available following the execution of the select query. fetch is now used to get the values of these columns from the RDBMS. This retrieves the data from the memory of DB Link.

IldRequest::fetch attempts to get the first available row of the results set. If there is a row available, the hasTuple method returns IldTrue.

Since fetch returns a reference to an IldRequest instance, the rows are fetched in a loop as follows:

while (request->fetch().hasTuple()) {

cout << " " ;

for (i = 0 ; i < nbCols ; ++i) {

if (request->isColNull(i))

cout << "-" ;

else

switch (request->getColType(i)) {

case IldIntegerType :

cout << request->getColIntegerValue(i) ;

break ;

case IldStringType :

cout << "'" << request->getColStringValue(i) << "'" ;

break ;

default :

// Other possible types are not handled here.

break ;

}

cout << endl ;

}

cout << endl ;

}

As long as there is a tuple available, one of the IldRequest::getCol<dataType>Value(IldUShort i) methods is used to retrieve the values of the results set. The method depends on the column type. The DB Link Reference Manual contains a complete list of these methods.

Note A column of any type may be null. This is detected by the method IldRequest::isColNull(IldUShort i).

Conclusion

This step described the simplest way to read the data recorded in the database. This includes the Metadata of the results set, as well as the data itself. You can now run queries against the database. Since your queries will probably be more sophisticated than these, the sample code can be changed to improve performance. Nevertheless, the basic approach will be the same for all DB Link applications.

See source code.