In many ways, class RWDBTable is the fundamental data structure of the DB Interface Module. The implementation of tables in the DB Interface Module parallels the relational view of data. In A Guide to the SQL Standard (see Appendix A), Date says of the table expression in SQL:
"...in many ways [the table expression] can be regarded as being at the top of the syntax tree... the whole point is, precisely, that the expression does evaluate to a table..."
Class RWDBTable represents a tabular collection of data. A base class from which a family of classes derive, RWDBTable provides an interface for the following types of tables:
The database table: a handle for a table physically stored in a database.
The derived table: representing a SQL query as a table, which can be used in other queries instead of a database table.
The memory table: a collection of rows that resides in program memory.
The result table: the result of a query, corresponding to an SQL table expression, which is transient, existing only while data is being retrieved.
The physical location of the data is transparent to RWDBTable clients. The interface to the data is the same, whether it resides in persistent storage or in program memory, or is completely transient.
RWDBTable provides a uniform view of tabular data. The interface to the data is the same wherever it is stored.
RWDBTable instances are produced by RWDBDatabase instances and by instances of a class, RWDBResult, which is introduced in Section 4.4.5. As you will see, the basic SQL operations SELECT, INSERT, DELETE, and UPDATE are handled by the classes RWDBSelector, RWDBInserter, RWDBDeleter, and RWDBUpdater, respectively. These classes in turn produce instances of class RWDBResult. Thus, all data manipulation operations result in a sequence of tables, which your application may read or not, as you see fit.
RWDBTable provides a variety of data manipulation and data definition services. This chapter and Chapter 5, "The Data Manipulation Classes," cover the basics; the SourcePro C++ API Reference Guide provides a complete listing of its features.
The most straightforward way to obtain an RWDBTable is to request one by name from RWDBDatabase:
RWDBTable purchases = myDbase.table("purchase");
|
The purchases object allocated by this code fragment represents the tabular collection of data known as the purchase table in myDbase. Allocating it is relatively inexpensive, owing to one of the design axioms of the DB Interface Module:
All database access is delayed until absolutely necessary, and unnecessary database interaction is avoided entirely.
This axiom exists because hitting the database is the most expensive thing that database applications do. In the example above, it's unnecessary to consult the database in order to create the table object; database interaction is deferred until database services are requested. Here's an illustration:
RWDBTable purchases = myDbase.table("purchase"); //1
if (!purchases.exists(conn1)) { //2
std::cout << "purchase table does not exist!" << std::endl;
}
else {
RWDBSchema purchasesSchema = purchases.schema(); //3
std::cout << "Columns in the " << purchases.name() //4
<< " table:" << std::endl;
size_t numCols = purchasesSchema.entries(); //5
for (size_t i = 0; i < numCols; i++) { //6
std::cout << "\t" << purchasesSchema[i].name() << std::endl; //7
}
}
|
Here //1 does not require the application to access the database. The exists() call on //2 must access the database to determine whether the purchase table actually exists. The API of the DB Interface Module makes it easy to determine whether a method will access the database or not:
Methods that require database access may be called with a connection parameter. Methods without a form that accepts a connection parameter do not access the database.
A table in a relational database is defined by its schema, which is an ordered collection of column definitions. The RWDBSchema object declared on //3 of the example above represents the schema of the purchase table. It is an ordered collection of RWDBColumn instances. To illustrate its use, the code on //5 determines the number of columns in the purchase table, and the code on //6 and //7 print the name of each column.
It is worth mentioning that the schema() method on //3 is simply an accessor function, requiring no database interaction. The schema information was actually retrieved from the database by the exists() call on //2. If we had not called exists(), the schema() call would have returned an RWDBSchema with zero entries. Note that RWDBTable has a fetchSchema(const RWDBConnection&) method as well, if you need to explicitly access schema information from a table.
Finally, a table in a relational database always has an owner, the person who controls access to the table and data within the table. The owner of a table can grant privileges to other users, such as access, insert, update, and so on. For example, an application may be able to determine if a table exists, but not update the data. Be sure to determine the ownership and privileges of a table before working with it.
Before we look at the other types of tables available in the DB Interface Module, let's look at how the data in a table is read. The next example program shows how to read information from a table using the class RWDBReader. The database table it references is the purchase table, which is part of the tutorials for SourcePro DB.
You must install and compile the tutorials when you install the SourcePro DB in order to run this example. See Installing and Building Your SourcePro C++ Products and Building Your Applications for more information.
To run this example, change the parameters for the RWDBManager::database() call to fit your system.
// Example Program 2 - Reading a database table
#include <rw/rstream.h>
#include <rw/db/db.h>
int
main()
RWDBDatabase myDbase = RWDBManager::database(
"libctl8012d.so", // DB Access Module name
"SYBASE100", // server name
"user", // user name
"pwd", // password
"DEMO_DB" // database name
);
RWDBConnection myConnection = myDbase.connection();
RWDBTable purchases = myDbase.table("purchase");
RWDBReader rdr = purchases.reader(myConnection);
int videoID;
int supplierID;
int purchaseOrderNumber;
RWDecimalPortable pricePerUnit;
int quantity;
while (rdr()) {
RWDateTime date;
rdr >> videoID >> supplierID >> purchaseOrderNumber //1
>> pricePerUnit >> quantity >> date;
std::cout << videoID << "\t" << supplierID << "\t"
<< purchaseOrderNumber << "\t" << pricePerUnit << "\t"
<< quantity << "\t" << date.asString() << std::endl;
}
return 0;
}
|
RWDBReader reads tabular data, providing sequential access to a table's rows, and random access to fields within a row.
While traversing a table's rows, a reader acts much like an iterator. When a reader is first obtained from a table, it is positioned before the first row of the table. The C++ function call operator() is used to advance the reader to the next row. When there are no more rows, it returns 0.
Within a row, the reader is used with the extraction operator >>, which extracts values from the reader into program variables. Because indexing by column name and column number is also supported, we could have written //1 of our example like this:
rdr["videoID"] >> videoID; rdr["supplierID"] >> supplierID; // etc. |
If we were concerned about NULL values in certain columns, we could have used indicator variables with our reader as a way to detect them:
RWDBNullIndicator nullVideo, nullSupplier;
int videoID;
int supplierID;
int purchaseOrderNumber;
RWDecimalPortable pricePerUnit;
int quantity;
RWDateTime date;
while (rdr()) {
rdr >> nullVideo >> videoID >> nullSupplier >> supplierID
>> purchaseOrderNumber >> pricePerUnit >> quantity >> date;
if (nullVideo || nullSupplier) {
std::cout << "Missing Information!" << std::endl;
}
else {
std::cout << videoID << "\t" << supplierID << "\t"
<< purchaseOrderNumber << "\t" << pricePerUnit << "\t"
<< quantity << "\t" << date.asString() << std::endl;
}
}
|
The extraction operator is defined for all the C++ and DB Interface Module data types. Naturally, it is easy to define it for your own classes. For example:
struct Purchase {
int videoID;
int supplierID;
int purchaseOrderNumber;
RWDecimalPortable pricePerUnit;
int quantity;
RWDateTime date;
};
RWDBReader&
operator>>(RWDBReader& rdr, Purchase& p) {
rdr >> p.videoID >> p.supplierID
>> p.purchaseOrderNumber
>> p.pricePerUnit >> p.quantity >> p.date;
return rdr;
}
|
Now we can read from the purchase table directly into Purchase instances:
Purchase p;
while (rdr()) {
rdr >> p;
// process p;
}
|
This is an important technique, and it is explained more thoroughly in Chapter 17, "Retrieving Data from a Table," the first advanced tutorial.
The RWDBTable interface supports derived tables representing a SQL query. The results from a derived table can be used in queries in the same way as a persistent table. The tabular result set produced from the execution of the SQL query constitutes the contents of the derived table. The derived table becomes part of the SQL that defines and uses it. It is created on the fly by the database while executing the SQL.
Derived tables can be used in the FROM clause of a SQL query in the same way a persistent database table is used. As a derived table does not physically exist in the database, it must have a table tag.
To obtain a derived table, use API RWDBDatabase::table(const RWDBSelectorBase&). This method is similar to that which obtains the handle to the persistent database table, except that, rather than the name of the table, this method accepts the RWDBSelectorBase instance representing the SQL query that creates the derived table. For example:
RWDBTable derivedTab = myDbase.table(select); |
where myDbase is a valid RWDBDatabase object and select is a RWDBSelectorBase instance. The derivedTab instance can now be used similarly to a persistent table in a SQL query.
Following is an example of using a derived table. It assumes that myDbase is a valid RWDBDatabase object and myConn a valid RWDBConnection object.
RWDBTable tabLoc = myDbase.table("location"); //1
RWDBTable tabEmp = myDbase.table("employee"); //2
RWDBSelector derivedSel = myDbase.selector(); //3
derivedSel << tabLoc; //4
derivedSel.where(tabLoc["country"] == "ABCD"); //5
RWDBTable tabLocDerived = myDbase.table(derivedSel); //6
RWDBSelector sel = myDbase.selector(); //7
sel << tabEmp["id"] << tabEmp["name"] << tabLocDerived["name"]; //8
sel.where(tabEmp["locid"] == tabLocDerived["id"]); //9
RWDBResult result = sel.execute(myConn); //10
|
Lines //1 and //2 create handles to the persistent tables – location and employee respectively. Line //3 through //5 create an RWDBSelector object that represents a SQL that fetches all records where the country is ABCD. The RWDBSelector is explained in detail in Section 5.3, "Selecting Data." Line //6 defines a derived table encapsulating the above SQL query.
Lines //7 through //9 define an RWDBSelector that uses the derived table created in //6, as well as the persistent table employee, to fetch employee information for all employees in the country ABCD. Finally, line //10 executes the query on the supplied connection.
By encapsulating the derived table functionality in the RWDBTable interface, both persistent tables and derived tables can be used with one consistent API.
So far, we have declared all our tables as instances of the base class RWDBTable. We did this to emphasize the uniform interface to tabular data provided by RWDBTable. However, there are alternatives to RWDBTable. For example, class RWDBMemTable represents a tabular collection of data stored in program memory. RWDBDatabase instances can produce memory tables in much the same way that they produce database tables:
RWDBTable purchases = myDbase.memTable("purchase", myConnection); //1
|
The result of this call is to copy all the rows of data from the purchase table in myDbase into program memory. Since this task requires database interaction, you have the option of supplying a connection. You can also obtain memory tables by copying database tables. The constructor on //3 below has the same effect as //1 above.
RWDBTable dbPurchases = myDbase.table("purchase"); //2
RWDBTable mtPurchases =
RWDBMemTable(dbPurchases, myConnection); //3
|
Note that as a direct result of the inheritance mechanism of C++, you can read the dbPurchases object and the mtPurchases object in the same way, with a reader:
RWDBReader dbRdr = dbPurchases.reader(); RWDBReader mtRdr = mtPurchases.reader(); |
RWDBMemTable provides other services as well, including an adjustable capacity feature and unrestricted random access via double indexing. For example:
RWDBMemTable mt20Purchases(dbPurchases, 20); //4
size_t numberOfColumns = mt20Purchases[0].entries(); //5
for (int i = 0; i < mt20Purchases.entries(); i++) { //6
for (int j = 0; j < numberOfColumns; j++) //7
std::cout << mt20Purchases[i][j].asString() << "\t"; //8
std::cout << std::endl;
}
|
On //4 of the example above, we define a memory table restricted to a maximum of twenty entries. Thereafter, we output each cell of the memory table as if the memory table were a two-dimensional array. On //5, we determine how many columns there will be by accessing the first row and asking how many columns it has. The loops on //6 and //7 set up the indexing of the two-dimensional table, and //8 prints a cell of the table. Within the for loop, the first invocation of operator[] on the RWDBMemTable returns an instance of RWDBRow. The second invocation of operator[] is applied to the row to return an RWDBValue. The call to asString() is applied to the RWDBValue to ensure properly formatted output.
In addition to class RWDBMemTable, class RWDBTPtrMemTable<T,C> provides another alternative to RWDBTable. This class is a template-based memory table, and is documented in the SourcePro C++ API Reference Guide.
There are limitations regarding the use of memory tables, as not all functionality of tables within databases is supported. Please consult the SourcePro C++ API Reference Guide to determine the functionality available to your application.
A result table represents a collection of tabular data that resides neither in a database nor in program memory. Instead, it is generated as the result of a database query.
A result table of the DB Interface Module corresponds to the terms table expression, select expression, result set, and record set, which are used in other languages.
Result tables are produced by the table() method of class RWDBResult. Class RWDBResult represents a sequence of zero or more RWDBTable instances. An RWDBResult is returned whenever a database operation may produce results. This can happen more often than you may think. For example, the DB Interface Module recognizes that some database vendors provide:
Stored procedures that can execute more than one SELECT statement
Triggers that can cause results to be generated as the result of an INSERT, DELETE, or UPDATE statement
In the DB Interface Module, whenever the RWDBInserter, RWDBBulkInserter, RWDBDeleter, or RWDBUpdater calls its execute() method, an RWDBResult is produced.
Here is an introductory example that illustrates the interface for generating and reading result tables. Imagine that the getSqlInput() routine gets one or more SQL statements from somewhere, perhaps interactively from a user.
RWCString sql = getSqlInput();
RWDBResult results = myConnection.executeSql(sql);
RWDBTable resTab = results.table(); //1
while (resTab.isValid()) {
RWDBSchema resSchema = resTab.schema(); //2
int numCols = resSchema.entries();
std::cout << "Got result set with " << numCols << "columns";
RWDBReader rdr = resTab.reader(); //3
int numRows = 0;
while (rdr()) {
++numRows;
}
std::cout << " and " << numRows << " rows" << std::endl << std::endl;
resTab = results.table();
}
|
The RWDBTable instance declared on //1 is a result table. The tabular data it represents is not physically stored in the database or in memory, but is generated from the expression sent to the database server. Nevertheless, it uses what is by now a familiar interface. On //2 we retrieve the table's schema, just as we have done previously for database tables. On //3, we obtain a reader in the usual way. This reader has the same functionality as the readers you have encountered thus far; we have omitted processing the rows in order to keep the example compact.
In Section 4.4, we explained class RWDBTable, possibly the most important class in the DB Interface Module, and introduced the classes RWDBMemTable, RWDBReader, and RWDBResult.
Class RWDBTable is a base class for a family of classes that represent collections of tabular data. The data may reside in a database, in memory, or as a table expression. RWDBTable presents a uniform interface to each table class.
Data is read from tables using class RWDBReader, which provides sequential access to rows within a table, and random access to columns within each row.
The specialization RWDBMemTable adds complete random access using double indexing, as well as other specialized features.
Class RWDBResult encapsulates the result of a database query. It represents a sequence of zero or more result tables.
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.