On 13-Apr-2006 Hardy Merrill wrote:
> It's been a long time since I've actively written Perl (and DBI) code, but I
> wanted to add that IIRC if you use "trace" (or $dbh->trace(2) or trace(3)
> ??)
> around the DBI code in question, the trace output includes(?) the SQL along
> with the placeholder values being inserted. Hope I'm not mistating the facts
> here but it's at least worth a look at the DBI docs - look up "trace".
>
> HTH.
>
> Hardy Merrill
DBI's trace via $h->trace(n) or DBI_TRACE=n=file does include SQL etc.
However, the trace at level 1 is still massive. As an example my small test
code produces a log file 3855 bytes long containing only SQL, parameters,
errors etc the first part of which looks like:
DEBUG - connect: DBI:mysql:mjetest, xxx
INFO - DBI: 1.50, DBIx::Log4perl: 0.01, Driver: mysql(3.0002_4)
DEBUG - do: 'drop table if exists mytest'
WARN - no effect from : 'drop table if exists mytest'
DEBUG - do: 'create table mytest (a int primary key, b char(20))'
WARN - no effect from : 'create table mytest (a int primary key, b char(20))'
DEBUG - prepare: 'insert into mytest values (?,?)'
DEBUG - $execute = [1,'one'];
DEBUG - $execute = [2,'two'];
DEBUG - $execute = [3,'three'];
DEBUG - prepare: 'select * from mytest where b = ?'
DEBUG - execute: 'one'
DEBUG - $fetchrow_arrayref = ['1','one'];
DEBUG - execute: 'one'
DEBUG - $fetchrow_array = ['1','one'];
DEBUG - execute: 'one'
DEBUG - $fetch = ['1','one'];
DEBUG - $fetchrow_hashref = {'a' => '1','b' => 'one'};
DEBUG - $do = ['delete from mytest where b = ?',undef,'notexists'];
WARN - $no effect from = ['delete from mytest where b = ?',undef,'notexists'];
DBI trace level 1 produces a file 258851 bytes (67 times larger) and looks like
this:
DBI 1.50-nothread default trace level set to 0x0/3 (pid 20786)
-> DBIx::Log4perl->connect(DBI:mysql:mjetest, xxx, ****, HASH(0x811df00))
-> DBIx::Log4perl->install_driver(mysql) for linux perl=5.008007 pid=20786
ruid=1005 euid=1005
install_driver: DBD::mysql version 3.0002_4 loaded from
/usr/local/lib/perl5/site_perl/5.8.7/i686-linux/DBD/mysql.pm
New DBI::dr (for DBD::mysql::dr, parent=, id=)
dbih_setup_handle(DBI::dr=HASH(0x8313570)=>DBI::dr=HASH(0x83e8e20),
DBD::mysql::dr, 0, Null!)
dbih_make_com(Null!, 0, DBD::mysql::dr, 84, 0) thr#0
<- install_driver= DBI::dr=HASH(0x8313570)
-> connect for DBD::mysql::dr (DBI::dr=HASH(0x8313570)~0x83e8e20 'mjetest'
'xxx' **** HASH(0x84015fc))
New DBI::db (for DBD::mysql::db, parent=DBI::dr=HASH(0x83e8e20),
id=HASH(0x8401674))
dbih_setup_handle(DBI::db=HASH(0x83e8e50)=>DBI::db=HASH(0x84015f0),
DBD::mysql::db, 83132c4, HASH(0x8401674))
dbih_make_com(DBI::dr=HASH(0x83e8e20), 83d2960, DBD::mysql::db, 1068,
8401644) thr#0
imp_dbh->connect: dsn = mjetest, uid = xxx, pwd = xxx
imp_dbh->my_login : dbname = mjetest, uid = xxx, pwd = xxx,host = NULL,
port = NULL
imp_dbh->mysql_dr_connect: host = |NULL|, port = 0, uid = xxx, pwd = xxx
imp_dbh->mysql_dr_connect: client_flags = 2
imp_dbh->mysql_dr_connect: <- <- connect= DBI::db=HASH(0x83e8e50) at DBI.pm
line 617 via ./MyDBI_test.pl line 13
-> STORE for DBD::mysql::db (DBI::db=HASH(0x84015f0)~INNER 'RootClass'
'DBIx::Log4perl')
STORE DBI::db=HASH(0x84015f0) 'RootClass' => 'DBIx::Log4perl'
<- STORE= 1 at DBI.pm line 650 via ./MyDBI_test.pl line 13
_set_isa([DBIx::Log4perl])
DBIx::Log4perl::db::ISA skipped (already set to DBI::db DBIx::Log4perl)
DBIx::Log4perl::st::ISA skipped (already set to DBI::st DBIx::Log4perl)
-> STORE for DBD::mysql::db (DBIx::Log4perl::db=HASH(0x84015f0)~INNER
'PrintError' 1)
STORE DBIx::Log4perl::db=HASH(0x84015f0) 'PrintError' => 1
<- STORE= 1 at DBI.pm line 664 via ./MyDBI_test.pl line 13
-> STORE for DBD::mysql::db (DBIx::Log4perl::db=HASH(0x84015f0)~INNER
'AutoCommit' 1)
<- STORE= 1 at DBI.pm line 664 via ./MyDBI_test.pl line 13
-> STORE for DBD::mysql::db (DBIx::Log4perl::db=HASH(0x84015f0)~INNER
'HandleError' CODE(0x837316c))
STORE DBIx::Log4perl::db=HASH(0x84015f0) 'HandleError' => CODE(0x837316c)
<- STORE= 1 at DBI.pm line 667 via
/home/martin/xxx/tools/modules/XXX/DBIx-Log4perl/lib//DBIx/Log4perl.pm line 229
<> FETCH= CODE(0x837316c) ('HandleError' from cache) at DBI.pm line 667 via
./MyDBI_test.pl line 13
-> STORE for DBD::mysql::db (DBIx::Log4perl::db=HASH(0x84015f0)~INNER
'Username' 'xxx')
STORE DBIx::Log4perl::db=HASH(0x84015f0) 'Username' => 'xxx'
<- STORE= 1 at DBI.pm line 667 via
/home/martin/xxx/tools/modules/XXX/DBIx-Log4perl/lib//DBIx/Log4perl.pm line 229
<> FETCH= 'xxx' ('Username' from cache) at DBI.pm line 667 via
./MyDBI_test.pl line 13
<- connect= DBIx::Log4perl::db=HASH(0x83e8e50)
-> STORE for DBD::mysql::db (DBIx::Log4perl::db=HASH(0x84015f0)~INNER
'dbi_connect_closure' CODE(0x83e8eec))
STORE DBIx::Log4perl::db=HASH(0x84015f0) 'dbi_connect_closure' =>
CODE(0x83e8eec)
<- STORE= 1 at DBI.pm line 683 via
/home/martin/xxx/tools/modules/XXX/DBIx-Log4perl/lib//DBIx/Log4perl.pm line 229
-> STORE for DBD::mysql::db (DBIx::Log4perl::db=HASH(0x84015f0)~INNER
'private_DBIx_Log4perl' HASH(0x837cde0))
STORE DBIx::Log4perl::db=HASH(0x84015f0) 'private_DBIx_Log4perl' =>
HASH(0x837cde0)
<- STORE= 1 at Log4perl.pm line 232 via ./MyDBI_test.pl line 13
-> FETCH for DBD::mysql::db (DBIx::Log4perl::db=HASH(0x84015f0)~INNER
'Driver')
.. FETCH DBIx::Log4perl::db=HASH(0x84015f0) 'Driver' =
DBI::dr=HASH(0x8313570)
<- FETCH= DBI::dr=HASH(0x8313570) at Log4perl.pm line 236 via
./MyDBI_test.pl line 13
<> FETCH= 'mysql' ('Name' from cache) at Log4perl.pm line 236 via
./MyDBI_test.pl line 13
-> FETCH for DBD::mysql::db (DBIx::Log4perl::db=HASH(0x84015f0)~INNER
'Driver')
.. FETCH DBIx::Log4perl::db=HASH(0x84015f0) 'Driver' =
DBI::dr=HASH(0x8313570)
<- FETCH= DBI::dr=HASH(0x8313570) at Log4perl.pm line 237 via
./MyDBI_test.pl line 13
<> FETCH= 'mysql' ('Name' from cache) at Log4perl.pm line 237 via
./MyDBI_test.pl line 13
just for the connect.
My application is running all the time and when it goes wrong I need as much
info as possible (especially whilst we are developing it). It is not practical
to use DBI's trace - it is too slow, uses too much disk space and is too
difficult to find what I need.
Martin
--
Martin J. Evans
Easysoft Ltd, UK
http://www.easysoft.com
>>>> "Martin J. Evans" <[EMAIL PROTECTED]> 4/13/2006 10:08:12 AM >>>
>> ----- Forwarded message from Tomas Karlsson <[EMAIL PROTECTED]> -----
>> I would like to suggest a new feature/function in DBI. It would really
>> be nice if there was a way to print out the actual sql statement for
>> debugging purpose, in particular in cases with placeholders like this
>> example:
>
> I've been working on something that would probably satisfy this but up until
> now just using it internally in a project I'm working on here.
>
> If you replace your:
>
> use DBI;
> $dbh = DBI->connect()
>
> with
>
> use DBIx::Log4perl;
> $dbh = DBIx::Log4perl->connect()
>
> create a Log4perl config file (example included) and optionally set
> $DBIx::Log4perl::LogMask you will get a variety of SQL, parameters, methods
> called etc in the log file.
>
> e.g. like these:
>
> DEBUG - prepare: 'insert into mytest values (?,?)'
> DEBUG - $execute = [1,'one'];
> DEBUG - $execute = [2,'two'];
> DEBUG - $execute = [3,'three'];
>
> DEBUG - $bind_param_array = [1,[51,1,52,53]];
> DEBUG - $bind_param_array = [2,['fiftyone','fiftytwo','fiftythree','one']];
> DEBUG - $execute_array = {'ArrayTupleStatus' => []};
> DEBUG - $execute = [51,'fiftyone'];
> DEBUG - $execute = [1,'fiftytwo'];
> DEBUG - $execute = [52,'fiftythree'];
> DEBUG - $execute = [53,'one'];
> ERROR - execute_array error:
> ERROR - $Error = [1062,'Duplicate entry \'1\' for key 1','S1000'];
> ERROR - for 1,fiftytwo
>
> and if an error is caught something like this:
>
> FATAL
> DB: mjetest, Username: bet
> handle type: st
> SQL: insert into mytest values (?,?)
> msg: Duplicate entry '1' for key 1
> ParamValues: 1,onetwothree,
> DBI error trap at /home/martin/tools/modules/DBIx-Log4perl/lib//DBIx/L
> og4perl/st.pm line 18
> DBIx::Log4perl::st::execute('DBIx::Log4perl::st=HASH(0x84018fc)')
> called
> at ./MyDBI_test.pl line 91
>
> I never really intended to release it as such and it is rather rough around
> the
> edges but if it helps someone else fair enough.
>
> I'd be happy to hear any comments, suggestions, fixes, enhancements etc but
> please bare in mind 1) I've packaged it up and documented it in about 1 hour
> this morning to get it here and 2) it pretty much does what I need it for now
> and never started out as something anyone else would use.
>
> ftp://ftp.easysoft.com/pub/odbc-odbc-bridge/Perl/modules/DBIx-Log4perl-0.01.ta
> r.
> gz
>
> I'm afraid you will need DBI 1.50 and Log::Log4perl 1.04 to get the best
> results although it will work with earlier Log4perl (1.01) but some of the
> logging does not come out. You may also want to look a the notes in the pod
> as
> you will need to make a small fix to DBI to get Username and ParamArrays out.
>
> Martin
> --
> Martin J. Evans
> Easysoft Ltd, UK
> http://www.easysoft.com
>
>
> On 12-Apr-2006 Tim Bunce wrote:
>> ----- Forwarded message from Tomas Karlsson <[EMAIL PROTECTED]> -----
>>
>> Date: Wed, 12 Apr 2006 16:47:18 -0400
>> From: Tomas Karlsson <[EMAIL PROTECTED]>
>> Reply-To: [EMAIL PROTECTED]
>> Organization: 10 East Corp.
>> To: [EMAIL PROTECTED]
>> Subject: suggestion for DBI
>>
>> Hi Tim,
>>
>> I would like to suggest a new feature/function in DBI. It would really
>> be nice if there was a way to print out the actual sql statement for
>> debugging purpose, in particular in cases with placeholders like this
>> example:
>>
>> my @row = ($employee_id, $dist_id, $delivery_carrier, $delivery_method,
>> $weight, $shipping_charges, $comments, $date_shipped);
>>
>> $sql = qq|
>> INSERT INTO per_emp_dist
>> (employee_id, dist_id, delivery_carrier, delivery_method,
>> weight, shipping_charges, comments, date_shipped)
>> VALUES ( @{[ join ',' => ('?') x @row ]} )|;
>>
>> $sth = $dbh->prepare($sql);
>> $sth->execute(@row);
>>
>>
>> Thanks for the grand DBI module!
>>
>> Tomas
>>
>> ----- End forwarded message -----
