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 Section 13.1.4.
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 Tools.h++ and DBTools.h++ primitive types are supported:
RWCString *bunchOfStrings = new RWCString[200]; RWDBTBuffer<RWCString> stringsBuffer(bunchOfStrings, 200);
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 ourselves, 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 RWDates
RWDBTBuffer<RWDate> someDates(100);
// Change element index 20:
someDates[20] = RWDate(28,7,1979);
// Now get the date using RWDBTBuffer<RWDate>::operator[]:
cout << "A date: " << someDates[20];
// When we leave this block, the 100 RWDates are automatically
// deallocated.
}
In this example, the destructor of RWDBTBuffer automatically destroys the 100 RWDates 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:
RWDBOSql osql("SELECT NAME, HIRE_YEAR FROM EMPLOYEES"); //1
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 RWDBBuffers can contain, however, fetch() fills the buffers to capacity.
In the previous example, the capacity is 10 rows, so 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"); // 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 expression, 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:
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 access library guide from your database vendor should provide details on this feature, including placeholder syntax. 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(2); //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 substituted for :ph0 in the original Oracle SQL statement, and the string "Gadget2000" is substituted for :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 SQL statement is executed 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, respectively. In this case, only the first 20 entries of each buffer are used when execute() is invoked, because the length of the smallest buffer is 20.
©Copyright 2000, Rogue Wave Software, Inc.
Contact Rogue Wave about documentation or support issues.