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
27 #--or--
28 log4j.appender.DBAppndr.bufferSize = 2
29 #just pass through the array of message items in the log statement
31 log4j.appender.DBAppndr.layout = Log::Log4perl::Layout::NoopLayout
32 log4j.appender.DBAppndr.warp_message = 0
33 };
34 $logger->warn( $custid, 'big problem!!', $ip_addr );
36
38 This is a very young module and there are a lot of variations in setups
39 with different databases and connection methods, so make sure you test
40 thoroughly! Any feedback is welcome!
41
43 This is a specialized Log::Dispatch object customized to work with
44 log4perl and its abilities, originally based on Log::Dispatch::DBI by
45 Tatsuhiko Miyagawa but with heavy modifications.
46 It is an attempted compromise between what Log::Dispatch::DBI was doing
48 and what log4j's JDBCAppender does. Note the log4j docs say the
49 JDBCAppender "is very likely to be completely replaced in the future."
50 The simplest usage is this:
52 log4j.category = WARN, DBAppndr
54 log4j.appender.DBAppndr = Log::Log4perl::Appender::DBI
55 log4j.appender.DBAppndr.datasource = DBI:CSV:f_dir=t/tmp
56 log4j.appender.DBAppndr.username = bobjones
57 log4j.appender.DBAppndr.password = 12345
58 log4j.appender.DBAppndr.sql = \
59 INSERT INTO logtbl \
60 (loglevel, message) \
61 VALUES ('%c','%m')
62 log4j.appender.DBAppndr.layout = Log::Log4perl::Layout::PatternLayout
64 $logger->fatal('fatal message');
67 $logger->warn('warning message');
68 ===============================
70 |FATAL|fatal message |
71 |WARN |warning message |
72 ===============================
73 But the downsides to that usage are:
75 · You'd better be darn sure there are not quotes in your log message,
77 or your insert could have unforseen consequences! This is a very
78 insecure way to handle database inserts, using place holders and
79 bind values is much better, keep reading. (Note that the log4j docs
80 warn "Be careful of quotes in your messages!") *.
81 · It's not terribly high-performance, a statement is created and
83 executed for each log call.
84 · The only run-time parameter you get is the %m message, in reality
86 you probably want to log specific data in specific table columns.
87 So let's try using placeholders, and tell the logger to create a
89 prepared statement handle at the beginning and just reuse it (just like
90 Log::Dispatch::DBI does)
91 log4j.appender.DBAppndr.sql = \
93 INSERT INTO logtbl \
94 (custid, loglevel, message) \
95 VALUES (?,?,?)
96 #---------------------------------------------------
98 #now the bind values:
99 #1 is the custid
100 log4j.appender.DBAppndr.params.2 = %p
101 #3 is the message
102 #---------------------------------------------------
103 log4j.appender.DBAppndr.layout = Log::Log4perl::Layout::NoopLayout
105 log4j.appender.DBAppndr.warp_message = 0
106 log4j.appender.DBAppndr.usePreparedStmt = 1
108 $logger->warn( 1234, 'warning message' );
111 Now see how we're using the '?' placeholders in our statement? This
113 means we don't have to worry about messages that look like
114 invalid input: 1234';drop table custid;
116 fubaring our database!
118 Normally a list of things in the logging statement gets concatenated
120 into a single string, but setting "warp_message" to 0 and using the
121 NoopLayout means that in
122 $logger->warn( 1234, 'warning message', 'bgates' );
124 the individual list values will still be available for the DBI appender
126 later on. (If "warp_message" is not set to 0, the default behavior is
127 to join the list elements into a single string. If PatternLayout or
128 SimpleLayout are used, their attempt to "render()" your layout will
129 result in something like "ARRAY(0x841d8dc)" in your logs. More
130 information on "warp_message" is in Log::Log4perl::Appender.)
131 In your insert SQL you can mix up '?' placeholders with conversion
133 specifiers (%c, %p, etc) as you see fit--the logger will match the
134 question marks to params you've defined in the config file and populate
135 the rest with values from your list. If there are more '?'
136 placeholders than there are values in your message, it will use undef
137 for the rest. For instance,
138 log4j.appender.DBAppndr.sql = \
140 insert into log4perltest \
141 (loglevel, message, datestr, subpoena_id)\
142 values (?,?,?,?)
143 log4j.appender.DBAppndr.params.1 = %p
144 log4j.appender.DBAppndr.params.3 = %d
145 log4j.appender.DBAppndr.warp_message=0
147 $logger->info('arrest him!', $subpoena_id);
150 results in the first '?' placholder being bound to %p, the second to
152 "arrest him!", the third to the date from "%d", and the fourth to your
153 $subpoenaid. If you forget the $subpoena_id and just log
154 $logger->info('arrest him!');
156 then you just get undef in the fourth column.
158 If the logger statement is also being handled by other non-DBI
160 appenders, they will just join the list into a string, joined with
161 $Log::Log4perl::JOIN_MSG_ARRAY_CHAR (default is an empty string).
162 And see the "usePreparedStmt"? That creates a statement handle when
164 the logger object is created and just reuses it. That, however, may be
165 problematic for long-running processes like webservers, in which case
166 you can use this parameter instead
167 log4j.appender.DBAppndr.bufferSize=2
169 This copies log4j's JDBCAppender's behavior, it saves up that many log
171 statements and writes them all out at once. If your INSERT statement
172 uses only ? placeholders and no %x conversion specifiers it should be
173 quite efficient because the logger can re-use the same statement handle
174 for the inserts.
175 If the program ends while the buffer is only partly full, the DESTROY
177 block should flush the remaining statements, if the DESTROY block runs
178 of course.
179 * As I was writing this, Danko Mannhaupt was coming out with his
181 improved log4j JDBCAppender (http://www.mannhaupt.com/danko/projects/)
182 which overcomes many of the drawbacks of the original JDBCAppender.
183
185 Or another way to say the same thing:
186 The idea is that if you're logging to a database table, you probably
188 want specific parts of your log information in certain columns. To
189 this end, you pass an list to the log statement, like
190 $logger->warn('big problem!!',$userid,$subpoena_nr,$ip_addr);
192 and the array members drop into the positions defined by the
194 placeholders in your SQL statement. You can also define information in
195 the config file like
196 log4j.appender.DBAppndr.params.2 = %p
198 in which case those numbered placeholders will be filled in with the
200 specified values, and the rest of the placeholders will be filled in
201 with the values from your log statement's array.
202
204 usePreparedStmt
205 See above.
206 warp_message
208 see Log::Log4perl::Appender
209 max_col_size
211 If you're used to just throwing debugging messages like huge
212 stacktraces into your logger, some databases (Sybase's DBD!!) may
213 suprise you by choking on data size limitations. Normally, the
214 data would just be truncated to fit in the column, but Sybases's
215 DBD it turns out maxes out at 255 characters. Use this parameter
216 in such a situation to truncate long messages before they get to
217 the INSERT statement.
218
220 If you want to get your dbh from some place in particular, like maybe a
221 pool, subclass and override _init() and/or create_statement(), for
222 instance
223 sub _init {
225 ; #no-op, no pooling at this level
226 }
227 sub create_statement {
228 my ($self, $stmt) = @_;
229 $stmt || croak "Log4perl: sql not set in ".__PACKAGE__;
231 return My::Connections->getConnection->prepare($stmt)
233 || croak "Log4perl: DBI->prepare failed $DBI::errstr\n$stmt";
234 }
235
237 If you're using "log4j.appender.DBAppndr.usePreparedStmt" this module
238 creates an sth when it starts and keeps it for the life of the program.
239 For long-running processes (e.g. mod_perl), connections might go stale,
240 but if "Log::Log4perl::Appender::DBI" tries to write a message and
241 figures out that the DB connection is no longer working (using DBI's
242 ping method), it will reconnect.
243 The reconnection process can be controlled by two parameters,
245 "reconnect_attempts" and "reconnect_sleep". "reconnect_attempts"
246 specifies the number of reconnections attempts the DBI appender
247 performs until it gives up and dies. "reconnect_sleep" is the time
248 between reconnection attempts, measured in seconds.
249 "reconnect_attempts" defaults to 1, "reconnect_sleep" to 0.
250 Alternatively, use "Apache::DBI" or "Apache::DBI::Cache" and read
252 CHANGING DB CONNECTIONS above.
253 Note that "Log::Log4perl::Appender::DBI" holds one connection open for
255 every appender, which might be too many.
256
258 Log::Dispatch::DBI
259 Log::Log4perl::JavaMap::JDBCAppender
261
263 Copyright 2002-2009 by Mike Schilli <m@perlmeister.com> and Kevin Goess
264 <cpan@goess.org>.
265 This library is free software; you can redistribute it and/or modify it
267 under the same terms as Perl itself.
268perl v5.12.2 2010-08-31 Appender::DBI(3)