DB Interface Module User’s Guide : PART V The Tutorials : Chapter 23 Updating Data in Multiple Tables : The Program
The Program
This section examines the t7 program, which writes a number of invoices to a file called t7out.txt. Each invoice represents a customer renting one or more videos from the video store.
The Main Routine
The following is the main routine for the tutorial. The line numbers correspond to the comments that follow the code.
 
#include <rw/db/db.h> //1
#include "tututil.h" //2
#include "invoice.h" //3
 
int main(int argc, char** argv) //4
{
associateStreams("t7in.dat", "t7out.txt", "t7err.txt"); //5
RWDBManager::setErrorHandler(outputStatus); //6
 
RWCString serverType, serverName, userName,
password, databaseName, pstring; //7
 
initializeDatabaseArguments(argc, argv, serverType,
serverName, userName, password,
databaseName, pstring); //8
 
RWDBDatabase aDB = RWDBManager::database(serverType, serverName,
userName, password,
databaseName,pstring); //9
if( aDB.isValid() )
{
unsigned long aCustomerID; //10
unsigned long aVideoID; //11
while (!inStream.eof()
&& !inStream.bad() && !inStream.fail()) { //12
inStream >> aCustomerID; //13
if (!inStream.eof()
&& !inStream.bad() && !inStream.fail()) //14
// create an invoice
VVInvoice aInvoice(aCustomerID, aDB); //15
// add each video rental
do { //16
inStream >> aVideoID; //17
if (aVideoID) { //18
aInvoice.addRental(aVideoID); //19
}
} while (aVideoID); //20
// print the invoice
aInvoice.print(); //21
}
}
}
closeStreams("t7in.dat", "t7out.txt", "t7err.txt");
return 0; //22
} //23
 
Here is a line-by-line description of the program:
//1 Include the declarations for the DB Interface Module classes used in this program.
//2 Include the declarations for the utility routines commonly used by all the tutorials.
//3 Include the declaration of the VVInvoice class used only by this tutorial.
//4-8 These lines are for initialization. They are common to all the tutorials and are explained in the comments in “The Main Routine.”
//9 Here an actual connection to a database server is established. The variable aDB serves as a handle to the database defined by arguments to the RWDBManager::database() function.
//10-11 These are definitions for the customer ID and video ID. These will contain the actual data read in from the stream. The values will be passed to the VVInvoice constructor and the addRental()member function.
//12 This is the start of the main processing loop, which reads the data from the stream and passes the data to the two processing routines. The condition of the stream is tested to determine if all the data was processed.
//13 The first value in the stream is read into a customer ID.
//14 This line tests the stream for end-of-file, which is only read when a customer ID is read, so the extra statement is needed.
//15 A VVInvoice object is constructed with the customer ID fetched on //14.
//16 This is the start of the video rental processing loop. Each line of the stream contains a customer ID followed by a series of video IDs. The series of videos is ended by a video ID equal to zero. If a valid video ID is read, a rental transaction is added to the invoice.
//17 The next value in the stream is read into a video ID.
//18 The video ID is checked to determine if the end of the video list was read. If the video ID is valid, a rental transaction is added to the invoice.
//19 A rental is added to the invoice using the video ID obtained above via a call to the VVInvoice addRental() function. Further explanation of addRental() is in “VVInvoice::addRental.”
//20 End of the video rental processing loop, which checks the video ID for a zero value that marks the end of the series.
//21 After all the rentals for a customer have been added, the invoice is printed. An invoice retains all knowledge of customer ID, invoice ID, and rentals, so no parameters are needed for the print() member function. Because most of the code in print() deals with output formatting, and the database lookup routines were featured in previous tutorials, there is no line-by-line explanation in this tutorial.
//22 Normal program exit.
//23 Destructors for all the objects are called here. The database closes automatically when its destructor is called.
VVInvoice::addRental
The invocation of the addRental() function inserts new data into the rentals table and updates the videos table. The source code of this member function can be found in the source file invoice.cpp.
 
void
VVInvoice::addRental(unsigned long aVideoID) //1
{
RWDBConnection dbSession = aDB_.connection(); //2
dbSession.beginTransaction(); //3
RWDateTime rentalDate(9,6,2000); //4
// due date is three(3) days from default
RWDateTime dueDate(rentalDate); //5
dueDate = dueDate + (3 * RWDateTime::millisecsInDay); //6
// returnDate is not assigned now, so make it invalid
RWDateTime returnDate; //7
 
// add a rental transaction
VVRentalTransactionRepository rentals(aDB_, rentalTableName); //8
VVRentalTransaction aRental(aCustomerID_, aVideoID,
aInvoiceID_, rentalDate,
dueDate, returnDate); //9
rentals.insert(aRental, dbSession); //10
 
// update video inventory
VVVideoRepository videos(aDB_, videoTableName); //11
VVVideo aVideo("", aVideoID, 0, "", 0, 0, ""); //12
aVideo = videos.find(aVideo); //13
if (aVideo.numOnHand() > 0) //14
aVideo.numOnHand(aVideo.numOnHand() - 1); //15
videos.update(aVideo, aVideo, dbSession); //16
// commit the transaction
dbSession.commitTransaction(); //17
}
//1 This line defines the addRental() member function. It accepts one parameter, a video ID. The invoice ID and customer ID were saved as local data members of the VVInvoice when it was constructed. A database was also retained to make access to the database simpler for all member functions.
//2 Because two tables are updated by this routine, a single connection is used to control the table manipulations as a single transaction. This is only possible through a common connection.
//3 The first step in grouping several data manipulations into a single transaction is to turn off the default auto-commit behavior of the DB Interface Module. By calling beginTransaction(), the connection is reset to not make data manipulations permanent until commitTransaction() is invoked. See //17.
//4-7 The RWDateTime instances created on these lines are used to initialize a new VVRentalTransaction instance on line 9.
//8 An instance of the class VVRentalTransactionRepository is created on this line to represent the rentals table. The first argument, aDB_, identifies the database where the instance’s data resides, which is the same database where invoice’s data resides. The second argument identifies the specific table name that holds the rental transaction information.
//9 A new VVRentalTransaction instance is created on this line to represent a rental of the video identified by aCustomerID_, aVideoID and aInvoiceID_. Rental dates and times are also provided.
//10 The new rental is inserted into the rental table.
//11 Now the video table must be updated to reflect the stock going down by one video. On this line, an instance of the class VVVideoRepository is created to represent the videos table. The first argument, aDB_, identifies the database where the instance’s data resides, which is the same database where the invoice’s data resides. The second argument identifies the specific table name that holds the video information.
//12 An incomplete VVVideo instance is created to pass to the find() member function of VVVideoRepository. Only the video ID is provided.
//13 The find() member function of VVVideoRepository takes an instance of VVVideo that has only partial information and finds a matching record in the videos table. It then returns an instance of VVVideo containing the complete record. On this line, a VVVideo instance containing only a VideoID is given to the find() function. Once the routine returns, the instance of VVVideo contains complete information about the video with the correct video ID. The find() member function of VVVideoRepository is explained in “VVVideoRepository::find.”
//14-15 The following section of code updates the quantity on hand for the video being rented. It does this by using the video found in the previous line. It decrements the numOnHand field by one.
//16 On this line, an update of the video record takes place. The first parameter is used only for a key to the video that is to be updated. The second parameter is the new value for the video record. Since these are the same, the update is to the same video. This routine is explained in more detail below. The third parameter is the common connection. This groups this update with the previous addition of a video rental.
//17 Once both tables have been updated, this line commits all data manipulations as a single transaction. In this case, the addition of a new video rental on //10 and the update of the number of videos on hand on //16 are committed together.
VVVideoRepository::find
The find() member function of the class VVVideoRepository, takes a VVVideo instance as an argument, with only some of its fields filled in. It uses this partial VVVideo as a search criterion. The function returns the first video it finds that matches the field passed in. The source code of this member function follows, taken from the file vidrep.cpp.
 
VVVideo
VVVideoRepository::find(const VVVideo& aVideo) const //1
{
RWDBCriterion searchCriterion("0 = 0", 0, 0); //2
RWCString title = aVideo.title(); //3
if (!title.isNull()) //4
searchCriterion = searchCriterion &&
titleColumn_.like(title); //5
if (aVideo.id()) //6
searchCriterion = searchCriterion &&
idColumn_ == aVideo.id(); //7
if (aVideo.year()) //8
searchCriterion = searchCriterion &&
yearColumn_ == aVideo.year(); //9
RWCString category = aVideo.category(); //10
if (!category.isNull()) //11
searchCriterion = searchCriterion &&
categoryColumn_.like(category); //12
if (aVideo.quantity())
searchCriterion = searchCriterion &&
quantityColumn_ == aVideo.quantity(); //13
if (aVideo.numOnHand()) //14
searchCriterion = searchCriterion &&
numOnHandColumn_ == aVideo.numOnHand(); //15
 
RWCString synopsis = aVideo.synopsis(); //16
if (!synopsis.isNull()) //17
searchCriterion = searchCriterion &&
synopsisColumn_.like(synopsis); //18
 
RWDBSelector aSelector = aDB_.selector(searchCriterion); //19
aSelector << table_; //20
RWDBReader aReader = aSelector.reader(); //21
VVVideo theVideo; //22
if (aReader()) //23
aReader >> theVideo; //24
 
return theVideo; //25
 
//1 This is the definition of the find() member function for the VVVideoRepository class. It accepts a single parameter, an instance of VVVideo. This VVVideo instance is designed as a criterion for selecting a specific video. The fields within the VVVideo are used to select the first video that matches the fields, returning the completed VVVideo.
//2 Throughout the following section of code, a condition is created based on the fields that have values. Building the condition dictates surveying the fields of this VVVideo, and for each nonzero or non-null value found, adding an appropriate clause to the condition. Clauses are added to the condition using a logical and operation &&. Since it is not known which field will be the first to have a value, the search criterion is first set to a simple expression of true. The && operator can be applied to additional conditions as they are found. This line of code creates a predicate in the form of an RWDBCriterion with true as its value.
//3 The RWCString created on this line is a copy of the VVVideo instance’s title field.
//4-5 If the title field is not null, then a value exists in the field that should be used as a search criterion. The existing search criterion is combined with an expression using the && operator that translates to the equivalent of ... AND video.title LIKE 'sometitle'.
NOTE >> Do not confuse the RWCString::isNull method with NULL database values. The RWCString::isNull method returns true if the string is empty. However, some database vendors do not consider an empty string to be the same as a NULL value.
//6-7 If the ID field has a nonzero value, it should be added to the search criterion. (Zero is an invalid value for an ID.) The existing search criterion is combined with an expression using the && operator that translates to the equivalent of ... AND video.ID = 69, where 69 is an arbitrarily chosen value that represents the value in aVideo.id().
//8-18 These lines continue in the same vein, adding predicates to the search criterion as the routine finds nonempty fields.
//19 Here, a selector is created by the database object. The search criterion is passed to the constructor where it is used in the WHERE clause of the SELECT statement.
//20 Shifting a table instance into an RWDBSelector instance selects all columns from that table.
//21 Creating a reader here submits the query to the server.
//22 Instantiate a VVVideo instance to hold the results of the query.
//23 Advance the reader to the first row of the result set.
//24 Read the row into the instance of VVVideo.
//25 Return the VVVideo. If the query resulted in multiple matches, the additional rows are discarded when the RWDBReader is destroyed.
VVVideoRepository::update
The update() member function of VVVideoRepository accepts three parameters, first two of which are instances of VVVideo. The first instance identifies the record to be changed, while the second instance is used as new information. The third parameter is an instance of RWDBConnection which is used to execute the update.
 
VVVideoRepository&
VVVideoRepository::update(const VVVideo& originalVideo,
const VVVideo& newVideo,
const RWDBConnection& aConnection) //1
{
RWDBUpdater anUpdater = table_.updater(); //2
anUpdater.where(idColumn_ == originalVideo.id()); //3
 
anUpdater << titleColumn_.assign(newVideo.title())
<< idColumn_.assign(newVideo.id())
<< yearColumn_.assign(newVideo.year())
<< categoryColumn_.assign(newVideo.category())
<< quantityColumn_.assign(newVideo.quantity())
<< numOnHandColumn_.assign(newVideo.numOnHand())
<< synopsisColumn_.assign(newVideo.synopsis()); //4
 
anUpdater.execute(aConnection); //5
 
return *this;
}
 
//1 This is the definition of the update() member function for the VVVideoRepository class. It accepts three parameters, first two of which are instances of VVVideo. The first represents the original video to update, the second represents the new values. Only the ID field of the original record is used to identify the record to be changed. The third parameter is an instance of RWDBConnection which is used to execute the update.
//2 An RWDBUpdater instance associated with the videos table is created here.
//3 A condition is created here that identifies the row in the videos table to be updated. The condition takes the form of an RWDBCriterion that is created by applying the operator== to the RWDBColumn instance idColumn_. Assuming that the original ID was 34, this predicate will be the equivalent of videos.ID = 34.
//4 A series of RWDBAssignment instances are created on this line and shifted in the RWDBUpdater defining the SET clause of the UPDATE statement. Each RWDBAssignment instance is created using the RWDBColumn::assign() method. Each column of the videos table is assigned the corresponding value from the newVideo object, thus updating the row selected by the condition on //3 to match the values in newVideo.
//5 The RWDBUpdater is executed using the supplied RWDBConnection.