On Wed, Jan 25, 2012 at 08:37:57PM +0000, Martin J. Evans wrote:
> 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?
Adding a private odbc_get_diag_field method would be good.
The user (or driver) could call it automatically from the HandleError
hook.
Since the DBI is 'aligned' with the ODBC/SQLCLI standards I'd be open to
extending the DBI specification in this direction. Similar to the way
get_info is based closely on SQLGetInfo.
Also, perhaps drivers (or DBI?) could provide a way for users to specify
which 'DiagIdentifier's they'd like to have automatically checked for
and appended to errstr. Something like:
$h->{AutoDiagnostics} = [ $SQL_DIAG_COLUMN_NUMBER, $SQL_DIAG_ROW_NUMBER ];
Tim.
