Better diagnostic support in DBI

[pa...@cpan.org]

Hi! DBI currently supports 3 functions to retrieve diagnostic
informations

https://metacpan.org/pod/DBI#err
https://metacpan.org/pod/DBI#errstr
https://metacpan.org/pod/DBI#state

which return the last one database native code, message and SQLSTATE.
Plus there warning or note information is indicated by zero native code
or by empty string in native code.

This API has two big limitations:

1) It does not support providing database native code for warnings and
note informations.

2) It does not support providing more warnings, errors or note
informations.

It most cases database native code is what application can "parse" and
decide how to handle it. So absence of database native code for warnings
or note informations can be a problem -- as currently DBI application
needs to parse string message from $dbh->errstr.

Also databases can return more warnings or note informations for the
last executed call. For this case DBI defines API for errstr just as:

"The returned string may contain multiple messages separated by newline
characters."

So it is harder for DBI application to parse multi warnings. And there
is not information for which warning is value from $dbh->state.



Based on above two limitations I would like to propose a new API for
diagnostic messages (errors, warnings and note / success with
information).

New method call "get_diagnostics()" for all DBI handles. It would
return array of diagnostics member. Each diagnostic member would contain
reference to array with 4 members: type, native code, message, sqlstate.

Type can be non-zero for errors, zero for warnings and empty string for
notes / success with information. Like what $dbh->err now returns.

Native code is native database code, same what $dbh->err returns for
errors. But for warnings and notes it would also contain database native
code, not just false value

Message and sqlstate would return $dbh->errstr and $dbh->state.

Example of usage:

my @array = $dbh->get_diagnostics();
foreach (@array) {
my ($type, $code, $message, $sqlstate) = @{$_};
if ($type) {
print "Error $code ($sqlstate): $message\n";
} elsif ($type eq '0') {
print "Warning $code ($sqlstate): $message\n";
} else {
# Process success with information message
}
}

What do you think about it?

[Tim Bunce]

A key goal of the DBI is to provide a database independant interface to
databases to enable application portability. What you're suggesting
seems inherently database specific.

The DBI defines a way for drivers to offer database specific functionality.

A good way to move a suggestion like this move forward is to work with
multiple driver authors to add similar functionality to their drivers.
Exposing the full features that a specific database makes available, via a
database specific interface, is immediately helpful for users of that driver.

Once multiple drivers provide similar functionality then it would be
reasonable to work on a design for extending the DBI itself.

Tim.

[pa...@cpan.org]

On Wednesday 23 January 2019 20:19:27 Tim Bunce wrote:
> A key goal of the DBI is to provide a database independant interface to
> databases to enable application portability. What you're suggesting
> seems inherently database specific.

As wrote DBI already has API for providing last error/warning/note and I
just tried to extend this API to provide list, not just one value. So
tried to describe some API which returns list.

Do you think that providing list of errors/warnings/notes is database
specific?

For example MySQL and MariaDB databases may produce more then one
warning for last executed statement. Same apply for PostgreSQL and
in databases with some kind of PL/SQL you can in most cases raise
warnings / errors too.

For example, here is simple PostgreSQL statement which cause generation
of more warnings and info messages:

\set VERBOSITY verbose
SET client_min_messages TO DEBUG;

DO $$ BEGIN RAISE NOTICE 'my notice' USING ERRCODE = '00001'; RAISE WARNING 'my warning' USING ERRCODE = '00002'; RAISE INFO 'my info' USING ERRCODE = '00003'; RAISE DEBUG 'my debug' USING ERRCODE = '00004'; RAISE WARNING 'my warning' USING ERRCODE = '00005'; END $$;

NOTICE: 00001: my notice
WARNING: 00002: my warning
INFO: 00003: my info
DEBUG: 00004: my debug
WARNING: 00005: my warning

And SQL statement for MariaDB which cause 2 warnings:

\W

SELECT CAST("1a" AS INT), CAST("2b" AS INT);

Warning (Code 1292): Truncated incorrect INTEGER value: '1a'
Warning (Code 1292): Truncated incorrect INTEGER value: '2b'

Both examples does not produce any error, just warnings or info
messages.

I guess that similar thing -- more then one warning for one statement --
can happen also in other databases.

> The DBI defines a way for drivers to offer database specific functionality.
>
> A good way to move a suggestion like this move forward is to work with
> multiple driver authors to add similar functionality to their drivers.

Therefore I'm writing to this list :-) What other developers think...

[pa...@cpan.org]

On Thursday 24 January 2019 13:57:23 Daniël van Eeden wrote:
> I think this would be very useful.
>
> Another thing you could consider is a hash where the error/warning number is
> the key and an list of msgs is the value.

I think this is just complicated to create create and use. Is there any
real benefit to have such hash?

> But your proposed solution is probably simpler to consume.

Yes, for DBI applications it should be easier to consume.

[pa...@cpan.org]

CCing DBD::Pg, DBD::Oracle and DBD::ODBC developers. What do you think
about following diagnostic API in DBI?

I looked at DBD::Pg and currently it does not support retrieving
warnings via $dbh->errstr or $dbh->state methods... So I think it that
my diagnostic API proposal can be useful for DBD::Pg too.

[pa...@cpan.org]

Hello, I would like to hear some feedback on this DBI API proposal.

Tim, what is opinion for adding that new diagnostic API into DBI?

[Tim Bunce]

> Tim, what is opinion for adding that new diagnostic API into DBI?

I'd much prefer to wait till multiple drivers have added their own
driver-specific, and driver-optimized, interface. And then have a
discussion about how the DBI might best provide a common API.

That approach has worked well in the past.

Tim.

[pa...@cpan.org]

On Monday 08 April 2019 20:49:25 Tim Bunce wrote:
> > Tim, what is opinion for adding that new diagnostic API into DBI?
>
> I'd much prefer to wait till multiple drivers have added their own
> driver-specific, and driver-optimized, interface. And then have a
> discussion about how the DBI might best provide a common API.

Hi Tim! DBD::Pg now has its own driver-specific interface:
https://metacpan.org/pod/DBD::Pg#pg_error_field