Dumper with DBI/DBD?

[kingram]

When using DBI and the associated Database module, what is the best way to find out the final statement and contents reaching the database?

Particularly when you are passing values? E.G.

$sql="insert into test (one, two, three) VALUES (?,?,?)"
@params = ('4','5',"str_to_date(\'$time_value\',\'%Y-%m-%d %h:%i:%s %p
+\')
$sth->prepare($sql);
print Dumper($sth);
my $rslt = $sth->execute($params[0],$params[1],$params[2]);
print Dumper($rslt);

Dumper just gives me:

$VAR1 = bless( {}, 'DBI::st' );
$VAR1 = 1;

Not sure how to make sense of the first response, or if I need to use a different tool. The 2nd response indicates a successful SQL operation, but the date_to_string function is failing to put the date into the record.

[marto]

If you're having problems debugging SQL/DBI I'd suggest using Tracing to try and find out where exactly the problem occurs, be it the query, DBI or the database drivers.

[kingram]

Thanks.
That helped.
DBI was doing it's job. The str_to_date function was being passed as a string instead of a function.
I've learned quite a bit since I started building this thing.
45 hours getting right with my Perl skills with a lot of good help from The Monks...
Cheers!

[marto]

I'm glad this helped, IMHO it's wise to familiarise yourself with debugging tools. An editor which supports Syntax_highlighting would also be advantageous.

[moritz]

Since your question has already been answered, here a small suggestion on how to do your SQL query:

$sql = "insert into test (one, two, three) VALUES (?,?,str_to_date(?, 
+'%Y-%m-%d %h:%i:%s %p'))";
@params = ('4', '5', $time_value);

[mje]

You may find DBIx::Log4perl useful. It can log just the SQL and and bound parameters at execute time.