On Wed, 9 Feb 2011 02:53:09 -0000, "Greg Sabino Mullane"
<g...@turnstep.com> wrote:
Sorry that this is a long post, but maybe that is the best way to state
what I am aiming at
> > Apparently some DBDs only want DBD tracing and not DBI tracing so
> > trace level is no good as it always includes DBI tracing. I think you
> > were talking about reserving some space for DBDs only - perhaps you
> > could remind us - the idea being settings (or a setting) which outputs
> > DBD tracing but not DBI tracing.
>
> Not sure if this has been covered upthread, but DBD::Pg makes heavy
> use of tracing flags; I'm not sure what you mean by trace level
> being no good for DBD but not DBI output.
Let me elaborate on this one, taking DBD::Unify as an example.
DBD::Unify uses a trace *level* for debugging. The higher the level,
the higher the output:
dbg (4, "DBD::Unify::sqlError: SQLCODE = %d", SQLCODE);
That statement causes that message to be printed if the debug/trace
level is 4 or higher. This one only occurs if an error occurred
somewhere. Here are the dbg calls from login/connect:
dbg (2, "Set DBD_VERBOSE = %d\n", dbd_verbose);
dbg (2, "Set DBD_VERBOSE = %d\n", dbd_verbose);
dbg (3, "DBD::Unify::db_login: dbname: %s\n", dbname);
dbg (4, " user = '%s', opt = '%s' (ignored)\n", user, opt);
dbg (4, " After uinimsg ('%s'), status = %ld\n", pgm, ustatus);
dbg (4, " After connect, sqlcode = %d\n", SQLCODE);
(void)sprintf (statement, "set current schema to \"%s\"", auth);
dbg (3, " %s\n", statement);
dbg (4, " After schema, sqlcode = %d\n", SQLCODE);
Not rocket science. Here's the extract from my docs:
--8<---
=item trace
The C<DBI-E<gt>trace (level)> call will promote the level to DBD::Unify,
showing both the DBI layer debugging messages as well as the DBD::Unify
specific driver-side debug messages.
It is however also possible to trace B<only> the DBD-Unify without the
C<DBI-E<gt>trace ()> call by using the C<uni_verbose> attribute on C<connect ()>
or by setting it later to the database handle, the default level is set from
the environment variable C<$DBD_TRACE> if defined:
$dbh = DBI->connect ("DBI::Unify", "", "", { uni_verbose => 3 });
$dbh->{uni_verbose} = 3;
As DBD::Oracle also supports this scheme since version 1.22, C<dbd_verbose>
is a portable alias for C<uni_verbose>, which is also supported in DBD::Oracle.
DBD::Unify now also allows an even finer grained debugging, by allowing
C<dbd_verbose> on statement handles too. The default C<dbd_verbose> for
statement handles is the global C<dbd_verbose> at creation time of the
statement handle.
The environment variable C<DBD_VERBOSE> is used if defined and overrules
C<$DBD_TRACE>.
$dbh->{dbd_verbose} = 4;
$sth = $dbh->prepare ("select * from foo"); # sth's dbd_verbose = 4
$dbh->{dbd_verbose} = 3; # sth's dbd_verbose = 4
$sth->{dbd_verbose} = 5; # now 5
Currently, the following levels are defined:
=over 2
=item 1 & 2
No DBD messages implemented at level 1 and 2, as they are reserved for DBI
=item 3
DBD::Unify::dbd_db_STORE (ScanLevel = 7)
DBD::Unify::st_prepare u_sql_00_000000 ("select * from foo")
DBD::Unify::st_prepare u_sql_00_000000 (<= 4, => 0)
DBD::Unify::st_execute u_sql_00_000000
DBD::Unify::st_destroy 'select * from parm'
DBD::Unify::st_free u_sql_00_000000
DBD::Unify::st 0x7F7F25CC 0x0000 0x0000 0x00000000 0x00000000 0x00000000
DBD::Unify::st destroyed
DBD::Unify::db_disconnect
DBD::Unify::db_destroy
=item 4
Level 3 plus errors and additional return codes and field types and values:
DBD::Unify::st_prepare u_sql_00_000000 ("select c_bar from foo where c_foo =
1")
After allocate, sqlcode = 0
After prepare, sqlcode = 0
After allocate, sqlcode = 0
After describe, sqlcode = 0
After count, sqlcode = 0, count = 1
DBD::Unify::fld_describe o_sql_00_000000 (1 fields)
After get, sqlcode = 0
DBD::Unify::st_prepare u_sql_00_000000 (<= 1, => 0)
DBD::Unify::st_execute u_sql_00_000000
After open, sqlcode = 0 (=> 0)
DBD::Unify::st_fetch u_sql_00_000000
Fetched sqlcode = 0, fields = 1
After get, sqlcode = 0
Field 1: c_bar: NUMERIC 4: (6030) 6030 ==
Fetch done
DBD::Unify::st_finish u_sql_00_000000
After close, sqlcode = 0
DBD::Unify::st_destroy 'select c_bar from foo where c_foo = 1'
DBD::Unify::st_free u_sql_00_000000
After deallocO, sqlcode = 0
After deallocU, sqlcode = 0
=item 5
Level 4 plus some content info:
DBD::Unify::st_fetch u_sql_00_000000
Fetched sqlcode = 0, fields = 1
After get, sqlcode = 0
Field 1: [05 00 04 00 00] c_bar: NUMERIC 4: (6030) 6030 ==
Fetch done
=item 6
Level 5 plus internal coding for exchanges and low(er) level return codes:
DBD::Unify::fld_describe o_sql_00_000000 (1 fields)
After get, sqlcode = 0
Field 1: [05 00 04 00 FFFFFFFF] c_bar
DBD::Unify::st_prepare u_sql_00_000000 (<= 1, => 0)
=item 7
Level 6 plus destroy/cleanup states:
DBD::Unify::st_free u_sql_00_000000
destroy allocc destroy alloco After deallocO, sqlcode = 0
destroy alloci destroy allocp After deallocU, sqlcode = 0
destroy stat destroy growup destroy impset
=item 8
No messages (yet) set to level 8 and up.
=back
-->8---
As DBD::Unify uses levels only at the moment, there was no way to
*ONLY* get the DBD trace/debug messages without also getting the DBI
messages, as I shared DBI's trace level. This only became an issue once
the DBD was getting useful. In the beginning when I started to write
it, I (of course) also needed the DBI traces.
At that point I came up with the "uni_verbose" attribute which could
be passed to the connect method. This way I could set the DBD trace
flags without getting the DBI messages. For me this was so useful, that
I wanted it in other DBD's too. I talked to John Scoles on a YAPC
meeting and he very much agreed with me on this one. In a hacking
session, I changed my uni_verbose to dbd_verbose, added support for
$DBD_VERBOSE and helped John to convert his fixed level-based debugging
to allow support for dbd_verbose. This way, $DBD_VERBOSE and the
dbd_verbose attribute suddenly become extremely useful in
cross-database debugging. Most of my DBI scripting is database agnostic
and works evenly well on Unify, Oracle, Postgres, SQLite, CSV and mysql.
This is also the reason why I seldom use debugging flags. Flags are not
the same across database drivers.
> If DBD::Pg, this is as simple as:
>
> $dbh->trace('pglibpq'); ## as one example
Yes, and this shows a very good reason for using flags. "libpq" is
something that *only* DBD::Pg has, so it is useless for DBD::Oracle.
If e.g. we all could agree on $DBD_VERBOSE level >= 3 printing all SQL
statements (just an example)
DBD::Unify::st_prepare u_sql_00_000000 ("select * from foo")
Then cross database scripting debugging could be made a whole lot more
useful. Also note that it imho would be no problem whatsoever to change
the implementation of debugging and debugging flags for DBD's as no
production code is (or at least should be) depending on debug output of
DBD's. What I mean is that if I change the definition of what is
printed at what level, I do not take away functionality that is only
used while debugging, I just redefine what is printed when, and I guess
that most people that debug using level-based debugging schemes will
start with level 9 when faced with serious trouble.
> We even have a specific flag for all DBD-specific and non-DBI output:
>
> $dbh->trace('DBD');
That is not (yet) documented in DBI, is it?
My ultimate goal would be to see a minimal set of flags documented in
the DBI that define how the DBD's are expected to debug/trace.
Something that has not been there before, so there will not be any
`alignment' in its use yet. But if we go there, I will change
DBD::Unify to mach it.
> Also, can you elaborate more on what things the CON and ENC will
> output? I'm wondering how similar the CON is to the DBD::Pg's
> 'pglogin' flag:
>
> =item pglogin
>
> Outputs a message showing the connection string right before a new
> database connection is attempted, a message when the connection
> was successful, and a message right after the database has been
> disconnected. Also output if trace level is 5 or greater.
--
H.Merijn Brand http://tux.nl Perl Monger http://amsterdam.pm.org/
using 5.00307 through 5.12 and porting perl5.13.x on HP-UX 10.20, 11.00,
11.11, 11.23 and 11.31, OpenSuSE 10.1, 11.0 .. 11.3 and AIX 5.2 and 5.3.
http://mirrors.develooper.com/hpux/ http://www.test-smoke.org/
http://qa.perl.org http://www.goldmark.org/jeff/stupid-disclaimers/
