Parsing Streamed XML to DOM
The server receives the request as a stream of bytes from the driver, and the driver also receives the response from the server as a stream of bytes. This time, the DOM methods cannot be used to interpret this stream of bytes. The stream is sent through a parser, which checks and converts it to our DOM structure. After it is converted to DOM, it can then be accessed and manipulated using the DOM methods. Let’s take the response for our request shown in Example 4. I explain the response and the role of parser in interpreting it. The response is shown in Example 8.
Example 8.
<ENVELOPE>
<HEADER>
<CONNECTID>12345</CONNECTID>
</HEADER>
<BODY>
<SQLExecStmtResponse ResultType="Resultset">
<RESULTDESC>
<ROWDESC>
<COL>
<NAME>author_name<NAME>
<ALIAS><ALIAS>
<TYPE>varchar<TYPE>
<LENGTH>64<LENGTH>
<PRECISION><PRECISION>
<NULLABILITY></NULLABILITY>
</COL>
<COL>
<NAME>id<NAME>
.
.
</COL>
</ROWDESC>
</RESULTDESC>
<RESULTDATA>
<ROW>
<COL>Author1</COL>
<COL>991</COL>
</ROW>
<ROW>
<COL>Author2</COL>
<COL>992</COL>
</ROW>
<RESULTDATA>
</SQLExecStmtResponse>
</BODY>
</ENVELOPE>
The typical response from the database server will be a resultset. I have divided it into two parts. The first part, Metadata, describes the resultset. This is contained inside the RESULTDESC tag. The second part is the data itself and is contained inside RESULTDATA tag.
Since I am describing a row of data in a resultset, I have used a ROWDESC tag inside the RESULTDESC tag. Each column is described with COL tag, which contains the six basic properties NAME, ALIAS, TYPE, LENGTH, PRECISION, and NULLABILITY for each column as child tags. Use of ROWDESC can be avoided and is a personal design choice; you can directly put the columns inside the RESULTDESC tag.
The RESULTDATA tag contains the data as rows and columns. Each row is contained in the ROW tag with the columns and values as child tags. The RESULTDATA contains as many ROW tags as there are rows in the resultset while each ROW contains as many COL tags as there are columns per row.
Example 8 shows how this response is converted into DOM before we serve it to our ODBC client. The parser is implemented as the XMLParser class and is available with the download. I will not go into the details of how the parser does its job. I have created it using YACC (Yet Another Compiler Compiler) and it goes through the stages typical of a parser — lexical, syntactic, semantic, analysis, and finally reduction of tags into my DOM class XMLNode. You can choose from one of the many parsers available in the market or create your own if you find this area of development interesting. We create an instance of the parser class with the buffer received on the socket connection and the size of the buffer. The GetObjectNode method parses it and returns an XMLNode object, which we can now use to interpret and serve the data.
Example 9 shows how you can serve the name of the second column (metadata) or data from the third column in the second row.
Example 9.
// allocate the XML parser with buffer to be parsed
xp = new XMLParser ( xbuf, size );
// parse and convert to DOM node
n = xp->GetObjNode ();
// release the parser
delete xp;
// check if parsing was successful
if ( n == NULL ) printf ( "failed\n" );
// check if the response is valid SOAP response
// code to check for ENVELOPE/HEADER/BODY etc
// store the full response form the server
pStmt->RespFull = n;
// store the result descriptor ENVELOPE-BODY-RESULTDESC
pStmt->RespRSDesc = n->GetLastChild()->GetFirstChild();
// store the result data ENVELOPE-BODY-RESULTDATA
pStmt->RespRSData = n->GetLastChild()->GetLastChild();
// initialize the state and position variables for fetch calls
pStmt->CurRowsetStartRow = NULL; // start of current rowset
pStmt->CurRowsetStartRowPos = 0; // absolute position
pStmt->CurRowsetEndRow = NULL; // end of current rowset
pStmt->CurRowsetEndRowPos = 0; // absolute position
// finally at some point in time
delete n;
It is basically understanding of the tree structure and walking through the children and siblings. The column information is stored inside ROWDESC, which itself is the child of RESULDESC. We get the node for the first column using the GetFirstChild method on the ROWDESC node, which is itself obtained using the GetFirstChild method on RESULTDESC object. Now we loop using GetNextSibling method of the DOM since the columns are all immediate children of ROWDESC and are therefore siblings to one another. Once we reach the desired column, we now loop again through its children comparing the names of the tags with the desired property; i.e., NAME. We take the text stored in it and serve it to the client. Note here that the text content of an element is not the value obtained via the GetNodeValue method, instead it is the node value of the first child element. This is the design of the XMLNode class which keeps it flexible enough to store mixed content; i.e., both text and elements within a node. The client typically specifies the buffer in which it requires the value. The value is copied to this client buffer, see Example 10.
Example 10.
// HOW TO serve the name of the second col (metadata)
// 1. start with the first COL in ROWDESC
rowdesc =pStmt->RespRSDesc->GetFirstChild();
coldesc = rowdesc->GetFirstChild();
// 2. loop to reach the second or nth column
for ( x = 1; x < 2; x ++, coldesc = coldesc->GetNextChild());
// 3. loop to find the desired property tag
colprop = coldesc->GetFirstChild ();
while ( colprop )
if ( stricmp ( colprop->GetNodeName(), "NAME" )
break;
// 4.copy the text contained as child node to the clients buffer
strcpy ( clientbuffer, colprop->GetFirstChild()->GetNodeValue()));
// HOW TO serve the data of third col of the second row
// 1. start with the first ROW in RESULTDATA
rowdata =pStmt->RespRSData->GetFirstChild();
// 2. loop to reach the second or nth row
for ( x = 1; x < 2; x ++,rowdata = rowdata->GetNextChild());
// 3. loop to reach the third col
coldata = rowdata->GetFirstChild ();
for ( y = 1;y < 3; x ++,coldata = coldata->GetNextChild());
// 4.convert data to type specified by client and copy to clients buffers
*( int* )clientbuffer = atoi ( colprop->GetFirstChild()->GetNodeValue());
Similarly, to obtain the actual value of the mth column of the nth row, we start with the first ROW of data, which is the child of RESULTDATA and then loop through its siblings using GetNextSibling until we reach the desired row. Note again that rows are siblings to one another. After we reach the row, we start with the first column using GetFirstChild on the ROW and then loop in a fashion similar to what we did for rows until we reach the desired column. The data is in text format since it has been received as an XML stream. The column may be of type float and the client may have asked for it as an integer and specified a buffer of appropriate size. You have to consider all three; i.e., text, float, and integer, and convert it to the destination data type in an optimized way. I have tried to provide the basic conversion in my download samples, but you may need to extend or change this area as per your requirements.
You will find that the architecture of calls and communications fits quite well into the WSDL (Web Services Description Language) specification; see Figure 5.
These are the latest guidelines in this area of software development (http://www.w3.org/TR/wsdl) and are actively supported by companies like Microsoft, Ariba, and IBM.
Data Transfer And Fetching Results
ODBC defines a standard way of fetching data from the driver. The application specifies a buffer for obtaining the data immediately or for a deferred fetch. For example, functions like SQLColAttribute, SQLDescribeCol, and so on, specify a buffer in which the driver puts in the information about columns immediately. On the other hand, SQLBindCol specifies a buffer along with a column number, which is stored by the driver in its internal structures associated with the connection and statement. When the application calls SQLFetch or any other fetch function, the driver uses the buffer specified using SQLBindCol to feed the results. The application has to make sure that it does not free the buffers as long as the driver can use it, and the driver makes sure that it maintains the correct associations with the columns and positions and never frees the buffer. The application also has to specify the length of the buffer so that the driver does not cause a protection fault by exceeding the buffer capacity. Sometimes the driver, because of the context and associated fixed length data type, implicitly knows the length of the buffer. In such cases, the application specified length is ignored.
Since the data to be returned may not be exactly equal to the buffer specified by the application, the driver also needs to return the length of data it is feeding into the buffer and whether to return that any truncation has taken place. The application specifies another fixed length buffer for this, which is typically referred to as length-or-indicator pointer and defined as Strlen_Or_Ind. It is a pointer of type SQLLEN (long data type of C language). The driver either puts in the length of returned data into this buffer or if the data is NULL terminated, the buffer is used as an indicator pointer and fed with a value -3, defined as SQL_NTS in SQL.H.
The application also uses the same technique for data it passes to the driver. It can either specify the length explicitly or indicate NULL terminated data with SQL_NTS.
ODBC allows the application to specify buffers for multiple rows to be retrieved in a single fetch referred to as rowset buffers. This means that the driver needs to know the exact layout of this buffer for the application to work correctly. The two possible layouts that the application can use are as follows:
- Bind an array to each column. This is called column-wise binding because each data structure (array) contains data for a single column.
- Define a structure to hold the data for an entire row and bind an array of these structures. This is called row-wise binding because each data structure contains the data for a single row.
The application then calls SQLBindCol to bind this array of buffers to columns just as it binds a simple buffer for each single row and column of data. To locate the position of the same column in the next row, the driver moves from its current position either by the size of the column buffer (column wise binding) or by the size of the row buffer (row wise binding). Don’t worry if it is a little confusing at first, most of your clients would not require a block fetch, so you can move on to complete this area a little later in your development cycle. The file GO_FETCH.CPP in the sample download shows how this is managed.
Descriptors — An Important Design Issue
A descriptor handle refers to a data structure that holds information about either columns or dynamic parameters. There are four types of descriptors as defined by ODBC. They are ARD (Application Row Descriptor), APD (Application Parameter Descriptor), IRD (Implementation Row Descriptor,) and IPD (Implementation Parameter Descriptor). Most if not all the IO between the driver, data source, and client takes place via these structures.
ODBC does not specify how you implement these structures or the physical layout of these structures. It says that a particular type of data, for example the column bindings, specified by the application be stored in one structure and referred to as the ARD in your design. The important requirement is that the application should have the ability to use a descriptor handle directly and set the attributes or retrieve data, which in most cases is done implicitly by commonly used functions like SQLBindCol, SQLColAttribute, etc. The application can allocate the descriptor explicitly using SQLAllocHandle or it can obtain the handle descriptor allocated by your driver using SQLGetStmtAttr.
Most of the clients will not require direct access to descriptors since no database operation requires that the client gain direct access to descriptors. However, for some applications, gaining direct access to descriptors streamlines many operations. For example, direct access to descriptors provides a way to rebind column data, which can be more efficient than calling SQLBindCol again, but this is uncommon.
The descriptors are also divided into headers and records. For instance, the binding information for columns specified by the application. The details for each column forms a record in the descriptor, while something common to all columns like binding type goes into the header of ARD. I show in Example 11 the ARD structure as used in my driver.
Example 11.
struct GODBCARDItem {
Word ColNum; // column number
Word DataConciseType; // concise data type
Word DataVerboseType; // basic data type
Word DateTimeIntervalCode; // date-time interval code
Long DateTimeIntervalPrec; // date-time precision
Long NumPrecRadix; // 2 if approx num type or 10 exact num type
Word Scale; // scale, right of decimal
void* DataPtr; // col data
Long DataSize; // size of data buffer
Long* SizePtr; // actual data size
Long* SizeIndPtr; // size indicator ptr
struct GODBCARDItem* Next; // next col binding
struct GODBCARDItem* Prev; // prev col binding
};
typedef struct GODBCARDItem GODBCARDITEM; // ARD-Col data type
typedef struct GODBCARDItem* PGODBCARDITEM; // ARD-Col as pointer type
struct TlyODBCARD {
// header fields
Word AllocType; // SQL_DESC_ALLOC_TYPE
ULong RowArraySize; // SQL_DESC_ARRAY_SIZE
UWord* ArrayStatusPtr; // SQL_DESC_ARRAY_STATUS_PTR
Long* BindOffsetPtr; // SQL_DESC_BIND_OFFSET_PTR
Long BindTypeOrSize; // SQL_DESC_BIND_TYPE
Word DescCount; // SQL_DESC_COUNT
// rows for binding each col
PGODBCARDITEM BindCols; // col bindings
// container
PGODBCSTMT Stmt; // container statememt
};
The one major advantage I found in following this design guide is a more precise framework and easy compliance with more clients. I state this because I initially ignored this and ended up with a complex design and still was unable to support the DTS (Data Transformation Services) module of SQL server for importing data. Finally, I had to redesign the entire thing and centralize all the IO via the descriptor concept. As you develop your own ODBC driver or browse through sample files (GO_DESC.CPP), you will find many of the calls are simply mapped to the four descriptor functions — SQLGetDescField/Rec and .SQLGetDescField/Rec.
I will briefly describe the purpose of each type of descriptor. You can find detailed information on layout and content of these structures from the files GODBC.H and GO_DESC.C in the download samples or on the MSDN site. The application descriptors are supposed to contain information specified by the client application. The application row descriptor therefore contains information about the row as required by the client; say it requires the third column in a buffer of 64 bytes, so this is stored in an ARD record. Similarly, while using parameterized queries, the details of the parameters as specified by the client or obtained from the client should be stored in the APD structures.
The implementation descriptors are supposed to contain data as obtained from the server or derived by the driver itself. Therefore, the result from the server is stored in the IRD structure. Similarly, the parameter related information from the server should be stored in the IPD. Personally, I found the ARD and IRD to be more explicit in terms of their definition and functionality as compared to APD and IPD, which overlap at places.
