http://qs321.pair.com?node_id=11125312

kaldor has asked for the wisdom of the Perl Monks concerning the following question:

Dear Monks,

I need some advice about logging with DBIx::Simple (+ SQL::Interp), since I can't figure it out myself.

I'd like a unified way to trace the SQL queries and log the application errors, so something like Log::Any seems the way to go. Of course, I'd rather see the placeholders replaced by quoted bind values in my logs, like DBI::Log does (but limited to DBI tracing). And there's also DBIx::LogAny.

If it's possible to use DBIx::Simple and DBIx::LogAny together, then I don't understand how to do it. For example, DBIx::Simple::connect seems to call DBI::connect and DBIx::LogAny::connect calls SUPER::connect, so what will happen if I use both modules? Should I pass them $dbh, but then in what order?

Thanks.

Replies are listed 'Best First'.
Re: Logging and tracing with DBIx::Simple
by ablanke (Monsignor) on Dec 17, 2020 at 00:21 UTC 
    # SQL::Interp prints DEBUG to STDERR
# whether TRACE_SQL is enabled
# SQL::Interp.pm:
# my $trace_sql_enabled = $ENV{TRACE_SQL} || 0;
# $trace_sql_enabled and print STDERR "DEBUG:interp[sql=$sql,bind=". j
+oin(':', @bind) . "]\n";
# TRACE_SQL=1 perl my_script.pl

use strict;
use warnings;
use feature 'say';
use DBIx::LogAny;
use DBIx::Simple;
use Log::Any::Adapter ('File', './log_file.log');
use SQL::Interp;

# connect to your DB using DBIx::LogAny
my $dbh = DBIx::LogAny->connect('dbi:SQLite:dbname=file.dat');
 
# Using an existing database handle (DBIx::LogAny) for DBIx::Simple
my $db = DBIx::Simple->connect($dbh);

# make your query
$db->iquery('SELECT 1')->into(my $one);
say $one;

    [download]
    call your script with enabled SQL::Interp SQL Trace: 
    TRACE_SQL=1 perl my_script.pl
DEBUG:interp[sql=SELECT 1,bind=]
1

    [download]
    DBIx::LogAny log output: 
    [Thu Dec 17 01:15:15 2020] connect(0): dbi:SQLite:dbname=file.dat, , $
+attr = undef;
[Thu Dec 17 01:15:15 2020] DBI: 1.643 , DBIx::LogAny: 0.06, Driver: SQ
+Lite(1.66)
[Thu Dec 17 01:15:15 2020] prepare(0.0): 'SELECT 1'
[Thu Dec 17 01:15:15 2020] execute(0.0)
[Thu Dec 17 01:15:15 2020] finish(0.0)
[Thu Dec 17 01:15:15 2020] finish(0.0)

    [download]
[reply]
[d/l]
[select]

      That's precisely what I needed, thank you!

[reply]
Re: Logging and tracing with DBIx::Simple
by perlfan (Vicar) on Dec 18, 2020 at 17:56 UTC
    Not sure how integrated of an experience you want, but Log::Log4perl might also be an option for you.
[reply]

Back to Seekers of Perl Wisdom