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

       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@lists.sourceforge.net

       Authors (please contact them via the list above, not directly): Mike Schilli  <m@perlmeister.com>,  Kevin
       Goess <cpan@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.

perl v5.36.0                                       2022-10-30                                 Appender::DBI(3pm)

       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!

       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";
           }

       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.

       * AsIwaswritingthis,DankoMannhauptwascomingoutwithhisimprovedlog4jJDBCAppender(http://www.mannhaupt.com/danko/projects/)whichovercomesmanyofthedrawbacksoftheoriginalJDBCAppender.

       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.

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

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

       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.

       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.

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

       Log::Dispatch::DBI

       Log::Log4perl::JavaMap::JDBCAppender

           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 );