In many ways, class RWDBTable is the fundamental data structure of the DBTools.h++ class library. The implementation of tables in DBTools.h++ parallels the relational view of data. In A Guide to the SQL Standard [1], 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 three kinds of tables:
Database Table: a handle for a table physically stored in a database.
Memory Table: a collection of rows that resides in program memory.
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.
NOTE: 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, that is introduced in Section 4.4.4. As you will see, the basic SQL operations SELECT, INSERT, DELETE, and UPDATE are handled by the DBTools.h++ 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 section covers the basics; the DBTools.h++ Class Reference 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 DBTools.h++ design axioms:
NOTE: 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
cout << "purchase table does not exist!" << endl;
}
else {
RWDBSchema purchasesSchema = purchases.schema(); //3
cout << "Columns in the " << purchases.name() //4
<< " table:" << endl;
int numCols = purchasesSchema.entries(); //5
for (int i = 0; i < numCols; i++) { //6
cout << "\t" << purchasesSchema[i].name() << 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 DBTools.h++ API makes it easy to determine whether a method will access the database or not:
NOTE: 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 accesss 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 DBTools.h++, 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 DBTools.h++ tutorials.
NOTE: You must install the tutorials and compile and run tutsetup.cpp when you install DBTools.h++ in order to run this example. See the Build Guide 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(
"libctlb7d.so", // access library name
"SYBASE100", // server name
"dbxx", // user name
"demodb", // password
"DEMO_DB" // database name
);
RWDBConnection myConnection = myDbase.connection();
RWDBTable purchases = myDbase.table("purchase");
RWDBReader rdr = purchases.reader(myConnection);
while (rdr()) {
int videoID;
int supplierID;
int purchaseOrderNumber;
RWDecimalPortable pricePerUnit;
int quantity;
RWDBDateTime date;
rdr >> videoID >> supplierID >> purchaseOrderNumber //1
>> pricePerUnit >> quantity >> date;
cout << videoID << "\t" << supplierID << "\t"
<< purchaseOrderNumber << "\t" << pricePerUnit << "\t"
<< quantity << "\t" << date.asString() << 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:
while (rdr()) {
RWDBNullIndicator nullVideo, nullSupplier; int videoID; int supplierID; int purchaseOrderNumber;
RWDecimalPortable pricePerUnit;
int quantity;
RWDBDateTime date;
rdr >> nullVideo >> videoID >> nullSupplier >> supplierID
>> purchaseOrderNumber >> pricePerUnit >> quantity >> date;
if (nullVideo || nullSupplier) {
cout << "Missing Information!" << endl;
}
else {
cout << videoID << "\t" << supplierID << "\t"
<< purchaseOrderNumber << "\t" << pricePerUnit << "\t"
<< quantity << "\t" << date.asString() << endl;
}
}
The extraction operator is defined for all the C++ and DBTools.h++ datatypes. 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;
RWDBDateTime 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:
while (rdr()) {
Purchase p;
rdr >> p;
// process p;
}
This is an important technique, and it is explained more thoroughly in Chapter 15, the first advanced tutorial.
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
cout << mt20Purchases[i][j].asString() << "\t"; //8
cout << 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 DBTools.h++ Class Reference.
There are limitations regarding the use of memory tables, as not all functionality of tables within databases is supported. Please consult the DBTools.h++ Class Reference 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.
NOTE: A DBTools.h++ result table 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, DBTools.h++ 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 DBTools.h++, 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();
cout << "Got result set with " << numCols << "columns";
RWDBReader rdr = resTab.reader(); //3
int numRows = 0;
while (rdr()) {
++numRows;
}
cout << " and " << numRows << " rows" << endl << 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 DBTools.h++ class library, 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 2000, Rogue Wave Software, Inc.
Contact Rogue Wave about documentation or support issues.