I've recently been asked why DBD::ODBC does not tell you which bound
parameter or bound column is in error when an insert/select specifies a
type which the driver cannot convert to e.g., column is varchar(10) and
bind column as an integer and the content cannot be converted to an
integer or you bind a string parameter 'fred' to an integer column;
there are other examples more complex. When you've only got a few
parameters/columns it is not difficult but when you've got tens of them
it is a different matter. The thing is many (most?) ODBC drivers can
tell you which bound column/parameter is in error via
SQLGetDiagField(SQL_DIAG_COLUMN_NUMBER).
set_err only allows a driver to add:
$rv = $h->set_err($err, $errstr, $state, $method, $rv);
native error code, error message, state, method and alternative return
value.
ODBC provides a number of other fields available per error - see
http://msdn.microsoft.com/en-us/library/windows/desktop/ms710181%28v=vs.85%29.aspx
Not all of them are that useful but a few like SQL_DIAG_COLUMN_NUMBER
are and SQL_DIAG_ROW_NUMBER is useful when doing execute_for_fetch as it
indicates the row in error. In the latter case I had to write special
code to handle that when I implemented execute_for_fetch as there was no
way in DBI to store it.
I can add methods or attributes to DBD::ODBC to allow you to retrieve
the additional fields for an error but it is problematic because which
error are we talking about if there are multiple ones for one ODBC call
- I think I'd end up simply adding Perl wrappers for the actual ODBC
calls below and I doubt anyone would use them. Basically in ODBC when an
error occurs the ODBC 3 way of doing things is:
assuming the last operation was on statement
recno = 1;
do {
SQLGetDiagRec(this_is_a_statement_handle, sth, recno, &state,
&native_error, &msg_text, msg_text_buf_len,
&length_of_returned_msg_text);
# these values are passed to set_err
# DBD::ODBC does not currently do the following except in
execute_for_fetch special case
# and it has nothing to do with set_err right now:
for (each additional field of interest) {
SQLGetDiagField(...);
}
# obviously there is logic to differentiate errors and warnings and
info here
set_err(...);
recno++;
} while we got an error
I'm looking for ideas of how best to do this. An obvious choice would be
to add a hash to the end of set_err where a DBD could add whatever
additional keys are meaningful and perhaps we could define some standard
ones - but that is just an option and I'm not sure what happens if there
are multiple errors (other than when you've got an error handler).
Any comments? What do other DBDs have access to that does not fit into
set_err? Even if people don't think there is any need to add anything to
DBI, do any other DBDs have anything to do this?
Martin
