Open SQL Query Examples
Example 8 – Executing a SELECT query using an RWDBOSql object
These examples illustrate how an RWDBOSql object can be used to execute SELECT queries and retrieve their results. The SELECT query is executed on table emp and retrieves all columns of the table for employees in a particular department.
The first example assumes awareness of the structure of table emp (Open SQL Examples ) and hence the schema of the result set returned by the query.
 
RWDBOSql openSql("SELECT * FROM emp WHERE dept = $1", RWDBOSql::Query); //1
RWDBTBuffer<int> queryDeptBuff; //2
queryDeptBuff[0] = 3; //3
 
openSql << queryDeptBuff; //4
openSql.execute(cn); //5
 
const size_t ROWS_TO_FETCH = 5; //6
 
RWDBTBuffer<int> empnoBuff(ROWS_TO_FETCH), mgrBuff(ROWS_TO_FETCH),
deptBuff(ROWS_TO_FETCH);
RWDBTBuffer<RWCString> enameBuff(ROWS_TO_FETCH), posnBuff(ROWS_TO_FETCH);
RWDBTBuffer<RWDecimalPortable> salBuff(ROWS_TO_FETCH); //7
 
openSql[0] >> empnoBuff >> enameBuff >> posnBuff >>
mgrBuff >> salBuff >> deptBuff; //8
 
long rowsFetched = 0;
while ((rowsFetched = openSql.fetch().rowsFetched()) > 0) { //9
for (size_t i = 0; i < rowsFetched; ++i) {
cout << empnoBuff[i] << "\t" << enameBuff[i] << "\t"
<< posnBuff[i] << "\t"; //10
if (mgrBuff.isNull(i)) { //11
cout << "NULL";
}
else {
cout << mgrBuff[i];
}
cout << "\t" << salBuff[i] << "\t" << deptBuff[i] << endl;
}
}
//1 Creates an RWDBOSql object with the supplied SQL query and the hint RWDBOSql::Query indicating that the SQL is a SELECT statement. The “$1” in the SQL statement is a placeholder for the department number whose employees are to be selected. The placeholder needs to be bound before executing the RWDBOSql.
//2 Creates an RWDBTBuffer object that will be bound to the placeholder. It is templatized on type int, as the dept column is of server type INT. The buffer will have a single entry of type int.
//3 Uses the index operator to access the single entry from the buffer created in //2, assigning it a department number whose records are to be queried.
//4 Binds the buffer to the RWDBOSql object. The SQL query has only one placeholder, hence only one buffer needs to be bound. The RWDBOSql object is now ready for execution.
//5 Executes the RWDBOSql object on a supplied RWDBConnection object.
//6 Defines a constant for the number of rows to fetch at a time from the query result set.
//7 Creates RWDBTBuffer objects to be used in fetching the query result set. One object is needed per column of the result set. The types on which they are templatized are based on the mapping of the expected server types of the result columns to the SourcePro types. The type mapping is provided in Datatypes. Each buffer is created with the number of entries equal to the number of rows to be fetched at a time.
//8 Binds the output buffers to the first RWDBMultiRow object in the RWDBOSql. As this is the first (and only) result set from the execution of the RWDBOSql, we bind to the first RWDBMultiRow object that is accessed by using the index operator on RWDBOSql.
//9 Each call to fetch() fetches the result into the bound buffers. Call rowsFetched() returns the number of rows fetched. Call to fetch() is repeated until all rows in the result set are fetched, indicated by rowsFetched() returning 0.
//10 Uses the index operator on the bound output buffers to access the fetched data.
//11 Uses the isNull() method on the bound output buffer mgrBuff to determine if a particular entry in the buffer is NULL.
The second example assumes execution of an ad hoc query for which the schema of the result set is not known beforehand.
 
RWDBOSql openSql("SELECT * FROM emp WHERE dept = $1", RWDBOSql::Query);
RWDBTBuffer<int> queryDeptBuff;
queryDeptBuff[0] = 3;
 
openSql << queryDeptBuff;
openSql.execute(cn);
 
const size_t ROWS_TO_FETCH = 5;
 
RWDBMultiRow mRow(openSql.schema(), ROWS_TO_FETCH); //1
openSql[0] = mRow; //2
 
long rowsFetched = 0;
while ((rowsFetched = openSql.fetch().rowsFetched()) > 0) { //3
for (size_t row = 0; row < rowsFetched; ++row) {
for (size_t col = 0; col < mRow.numberOfColumns(); ++col) { //4
RWDBValue val = mRow[row][col]; //5
if ( val.isNull() ) { //6
cout << "NULL" << "\t";
}
else {
cout << val.asString() << "\t";
}
}
cout << endl;
}
}
The steps till the execution of the RWDBOSql are the same as the previous example.
//1 Fetches the schema of the available result set by using the schema() method on the RWDBOSql. RWDBSchema API can be used to browse through the contents of the result set schema. Here, we use the returned schema directly to create an RWDBMultiRow which creates buffers matching the datatypes of the result set columns. Each buffer will be created with ROWS_TO_FETCH entries.
//2 Assigns the created RWDBMultiRow to the RWDBOSql for use in fetching the first result set, by assigning to the 0th position in the RWDBOSql's result sets.
//3 Fetches rows into the buffers in the RWDBMultiRow with the fetch() call. The number of rows fetched are returned by the rowsFetched() call. This is repeated till all rows in the result set are fetched, which is indicated by the rowsFetched() returning 0.
//4 Finds the number of buffers in the RWDBMultiRow by using method numberOfColumns().
//5 Data inside the buffers in the RWDBMultiRow can be accessed as an RWDBRow object by calling the index operator on RWDBMultiRow passing in the row number needed. Each value in the RWDBRow instance can be accessed as RWDBValue using the index operator on the RWDBRow passing the column number needed.
//6 RWDBValue API can be used to determine the type of the value, checking if it is NULL, or to process it in other ways.