Output Binding and Fetching Using RWDBTBuffer
Now that you know how to encapsulate buffers with RWDBTBuffer, let’s see how to associate RWDBTBuffers with an RWDBOSql so that the results produced by executing an SQL statement are placed into the buffer. This procedure is also known as output binding and fetching. To perform output binding and fetching, we use operator[] and operator>>, which allow us to specify a particular result set and result column on which to bind a buffer. Let’s look at an example:
RWDBOSql osql("SELECT NAME, HIRE_YEAR FROM EMPLOYEES", //1
RWDBOSql::Query);
RWDBTBuffer<RWCString> nameBuffer(10); //2
RWDBTBuffer<unsigned> hireBuffer(10); //3
// Bind nameBuffer and hireBuffer to the first two columns
// in the 0th result set.
osql[0] >> nameBuffer >> hireBuffer; //4
On //1, we create a statement that returns a result set with two columns: one column of the string type of our database, and one column of the unsigned integral type of our database. On //2 and //3, we create 10 element RWDBTBuffers, which we bind to the result set, one RWDBTBuffer per column. On //4, we bind nameBuffer and hireBuffer to the 0th result set, as the comment indicates.
When the statement has executed, we can fetch the results using the fetch() method. Calling fetch() causes as much data as possible, for the current result set, to be fetched into the output buffers.
If our query produces fewer rows than the number of elements in the RWDBTBuffers, then the buffers are not entirely filled by the fetch() call. If our query produces more rows than the RWDBTBuffers can contain, however, fetch() fills the buffers to capacity. The capacity of the buffers is determined as the smallest number of elements of all the RWDBTBuffer instances bound to the result set being fetched. In the previous example, the capacity is 10 rows, as both buffers can contain 10 elements. If rows still remain for us to fetch, subsequent calls to fetch() would result in more rows being fetched into the buffers, and overwriting the data present there.
Now let’s look at an example that fetches data:
RWDBDatabase aDB = RWDBManager::database(...); // 1
RWDBConnection aConn = aDb.database();
…
RWDBOSql anOSql("SELECT ID FROM INVENTORY", RWDBOSql::Query); // 2
anOSql.execute(aConn); // 3
if( anOSql.isValid() && anOSql.hasResult() ) { // 4
unsigned long idArray[100]; // 5
RWDBTBuffer<unsigned long> idBuffer(idArray, 100); // 6
anOSql[0] >> idBuffer; // 7
while(anOSql.fetch(), anOSql.rowsFetched() > 0) { // 8
printInventoryIDs(idArray, anOSql.rowsFetched()); // 9
}
}
On //1, we create an RWDBDatabase in order to connect to our database. On //2, we create an RWDBOSql that encapsulates a query that should return a single-column result set. For this example, we assume ID is an unsigned integer column. On //3, we execute the query using the execute() method. Note that no output buffers are bound at this time; you can choose to bind your output RWDBTBuffers either before or after execution. Of course, you must bind the output buffers before calling fetch(), or there is no place into which to fetch the data!
On //4, we call isValid() to check that no error occurred. We also call RWDBOSql::hasResult() to check that results were actually produced. The method hasResult() returns true if there are available result sets that you haven’t yet processed.
On //5, we create an array of 100 unsigned longs on the stack. On //6, we encapsulate the array in an RWDBTBuffer, idBuffer, so that the array can be used as an output binding. On //7, we bind idBuffer to the first column in the 0th result set, the ID column specified in our SELECT.
Line //8 begins the results processing loop. In the while loop, we call fetch() to fetch data, and exit the loop if rowsFetched() returns 0. As long as rowsFetched() remains greater than 0, indicating that there is result data, the function printInventoryIDs() prints the result data that is fetched.
On each call to fetch(), RWDBOSql fetches as many rows as possible from the result set. In this case, the output binding holds 100 entries, so at most 100 rows are fetched. If fewer rows are available in the result set, all the rows are fetched. After each call to fetch(), the function rowsFetched() returns the number of rows fetched, which indicates what part of the array contains fetched data. When all the result data is exhausted, calling fetch() returns no rows and rowsFetched() returns 0, which tells us to exit the loop.
