Re: Adding more information to errors

On 30/01/12 23:18, Tim Bunce wrote:
> 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.

Yes. I'm happy to add this to DBD::ODBC first and see where it gets me before making any observations for DBI.

> 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.

All good but my first problem is that DBI does not seems to have the idea of records or errors (multiple, separate errors after a single method call) (least I don't think it has). The diagnostic fields are per error record in ODBC. So, say you call SQLExecute it could produce 2 errors:

1. constraint violation (record 1)
2. statement terminated (record 2)

The diagnostic fields are per record so when someone wants diagnostic fields from record 1 they need to say that. For DBD::ODBC I think that might end up duplicating SQLGetDiagRec (called repeatedly normally with an incrementing record number until no more records are returned) and then a SQLGetDiagField to get the additional diagnostic fields for that record.

In DBI, I believe multiple calls to set_err get wound up into one:

use DBI;
use strict;
use warnings;

sub test {
     my ($sth, $sql) = @_;

     print "$sth, $sql\n";

     $sth->set_err(1, 'first_error_text', 'XXXXX');
     $sth->set_err(1, 'second_error_text', 'YYYYY');
     undef $_;
}
my $h = DBI->connect('dbi:ODBC:mydsn', 'user', 'password',
                      {RaiseError => 1, PrintError => 0,
                       Callbacks => {prepare => \&test}});
my $s = $h->prepare(q/select * from does_not_exist/);

$ perl set_err.pl
DBI::db=HASH(0x9eea5b0), select * from does_not_exist
DBD::ODBC::db prepare failed: first_error_text [state was XXXXX now YYYYY]
second_error_text at set_err.pl line 20.

and of course, IIRC there is only one set of error scalars across all DBI handles.

frew is busy writing DBIx::Exceptionsso I might keep my eye on that too.

I'll play around with DBD::ODBC first and see where I get.

Martin
-- 
Martin J. Evans
Easysoft Limited
http://www.easysoft.com