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.
