RWDBMultiRow
In previous sections, we explained how class
RWDBOSql encapsulates SQL statements, and class
RWDBTBuffer<T> encapsulates buffers of data for binding to SQL statements. In this section, we explore class
RWDBMultiRow, another component of the Open SQL API.
Class
RWDBMultiRow is a collection of
RWDBTBuffers. It allows you to add Open SQL to applications using value-based classes like
RWDBResult,
RWDBReader,
RWDBRow, and
RWDBValue. With
RWDBMultiRow, you can:
• Assemble
RWDBTBuffers into a collection that provides row operations on its entries, enabling you to group together an entire result set or set of input values.
• Automatically create an appropriate set of
RWDBTBuffers from an
RWDBSchema, allowing you to create buffers from the schema of a database table or from the schema of a result set.
• Create an
RWDBRow containing
RWDBValues from a slice across several
RWDBTBuffers, so you can easily pass database data in your application without knowing its type.
Let’s look at several examples that show how
RWDBMultiRow can be used in an application.
Using RWDBMultiRow for Ad Hoc Queries
In
“Output Binding and Fetching Using RWDBTBuffer”, we created our own
RWDBTBuffers when buffers were needed to hold query results. We could do this since we knew the format of our results in advance. If the format of results is not known, however,
RWDBMultiRow can help create
RWDBTBuffers for results. Let’s see how this is done.
In the example in
“Output Binding and Fetching Using RWDBTBuffer”, we used
RWDBMultiRow when we made output bindings for our results:
...
anOSql[0] >> idBuffer; // 7
...
The expression
anOSql[0] returns an
RWDBMultiRow reference, and the
RWDBMultiRow operator>> appends an
RWDBTBuffer to itself.
RWDBOSql internally stores an
RWDBMultiRow for each result set, and
operator[] returns a reference to the
RWDBMultiRow for the specified result set. In some cases, the number or type of columns in the result set is unknown until runtime. As we will see below, we can use
RWDBMultiRow to automatically create
RWDBTBuffers for us in these cases.
Class
RWDBOSql provides a method,
schema(), which returns an
RWDBSchema representing the schema of the current result set. Each column in the returned
RWDBSchema details information about the corresponding results column, including its name, datatype, and other information.
RWDBMultiRow provides a constructor that takes an
RWDBSchema, and creates appropriate
RWDBTBuffers. Here’s an example of how we could use
RWDBMultiRow to automatically create output bindings:
RWCString aQuery = getUserSpecifiedQuery(); //1
RWDBOSql anOSql(aQuery, RWDBOSql::Query); //2
anOSql.execute(aConn); //3
RWDBMultiRow outputBindings(anOSql.schema(), 1); //4
anOSql[0] = outputBindings; //5
while(anOSql.fetch(), anOSql.rowsFetched() > 0) { //6
processResults(outputBindings); //7
}
On
//1 and
//2, an
RWDBOSql is created from a user-specified query, and on
//3, the query is executed. Note that the output bindings are not yet specified at this time. As we explained in the example in
“Output Binding and Fetching Using RWDBTBuffer”, output bindings may be specified before or after
execute() is invoked, as long as they are specified before
fetch() is invoked.
On
//4, we call
anOSql.schema(), which returns an
RWDBSchema representing the schema of the result set. We use this
RWDBSchema to construct a new
RWDBMultiRow, which immediately uses this
RWDBSchema to construct an
RWDBTBuffer for each column. The
RWDBMultiRow instantiates the
RWDBTBuffer templates on the types corresponding to each column’s type, and appends the new
RWDBTBuffers to itself. The
1 corresponds to the number of entries each
RWDBTBuffer will have; each
RWDBTBuffer will have
1 entry.
On
//5, we use the new
RWDBMultiRow as the output bindings for the 0th result set of our
RWDBOSql. Like many classes of the standard interface of the DB Interface Module,
RWDBMultiRow is designed around the Interface/Implementation paradigm, and the state of each
RWDBMultiRow is held in a hidden, reference-counted implementation class. Using the assignment operator causes the
RWDBOSql’s internal
RWDBMultiRow for the 0th result set to refer to the implementation of the
RWDBMultiRow we just constructed. The
RWDBOSql now uses our newly created
RWDBTBuffers, but no performance is lost by creating a copy.
Although creating this
RWDBMultiRow from an
RWDBSchema is a useful technique, please note that it requires extra steps while trying to access the data fetched. These extra steps are described in the next section
“Obtaining Data from RWDBMultiRow”. All these can impose quite a performance penalty, depending on how frequently they are used.
NOTE >> This technique may impede performance.
For this reason, we recommend that you use these features only when it is impossible to know the results of a query and
RWDBTBuffers cannot be created based on the
RWDBSchema returned by the
RWDBOSql::schema()_ call. In other cases, it is preferable to directly instantiate
RWDBTBuffers of the appropriate type and directly bind them to your
RWDBOSql.
Lines
//6 and
//7 perform the standard results-processing loop that we use in
“Output Binding and Fetching Using RWDBTBuffer”. The
processResults() function processes the results fetched into the given
RWDBMultiRow and its
RWDBTBuffers.
Obtaining Data from RWDBMultiRow
When using
RWDBMultiRow, you have two options for obtaining data from its
RWDBTBuffers: the
bufferAt() method, and the
operator[]. The
bufferAt() method provides access to a contained
RWDBTBuffer by returning a pointer to an object that is an
RWDBAbstractBuffer, the base class of all
RWDBTBuffer template instantiations. Since
RWDBTBuffer is a template class, however, you may
not know the specific type on which the template is instantiated; not knowing the type makes the method unusable for ad hoc query situations, like the one above. For these situations,
RWDBMultiRow also provides an
operator[], returning an
RWDBRow.
Class
RWDBMultiRow’s
operator[], which takes a single
size_t parameter,
n, creates a row of data by taking a cross-section of all the data in its
RWDBTBuffers at entry
n. It converts the data to
RWDBValues, and returns the
RWDBValues in an
RWDBRow. Using
operator[], you have a convenient row-access method to
RWDBMultiRow. Furthermore, since the data is converted to
RWDBValues, you can easily interrogate the data’s type, convert the data to other types, and format the data as a string for output, using methods on
RWDBValue.
Let’s now use
operator[] to write the
processResults() function we called in the example in
“Using RWDBMultiRow for Ad Hoc Queries”:
void processResults(RWDBMultiRow& theRows)
{
// We assume there is only one row in the MultiRow.
// Create an RWDBRow for the data in the 0th entries
// of the RWDBTBuffers.
RWDBRow r = theRows[0]; //1
// Print out the values in the row.
for(size_t i = 0; i < r.entries(); i++) { //2
RWDBValue theField = r[i]; //3
cout << theField.asString(); //4
cout << '\t';
}
cout << endl;
}
On
//1, an
RWDBRow is created from the 0th entries in the
RWDBTBuffers contained by the passed-in
RWDBMultiRow. When this occurs, all the 0th entries in the contained
RWDBTBuffers are automatically converted into
RWDBValues and appended to the
RWDBRow. As in
“Using RWDBMultiRow for Ad Hoc Queries”, please note that this feature adds overhead to an application in both performance and memory usage. While it may be very helpful in some situations, its use should be restricted to situations where the types of data are not known and
RWDBTBuffers cannot be created based on the
RWDBSchema returned by the
RWDBOSql::schema() call.
NOTE >> This technique may impede performance.
On
//2, we loop through the
RWDBRow, using its
entries() method. On
//3, inside the loop, we obtain the
RWDBValue for the current entry in the
RWDBRow using
RWDBRow’s
operator[]. On
//4, we use the
asString() method of
RWDBValue, which can convert any data into a string, and we output this string to
cout.