Dump prepared sql query from DBI statement in PERL

Question

im using DBI in Perl to connect to my PostgreSQL database. Everything works fine, but in my debugging (print results, etc.) Iam cannot check if the perls DBI module is actually programmed.

I have something like this:

$sth->prepare( qq{SELECT * FROM company WHERE companyname LIKE ? AND city = ?}); $sth->execute( $name.'%', $city); 

I can’t see what the sql query looks like after calling execute, because execution is the last step that parameters are bound to the query.

I would like to have something like $sth->getLastExecutedQuery() or something to see what the query looks like.

In this case, the getLastExecutedQuery() function will return:

 SELECT * FROM company WHERE companyname LIKE 'Company Name%' AND city = 'City name'; 

Is there any way to get this? Its just for debugging purposes.

Answer 1

DBI supports the following: There is a DBI->trace($tracefile_handle) (tracks all interactions with DBI) or $dbh->trace($tracefile_handle) that will only track interactions on a specific descriptor. The default output is for STDERR, but by supplying $tracefile_handle , you can explicitly send the output to another file (or just use shell redirection).

DBD :: pg also supports $h->trace('SQL'); This must be supported by the DBD driver to work, but fortunately DBD :: Pg supports this feature.

The documentation for DBI, CPAN - DBI , and for DBD :: Pg at CPAN - DBD :: Pg really gives you everything you need to know when tracing.

Answer 2

Use the DBI trace facility . It works as follows:

 use strict; use warnings; use DBI; my %opt = ( RaiseError => 1 ); my $dbh = DBI->connect( 'dbi:mysql:test', 'fred', 'secret', \%opt ); $dbh->trace(2); # level 2 shows statement with inserted parameters my $sql_i = 'insert into t1 (a, b) values ( ?, ? )'; my $sth_i = $dbh->prepare( $sql_i ); for ( qw/ eins zwei drei / ) { $sth_i->execute( $_, $_ ); } $dbh->disconnect; 

Answer 3

Besides the tracking that others have talked about, you should look at https://metacpan.org/pod/DBI#Statement , which gives you the last SQL query and https://metacpan.org/pod/DBI#ParamValues and https: / /metacpan.org/pod/DBI#ParamTypes , which will tell you about your options.

There is also DBIx :: Log4perl , which can log what you want without all the DBI tracing.