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
