Class RWDBTBuffer<T>
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”.
Instantiating RWDBTBuffer
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:
long *pLong = new long;
Next, we create an
RWDBTBuffer, specifically an
RWDBTBuffer<long>, to encapsulate the pointer:
RWDBTBuffer<long> aBuffer(pLong);
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:
long *pLongs = new long[100]; // create array of 100 longs
RWDBTBuffer<long> anotherBuffer(pLongs, 100); // encapsulate it
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:
RWCString *bunchOfStrings = new RWCString[200];
RWDBTBuffer<RWCString> stringsBuffer(bunchOfStrings, 200);
Memory Management in RWDBTBuffer
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:
{
// Allocates 100 RWDateTimes
RWDBTBuffer<RWDateTime> someDates(100);
// Change element index 20:
someDates[20] = RWDateTime(28,7,1979);
// Now get the date using RWDBTBuffer<RWDateTime>::operator[]:
cout << “A date: “ << someDates[20];
// When we leave this block, the 100 RWDateTimes are
// automatically deallocated.
}
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.
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.
Using RWDBTBuffers for Input
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:
UPDATE EMPLOYEES SET FIRSTNAME = ?, LASTNAME = ?,
POSITIONID = ? WHERE EMPID = ?
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:
UPDATE EMPLOYEES SET FIRSTNAME = :ph0, LASTNAME = :ph1,
POSITIONID = :ph2 WHERE EMPID = :ph3
On Sybase, however, the following syntax would be necessary:
UPDATE EMPLOYEES SET FIRSTNAME = @p0, LASTNAME = @p1,
POSITIONID = @p2 WHERE EMPID = @p3
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:
RWDBDatabase aDB = RWDBManager::database(...); //1
RWDBConnection aConn = aDb.connection();
…
RWDBOSql renamer("UPDATE INVENTORY SET MAKE=:ph0 "
" WHERE MAKE=:ph1"); //2
RWDBTBuffer<RWCString> oldName(1), newName(1); //3
renamer << oldName << newName; //4
oldName[0] = "Gadget-O-Matic"; //5
newName[0] = "Gadget2000"; //6
renamer.execute(aConn); //7
oldName[0] = "SuperWidget"; //8
newName[0] = "e-Widget"; //9
renamer.execute(aConn); //10
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.
Bulk/Array Input
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:
RWCString someData[1000]; //1
populateStrings(someData, 1000); //2
…
RWDBOSql inserter(“INSERT INTO STRINGS VALUES (@p1)”); //3
RWDBTBuffer<RWCString> dataBuffer(someData, 1000); //4
inserter << dataBuffer; //5
inserter.execute(aConn); //6
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.