Bug: SQLGetDiagRec returns SQL_SUCCESS_WITH_INFO

[Bob Hairgrove]

We are having problems retrieving diagnostic information from a DB2
database (server is 7.1.x, client is 8.1). The SQLGetDiagRec()
function, when called on a valid DB2 statement handle, always returns
SQL_SUCCESS_WITH_INFO instead of SQL_SUCCESS, as the documentation
says it should. SQL_SUCCESS_WITH_INFO, according to the documentation
excerpt from "The ODBC Programmer's Reference" posted below, means
that the text buffer for retrieving the error message passed to the
function was too small. The docs are quite clear about this. However,
this is never the case, as the buffer is large enough.

Here is the excerpt from the MSDN documentation mentioned above:

<quote>
SQLRETURN SQLGetDiagRec(
SQLSMALLINT HandleType,
SQLHANDLE Handle,
SQLSMALLINT RecNumber,
SQLCHAR * Sqlstate,
SQLINTEGER * NativeErrorPtr,
SQLCHAR * MessageText,
SQLSMALLINT BufferLength,
SQLSMALLINT * TextLengthPtr);
[...]
Returns:
SQL_SUCCESS, SQL_SUCCESS_WITH_INFO, SQL_ERROR, or SQL_INVALID_HANDLE.

Diagnostics:
SQLGetDiagRec does not post diagnostic records for itself. It uses the
following return values to report the outcome of its own execution:

- SQL_SUCCESS: The function successfully returned diagnostic
information.
- SQL_SUCCESS_WITH_INFO: The *MessageText buffer was too small
to hold the requested diagnostic message. No diagnostic
records were generated. To determine that a truncation
occurred, the application must compare BufferLength to
the actual number of bytes available, which is
written to *StringLengthPtr.
- SQL_INVALID_HANDLE: The handle indicated by HandleType and
Handle was not a valid handle.
- SQL_ERROR: One of the following occurred:
- RecNumber was negative or 0.
- BufferLength was less than zero.
- SQL_NO_DATA: RecNumber was greater than the number of
diagnostic records that existed for the handle specified
in Handle. The function also returns SQL_NO_DATA for any
positive RecNumber if there are no diagnostic records
for Handle.
</quote>

For those who have ever done ODBC programming with DB2 databases, it
should be obvious that this is NOT how DB2 acts when diagnosing
errors. I consider this to be a bug.

If it's not a bug, can anyone tell me how this is properly done?

Thanks.

Bob

[Bob Hairgrove]

bo**********@yahoo.com (Bob Hairgrove) wrote in message news:<67**************************@posting.google. com>...

We are having problems retrieving diagnostic information from a DB2
database (server is 7.1.x, client is 8.1). The SQLGetDiagRec()
function, when called on a valid DB2 statement handle, always returns
SQL_SUCCESS_WITH_INFO instead of SQL_SUCCESS, as the documentation
says it should. [snip]

The silence is deafening ... doesn't anyone have a clue?

[Ian]

Bob Hairgrove wrote:

We are having problems retrieving diagnostic information from a DB2
database (server is 7.1.x, client is 8.1). The SQLGetDiagRec()
function, when called on a valid DB2 statement handle, always returns
SQL_SUCCESS_WITH_INFO instead of SQL_SUCCESS, as the documentation
says it should. SQL_SUCCESS_WITH_INFO, according to the documentation
excerpt from "The ODBC Programmer's Reference" posted below, means
that the text buffer for retrieving the error message passed to the
function was too small. The docs are quite clear about this. However,
this is never the case, as the buffer is large enough.

Here is the excerpt from the MSDN documentation mentioned above:

<quote>
SQLRETURN SQLGetDiagRec(
SQLSMALLINT HandleType,
SQLHANDLE Handle,
SQLSMALLINT RecNumber,
SQLCHAR * Sqlstate,
SQLINTEGER * NativeErrorPtr,
SQLCHAR * MessageText,
SQLSMALLINT BufferLength,
SQLSMALLINT * TextLengthPtr);
[...]
Returns:
SQL_SUCCESS, SQL_SUCCESS_WITH_INFO, SQL_ERROR, or SQL_INVALID_HANDLE.

What do you get in MessageText??

IIRC, you will get SQL_SUCCESS_WITH_INFO when you do an operation and no
rows are affected (i.e. SQLCODE +100, which corresponds to SQLSTATE
'02000'). Are you sure that this is not what you're running into?

-----= Posted via Newsfeeds.Com, Uncensored Usenet News =-----
http://www.newsfeeds.com - The #1 Newsgroup Service in the World!
-----== Over 100,000 Newsgroups - 19 Different Servers! =-----

[Gert van der Kooij]

In article <67**************************@posting.google.com >, Bob
Hairgrove (bo**********@yahoo.com) says...

We are having problems retrieving diagnostic information from a DB2
database (server is 7.1.x, client is 8.1). The SQLGetDiagRec()
function, when called on a valid DB2 statement handle, always returns
SQL_SUCCESS_WITH_INFO instead of SQL_SUCCESS, as the documentation
says it should. SQL_SUCCESS_WITH_INFO, according to the documentation
excerpt from "The ODBC Programmer's Reference" posted below, means
that the text buffer for retrieving the error message passed to the
function was too small. The docs are quite clear about this. However,
this is never the case, as the buffer is large enough.

This is copied from the DB2 CLI Guide and Reference, hope it helps.

Usage

An application typically calls SQLGetDiagRec() when a previous call
to a DB2 CLI function has returned anything other than SQL_SUCCESS.
However, any function can post zero or more errors each time it is
called, so an application can call SQLGetDiagRec() after any function
call. An application can call SQLGetDiagRec() multiple times to
return some or all of the records in the diagnostic data structure.

SQLGetDiagRec() returns a character string containing multiple fields
of the diagnostic data structure record. More information about the
data returned can be found in SQLGetDiagField - Get a Field of
Diagnostic Data.

SQLGetDiagRec() cannot be used to return fields from the header of
the diagnostic data structure (the RecNumber argument must be greater
than 0). The application should call SQLGetDiagField() for this
purpose.

SQLGetDiagRec() retrieves only the diagnostic information most
recently associated with the handle specified in the Handle argument.
If the application calls another function, except SQLGetDiagRec() or
SQLGetDiagField(), any diagnostic information from the previous calls
on the same handle is lost.

An application can scan all diagnostic records by looping,
incrementing RecNumber, as long as SQLGetDiagRec() returns
SQL_SUCCESS. Calls to SQLGetDiagRec() are non-destructive to the
header and record fields. The application can call SQLGetDiagRec()
again at a later time to retrieve a field from a record, as long as
no other function, except SQLGetDiagRec() or SQLGetDiagField(), has
been called in the interim. The application can also retrieve a count
of the total number of diagnostic records available by calling
SQLGetDiagField() to retrieve the value of the SQL_DIAG_NUMBER field,
then call SQLGetDiagRec() that many times.

[Bob Hairgrove]

Ian <ia*****@mobileaudio.com> wrote in message news:<40**********@corp.newsgroups.com>...

IIRC, you will get SQL_SUCCESS_WITH_INFO when you do an operation and no
rows are affected (i.e. SQLCODE +100, which corresponds to SQLSTATE
'02000'). Are you sure that this is not what you're running into?

No. The error is generated by a constraint violation after attempting
an INSERT. Running ODBC trace gives us the details. However, in our
own ODBC classes, after the SQL statement generates the error
(returning SQL_ERROR), we call SQLGetDiagRec() and receive
SQL_SUCCESS_WITH_INFO instead of SQL_SUCCESS.

IIRC there is no message written ... but maybe I just didn't look
because the behavior is not as documented.

[PM \(pm3iinc-nospam\)]

Just ideas...

v8 client to v7 server 'may' cause some problems.
e.g. i'd visit the incompatibilities sections of the doc like
Supported and non-supported client configuration scenarios
odbc trace says what about your problem?
character expansion problem? unicode/codepages/... ?
I know that some drivers have some internal Retry operations. (maybe it's
causing you problems?)

Maybe you can try this to see if it makes any difference.
In v8 fp1
http://publib.boulder.ibm.com/infoce...help/index.jsp
DESCRIBEINPUTONPREPARE CLI/ODBC Configuration Keyword

By default, DB2 CLI does not request input parameter describe information
1when it prepares an SQL statement. If an application has correctly 1bound
parameters to a statement, then this describe information is unnecessary
1and not requesting it improves performance. If, however, parameters 1have
not been correctly bound, then statement execution will fail and cause 1the
CLI error recovery retry logic to request input parameter describe
1information. The result is an additional server request and reduced
1performance, compared to if the describe information had been requested
with 1the prepare. Setting DESCRIBEINPUTONPREPARE to 1 causes the input
1describe information to be requested with the prepare. This setting may
1improve performance for applications which rely heavily on the CLI retry
logic 1to recover from application binding errors.

v8 incompatibilities with prev rlz section

Some application features and tasks:
a.. The DESCRIBE INPUT statement is not supported with one exception for
ODBC/JDBC applications. In order to support DB2 UDB Version 8 clients
running ODBC/JDBC applications accessing DB2 UDB Version 7 servers, a fix
for DESCRIBE INPUT support must be applied to all DB2 UDB Version 7 servers
where this type of access is required. This fix is associated with APAR
IY30655 and will be available before the DB2 UDB Version 8 General
Availability date. Use the "Contacting IBM" information in any DB2 Universal
Database document to find out how to get the fix associated with APAR
IY30655. The DESCRIBE INPUT statement is a performance and usability
enhancement to allow an application requestor to obtain a description of
input parameter markers in a prepared statement. For a CALL statement, this
includes the parameter markers associated with the IN and INOUT parameters
for the stored procedure.
etc.

PM
"Bob Hairgrove" <bo**********@yahoo.com> a écrit dans le message de
news:67**************************@posting.google.c om...

We are having problems retrieving diagnostic information from a DB2
database (server is 7.1.x, client is 8.1). The SQLGetDiagRec()
function, when called on a valid DB2 statement handle, always returns
SQL_SUCCESS_WITH_INFO instead of SQL_SUCCESS, as the documentation
says it should. SQL_SUCCESS_WITH_INFO, according to the documentation
excerpt from "The ODBC Programmer's Reference" posted below, means
that the text buffer for retrieving the error message passed to the
function was too small. The docs are quite clear about this. However,
this is never the case, as the buffer is large enough.

Here is the excerpt from the MSDN documentation mentioned above:

<quote>
SQLRETURN SQLGetDiagRec(
SQLSMALLINT HandleType,
SQLHANDLE Handle,
SQLSMALLINT RecNumber,
SQLCHAR * Sqlstate,
SQLINTEGER * NativeErrorPtr,
SQLCHAR * MessageText,
SQLSMALLINT BufferLength,
SQLSMALLINT * TextLengthPtr);
[...]
Returns:
SQL_SUCCESS, SQL_SUCCESS_WITH_INFO, SQL_ERROR, or SQL_INVALID_HANDLE.

Diagnostics:
SQLGetDiagRec does not post diagnostic records for itself. It uses the
following return values to report the outcome of its own execution:

- SQL_SUCCESS: The function successfully returned diagnostic
information.
- SQL_SUCCESS_WITH_INFO: The *MessageText buffer was too small
to hold the requested diagnostic message. No diagnostic
records were generated. To determine that a truncation
occurred, the application must compare BufferLength to
the actual number of bytes available, which is
written to *StringLengthPtr.
- SQL_INVALID_HANDLE: The handle indicated by HandleType and
Handle was not a valid handle.
- SQL_ERROR: One of the following occurred:
- RecNumber was negative or 0.
- BufferLength was less than zero.
- SQL_NO_DATA: RecNumber was greater than the number of
diagnostic records that existed for the handle specified
in Handle. The function also returns SQL_NO_DATA for any
positive RecNumber if there are no diagnostic records
for Handle.
</quote>

For those who have ever done ODBC programming with DB2 databases, it
should be obvious that this is NOT how DB2 acts when diagnosing
errors. I consider this to be a bug.

If it's not a bug, can anyone tell me how this is properly done?

Thanks.

Bob

[Bob Hairgrove]

bo**********@yahoo.com (Bob Hairgrove) wrote in message news:<67**************************@posting.google. com>...

We are having problems retrieving diagnostic information from a DB2
database

[snip]

OK, it was my own fault ... I was passing sizeof(myBuffer) for the
BufferLength parameter. Originally it was declared as an array, but
somewhere down the line it got changed to a pointer to dynamically
allocated memory. sizeof(a pointer) returns 4, of course (at least on
32-bit machines).

Sorry for wasting your time (I really did look a long time for that
bug! <g>).