Class RWDBInserter is used to insert data into database tables. An inserter is obtained by request from a database table, and it must be supplied with data to insert. The data can come from program variables or literals that you provide from functions supplied by the database, from other tables in the same database, or from tables in another database. We'll examine each method separately.
In the first form of insertion, values are supplied directly from program variables:
int videoID = 1234;
int supplierID = 11;
int purchaseOrderNumber = 999;
RWDecimalPortable pricePerUnit("29.95");
int quantity = 12;
RWDBDateTime date;
RWDBTable purchases = myDbase.table("purchase");
RWDBInserter insert = purchases.inserter();
insert << videoID << supplierID << purchaseOrderNumber
<< pricePerUnit << quantity << date; //1
insert.execute(myConnection);
As usual, an inserter is obtained for the purchase table. On //1, we supply values for a row in the table, adding them to the inserter with the insertion operator. When the execute() method is invoked, the database is accessed and a row of data is inserted into the table.
To populate a database table with data selected from other database tables, we use a selector to provide data to an inserter. In this example, we want to ensure that we have 20 copies of each video in our inventory, so we generate purchase orders to replace copies that are sold. For simplicity, we assume there are no more than 20 copies of a particular video on hand.
RWDBTable sales = myDbase.table("sales");
RWDBTable videos = myDbase.table("videos");
RWDBTable suppliers = myDbase.table("supplier");
RWDBSelector select = myDbase.selector(); //1
select << sales["videoID"] << suppliers["ID"]
<< nextOrderNumber() << videos["price"]
<< 20 - videos["OnHand"] << rwdbSystemDateTime();
select.where(sales["ID"] == videos["ID"] &&
suppliers["ID"] == videos["supplierID"]);
RWDBTable purchases = myDbase.table("purchase"); //2
RWDBInserter insert = purchases.inserter(select); //3
insert.execute(myConnection); //4
The first few lines of the example instantiate three tables. Starting on //1, an RWDBSelector is constructed in the familiar way. The selector extracts the desired rows from a join of the sales, videos, and supplier tables. On //2, a reference to the purchase table is instantiated, and on //3 an inserter is obtained and provided with data from the selector. Until now, there is no interaction with the database. Finally, rows are inserted into the purchase table by invoking the inserter's execute() method on //4. As recommended, we specify a connection for the database interaction.
Notice that no data flows from the database into the application. The selection and insertion are all done within the database server.
Now let's suppose that the purchase table resides in an entirely different database from the sales, videos, and supplier tables. In this case, the data must be selected from one database into our application, then transferred from our application into the other database. With DBTools.h++, this is easy. Simply substitute the following code for //2-//4 in the previous example:
RWDBTable purchases = anotherDbase.table("purchase");
RWDBInserter insert = purchases.inserter(); //1
RWDBReader reader = select.reader(myConnection); //2
while(reader()) {
insert << reader; //3
insert.execute(anotherConnection); //4
}
On //1 an inserter is obtained for the purchase table, without supplying an argument. On //2 a reader for the previously constructed selector is declared, and on
//3 the insertion operator is used to supply the inserter with data from the reader. When the inserter is executed on //4, the purchase table is populated with the current row from the reader. This time there are two database interactions:
Rows are read into the application using myConnection with myDbase
Rows are inserted using anotherConnection with anotherDbase.
Normally, we wouldn't expect an insert operation to produce any results. However, some database implementations include triggers, which can cause other events not necessarily related to the insertion to occur. To allow applications to handle this sort of behavior, the execute() method of every DBTools.h++ class returns an RWDBResult instance.
NOTE: Classes with an execute() method return RWDBResult as their return type.
An RWDBResult encapsulates the notion of a sequence of zero or more tables. Its table() method is used to extract the next table from the sequence. Consequently, where our examples have shown:
insert.execute(myConnection); //1
we might have substituted a loop:
RWDBResult result = insert.execute(myConnection);
RWDBTable resultTable = result.table();
while(resultTable().isValid()) {
// process table of results
resultTable = result.table();
}
If your application has a sensible way to process these unexpected sets of results, you may prefer the second approach. If not, you can safely ignore the results as we did on //1, since DBTools.h++ takes responsibility for keeping connections in a consistent state. In these examples, any unexpected results are silently discarded next time the connection is used.
So far, all our inserter examples have specified a connection to the execute() method. While not strictly necessary, this is probably a good idea. Consider the following example:
RWDBInserter insert = someTable.inserter(); insert << x1 << y1; insert.execute(); insert << x2 << y2; insert.execute(); insert << x3 << y3; insert.execute(); // etc.
If this fragment were run, we would discover that each execute() call causes another database connection to be opened. This is because the temporary RWDBResult instances produced by the execute() calls are not destroyed until the current code block is exited, so their connections cannot be reused. Opening additional connections can be circumvented by placing each execute() call in its own block, like this:
{ insert.execute(); }
A better idea is to use connections explicitly, like this:
RWDBInserter insert = someTable.inserter(); insert << x1 << y1; insert.execute(myConnection); insert << x2 << y2; insert.execute(myConnection); insert << x3 << y3; insert.execute(myConnection); // etc.
RWDBUpdater and RWDBDeleter handle connections the same way as RWDBInserter and RWDBBulkInserter. See Chapter 7 for more information on RWDBBulkInserter.
NOTE: By using explicit connections, you avoid opening unneeded connections and reduce the risk of an error if there aren't enough connections available.
You can use class RWDBInserter to insert data into database tables. There are three basic approaches:
To insert data selected from other tables in the same database, provide the inserter with a selector.
To insert data from outside the database, provide the inserter with a reader.
To insert data from your program, use the insertion operator for each data item.
An inserter's execute() method returns an RWDBResult, which can be safely ignored. Using explicit connections for the execute() method is recommended, to avoid opening unnecessary multiple connections.
©Copyright 2000, Rogue Wave Software, Inc.
Contact Rogue Wave about documentation or support issues.