Log::Log4perl::Appender::DBI - phpMan

NAME
    Log::Log4perl::Appender::DBI - implements appending to a DB

SYNOPSIS
        my $config = q{
         log4j.category = WARN, DBAppndr
         log4j.appender.DBAppndr             = Log::Log4perl::Appender::DBI
         log4j.appender.DBAppndr.datasource  = DBI:CSV:f_dir=t/tmp
         log4j.appender.DBAppndr.username    = bobjones
         log4j.appender.DBAppndr.password    = 12345
         log4j.appender.DBAppndr.sql         = \
            insert into log4perltest           \
            (loglevel, custid, category, message, ipaddr) \
            values (?,?,?,?,?)
         log4j.appender.DBAppndr.params.1 = %p
                                       #2 is custid from the log() call
         log4j.appender.DBAppndr.params.3 = %c
                                       #4 is the message from log()
                                       #5 is ipaddr from log()

         log4j.appender.DBAppndr.usePreparedStmt = 1
          #--or--
         log4j.appender.DBAppndr.bufferSize = 2

         #just pass through the array of message items in the log statement
         log4j.appender.DBAppndr.layout    = Log::Log4perl::Layout::NoopLayout
         log4j.appender.DBAppndr.warp_message = 0

         #driver attributes support
         log4j.appender.DBAppndr.attrs.f_encoding = utf8
        };

    Log::Log4perl::init ( \$config ) ;

    my $logger = Log::Log4perl->get_logger () ;
        $logger->warn( $custid, 'big problem!!', $ip_addr );

CAVEAT
    This is a very young module and there are a lot of variations in setups
    with different databases and connection methods, so make sure you test
    thoroughly! Any feedback is welcome!

DESCRIPTION
    This is a specialized Log::Dispatch object customized to work with
    log4perl and its abilities, originally based on Log::Dispatch::DBI by
    Tatsuhiko Miyagawa but with heavy modifications.

    It is an attempted compromise between what Log::Dispatch::DBI was doing
    and what log4j's JDBCAppender does. Note the log4j docs say the
    JDBCAppender "is very likely to be completely replaced in the future."

    The simplest usage is this:

        log4j.category = WARN, DBAppndr
        log4j.appender.DBAppndr            = Log::Log4perl::Appender::DBI
        log4j.appender.DBAppndr.datasource = DBI:CSV:f_dir=t/tmp
        log4j.appender.DBAppndr.username   = bobjones
        log4j.appender.DBAppndr.password   = 12345
        log4j.appender.DBAppndr.sql        = \
           INSERT INTO logtbl                \
              (loglevel, message)            \
              VALUES ('%c','%m')

        log4j.appender.DBAppndr.layout    = Log::Log4perl::Layout::PatternLayout


        $logger->fatal('fatal message');
        $logger->warn('warning message');

        ===============================
        |FATAL|fatal message          |
        |WARN |warning message        |
        ===============================

    But the downsides to that usage are:

    *   You'd better be darn sure there are not quotes in your log message,
        or your insert could have unforeseen consequences! This is a very
        insecure way to handle database inserts, using place holders and
        bind values is much better, keep reading. (Note that the log4j docs
        warn "Be careful of quotes in your messages!") *.

    *   It's not terribly high-performance, a statement is created and
        executed for each log call.

    *   The only run-time parameter you get is the %m message, in reality
        you probably want to log specific data in specific table columns.

    So let's try using placeholders, and tell the logger to create a
    prepared statement handle at the beginning and just reuse it (just like
    Log::Dispatch::DBI does)

        log4j.appender.DBAppndr.sql = \
           INSERT INTO logtbl \
              (custid, loglevel, message) \
              VALUES (?,?,?)

        #---------------------------------------------------
        #now the bind values:
                                      #1 is the custid
        log4j.appender.DBAppndr.params.2 = %p
                                      #3 is the message
        #---------------------------------------------------

        log4j.appender.DBAppndr.layout    = Log::Log4perl::Layout::NoopLayout
        log4j.appender.DBAppndr.warp_message = 0

        log4j.appender.DBAppndr.usePreparedStmt = 1


        $logger->warn( 1234, 'warning message' );

    Now see how we're using the '?' placeholders in our statement? This
    means we don't have to worry about messages that look like

        invalid input: 1234';drop table custid;

    fubaring our database!

    Normally a list of things in the logging statement gets concatenated
    into a single string, but setting "warp_message" to 0 and using the
    NoopLayout means that in

        $logger->warn( 1234, 'warning message', 'bgates' );

    the individual list values will still be available for the DBI appender
    later on. (If "warp_message" is not set to 0, the default behavior is to
    join the list elements into a single string. If PatternLayout or
    SimpleLayout are used, their attempt to "render()" your layout will
    result in something like "ARRAY(0x841d8dc)" in your logs. More
    information on "warp_message" is in Log::Log4perl::Appender.)

    In your insert SQL you can mix up '?' placeholders with conversion
    specifiers (%c, %p, etc) as you see fit--the logger will match the
    question marks to params you've defined in the config file and populate
    the rest with values from your list. If there are more '?' placeholders
    than there are values in your message, it will use undef for the rest.
    For instance,

            log4j.appender.DBAppndr.sql =                 \
               insert into log4perltest                   \
               (loglevel, message, datestr, subpoena_id)\
               values (?,?,?,?)
            log4j.appender.DBAppndr.params.1 = %p
            log4j.appender.DBAppndr.params.3 = %d

            log4j.appender.DBAppndr.warp_message=0


            $logger->info('arrest him!', $subpoena_id);

    results in the first '?' placeholder being bound to %p, the second to
    "arrest him!", the third to the date from "%d", and the fourth to your
    $subpoenaid. If you forget the $subpoena_id and just log

            $logger->info('arrest him!');

    then you just get undef in the fourth column.

    If the logger statement is also being handled by other non-DBI
    appenders, they will just join the list into a string, joined with
    $Log::Log4perl::JOIN_MSG_ARRAY_CHAR (default is an empty string).

    And see the "usePreparedStmt"? That creates a statement handle when the
    logger object is created and just reuses it. That, however, may be
    problematic for long-running processes like webservers, in which case
    you can use this parameter instead

        log4j.appender.DBAppndr.bufferSize=2

    This copies log4j's JDBCAppender's behavior, it saves up that many log
    statements and writes them all out at once. If your INSERT statement
    uses only ? placeholders and no %x conversion specifiers it should be
    quite efficient because the logger can re-use the same statement handle
    for the inserts.

    If the program ends while the buffer is only partly full, the DESTROY
    block should flush the remaining statements, if the DESTROY block runs
    of course.

    * *As I was writing this, Danko Mannhaupt was coming out with his
    improved log4j JDBCAppender (http://www.mannhaupt.com/danko/projects/)
    which overcomes many of the drawbacks of the original JDBCAppender.*

DESCRIPTION 2
    Or another way to say the same thing:

    The idea is that if you're logging to a database table, you probably
    want specific parts of your log information in certain columns. To this
    end, you pass an list to the log statement, like

        $logger->warn('big problem!!',$userid,$subpoena_nr,$ip_addr);

    and the array members drop into the positions defined by the
    placeholders in your SQL statement. You can also define information in
    the config file like

        log4j.appender.DBAppndr.params.2 = %p

    in which case those numbered placeholders will be filled in with the
    specified values, and the rest of the placeholders will be filled in
    with the values from your log statement's array.

MISC PARAMETERS
    usePreparedStmt
        See above.

    warp_message
        see Log::Log4perl::Appender

    max_col_size
        If you're used to just throwing debugging messages like huge
        stacktraces into your logger, some databases (Sybase's DBD!!) may
        surprise you by choking on data size limitations. Normally, the data
        would just be truncated to fit in the column, but Sybases's DBD it
        turns out maxes out at 255 characters. Use this parameter in such a
        situation to truncate long messages before they get to the INSERT
        statement.

CHANGING DBH CONNECTIONS (POOLING)
    If you want to get your dbh from some place in particular, like maybe a
    pool, subclass and override _init() and/or create_statement(), for
    instance

        sub _init {
            ; #no-op, no pooling at this level
        }
        sub create_statement {
            my ($self, $stmt) = @_;

            $stmt || croak "Log4perl: sql not set in ".__PACKAGE__;

            return My::Connections->getConnection->prepare($stmt)
                || croak "Log4perl: DBI->prepare failed $DBI::errstr\n$stmt";
        }

LIFE OF CONNECTIONS
    If you're using "log4j.appender.DBAppndr.usePreparedStmt" this module
    creates an sth when it starts and keeps it for the life of the program.
    For long-running processes (e.g. mod_perl), connections might go stale,
    but if "Log::Log4perl::Appender::DBI" tries to write a message and
    figures out that the DB connection is no longer working (using DBI's
    ping method), it will reconnect.

    The reconnection process can be controlled by two parameters,
    "reconnect_attempts" and "reconnect_sleep". "reconnect_attempts"
    specifies the number of reconnections attempts the DBI appender performs
    until it gives up and dies. "reconnect_sleep" is the time between
    reconnection attempts, measured in seconds. "reconnect_attempts"
    defaults to 1, "reconnect_sleep" to 0.

    Alternatively, use "Apache::DBI" or "Apache::DBI::Cache" and read
    CHANGING DB CONNECTIONS above.

    Note that "Log::Log4perl::Appender::DBI" holds one connection open for
    every appender, which might be too many.

SEE ALSO
    Log::Dispatch::DBI

    Log::Log4perl::JavaMap::JDBCAppender

LICENSE
    Copyright 2002-2013 by Mike Schilli <m AT perlmeister.com> and Kevin Goess
    <cpan AT goess.org>.

    This library is free software; you can redistribute it and/or modify it
    under the same terms as Perl itself.

AUTHOR
    Please contribute patches to the project on Github:

        http://github.com/mschilli/log4perl

    Send bug reports or requests for enhancements to the authors via our

    MAILING LIST (questions, bug reports, suggestions/patches):
    log4perl-devel AT lists.net

    Authors (please contact them via the list above, not directly): Mike
    Schilli <m AT perlmeister.com>, Kevin Goess <cpan AT goess.org>

    Contributors (in alphabetical order): Ateeq Altaf, Cory Bennett, Jens
    Berthold, Jeremy Bopp, Hutton Davidson, Chris R. Donnelly, Matisse
    Enzer, Hugh Esco, Anthony Foiani, James FitzGibbon, Carl Franks, Dennis
    Gregorovic, Andy Grundman, Paul Harrington, Alexander Hartmaier David
    Hull, Robert Jacobson, Jason Kohles, Jeff Macdonald, Markus Peter, Brett
    Rann, Peter Rabbitson, Erik Selberg, Aaron Straup Cope, Lars Thegler,
    David Viner, Mac Yang.