ID Number: Q65349
1.00 1.10
OS/2
Summary:
DB-LIBRARY (db-lib) handles SQL Server informational and error messages
differently in SQL Server version 1.1.
In SQL Server version 1.0, the following occurs:
1. The user supplied error-handling function, installed by
dberrhandle(), is called automatically whenever the application
encounters a db-lib error.
2. The user supplied message-handling function, installed by
dbmsghandle(), is called in response to informational or error
messages returned from SQL Server. Furthermore, because the error
handler is not called, dbsqlexec() returns SUCCEED.
In SQL Server version 1.1, the following occurs:
1. The user supplied error-handling function, installed by
dberrhandle(), is called automatically whenever the application
encounters a DB-LIBRARY (db-lib) error. There is no change here.
2. The user supplied message-handling function, installed by
dbmsghandle(), is called in response to informational or error
messages returned from SQL Server. In addition, if SQL Server
returns an error token to db-lib, meaning that the operation
generated an error, the error handler is called with the
message:
General SQL Server error: Check messages from the SQL Server
Since the error handler is called, the db-lib API call involved
[dbsqlexec() in most cases] will return FAIL.
This new functionality allows applications to effectively handle
errors generated by SQL Server (for example, by stored procedures
through RAISERROR) through the message and error handling functions
and the fact that the db-lib call involved [for example, dbsqlexec()]
will now return FAIL.
More Information:
The following example demonstrates this type of functionality:
1. Create the following stored procedure with the following script:
create procedure test_sp
as
raiserror 99999 "Testing DB-Lib error handling"
2. Compile the db-lib program below using both the version 1.1
libraries and the version 1.0 libraries.
a. Executing the program linked with the version 1.0 libraries
yields the following output:
sql server message: 5701, state 2, severity 0:
Changed database context from 'master' to 'master'.
sql server message: 5701, state 1, severity 0:
Changed database context from 'master' to 'pubs'.
Executing : test stored procedure
sql server message: 99999, state 1, severity 16:
This is a test
Proc : dbsqlexec ==> rc = SUCCEED
b. Executing the program linked with the version 1.1 libraries
yields the following output:
sql server message: 5701, state 2, severity 0:
Changed database context from 'master' to 'master'.
sql server message: 5701, state 1, severity 0:
Changed database context from 'master' to 'pubs'.
Executing : test stored procedure
sql server message: 99999, state 1, severity 16:
This is a test
dblib error:
General SQL Server error: Check messages from the SQL
Server.
Proc : dbsqlexec ==> rc = FAIL
Test Program
------------
The following test program was used to produce the above results:
#include <stdio.h>
#define DBMSOS2
#include <sqlfront.h>
#include <sqldb.h>
void main( void);
void results(int rc, char proc[]);
int err_handler( DBPROCESS *, int, int, int, char *, char *);
int msg_handler( DBPROCESS *, DBINT, int, int, char *);
#define USERNAME "sa"
#define PASSWORD ""
#define SERVERNAME "joeserver"
void main()
{
LOGINREC *login;
DBPROCESS *dbproc;
unsigned char cmd[ 256];
int true;
int rc;
char surname[15], name[15];
/*
* Install db handling routines
*/
dberrhandle( err_handler);
dbmsghandle( msg_handler);
/*
* Log into server
*/
login = dblogin();
DBSETLUSER( login, USERNAME);
DBSETLPWD( login, PASSWORD);
DBSETLAPP( login, "Test");
dbproc = dbopen( login, SERVERNAME);
dbuse(dbproc,"pubs");
printf("\n\nExecuting : test stored procedure\n");
dbcmd(dbproc,"test_sp");
rc = dbsqlexec(dbproc);
results(rc, "dbsqlexec");
dbresults(dbproc);
while (dbnextrow(dbproc) != NO_MORE_ROWS) ;
dbexit();
}
void results(int rc, char proc[])
{
char retcode[15];
switch (rc)
{
case SUCCEED : strcpy(retcode,"SUCCEED");
break;
case FAIL : strcpy(retcode,"FAIL");
break;
case MORE_ROWS : strcpy(retcode,"MORE_ROWS or REG_ROW");
break;
case NO_MORE_ROWS : strcpy(retcode,"NO_MORE_ROWS ");
break;
default : strcpy(retcode,"RC not defined");
}
printf("Proc : %s ==> rc = %s\n",proc,retcode);
}
/*
* Error handler
*/
int err_handler( DBPROCESS *dbprocess, int severity, int db_error,
int os_error, char *db_error_str, char *os_error_str)
{
if (( dbprocess == NULL) || ( DBDEAD( dbprocess)))
{
printf( "DB process is NULL or DEAD" );
return INT_EXIT;
}
else
{
printf( "dblib error:\n\t %s\n", db_error_str);
if ( os_error != DBNOERR)
{
printf( "os error\n\t %s\n", os_error_str);
}
return INT_CANCEL;
}
}
/*
* Message handler
*/
int msg_handler( DBPROCESS *dbprocess, DBINT msgno, int msg_state,
int severity, char *msg_text)
{
printf( "sql server message: %ld, state %d, severity %d:\n\t %s\n",
msgno, msg_state, severity, msg_text);
return 0;
}
Additional reference words: dblib