Re: Issue with SQLParamData and SQLPutData and image column

On Thu, 18 Aug 2011 10:54:56 -0400
Michel Chamberland <[email protected]> wrote:

> is there a valid case where a SQLParamData could return ERROR but
> GetDiagRect returns NO_DATA?

http://msdn.microsoft.com/en-us/library/ms712366(VS.85).aspx

	"When SQLParamData returns SQL_ERROR or SQL_SUCCESS_WITH_INFO,
an associated SQLSTATE value can be obtained by calling SQLGetDiagRec"

I would say, no, there's no valid case.  But the behavior is easily
explained by looking at src/odbc/odbc.c::_SQLParamData().  There are
several cases where it returns SQL_ERROR without first populating a
diagnostic record with odbc_errs_add().  

1.  Line 6047, when a parameter is out of bounds.  AFAICT this should be
HY000, General error.  

2.  Line 6062, when parse_prepared_query() fails.  It populates one
return code, HY001, Memory allocation error (correct).  It also calls:

2a. prepared_rpc() returns SQL_ERROR if tds_alloc_param_data() fails,
without setting HY001.  (2 places)

2b. prepared_rpc() returns SQL_ERROR if parse_const_param() fails,
without setting an error code.  

2c. prepared_rpc() returns SQL_ERROR if the complicated test on line
164 of src/odbc/prepare_query.c fails.  

2d. prepared_rpc() returns SQL_ERROR if odbc_sql2tds() fails.
odbc_sql2tds() sets an error for most conditions, but not when
odbc_get_octet_len() fails, or when numeric precision fails (line
429).  

When I was testing the ODBC driver a few years ago, I found many such
conditions, and started sprinkling calls to odbc_errs_add() whenever I
hit an error without a description.  Most of the work is more detective
work than programming: find the return point, assess the condition
from reading the code and documentation, and add the line.  

In fact that was my early experience using db-lib in production.  If it
worked, it worked great, but when it didn't work, you likely needed the
debugger.  To save time, I fixed the code.  I meticulously added
parameter checking to each and every public db-lib function; I even
read all the documented error messages and looked for places to put
them if they didn't already appear somewhere in the code.  Nowadays I
consider it a bug if an erroneous user-provided value *doesn't* provoke
a db-lib error message.  

ODBC and db-lib are basically equivalent in functional power, except
for bcp.  The db-lib code is 10% bigger by source code byte count,
about the same number of lines.  ODBC has only 74 functions to db-lib's
255, testifying to the complexity of ODBC functions.  Which
explains why getting the error diagnotics right is important.  

--jkl