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" <[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.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 <[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 -----
