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