RWDBTBuffer<T> is a template class designed to provide a type-safe encapsulation of a pointer to data or to an array of data. It is used to provide information about your program variables to class RWDBOSql, as explained in “Obtaining the Results.”

Because RWDBTBuffer is a template class, you instantiate the class on a particular type, the type pointed to by the pointer that the class encapsulates. To demonstrate how to instantiate RWDBTBuffer, we first create a pointer to some data:

Next, we create an RWDBTBuffer, specifically an RWDBTBuffer<long>, to encapsulate the pointer:

If we want our RWDBTBuffer to encapsulate a pointer to an array of objects rather than a single object, we pass in the number of elements in the array:

Of course, we can also create RWDBTBuffers for more than just C++ fundamental types. All the primitive types of the Essential Tools Module and the DB Interface Module are supported:

Since we allocated our own memory for data buffers in the previous section, we are now responsible for deallocating it. Of course, we can always use delete to deallocate the memory directly. Alternatively, however, we can let RWDBTBuffer allocate and deallocate storage automatically by using a constructor that doesn’t take a pointer to our data. Allowing RWDBTBuffer to manage its own memory frees us from explicitly managing memory, and simplifies our code in many cases.

To take advantage of automatic memory management, we simply construct the RWDBTBuffer, specifying only the number of elements to allocate. When we need access to the data, we use the overloaded operator[], as shown in this example:

In this example, the destructor of RWDBTBuffer automatically destroys the 100 RWDateTimes allocated by the constructor. Had we passed in a pointer to our own array in the RWDBTBuffer constructor, it would not have been deallocated, because it is not owned by the RWDBTBuffer instance. RWDBTBuffer deletes only the data it owns.

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:

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.

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:

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.

In many cases, when you are designing or coding an application, you know the form of an SQL statement to be executed, but not the data. Perhaps you expect the data to vary during execution. For example, knowing the schema of a database table, you know that a certain object in your program must always execute the statement:

However, the data that should take the place of the ?s in this statement won’t be known until runtime. You could construct a new statement each time your object performs this operation, and include the data in the text of your statement, but this procedure would be highly inefficient. Instead, you can use RWDBTBuffers to bind data into your statement where needed. Assigning data to a variable at runtime is known as input binding. It is a feature provided by most supported databases.

To use this feature, we first create an SQL statement like the one above, with the data left unspecified. Instead of the data, we use a placeholder, a piece of vendor-specific syntax that indicates that data will be provided through input binding. The documentation from your database vendor should provide details on this feature, including placeholder syntax. Placeholder syntax is also mentioned in the Open SQL section of the Access Module User's Guides.

For example, on Oracle, we would create the above statement using this syntax:

On Sybase, however, the following syntax would be necessary:

Once you’ve created a statement with the proper syntax, you simply construct RWDBTBuffers for the data that should be provided for these placeholders, and use operator<< to bind them into your RWDBOSql instance. Invoking execute() will cause the statement, along with the current value of your bound-in data, to be sent to your database for execution. Let’s look at an example, using Oracle syntax:

On //1, we create an RWDBDatabase to establish database connectivity. On //2, we create an RWDBOSql to encapsulate the SQL statement we’ll use for renaming entries in our INVENTORY table. On //3, we create two RWDBTBuffers, one for each placeholder in our renamer, and on //4, we bind the RWDBTBuffers to the RWDBOSql. Note that operator<< works positionally, in the sense that each call to operator<< advances the position to the next placeholder in our SQL statement.

Lines //5 and //6 set the values used for the first execution of the statement, which is actually performed on //7. The string "Gadget-O-Matic" on //5 is bound to :ph0 in the Oracle SQL statement, and the string "Gadget2000" is bound to :ph1. Please note that RWDBOSql does not itself replace the placeholders in the SQL statement with the provided values. The statement and the data are simply provided to the database. The database interprets the statement, finds the placeholders in the statement, and matches them with the provided data.

On //8-//10, new data is provided, and the UPDATE is re-executed with that data. For most databases, this operation is much faster than creating and executing a new statement because the database doesn’t need to reparse the SQL.

For simplicity, error checking is not performed in this example.

Along with input binding of single values to placeholders, some databases allow arrays of data to be bound into an SQL statement. When an array is bound, the database executes the SQL statement once for each array entry. This operation is often termed bulk binding or array binding. Since RWDBTBuffer can encapsulate either a single value or an array, using the array binding capabilities of your database is simply a matter of supplying RWDBTBuffers with more than one entry. Here’s an example using Sybase syntax:

On //1, we create an array of RWCStrings, which is populated on //2 by a function defined elsewhere. On //3, we create an RWDBOSql, which will be used for inserting the data into the STRINGS table. On //4, an RWDBTBuffer is created to encapsulate the array created earlier, and on //5, the RWDBTBuffer is bound into the RWDBOSql. Line //6 executes the statement, causing all 1000 entries to be inserted into the STRINGS table.

If you bind multiple RWDBTBuffers to an RWDBOSql with multiple placeholders, the RWDBOSql uses the size of the smallest RWDBTBuffer to determine how many times to execute its encapsulated SQL statement. For example, consider a statement with three placeholders, and three RWDBTBuffers of lengths 20, 40, and 50 entries, respectively. In this case, only the first 20 entries of each buffer are used when execute() is invoked, because the entries of the smallest buffer total 20.