(Fwd) suggestion for DBI

----- Forwarded message from Tomas Karlsson -----

Date: Wed, 12 Apr 2006 16:47:18 -0400
From: Tomas Karlsson
Reply-To: karlsson@10east.com
Organization: 10 East Corp.
To: Tim.Bunce@pobox.com
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 -----

Re: (Fwd) suggestion for DBI

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1


> 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'm not really seeing the advantage of this - such a low-level debugging
belongs in your database server logs. However, many of the drivers
already do print out some version of what you want - try calling
$dbh->trace(15) and see what happens. In the future, there will also
be finer control over the trace messages so that you could only see
the parts of the trace you were interested in.

I cannot speak for the other drivers, but what you want will likely never
happen in DBD::Pg, as it does not send an "actual sql statement" to the server
anymore, but sends the prepared statement, and then an array of values with
each execute, plus an array of lengths if the data is binary.

- --
Greg Sabino Mullane greg@turnstep.com
PGP Key: 0x14964AC8 200604130119
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B90 6714964AC8

-----BEGIN PGP SIGNATURE-----

iD8DBQFEPd+xvJuQZxSWSsgRAtaaAKDrb3wQydJxgU8i9e10wUffn36duACf QaAN
qGNG1cN/DuEv6lddTbBEUtQ=
=eid8
-----END PGP SIGNATURE-----

RE: (Fwd) suggestion for DBI

> ----- Forwarded message from Tomas Karlsson -----
> 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(0x84018 fc)') 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/DBI x-Log4perl-0.01.tar.
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 -----
>
> Date: Wed, 12 Apr 2006 16:47:18 -0400
> From: Tomas Karlsson
> Reply-To: karlsson@10east.com
> Organization: 10 East Corp.
> To: Tim.Bunce@pobox.com
> 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 -----

RE: (Fwd) suggestion for DBI

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

>>> "Martin J. Evans" 4/13/2006 10:08:12 AM =
>>>
> ----- Forwarded message from Tomas Karlsson -----
> I would like to suggest a new feature/function in DBI. It would =
really=20
> be nice if there was a way to print out the actual sql statement for=20
> debugging purpose, in particular in cases with placeholders like this=20
> 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 =3D DBI->connect()

with=20

use DBIx::Log4perl;
$dbh =3D 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 =3D [1,'one'];
DEBUG - $execute =3D [2,'two'];
DEBUG - $execute =3D [3,'three'];

DEBUG - $bind_param_array =3D [1,[51,1,52,53]];
DEBUG - $bind_param_array =3D [2,['fiftyone','fiftytwo','fiftythree','one']=
];
DEBUG - $execute_array =3D {'ArrayTupleStatus' =3D> []};
DEBUG - $execute =3D [51,'fiftyone'];
DEBUG - $execute =3D [1,'fiftytwo'];
DEBUG - $execute =3D [52,'fiftythree'];
DEBUG - $execute =3D [53,'one'];
ERROR - execute_array error:
ERROR - $Error =3D [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=3DHASH(0x840 18fc)')=
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/DBI x-Log4perl-0.01=
..tar.
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=20


On 12-Apr-2006 Tim Bunce wrote:
> ----- Forwarded message from Tomas Karlsson -----
>=20
> Date: Wed, 12 Apr 2006 16:47:18 -0400
> From: Tomas Karlsson
> Reply-To: karlsson@10east.com=20
> Organization: 10 East Corp.
> To: Tim.Bunce@pobox.com=20
> Subject: suggestion for DBI
>=20
> Hi Tim,
>=20
> I would like to suggest a new feature/function in DBI. It would =
really=20
> be nice if there was a way to print out the actual sql statement for=20
> debugging purpose, in particular in cases with placeholders like this=20
> example:
>=20
> my @row =3D ($employee_id, $dist_id, $delivery_carrier, $delivery_method,=

> $weight, $shipping_charges, $comments, $date_shipped);
>=20
> $sql =3D qq|
> INSERT INTO per_emp_dist
> (employee_id, dist_id, delivery_carrier, delivery_method,
> weight, shipping_charges, comments, date_shipped)
> VALUES ( @{[ join ',' =3D> ('?') x @row ]} )|;
>=20
> $sth =3D $dbh->prepare($sql);
> $sth->execute(@row);
>=20
>=20
> Thanks for the grand DBI module!
>=20
> Tomas
>=20
> ----- End forwarded message -----

RE: (Fwd) suggestion for DBI

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(0x83 e8e20),
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(0x84 015f0),
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/L og4perl.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/L og4perl.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/L og4perl.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" 4/13/2006 10:08:12 AM >>>
>> ----- Forwarded message from Tomas Karlsson -----
>> 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(0x84018 fc)')
> 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/DBI x-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 -----
>>
>> Date: Wed, 12 Apr 2006 16:47:18 -0400
>> From: Tomas Karlsson
>> Reply-To: karlsson@10east.com
>> Organization: 10 East Corp.
>> To: Tim.Bunce@pobox.com
>> 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 -----