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, 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;
RWDateTime date(RWDateTime::setCurrentTime);
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 SourcePro DB, 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 class in the DB Interface Module returns an RWDBResult instance with the exception of RWDBOSql.
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 the DB Interface Module takes responsibility for keeping connections in a consistent state. In these examples, any unexpected results are silently discarded the 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. |
In this example, each call to execute() causes another database connection to be opened. When execute() is called without a connection, a connection is automatically requested from the connection pool inside the RWDBDatabase. For some C++ compilers, the temporary RWDBResult instances produced by the execute() calls are destroyed only when the current code block is exited, so their associated connections are busy and unavailable until the end of the block. The RWDBInserter may also keep a reference to the last connection on which it was executed, marking that connection busy as well. In short, each call to execute() causes a new connection to be created because none of the previously created connections are available.
A better idea is to use one connection explicitly, or increase the size of the connection pool by using RWDBDatabase::defaultConnections() method.
RWDBInserter insert = someTable.inserter(); insert << x1 << y1; insert.execute(myConnection); insert << x2 << y2; insert.execute(myConnection); insert << x3 << y3; insert.execute(myConnection); // etc. |
In this code, the same connection is reused for all the insertions. You avoid opening unneeded connections, and reduce the risk of an error if there aren't enough connections available. You may also improve performance, since you reduce the overhead associated with opening new connections.
Explicit connections may improve performance. Increasing the size of connection pool may also improve performance.
RWDBUpdater and RWDBDeleter handle connections the same way as RWDBInserter and RWDBBulkInserter. See Chapter 8, "Bulk Reading and Writing," for more information on RWDBBulkInserter.
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 © Rogue Wave Software, Inc. All Rights Reserved.
The Rogue Wave name and logo, and SourcePro, are registered trademarks of Rogue Wave Software. All other trademarks are the property of their respective owners.
Provide feedback to Rogue Wave about its documentation.