15.3 The Data Callback Classes
The Open SQL API includes support for user-defined callback methods that facilitate the handling of character and binary data in pieces. Data callbacks are useful for very large data that may not fit in memory for a single database call. They allow data from any source to be sent to the database or to be read from the database and written to any sink. Supported types include the large object types (LOBs).
The DB Interface module provides a set of base classes that define interfaces for specifying data callback methods:
15.3.1 Fetching Data
Suppose we want to fetch a very large image from a database. As shown in
Section 15.2.3, “Output Binding and Fetching Using RWDBTBuffer,” we would fetch the data using RWDBOSql something like this:
RWDBOSql sql("select id, image from mytable where id = 1001");
RWDBTBuffer<long> idBuf(1);
RWDBTBuffer<RWDBBlob> buffer(1);
sql.execute(aConnection);
sql[0] >> idBuf >> buffer;
while (sql.fetch(), sql.rowsFetched() > 0) {
// process data in idBuf and buffer
}
When
RWDBOSql::execute(…) is called, the
SELECT statement is executed. On each call to
fetch(),
RWDBOSql fetches as many rows as possible from the result set and puts the data into the
RWDBTBuffer instances. Since
idBuf and
buffer can hold only one entry, at most one row is fetched on each call to
fetch(). SourcePro DB attempts to allocate enough memory to contain the entire data set. It then passes this data to the user application. If the data does not fit in the buffer provided, the data received by the application will be truncated.
The following diagram illustrates this operation:
As a way to limit the amount of memory required to handle LOBs, SourcePro DB provides an alternative API that allows callback classes to be associated with the
RWDBOSql rather than
RWDBTBuffer instances. Based the example above, the data callback class
MyDataCallback, derived from
RWDBBinaryCallback, is used in place of an
RWDBTBuffer to fetch the image data:
RWDBOSql sql("select id, image from mytable where id = 1001");
RWDBTBuffer<long> idBuf(1);
MyDataCallback buffer(1);
sql.execute(aConnection);
sql[0] >> idBuf >> buffer;
while (sql.fetch(), sql.rowsFetched() > 0) {
// process data
}
This time when RWDBOSql::fetch() is called the data set is retrieved one row at a time. The ID value is written into the idBuf in the usual manner, but the image data is fetched one piece at a time. The callback method defined by MyDataCallback is invoked repeatedly, once for each piece of data, until all of the data has been fetched. Thus, only one piece of the data needs to fit in memory at any one time.
The operation now looks like this:
15.3.2 Inserting Data
Similarly, when inserting data, the entire value must fit in memory at one time. For example, using
RWDBTBuffer we would write:
RWDBOSql sql("insert into mytable values(:ph0, :ph1)");
RWDBTBuffer<long> id(1);
RWDBTBuffer<RWDBBlob> image(1);
populateBuffers(id, image, 1);
sql << id << image;
sql.execute(aConnection);
When RWDBOSql::execute(..) is called, one row of data is inserted into the table. The data is read from the memory associated with the RWDBTBuffer instances.
The following diagram illustrates this operation:
Once again, the amount of memory used can be limited by supplying a data callback class in place of the RWDBTBuffer instances to send the data.
RWDBOSql sql("insert into mytable values(:ph0, :ph1)");
RWDBTBuffer<long> id(1);
MyDataCallback image(1);
populateBuffer(id, 1);
sql << id << image;
sql.execute(aConnection);
When
RWDBOSql::execute(..) is called to send the data, the id value is read from the
RWDBTBuffer in the usual manner. The image data, however, is obtained from the instance of
MyDataCallback by invoking the callback method to get the data and send it to the server one piece at a time. The callback is invoked as many times as needed to send all the pieces to the server.
Here is the illustration for the operation:
15.3.3 Implementing a Custom Callback Class
To use the data callback feature:
1. Create a derived class from one of the three callback interface classes.
2. Implement the necessary callback methods in the derived class.
3. Provide an instance of the derived class to an
RWDBOSql instance.
To fetch or insert character data, derive from the base class
RWDBCharCallback; for binary data, derive from
RWDBBinaryCallback; and for UTF-16 data, derive from
RWDBUChar16Callback. Note that only LOB data types are supported. Then define both of the pure virtual methods
onFetch() and
onSend(), and if needed,
getLength().
15.3.3.1 Implementing onFetch()
To fetch data, implement the
onFetch() method. This discussion assumes the use of
RWDBBinaryCallback. The other two callback classes have slightly different signatures.
virtual bool onFetch(size_t rownum, const void* theData,
size_t byteLength, RWDBNullIndicator ni, bool& lastPiece);
The parameters are:
rownum | The row in the current rowset this data is from (0-based indexing)
This parameter is always zero if the database vendor does not support array binding, and therefore operates on just one row at a time. If your vendor does support array binding, you can use multirow operations by specifying a value greater than one for the entries parameter in the constructor for the callback class you are using. The entries value determines the size of the rowsets returned by each call to the database.
For example, if the result of a SELECT statement consists of 10 rows of data, the size of the result set is 10. If the callbacks are created with an entries value of 3, the first rowset will have 3 rows from the result set, 0 - 2, the second rowset will have rows 3 - 5, the third rowset will have rows 6 - 8 and the last rowset just a single row, row 9. However, rownum indicates the row in the current rowset, not the row in the result set. So, for example, when the first rowset is returned, rownum can have a value from 0 to 2. When the second rowset is returned, with rows 3 - 5, rownum will still have a value from 0 to 2, because it indicates the row in the current rowset, not the row as it was in the complete result set. |
theData | A pointer to an array containing the piece of data currently being processed
Note: if the ni parameter is true, theData is undefined. |
byteLength | The length in bytes of the data in theData
Note: if the ni parameter is true, theData is undefined. |
ni | A boolean value indicating whether the value returned by the call to onFetch() is null
This value is true for null values, otherwise false. |
lastPiece | A boolean value indicating whether this is the last piece of data
This parameter is true if this is last piece of data for the current row, otherwise false. If there might be reason to stop processing a row in the result set before all the data has been fetched, this parameter can be set to true to discontinue processing for the current row and move onto the next one. |
15.3.3.2 Implementing onSend()
To insert data, implement the onSend() method:
virtual bool onSend(size_t rownum, void* theData,
size_t& byteLength, RWDBNullIndicator& ni, bool& lastPiece);
The parameters are:
rownum | The row for which data is needed (0-based indexing)
This parameter indicates the row of data being sent to the server.
For example, consider this code:
RWDBOSql inserter("insert into strings values(:ph0)");
MyDataCallback dataBuffer(1000);
inserter << dataBuffer;
inserter.execute(aConn);
When RWDBOSql::execute(..) is called, 1000 strings are inserted into the database by repeatedly calling the onSend() method associated with dataBuffer. For each row, the onSend() method is invoked as many times as needed until all the data is sent. The rownum indicates the row in the 1000 rows for which data is needed. Therefore, rownum will have values from 0 to 999. |
theData | A pointer to the array where the onSend() method should write the data to be sent
This location is passed to onSend() by the access module, which has allocated a location to receive the data. |
byteLength | The number of bytes in theData to be sent to the server
Initially this parameter holds the maximum size of the data that can be sent to the server, as specified by the piece size value for the access module. The onSend() method should set this variable to the actual size of the data being sent to the server. |
ni | A boolean reference value for indicating nulls
This boolean value is set to true to indicate that a null value is being sent to the database, otherwise false.
If this parameter is set to true, the parameters theData, byteLength, and lastPiece are irrelevant. |
lastPiece | A boolean reference value for indicating that this is the last piece of data for the current value
When the last piece of data for the current row's value is written by onSend() to the array theData, lastPiece must be set to true. |
The onSend() method must return true to continue the operation. If it returns false, or throws an exception, the operation is terminated and no subsequent calls are made to onSend().
15.3.3.3 Implementing getLength()
Some databases require knowing at the beginning of the operation the total length of the data to be inserted into a row. Check the access module documentation for your database to determine whether this information is required. If so, you must implement the getLength() method:
size_t getLength(size_t rownum) const;
The rownum parameter has the same meaning as it has for onSend() above. This method is called just before the initial call to onSend() for each row to get the total length of the data being sent for the row.
Note that if the getLength() method is needed but not overridden from the base class, the inserting of data will fail.
15.3.4 Using a Custom Callback Class with Open SQL
This section provides two examples illustrating how to implement and use a custom callback class for both fetching and inserting data.
15.3.4.1 Fetching Data with a Custom Callback Class
To use a custom callback class with Open SQL to fetch data, the
onFetch() method must be implemented. An instance of the class is associated with an
RWDBOSql so the results produced by executing a SQL statement are passed to the callback class. Here is an example fetching data with a custom callback class based on the image data example in
Section 15.3.1, “Fetching Data.”
class MyCallback : public RWDBBinaryCallback
{
public:
MyCallback(size_t rowsetSize) : RWDBBinaryCallback(rowsetSize), ourRowCount(0)
{ }
virtual ~MyCallback() { }
bool onFetch(size_t rownum, const void* theData, size_t length,
RWDBNullIndicator ni, bool& lastPiece)
{
if (!ni) {
// assume that the data is written to some pre-defined location
passToSink(ourRowCount, theData, length);
}
else {
passNullToSink(ourRowCount);
}
if (lastPiece) {
++ourRowCount;
}
return true;
}
. . .
private:
size_t ourRowCount;
};
RWDBDatabase aDB = RWDBManager::database(...);
RWDBConnection aConn = aDb.connection();
…
RWDBOSql anOSql("SELECT ID, IMAGE FROM INVENTORY", RWDBOSql::Query);
anOSql.execute(aConn);
if (anOSql.isValid() && anOSql.hasResult()) {
unsigned long idArray[100]; // 1
RWDBTBuffer<unsigned long> idBuffer(idArray, 100); // 2
MyCallback myCB(100); // 3
anOSql[0] >> idBuffer >> myCB; // 4
while (anOSql.fetch(), anOSql.rowsFetched() > 0) { // 5
printInventoryIDs(idArray, anOSql.rowsFetched());
}
}
In the calls to
fetch(), the custom callback class
onFetch() method is called repeatedly until all the data is fetched for the rows returned. On each call to
fetch(),
RWDBOSql fetches as many rows as possible from the result set. In this case, the output binding holds 100 entries and the custom callback class was constructed with an
entries value of 100, so at most 100 rows are fetched. If fewer rows are available in the result set, all the rows are fetched.
After each call to fetch(), the function rowsFetched() returns the number of rows fetched, which indicates the part of the array that contained the fetched data and how many rows of data were passed to the callback's onFetch() method. When all the result data is exhausted, calling fetch() returns no rows and rowsFetched() returns 0, exiting the loop.
15.3.4.2 Inserting Data with a Custom Callback Class
To use a custom callback class with Open SQL to insert data, the
onSend() method must be implemented. Also, the
getLength() method may need to be overridden depending on whether the database vendor requires the total length of the data to be inserted before the operation is executed. See the relevant access module guide to determine if getLength() is required. An instance of the callback class is associated with an
RWDBOSql that is set up to insert data.
Here is an example of inserting data with a custom callback class. It differs slightly from earlier examples in using
RWDBCharCallback rather than
RWDBBinaryCallback. Note that the datatype of
theData has changed.
class MyCallback : public RWDBCharCallback
{
public:
MyCallback(size_t rowsetSize) : RWDBCharCallback(rowsetSize)
{ }
virtual ~MyCallback() { }
bool onSend(size_t rownum, char* theData, size_t& length,
RWDBNullIndicator& ni, bool& lastPiece)
{
// Assume the data is being read from some pre-defined source
if (!getDataFromDataSource(rownum, theData, length, ni, lastPiece))
return false;
return true;
}
// We may or may not need to return the length of the data we're sending.
// This depends on the access module in use.
size_t getLength(size_t rownum) const
{
return getDataLengthFromDataSource(rownum);
}
. . .
};
RWDBDatabase aDB = RWDBManager::database(...);
RWDBConnection aConn = aDb.connection();
RWCString smallStringData[10];
populateStrings(smallStringData, 10);
…
RWDBOSql inserter("INSERT INTO STRINGS VALUES (:ph1, :ph2)"); // 1
RWDBTBuffer<RWCString> dataBuffer(smallStringData, 10); // 2
MyCallback myCB(10); // 3
inserter << dataBuffer << myCB; // 4
inserter.execute(aConn); // 5
15.3.5 Other Resources
For more information about the data callback classes:
See the class description for
RWDBDataCallback. This class description includes an additional extended example.
SourcePro DB ships an example for the data callback classes. This is located at:
<installdir>\examples\dbcore\datacb.cpp.
