|
Before building an SQL server class for Sybase/Microsoft's SQL Server, Mark had to unravel its client/server message-handling mechanism. Here's what he found, along with the C++ class library he eventually developed.
November 01, 1994
URL:http://www.drdobbs.com/database/an-sql-server-message-handling-class/184409340
Copyright © 1994, Dr. Dobb's Journal
Copyright © 1994, Dr. Dobb's Journal
Copyright © 1994, Dr. Dobb's Journal
Copyright © 1994, Dr. Dobb's Journal
Mark is a senior consultant with Semaphore, a consulting and training company specializing in object technology, client/server development, and distributed computing. Mark can be contacted on CompuServe at 76605,2346.
Wherever applications interact with the Sybase/Microsoft SQL Server by executing stored procedures, sending complete SQL statements, or some hybrid of the two, you'll find that you need a mechanism to handle communication between the client application and the server. Microsoft supplies DB-Library, an API of C-callable functions for sending data to or retrieving data from the server. Recently, however, my colleagues and I required an interface to an SQL Server which consisted of a layer of C++ classes on top of the DB-Library. In particular, we had to design classes to handle messages from the server and DB-Library to the client application.
In this article, I'll examine the nature of these messages, and present the classes we developed to handle them. In this application, the database logic was contained largely in stored procedures. Operations were performed by building SQL statements that invoked procedures and using DB-Library to send them to the server.
DB-Library and SQL Server identify a connection to a database using a structure called DBPROCESS that is allocated and maintained by DB-Lib. An application connects to a database by requesting a DBPROCESS, and receives a near pointer to use as a handle to it. "Processes" (or "Process objects") refer to objects in an application that own a DBPROCESS handle and use DB-Lib to send commands to the server.
When an application tells the server to perform a database operation, the server may generate a message in reply. DB-Library may also send messages to the program. There are over 1000 possible messages dealing with everything from communications problems to acknowledgment of a change of default database. When a message originates with DB-Library, it is normally an error. However, the server generates informational messages which are not necessarily errors. For my purposes here, the terms "error" and "message" are synonymous.
DB-Library is the clearinghouse for server messages. In addition, however, it generates its own messages. To receive message data, an application registers one of two callbacks with DB-Lib: one for server messages, the other for DB-Lib messages. In the Microsoft SQL Server C++ example code (available on CompuServe), Eric Reel implemented a pipeline allowing messages to be sent to the part of an application which prompted them. The idea is based on the assumption that there are classes encapsulating each process, as well as a central class responsible for logging in to the server and receiving and dispatching messages. Figure 1 shows a typical scheme in which messages arrive at the Process objects and are passed on to individual handlers. The scheme could just as easily use a central message handler, though it seems useful to allow different types of processes to have differently configured handlers. Each message handler has an interface which allows it to receive messages, yet it is unaware of how those messages got there.
Messages from the server or DB-Library consist of several items of data passed as arguments to the callback functions. Table 1 lists the parameters of a DB-Library error message. All data types are DB-Lib/SQL Server data types.
The first parameter is a handle to the DBPROCESS structure identifying the process which caused the message event. The second parameter, a DBSMALLINT, indicates the severity of the condition which caused the message. This value ranges from EXINFO, an informational message, to EXCONSISTENCY, a severe internal error in DB-Lib or SQL Server. The possible values for severity are assigned to constants in the DB-Lib header files. The third parameter, another DBSMALLINT, gives the number of the message. The range of possible values is assigned to constants in DB-Lib's header files. The fourth DBSMALLINT gives the operating-system error number if the message was generated by an operating-system error. The last two data items are both far pointers to null-terminated strings and are nonnull if valid. The first contains the text of any DB-Lib error message, and the second, the text of an operating-system error message.
Messages from the server are more involved. Table 2 lists the parameters of a server message. The first parameter is identical to that of a DB-Lib message: a handle to a DBPROCESS. The second, a DBINT, contains the SQL Server message number. Many of the message numbers have also been assigned to constants in DB-Lib. The third parameter is a DBSMALLINT and is described by the documentation as the "message state."
The severity level of the message is passed in the fourth parameter. It is ostensibly constrained to the same values as those in a DB-Lib message; however, we sometimes saw severity levels beyond those documented. The next three parameters are far pointers to null-terminated strings. The first contains the text of the server message; the second, the name of the server. If the message was generated by a stored procedure, then the third string contains its name, otherwise it is null. If the procedure name is nonnull, the last parameter, a DBSMALLINT, contains the number of the line in the procedure which caused the error. This value is useful in debugging stored procs.
Message data is stored and transmitted between objects in our interface layer in two structures declared in DBMSG.H (see Listing One).
Neither of the structures exactly duplicates the message data described previously. In particular, both structures omit the near pointer to the DBPROCESS structure. The reasons for this are that the DBPROCESS pointer is used to identify the Process object managing the affected database connection; the message data is then forwarded to it. Since every Process object contains a pointer to its DBPROCESS structure, there is no need to retain that data in the messages.
Both structures contain a flag called "received," which is an implementation detail of the message pipeline. When a message arrives in the central Manager class in the interface layer, it is sent to the appropriate process by calling an interface function of the Process class. Some messages from the server are accompanied by a notification message from DB-Lib; it is also possible for the Process object to have delayed forwarding messages long enough for both structures to have fresh message data. The received flag indicates when message data has been copied into the structure. It is cleared by the handler when the message(s) are processed.
Lastly, the null-terminated ASCII strings have been used to initialize objects of type String. Given the amount of text manipulation sometimes necessary, it is useful to have a robust string class, and String provides a full complement of features. (Documentation on String is provided electronically; see "Availability," page 3.)
The message-handling classes need to be able to handle the following:
The ErrStrategy class was designed to encapsulate the definition of a handling strategy (that is, the actions taken when a message is received), be customized for a given message, and provide for added functionality.
The interface to ErrStrategy consists largely of constructors and functions which access the data. There is no destructor required. Wherever possible, the implementations of the member functions have been inlined. ErrStrategy is intended to be copied and manipulated by the application, and we wanted it to involve as little overhead as possible.
There are three constructors for ErrStrategy objects. The first is a default constructor, taking no parameters. It initializes the object to some default values, the result of which is a fairly useless invalid object. The values assigned are the same as those for the ErrStrategy instance, ESZERO, that is used as a NULL instance. The default constructor allows arrays of ErrStrategy objects to be created. After creating an array of ErrStrategy instances, the application should cycle through the array and set them all to a valid state.
The second constructor is the initialization constructor. It takes eight parameters which completely define the handling for the message in question. These arguments correspond to the class data members discussed previously. Of note is the callFunc parameter, which is defaulted to NULL. This parameter specifies a callback function for the message. If no callback is to be specified, this argument can be ignored. The implementation of this constructor consists entirely of a member-initialization statement given after the argument list, with an empty function body.
Next is the copy constructor. Its sole parameter is a reference to a constant ErrStrategy instance which is copied into the instance being constructed. This constructor is actually implemented in terms of the next member function, the assignment operator, which does the actual work. The operator function also takes a constant reference to an ErrStrategy instance as its only argument, as does the relational-equality operator function, which follows. This operator returns nonzero if the instance matches the one for which the operator is invoked. Two ErrStrategy objects are considered equal if they refer to the same error source and number.
The rest of the ErrStrategy interface consists of access functions for member data that should be self-explanatory. Each function reads or writes a specific member of the private data described earlier, and all are in lined for performance reasons.
With the ErrStrategy class, we have an object which can represent the customized handling of messages. It can be assigned to, copied, compared, created in arrays, and manipulated through interface functions. We now need a class which puts the ErrStrategy, along with all the other details already described, to use. The next section will examine the DBMsgHandler class, which is the core of the message-handling system.
The DBMsgHandler class is responsible for providing the mechanisms to handle a message, track retry attempts, manage a list of custom strategies, and allow strategies to be added or removed. DBMsgHandlers have full copy and assignment semantics so that they can be easily shared among processes, or copied and modified using a default "base handler" as a reference.
The PendingErr structure is declared in the protected interface of DBMsgHandler, and each handler contains a single instance of this struct called "pending." The struct is used to hold all of the data on the message currently being processed. The source of this data is an ErrStrategy instance, if one has been defined for the message, or the default handling strategy if no custom strategy is available. In addition, PendingErr holds other control values needed by the handler. It serves as the basic control structure for handling messages, and its operation will be examined in more detail in the description of the HandleMsg member function.
The interface to DBMsgHandler begins with two constructors. The first takes a single parameter of type String, specifying the name to use for the message-log file. This parameter defaults to an empty string (""), allowing the constructor to serve as a default constructor. The log-file name defaults to SQLERROR.LOG if this parameter is NULL. The implementation of the constructor clears the PendingErr structure and initializes it to a NO ERROR condition, as defined in DBMSG.H. It then sets the default handling strategy by setting the action levels using the constants described earlier. Lastly, the name of the log file is set.
The remaining constructor is a copy constructor implemented in terms of the assignment-operator function. The assignment operator takes a single argument, a reference to a constant DBMsgHandler object, and performs the usual assignment of member data, including copying the source instance's ErrStrategy list. One twist in the mechanism is required: If the handler being assigned to has an error currently being retried, the error is flushed before the member assignment is done. Otherwise, a message might be lost when the source object was copied into the target. The assignment operator, and thus the copy ctor, copy the complete state of the source handler, including any pending errors.
The destructor for DBMsgHandler has the sole responsibility of cleaning up the list of ErrStrategy instances. It does so using the ClearStrategies member function. The destructor was made virtual because it was foreseen that more specialized message handlers might be derived from DBMsgHandler in the future.
The public-member function HandleMsg represents the mechanism for applying handling strategies to messages from the server or DB-Lib. This function is called by the Process object when a message is received, and passed two message structures of the types described previously. HandleMsg returns a value of type SqlAction, as defined in DBMSG.H. SqlAction provides three constants which allow the handler to inform the application about the status or consequences of processing a given message. These codes are SA_PROCEED, SA_RETRY, and SA_CANCEL.
If a message has not been defined as requiring a retry, HandleMsg will process the message and return either SA_PROCEED or SA_CANCEL. The first code tells the application that the message was informational or nonfatal and that it can proceed with the current task. The second represents handling of a fatal error and instructs the application to terminate the current task. The code returned depends on the terminate-severity level, which in turn depends on whether the handling is by the default strategy or a Boolean proceed flag in the custom handler.
The SA_RETRY code is returned when a message is caused by an operation which should be retried. The number of retries allowed is defined in either the default strategy or in a custom strategy for the message. The HandleMsg function will return SA_RETRY on each receipt of this message until the retry count is matched or until a different message is received. Receipt of a new message during a retry cycle causes the existing message to be flushed before the new message is processed. In either case, the return value is ultimately set to either SA_PROCEED or SA_CANCEL.
The HandleMsg member function operates as a state machine controlled by the current contents of the pending struct, which contains all of the information required to handle the message currently being processed. This includes all of the data from either the default or custom strategy, as well as the retry counter, copies of the two message structures, and the message source and number. During handling, the new message passed into the function by the Process object will be compared with the pending message data to determine what action to take next. The first task is to determine what kind of message the new data represents.
In most cases only one of the two types of messages will be sent to the application in response to a given operation. The only case we saw where two messages were received was when a server message caused DB-Lib to send the SQLESMSG notification. This message informs the app that a server message is coming. There may be other cases when both messages are sent, and the design of our message handler has assumed that other messages from DB-Lib always constitute an error condition. As mentioned earlier, some server messages are merely informational.
The HandleMsg function needs to have one message source and number in order to choose a strategy. If both kinds of messages need to be dealt with and the DB-Lib message is SQLESMSG, then it is ignored and the server message is handled. Otherwise, the DB-Lib message is handled. In either case, when action is taken to display a message or flush a message to the disk log, any data in either structure that is new since the last call to HandleMsg will be output.
An application can utilize several combinations of methods to handle messages using DBMsgHandler and ErrStrategy. A simple loop controlled as a result of the HandleMsg member function will automate retries of those errors that allow them, while those that don't will cause codes that can trigger the app to invoke its own error handling. Figure 2 illustrates an example of an application interacting with the message handler using this method. As an alternative, the application can define a custom strategy that specifies a callback function and use this function to take some action, either in place of, or in addition to, the action defined in the strategy.
Figure 1 SQL Server and DB-Library messages received by the Manager class are forwarded to individual processes and then on to a message handler.
Table 1: Parameters of a DB-Library message.
Table 2: Parameters of an SQL-Server message.
Figure 2 An application interacting with the DBMsgHandler class using the HandleMsg() member function to respond to server or DB-Lib messages.
Copyright © 1994, Dr. Dobb's Journal
Terms of Service | Privacy Statement | Copyright © 2024 UBM Tech, All rights reserved.
Data type Purpose
DBPROCESS NEAR* Pointer to DBPROCESS structure identifying
process which generated message.
DBSMALLINT Severity level of exception, falls between
EXINFO and EXCONSISTENCY.
DBSMALLINT DB-Library error number, nonzero if DB-Lib
error occurred.
DBSMALLINT Operating-system error number, nonzero if
operating-system error occurred.
LPSTR Pointer to null-terminated string containing
DB-Lib error message if DB-Lib error
number is nonzero.
LPSTR Pointer to null-terminated string containing
operating-system error message if
operating-system error number is nonzero.
Data type Purpose
DBPROCESS NEAR* Pointer to DBPROCESS structure identifying
process which generated message.
DBINT SQL-server message number.
DBSMALLINT Message state.
DBSMALLINT Severity level of exception, falls between
EXINFO and EXCONSISTENCY.
LPSTR Pointer to null-terminated string containing text
of server message.
LPSTR Pointer to null-terminated string containing
name of server.
LPSTR Pointer to null-terminated string containing
name of process generating message.
DBSMALLINT Number of the line in the above process which
caused the message to be generated.
Listing One
//************************************************************************
// DBMSG.H Class, data structure, and constant declarations for SQL
// Server/DB Lib message handling -- by Mark Betz
//************************************************************************
#ifndef DBMSG_H
# define DBMSG_H
# include <windows.h>
# define DBMSWIN // DB Library needs this for Windows
extern "C" // so the linker doesn't look for
{ // mangled names
#include <sqlfront.h> // Microsoft includes for DB Library
#include <sqldb.h>
}
# include <string.h> // string class
// action messages returned by DBMsgHandler after processing a server or
// DB Library message.
enum SqlAction
{
SA_CANCEL, // exit current procedure
SA_PROCEED, // proceed, non-fatal or informational
SA_RETRY // retry last operation
};
// error codes for use within DBMsgHandler and related classes. These
// represent errors which occur in the database interface. Server and
// DB Lib errors are signaled through the DBMsgHandler class.
enum DBErr
{
DB_OK, // no error
DB_ALLOCFAILED, // memory allocation failed
DB_IOERR, // file or device i/o error
};
// error-source constants, used by DBMsgHandler
enum ErrSource
{
ES_DBLIB, // error source was DB Library
ES_SERVER // error source was SQL Server
};
// error-display handling constants, used by DBMsgHandler
enum ErrDisplay
{
ED_ALERTONLY, // display an error alert/no info
ED_BRIEF, // display error text info only
ED_VERBOSE // display all error info
};
// default action levels for handling errors without custom strategies. These
// constants define the severity levels at which certain actions will occur,
// and the number of retries allowed.
const DEF_DISPLAY_LEVEL = EXCONVERSION; // display severity >=
const DEF_TERM_LEVEL = EXUSER; // terminate severity >=
const DEF_WRITE_LEVEL = EXUSER; // disk log severity >=
const DEF_DISPLAY_TYPE = ED_VERBOSE; // default display handling
const DEF_RETRY_CNT = 0; // default retries
// structure for DB_lib error messages, used by DBMsgHandler
struct ErrorStruct
{
int received; // true if error message received
int severity; // error severity
int dberr; // DB error code
int oserr; // operating system error code
String dberrstr; // DB error message text
String oserrstr; // OS error message text
};
// structure for SQL Server messages, used by DBMsgHandler
struct MessageStruct
{
int received; // true if server message received
int msgno; // server message number
int msgstate; // server message state
int severity; // message severity
String msgtext; // server message text
String server; // name of server issuing message
String process; // name of process causing message
int lineno; // line of process causing message
};
// function pointer type used in ErrStrategy
typedef void (*SqlErrCall)(MessageStruct&, ErrorStruct&);
class DBMsgHandler; // forward declaration
// SQL/DB Lib error strategy class. Used in DBMsgHandler to set custom
// strategies for handling DB Lib and SQL Server errors.
// IMPLEMENTATION: DBMSG.CPP
class ErrStrategy
{
friend class DBMsgHandler;
public:
ErrStrategy();
ErrStrategy( ErrSource src, int num, int retCnt, bool show, bool notFatal,
bool log, ErrDisplay disp, SqlErrCall callFunc = NULL );
ErrStrategy( const ErrStrategy& );
void operator = ( const ErrStrategy& );
int operator == ( const ErrStrategy& );
void SetSource( ErrSource src ) { source = src; }
ErrSource GetSource() const { return source; }
void SetErrNo( int num ) { errNo = num; }
int GetErrNo() const { return errNo; }
void SetRetryCnt( unsigned retCnt ) { retryCnt = retCnt; }
unsigned GetRetryCnt() const { return retryCnt; }
void SetDisplay( bool show ) { display = show; }
bool GetDisplay() const { return display; }
void SetProceed( bool procd ) { proceed = procd; }
bool GetProceed() const { return proceed; }
void SetWrite( bool log ) { write = log; }
bool GetWrite() const { return write; }
void SetDispType( ErrDisplay dispt ) { disptyp = dispt; }
ErrDisplay GetDispType() const { return disptyp; }
private:
ErrSource source; // the error source, ES_SERVER or ES_DBLIB
int errNo; // the error number
bool display; // display the error message
bool proceed; // ok to proceed after handling
bool write; // flush the error to a disk file
unsigned retryCnt; // number of retries allowed
ErrDisplay dispTyp; // how error display is handled if display == TRUE
SqlErrCall callBk; // function called on this error
ErrStrategy* next; // next strategy in the list
};
// for comparing against after an operation on strategies. MSGIMP is
// defined in DBMSG.CPP
#ifndef MSGIMP
extern ErrStrategy ESZERO;
#else
ErrStrategy ESZERO(
ES_SERVER, -32768, -1, FALSE, FALSE, FALSE, ED_VERBOSE, NULL);
#endif
// DBMsgHandler class. This class contains all the logic for handling
// errors using default and custom strategies.
// IMPLEMENTATION: DBMSG.CPP
class DBMsgHandler
{
public:
DBMsgHandler( const String& logName = "" );
DBMsgHandler( const DBMsgHandler& );
virtual ~DBMsgHandler();
void operator = ( const DBMsgHandler& );
void SetDisplayLevel( int severity );
int GetDisplayLevel() const { return displayLevel; }
void SetTermLevel( int severity );
int GetTermLevel() const { return termLevel; }
void SetWriteLevel( int severity );
int GetWriteLevel() const { return writeLevel; }
void SetRetryCount( unsigned retCount ) { retryCnt = retCount; }
unsigned GetRetryCnt () const { return retryCnt; }
void SetDisplayType( ErrDisplay dispt ) { disptyp = dispt; }
ErrDisplay GetDisplayType() const { return disptyp; }
DBErr GetStatus();
ErrStrategy AddErrStrategy( const ErrStrategy& );
ErrStrategy GetErrStrategy( int errno, ErrSource source );
ErrStrategy DelErrStrategy( int errno, ErrSource source );
DBErr LoadStrategies( const ErrStrategy* strats, int count,
bool clear = FALSE);
void ClearStrategies();
virtual SqlAction HandleMsg( const ErrorStruct&, const MessageStruct& );
protected:
struct PendingErr
{
ErrSource source;
int errNo;
unsigned retry;
unsigned retryCnt;
bool display;
bool write;
bool proceed;
ErrDisplay dispType;
ErrorStruct es;
MessageStruct ms;
SqlErrCall callf;
} pending;
private:
SqlAction ResolveErr();
bool IsPending( ErrSource msgSource, int msgNum );
void SetMsgData( const ErrorStruct&, const MessageStruct& );
void NotifyUser();
DBErr WriteLog();
int displayLevel;
int termLevel;
int writeLevel;
int retryCnt;
ErrDisplay dispTyp;
ErrStrategy* stratList;
String log;
String message;
DBErr status;
};
#endif // DBMSG_H