1Appender::DBI(3)      User Contributed Perl Documentation     Appender::DBI(3)
2
3
4

NAME

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

SYNOPSIS

9           my $config = q{
10            log4j.category = WARN, DBAppndr
11            log4j.appender.DBAppndr             = Log::Log4perl::Appender::DBI
12            log4j.appender.DBAppndr.datasource  = DBI:CSV:f_dir=t/tmp
13            log4j.appender.DBAppndr.username    = bobjones
14            log4j.appender.DBAppndr.password    = 12345
15            log4j.appender.DBAppndr.sql         = \
16               insert into log4perltest           \
17               (loglevel, custid, category, message, ipaddr) \
18               values (?,?,?,?,?)
19            log4j.appender.DBAppndr.params.1 = %p
20                                          #2 is custid from the log() call
21            log4j.appender.DBAppndr.params.3 = %c
22                                          #4 is the message from log()
23                                          #5 is ipaddr from log()
24
25            log4j.appender.DBAppndr.usePreparedStmt = 1
26             #--or--
27            log4j.appender.DBAppndr.bufferSize = 2
28
29            #just pass through the array of message items in the log statement
30            log4j.appender.DBAppndr.layout    = Log::Log4perl::Layout::NoopLayout
31            log4j.appender.DBAppndr.warp_message = 0
32
33            #driver attributes support
34            log4j.appender.DBAppndr.attrs.f_encoding = utf8
35           };
36
37           Log::Log4perl::init ( \$config ) ;
38
39           my $logger = Log::Log4perl->get_logger () ;
40           $logger->warn( $custid, 'big problem!!', $ip_addr );
41

CAVEAT

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

DESCRIPTION

48       This is a specialized Log::Dispatch object customized to work with
49       log4perl and its abilities, originally based on Log::Dispatch::DBI by
50       Tatsuhiko Miyagawa but with heavy modifications.
51
52       It is an attempted compromise between what Log::Dispatch::DBI was doing
53       and what log4j's JDBCAppender does.  Note the log4j docs say the
54       JDBCAppender "is very likely to be completely replaced in the future."
55
56       The simplest usage is this:
57
58           log4j.category = WARN, DBAppndr
59           log4j.appender.DBAppndr            = Log::Log4perl::Appender::DBI
60           log4j.appender.DBAppndr.datasource = DBI:CSV:f_dir=t/tmp
61           log4j.appender.DBAppndr.username   = bobjones
62           log4j.appender.DBAppndr.password   = 12345
63           log4j.appender.DBAppndr.sql        = \
64              INSERT INTO logtbl                \
65                 (loglevel, message)            \
66                 VALUES ('%c','%m')
67
68           log4j.appender.DBAppndr.layout    = Log::Log4perl::Layout::PatternLayout
69
70
71           $logger->fatal('fatal message');
72           $logger->warn('warning message');
73
74           ===============================
75           |FATAL|fatal message          |
76           |WARN |warning message        |
77           ===============================
78
79       But the downsides to that usage are:
80
81       •   You'd better be darn sure there are not quotes in your log message,
82           or your insert could have unforeseen consequences!  This is a very
83           insecure way to handle database inserts, using place holders and
84           bind values is much better, keep reading. (Note that the log4j docs
85           warn "Be careful of quotes in your messages!") *.
86
87       •   It's not terribly high-performance, a statement is created and
88           executed for each log call.
89
90       •   The only run-time parameter you get is the %m message, in reality
91           you probably want to log specific data in specific table columns.
92
93       So let's try using placeholders, and tell the logger to create a
94       prepared statement handle at the beginning and just reuse it (just like
95       Log::Dispatch::DBI does)
96
97           log4j.appender.DBAppndr.sql = \
98              INSERT INTO logtbl \
99                 (custid, loglevel, message) \
100                 VALUES (?,?,?)
101
102           #---------------------------------------------------
103           #now the bind values:
104                                         #1 is the custid
105           log4j.appender.DBAppndr.params.2 = %p
106                                         #3 is the message
107           #---------------------------------------------------
108
109           log4j.appender.DBAppndr.layout    = Log::Log4perl::Layout::NoopLayout
110           log4j.appender.DBAppndr.warp_message = 0
111
112           log4j.appender.DBAppndr.usePreparedStmt = 1
113
114
115           $logger->warn( 1234, 'warning message' );
116
117       Now see how we're using the '?' placeholders in our statement?  This
118       means we don't have to worry about messages that look like
119
120           invalid input: 1234';drop table custid;
121
122       fubaring our database!
123
124       Normally a list of things in the logging statement gets concatenated
125       into a single string, but setting "warp_message" to 0 and using the
126       NoopLayout means that in
127
128           $logger->warn( 1234, 'warning message', 'bgates' );
129
130       the individual list values will still be available for the DBI appender
131       later on.  (If "warp_message" is not set to 0, the default behavior is
132       to join the list elements into a single string.   If PatternLayout or
133       SimpleLayout are used, their attempt to render() your layout will
134       result in something like "ARRAY(0x841d8dc)" in your logs.  More
135       information on "warp_message" is in Log::Log4perl::Appender.)
136
137       In your insert SQL you can mix up '?' placeholders with conversion
138       specifiers (%c, %p, etc) as you see fit--the logger will match the
139       question marks to params you've defined in the config file and populate
140       the rest with values from your list.  If there are more '?'
141       placeholders than there are values in your message, it will use undef
142       for the rest.  For instance,
143
144               log4j.appender.DBAppndr.sql =                 \
145                  insert into log4perltest                   \
146                  (loglevel, message, datestr, subpoena_id)\
147                  values (?,?,?,?)
148               log4j.appender.DBAppndr.params.1 = %p
149               log4j.appender.DBAppndr.params.3 = %d
150
151               log4j.appender.DBAppndr.warp_message=0
152
153
154               $logger->info('arrest him!', $subpoena_id);
155
156       results in the first '?' placeholder being bound to %p, the second to
157       "arrest him!", the third to the date from "%d", and the fourth to your
158       $subpoenaid.  If you forget the $subpoena_id and just log
159
160               $logger->info('arrest him!');
161
162       then you just get undef in the fourth column.
163
164       If the logger statement is also being handled by other non-DBI
165       appenders, they will just join the list into a string, joined with
166       $Log::Log4perl::JOIN_MSG_ARRAY_CHAR (default is an empty string).
167
168       And see the "usePreparedStmt"?  That creates a statement handle when
169       the logger object is created and just reuses it.  That, however, may be
170       problematic for long-running processes like webservers, in which case
171       you can use this parameter instead
172
173           log4j.appender.DBAppndr.bufferSize=2
174
175       This copies log4j's JDBCAppender's behavior, it saves up that many log
176       statements and writes them all out at once.  If your INSERT statement
177       uses only ? placeholders and no %x conversion specifiers it should be
178       quite efficient because the logger can re-use the same statement handle
179       for the inserts.
180
181       If the program ends while the buffer is only partly full, the DESTROY
182       block should flush the remaining statements, if the DESTROY block runs
183       of course.
184
185       * As I was writing this, Danko Mannhaupt was coming out with his
186       improved log4j JDBCAppender (http://www.mannhaupt.com/danko/projects/)
187       which overcomes many of the drawbacks of the original JDBCAppender.
188

DESCRIPTION 2

190       Or another way to say the same thing:
191
192       The idea is that if you're logging to a database table, you probably
193       want specific parts of your log information in certain columns.  To
194       this end, you pass an list to the log statement, like
195
196           $logger->warn('big problem!!',$userid,$subpoena_nr,$ip_addr);
197
198       and the array members drop into the positions defined by the
199       placeholders in your SQL statement. You can also define information in
200       the config file like
201
202           log4j.appender.DBAppndr.params.2 = %p
203
204       in which case those numbered placeholders will be filled in with the
205       specified values, and the rest of the placeholders will be filled in
206       with the values from your log statement's array.
207

MISC PARAMETERS

209       usePreparedStmt
210           See above.
211
212       warp_message
213           see Log::Log4perl::Appender
214
215       max_col_size
216           If you're used to just throwing debugging messages like huge
217           stacktraces into your logger, some databases (Sybase's DBD!!) may
218           surprise you by choking on data size limitations.  Normally, the
219           data would just be truncated to fit in the column, but Sybases's
220           DBD it turns out maxes out at 255 characters.  Use this parameter
221           in such a situation to truncate long messages before they get to
222           the INSERT statement.
223

CHANGING DBH CONNECTIONS (POOLING)

225       If you want to get your dbh from some place in particular, like maybe a
226       pool, subclass and override _init() and/or create_statement(), for
227       instance
228
229           sub _init {
230               ; #no-op, no pooling at this level
231           }
232           sub create_statement {
233               my ($self, $stmt) = @_;
234
235               $stmt || croak "Log4perl: sql not set in ".__PACKAGE__;
236
237               return My::Connections->getConnection->prepare($stmt)
238                   || croak "Log4perl: DBI->prepare failed $DBI::errstr\n$stmt";
239           }
240

LIFE OF CONNECTIONS

242       If you're using "log4j.appender.DBAppndr.usePreparedStmt" this module
243       creates an sth when it starts and keeps it for the life of the program.
244       For long-running processes (e.g. mod_perl), connections might go stale,
245       but if "Log::Log4perl::Appender::DBI" tries to write a message and
246       figures out that the DB connection is no longer working (using DBI's
247       ping method), it will reconnect.
248
249       The reconnection process can be controlled by two parameters,
250       "reconnect_attempts" and "reconnect_sleep". "reconnect_attempts"
251       specifies the number of reconnections attempts the DBI appender
252       performs until it gives up and dies. "reconnect_sleep" is the time
253       between reconnection attempts, measured in seconds.
254       "reconnect_attempts" defaults to 1,  "reconnect_sleep" to 0.
255
256       Alternatively, use "Apache::DBI" or "Apache::DBI::Cache" and read
257       CHANGING DB CONNECTIONS above.
258
259       Note that "Log::Log4perl::Appender::DBI" holds one connection open for
260       every appender, which might be too many.
261

SEE ALSO

263       Log::Dispatch::DBI
264
265       Log::Log4perl::JavaMap::JDBCAppender
266

LICENSE

268       Copyright 2002-2013 by Mike Schilli <m@perlmeister.com> and Kevin Goess
269       <cpan@goess.org>.
270
271       This library is free software; you can redistribute it and/or modify it
272       under the same terms as Perl itself.
273

AUTHOR

275       Please contribute patches to the project on Github:
276
277           http://github.com/mschilli/log4perl
278
279       Send bug reports or requests for enhancements to the authors via our
280
281       MAILING LIST (questions, bug reports, suggestions/patches):
282       log4perl-devel@lists.sourceforge.net
283
284       Authors (please contact them via the list above, not directly): Mike
285       Schilli <m@perlmeister.com>, Kevin Goess <cpan@goess.org>
286
287       Contributors (in alphabetical order): Ateeq Altaf, Cory Bennett, Jens
288       Berthold, Jeremy Bopp, Hutton Davidson, Chris R. Donnelly, Matisse
289       Enzer, Hugh Esco, Anthony Foiani, James FitzGibbon, Carl Franks, Dennis
290       Gregorovic, Andy Grundman, Paul Harrington, Alexander Hartmaier  David
291       Hull, Robert Jacobson, Jason Kohles, Jeff Macdonald, Markus Peter,
292       Brett Rann, Peter Rabbitson, Erik Selberg, Aaron Straup Cope, Lars
293       Thegler, David Viner, Mac Yang.
294
295
296
297perl v5.36.0                      2023-01-20                  Appender::DBI(3)
Impressum