13.3 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, the third component of the Open SQL API.

Class RWDBMultiRow 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 appropriate 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.

13.3.1 Using RWDBMultiRow for Ad Hoc Queries

In Section 13.2.3, 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 Section 13.2.3, 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);                                        //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 Section 13.2.3, 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 0^th result set of our RWDBOSql. Like many classes of the standard DBTools.h++ interface, 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 0^th 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 that would not be needed if the schema of the result set were known. All these extra steps 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. 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 Section 13.2.3. The processResults() function processes the results fetched into the given RWDBMultiRow and its RWDBTBuffers.

13.3.2 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 Section 13.3.1:

void processResults(RWDBMultiRow& theRows)
{
    // We assume there is only one row in the MultiRow.
    // Create an RWDBRow for the data in the 0^th entries
    // of the RWDBTBuffers.
    RWDBRow r = theRows[0];                                     //1
    
    // Print out the values in the row.
    for(int i = 0; i < r.entries(); i++) {                      //2
        if(i>0) cout << `\t';               
        RWDBValue theField = r[i];                              //3
        cout << theField.asString();                            //4
    }
}

On //1, an RWDBRow is created from the 0^th entries in the RWDBTBuffers contained by the passed-in RWDBMultiRow. When this occurs, all the 0^th entries in the contained RWDBTBuffers are automatically converted into RWDBValues and appended to the RWDBRow. As in Section 13.3.1, 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.

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.