1Appender::DBI(3) User Contributed Perl Documentation Appender::DBI(3)
2
6 Log::Log4perl::Appender::DBI - implements appending to a DB
7
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 log4j.appender.DBAppndr.usePreparedStmt = 1
26 #--or--
27 log4j.appender.DBAppndr.bufferSize = 2
28 #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 #driver attributes support
34 log4j.appender.DBAppndr.attrs.f_encoding = utf8
35 };
36 Log::Log4perl::init ( \$config ) ;
38 my $logger = Log::Log4perl->get_logger () ;
40 $logger->warn( $custid, 'big problem!!', $ip_addr );
41
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
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 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 The simplest usage is this:
57 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 log4j.appender.DBAppndr.layout = Log::Log4perl::Layout::PatternLayout
69 $logger->fatal('fatal message');
72 $logger->warn('warning message');
73 ===============================
75 |FATAL|fatal message |
76 |WARN |warning message |
77 ===============================
78 But the downsides to that usage are:
80 • 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 • It's not terribly high-performance, a statement is created and
88 executed for each log call.
89 • 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 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 log4j.appender.DBAppndr.sql = \
98 INSERT INTO logtbl \
99 (custid, loglevel, message) \
100 VALUES (?,?,?)
101 #---------------------------------------------------
103 #now the bind values:
104 #1 is the custid
105 log4j.appender.DBAppndr.params.2 = %p
106 #3 is the message
107 #---------------------------------------------------
108 log4j.appender.DBAppndr.layout = Log::Log4perl::Layout::NoopLayout
110 log4j.appender.DBAppndr.warp_message = 0
111 log4j.appender.DBAppndr.usePreparedStmt = 1
113 $logger->warn( 1234, 'warning message' );
116 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 invalid input: 1234';drop table custid;
121 fubaring our database!
123 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 $logger->warn( 1234, 'warning message', 'bgates' );
129 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 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 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 log4j.appender.DBAppndr.warp_message=0
152 $logger->info('arrest him!', $subpoena_id);
155 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 $logger->info('arrest him!');
161 then you just get undef in the fourth column.
163 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 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 log4j.appender.DBAppndr.bufferSize=2
174 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 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 * 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
190 Or another way to say the same thing:
191 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 $logger->warn('big problem!!',$userid,$subpoena_nr,$ip_addr);
197 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 log4j.appender.DBAppndr.params.2 = %p
203 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
209 usePreparedStmt
210 See above.
211 warp_message
213 see Log::Log4perl::Appender
214 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
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 sub _init {
230 ; #no-op, no pooling at this level
231 }
232 sub create_statement {
233 my ($self, $stmt) = @_;
234 $stmt || croak "Log4perl: sql not set in ".__PACKAGE__;
236 return My::Connections->getConnection->prepare($stmt)
238 || croak "Log4perl: DBI->prepare failed $DBI::errstr\n$stmt";
239 }
240
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 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 Alternatively, use "Apache::DBI" or "Apache::DBI::Cache" and read
257 CHANGING DB CONNECTIONS above.
258 Note that "Log::Log4perl::Appender::DBI" holds one connection open for
260 every appender, which might be too many.
261
263 Log::Dispatch::DBI
264 Log::Log4perl::JavaMap::JDBCAppender
266
268 Copyright 2002-2013 by Mike Schilli <m@perlmeister.com> and Kevin Goess
269 <cpan@goess.org>.
270 This library is free software; you can redistribute it and/or modify it
272 under the same terms as Perl itself.
273
275 Please contribute patches to the project on Github:
276 http://github.com/mschilli/log4perl
278 Send bug reports or requests for enhancements to the authors via our
280 MAILING LIST (questions, bug reports, suggestions/patches):
282 log4perl-devel@lists.sourceforge.net
283 Authors (please contact them via the list above, not directly): Mike
285 Schilli <m@perlmeister.com>, Kevin Goess <cpan@goess.org>
286 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.
294perl v5.34.0 2022-01-21 Appender::DBI(3)