1POE::Component::SimpleDUBsIe(r3)Contributed Perl DocumenPtOaEt:i:oCnomponent::SimpleDBI(3)
2
6 use POE;
7 use POE::Component::SimpleDBI;
8 # Create a new session with the alias we want
10 POE::Component::SimpleDBI->new( 'SimpleDBI' ) or die 'Unable to create the DBI session';
11 # Create our own session to communicate with SimpleDBI
13 POE::Session->create(
14 inline_states => {
15 _start => sub {
16 # Tell SimpleDBI to connect
17 $_[KERNEL]->post( 'SimpleDBI', 'CONNECT',
18 'DSN' => 'DBI:mysql:database=foobaz;host=192.168.1.100;port=3306',
19 'USERNAME' => 'FooBar',
20 'PASSWORD' => 'SecretPassword',
21 'EVENT' => 'conn_handler',
22 );
23 # Execute a query and return number of rows affected
25 $_[KERNEL]->post( 'SimpleDBI', 'DO',
26 'SQL' => 'DELETE FROM FooTable WHERE ID = ?',
27 'PLACEHOLDERS' => [ qw( 38 ) ],
28 'EVENT' => 'deleted_handler',
29 );
30 # Retrieve one row of information
32 $_[KERNEL]->post( 'SimpleDBI', 'SINGLE',
33 'SQL' => 'Select * from FooTable LIMIT 1',
34 'EVENT' => 'success_handler',
35 'BAGGAGE' => 'Some Stuff I want to keep!',
36 );
37 # We want many rows of information + get the query ID so we can delete it later
39 my $id = $_[KERNEL]->call( 'SimpleDBI', 'MULTIPLE',
40 'SQL' => 'SELECT foo, baz FROM FooTable2 WHERE id = ?',
41 'PLACEHOLDERS' => [ qw( 53 ) ],
42 'EVENT' => 'multiple_handler',
43 );
44 # Quote something and send it to another session
46 $_[KERNEL]->post( 'SimpleDBI', 'QUOTE',
47 'SQL' => 'foo$*@%%sdkf"""',
48 'SESSION' => 'OtherSession',
49 'EVENT' => 'quote_handler',
50 );
51 # Changed our mind!
53 $_[KERNEL]->post( 'SimpleDBI', 'Delete_Query', $id );
54 # 3 ways to shutdown
56 # This will let the existing queries finish, then shutdown
58 $_[KERNEL]->post( 'SimpleDBI', 'shutdown' );
59 # This will terminate when the event traverses
61 # POE's queue and arrives at SimpleDBI
62 $_[KERNEL]->post( 'SimpleDBI', 'shutdown', 'NOW' );
63 # Even QUICKER shutdown :)
65 $_[KERNEL]->call( 'SimpleDBI', 'shutdown', 'NOW' );
66 },
67 # Define your request handlers here
69 'quote_handler' => \&FooHandler,
70 # And so on
71 },
72 );
73
75 This module simplifies DBI usage in POE's multitasking world.
76 This module is a breeze to use, you'll have DBI calls in your POE program
78 up and running in only a few seconds of setup.
79 This module does what XML::Simple does for the XML world.
81 If you want more advanced usage, check out:
83 POE::Component::LaDBI
84
86 This module works its magic by creating a new session with POE, then
87 spawning off a child process to do the "heavy" lifting. That way, your
88 main POE process can continue servicing other clients. Queries are put
89 into a queue, and processed one at a time.
90 The standard way to use this module is to do this:
92 use POE;
94 use POE::Component::SimpleDBI;
95 POE::Component::SimpleDBI->new( ... );
97 POE::Session->create( ... );
99 POE::Kernel->run();
101 Starting SimpleDBI
103 To start SimpleDBI, just call it's new method:
105 POE::Component::SimpleDBI->new( 'ALIAS' );
107 This method will die on error or return success.
109 NOTE: The act of starting/stopping SimpleDBI fires off _child events,
111 read the POE documentation on what to do with them :)
112 This constructor accepts only 1 argument: the alias. The default is
114 "SimpleDBI".
115 Commands
117 There are a few commands you can trigger in SimpleDBI. They are trig‐
119 gered via $_[KERNEL]->post( ... );
120 ID
122 All of the commands except for Delete_Query and shutdown return an id.
124 To get them, do this: my $id = $_[KERNEL]->call( 'SimpleDBI', ...
125 );
126 Afterwards, the id can be used to delete queries, look at Delete_Query
128 for more information.
129 Argument errors
131 All of the commands validate their arguments, and if an error happens (
133 missing argument, etc ), they will do either: - return undef and
134 forget that your request even existed - post to the SESSION/EVENT
135 with ERROR present in the data NOTE: The data will not have
136 an ID key present
137 Explanation of DO/SINGLE/MULTIPLE/QUOTE arguments
139 They are passed in via the $_[KERNEL]->post( ... );
141 NOTE: Capitalization is very important!
143 "SQL"
145 This is the actual SQL line you want SimpleDBI to execute. You can
146 put in placeholders, this module supports them.
147 "PLACEHOLDERS"
149 This is an array of placeholders.
150 You can skip this if your query does not utilize it.
152 "SESSION"
154 This is the session that will get the result
155 You can skip this, it defaults to the sending session
157 "EVENT"
159 This is the event, triggered whenever a query finished.
160 It will get a hash in ARG0, consult the specific queries on what
162 you will get.
163 NOTE: If the key 'ERROR' exists in the hash, then it will contain
165 the error string.
166 "BAGGAGE"
168 This is a special argument, you can "attach" any kind of baggage to
169 a query. The baggage will be kept by SimpleDBI and returned to the
170 Event handler intact.
171 This is good for storing data associated with a query like a client
173 object, etc.
174 You can skip this if your query does not utilize it.
176 "CONNECT"
178 This tells SimpleDBI to connect to the database
180 NOTE: if we are already connected, it will be a success ( SimpleDBI will not disconnect then connect automatically )
182 Accepted arguments:
184 DSN -> The DBI DSN string, consult the DBI docs on what this is
185 USERNAME -> The username for the connection
186 PASSWORD -> The password for the connection
187 SESSION -> The session to send the results
188 EVENT -> The event to send the results
189 NOW -> Tells SimpleDBI to bypass the queue and connect NOW!
190 CLEAR -> Tells SimpleDBI to clear the queue and connect NOW!
191 NOTE: if the DSN/USERNAME/PASSWORD/SESSION/EVENT does not exist, SimpleDBI assumes you wanted to use
193 the old connection and will use the cached values ( if you told it to DISCONNECT ).
194 Here's an example on how to trigger this event:
196 $_[KERNEL]->post( 'SimpleDBI', 'CONNECT',
198 'DSN' => 'DBI:mysql:database=foobaz;host=192.168.1.100;port=3306',
199 'USERNAME' => 'MyUser',
200 'PASSWORD' => 'MyPassword',
201 'EVENT' => 'conn_handler',
202 'NOW' => 1,
203 );
204 The NOW/CLEAR arguments are special, they will tell SimpleDBI to bypass the request queue and connect NOW...
206 The CLEAR argument will also delete all the requests waiting in the queue, they will get an ERROR result
207 They both default to false, supply a boolean value to turn them on
208 The Event handler will get a hash in ARG0:
210 {
211 'ERROR' => exists only if an error occured
212 'GONE' => exists only if the server was disconnected and the reconnect failed
213 'ACTION' => 'CONNECT'
214 'ID' => ID of the Query
215 'EVENT' => The event the query will respond to
216 'SESSION' => The session the query will respond to
217 }
218 Receiving this event without the ERROR key means SimpleDBI successfully connected and is waiting for queries
220 NOTE: You can do nifty things like:
222 $_[KERNEL]->post( 'SimpleDBI', 'CONNECT', 'DSN' => 'DBI:mysql:...', ... );
223 $_[KERNEL]->post( 'SimpleDBI', 'DO', ... );
224 $_[KERNEL]->post( 'SimpleDBI', 'SINGLE', ... );
225 $_[KERNEL]->post( 'SimpleDBI', 'DISCONNECT' );
226 $_[KERNEL]->post( 'SimpleDBI', 'CONNECT', 'DSN' => 'DBI:oracle:...', ... );
227 $_[KERNEL]->post( 'SimpleDBI', 'MULTIPLE', ... );
228 $_[KERNEL]->post( 'SimpleDBI', 'shutdown' );
229 They all will be executed in the right order!
231 As of 1.11 SimpleDBI now detects whether the backend lost the connection to the database server. The backend will
233 automatically reconnect if it happens, but if that fails, an error will be sent to the session/event specified here
234 with an extra key: 'GONE'. In this state SimpleDBI is deadlocked, any new queries will not be processed until a
235 CONNECT NOW event is issued! Keep in mind the SINGLE/etc queries WILL NOT receive an error if this happens, the error
236 goes straight to the CONNECT handler to keep it simple!
237 "DISCONNECT"
239 This tells SimpleDBI to disconnect from the database
241 NOTE: In the case that a DISCONNECT is issued when we are not connected, it will still succeed...
243 Accepted arguments:
245 SESSION -> The session to send the results
246 EVENT -> The event to send the results
247 NOW -> Tells SimpleDBI to bypass the queue and disconnect NOW!
248 CLEAR -> Tells SimpleDBI to clear the queue and disconnect NOW!
249 Here's an example on how to trigger this event:
251 $_[KERNEL]->post( 'SimpleDBI', 'DISCONNECT',
253 'EVENT' => 'disconn_handler',
254 'NOW' => 1,
255 );
256 The NOW/CLEAR arguments are special, they will tell SimpleDBI to bypass the request queue and connect NOW...
258 The CLEAR argument will also delete all the requests waiting in the queue, they will get an ERROR result
259 They both default to false, supply a boolean value to turn them on
260 The Event handler will get a hash in ARG0:
262 {
263 'ERROR' => exists only if an error occured
264 'ACTION' => 'DISCONNECT'
265 'ID' => ID of the Query
266 'EVENT' => The event the query will respond to
267 'SESSION' => The session the query will respond to
268 }
269 Receiving this event without the ERROR key means SimpleDBI successfully disconnected
271 BEWARE: There is the possibility of a deadlock:
273 $_[KERNEL]->post( 'SimpleDBI', 'CONNECT', ... );
274 $_[KERNEL]->post( 'SimpleDBI', 'MULTIPLE', ... );
275 $_[KERNEL]->post( 'SimpleDBI', 'DISCONNECT' );
276 $_[KERNEL]->post( 'SimpleDBI', 'DO', ... );
277 $_[KERNEL]->post( 'SimpleDBI', 'SINGLE', ... );
278 $_[KERNEL]->post( 'SimpleDBI', 'CONNECT' );
279 In this case, the DO/SINGLE queries will NEVER run until you issue a CONNECT with NOW enabled
281 "QUOTE"
283 This simply sends off a string to be quoted, and gets it back.
285 Accepted arguments:
287 SESSION -> The session to send the results
288 EVENT -> The event to send the results
289 SQL -> The string to be quoted
290 BAGGAGE -> Any extra data to keep associated with this query ( SimpleDBI will not touch it )
291 Internally, it does this:
293 return $dbh->quote( $SQL );
295 Here's an example on how to trigger this event:
297 $_[KERNEL]->post( 'SimpleDBI', 'QUOTE',
299 SQL => 'foo$*@%%sdkf"""',
300 EVENT => 'quote_handler',
301 );
302 The Event handler will get a hash in ARG0:
304 {
305 'ERROR' => exists only if an error occured
306 'ACTION' => 'QUOTE'
307 'ID' => ID of the Query
308 'EVENT' => The event the query will respond to
309 'SESSION' => The session the query will respond to
310 'SQL' => Original SQL inputted
311 'RESULT' => The quoted SQL
312 'PLACEHOLDERS' => Original placeholders ( may not exist if it was not provided )
313 'BAGGAGE' => whatever you set it to ( may not exist if it was not provided )
314 }
315 "DO"
317 This query is specialized for those queries where you UPDATE/DELETE/INSERT/etc.
319 THIS IS NOT FOR SELECT QUERIES!
321 Accepted arguments:
323 SESSION -> The session to send the results
324 EVENT -> The event to send the results
325 SQL -> The string to be quoted
326 PLACEHOLDERS -> Any placeholders ( if needed )
327 BAGGAGE -> Any extra data to keep associated with this query ( SimpleDBI will not touch it )
328 Internally, it does this:
330 $sth = $dbh->prepare_cached( $SQL );
332 $rows_affected = $sth->execute( $PLACEHOLDERS );
333 return $rows_affected;
334 Here's an example on how to trigger this event:
336 $_[KERNEL]->post( 'SimpleDBI', 'DO',
338 SQL => 'DELETE FROM FooTable WHERE ID = ?',
339 PLACEHOLDERS => [ qw( 38 ) ],
340 EVENT => 'deleted_handler',
341 );
342 The Event handler will get a hash in ARG0:
344 {
345 'ERROR' => exists only if an error occured
346 'ACTION' => 'DO'
347 'ID' => ID of the Query
348 'EVENT' => The event the query will respond to
349 'SESSION' => The session the query will respond to
350 'SQL' => Original SQL inputted
351 'RESULT' => Scalar value of rows affected
352 'PLACEHOLDERS' => Original placeholders ( may not exist if it was not provided )
353 'BAGGAGE' => whatever you set it to ( may not exist if it was not provided )
354 'INSERTID' => The insert ID - using $dbh->last_insert_id( undef, undef, undef, undef ) [ defaults to undef ]
355 }
356 "SINGLE"
358 This query is specialized for those queries where you will get exactly 1 result back.
360 Accepted arguments:
362 SESSION -> The session to send the results
363 EVENT -> The event to send the results
364 SQL -> The string to be quoted
365 PLACEHOLDERS -> Any placeholders ( if needed )
366 BAGGAGE -> Any extra data to keep associated with this query ( SimpleDBI will not touch it )
367 XXX Beware! I incorrectly stated that this returns lowercase rows, this is not true! XXX
369 Internally, it does this:
371 $sth = $dbh->prepare_cached( $SQL );
373 $sth->execute( $PLACEHOLDERS );
374 $result = $sth->fetchrow_hashref;
375 return $result;
376 Here's an example on how to trigger this event:
378 $_[KERNEL]->post( 'SimpleDBI', 'SINGLE',
380 SQL => 'Select * from FooTable',
381 EVENT => 'success_handler',
382 SESSION => 'MySession',
383 );
384 The Event handler will get a hash in ARG0:
386 {
387 'ERROR' => exists only if an error occured
388 'ACTION' => 'SINGLE'
389 'ID' => ID of the Query
390 'EVENT' => The event the query will respond to
391 'SESSION' => The session the query will respond to
392 'SQL' => Original SQL inputted
393 'RESULT' => Hash of columns - similar to fetchrow_hashref ( undef if no rows returned )
394 'PLACEHOLDERS' => Original placeholders ( may not exist if it was not provided )
395 'BAGGAGE' => whatever you set it to ( may not exist if it was not provided )
396 }
397 "MULTIPLE"
399 This query is specialized for those queries where you will get more than 1 result back.
401 XXX Keep in mind: the column names are all lowercased automatically! XXX
403 Accepted arguments:
405 SESSION -> The session to send the results
406 EVENT -> The event to send the results
407 SQL -> The string to be quoted
408 PLACEHOLDERS -> Any placeholders ( if needed )
409 BAGGAGE -> Any extra data to keep associated with this query ( SimpleDBI will not touch it )
410 Internally, it does this:
412 $sth = $dbh->prepare_cached( $SQL );
414 $sth->execute( $PLACEHOLDERS );
415 $sth->bind_columns( \( @$newdata{ @{ $sth->{'NAME_lc'} } } ) );
416 while ( $sth->fetch() ) {
417 push( @results, { @$newdata } );
418 }
419 return \@results;
420 Here's an example on how to trigger this event:
422 $_[KERNEL]->post( 'SimpleDBI', 'MULTIPLE',
424 SQL => 'SELECT foo, baz FROM FooTable2 WHERE id = ?',
425 EVENT => 'multiple_handler',
426 PLACEHOLDERS => [ qw( 53 ) ],
427 );
428 The Event handler will get a hash in ARG0:
430 {
431 'ERROR' => exists only if an error occured
432 'ACTION' => 'MULTIPLE'
433 'ID' => ID of the Query
434 'EVENT' => The event the query will respond to
435 'SESSION' => The session the query will respond to
436 'SQL' => Original SQL inputted
437 'RESULT' => Array of hash of columns - similar to array of fetchrow_hashref's ( undef if no rows returned )
438 'PLACEHOLDERS' => Original placeholders ( may not exist if it was not provided )
439 'BAGGAGE' => whatever you set it to ( may not exist if it was not provided )
440 }
441 "Delete_Query"
443 Call this event if you want to delete a query via the ID.
445 Returns:
447 undef if it wasn't able to find the ID
448 0 if the query is currently being processed
449 1 if the query was successfully deleted
450 Here's an example on how to trigger this event:
452 $_[KERNEL]->post( 'SimpleDBI', 'Delete_Query', $queryID );
454 IF you really want to know the status, execute a call on the event and check the returned value.
456 "Clear_Queue"
458 This event will clear the entire queue except the running query, if there is one.
460 You can also pass in one argument -> the error string to be used instead of the default, 'Cleared the queue'
462 All the queries in the queue will return ERROR to their respective sessions/events
464 "shutdown"
466 $_[KERNEL]->post( 'SimpleDBI', 'shutdown' );
468 This will signal SimpleDBI to start the shutdown procedure.
470 NOTE: This will let all outstanding queries run!
472 SimpleDBI will kill it's session when all the queries have been processed.
473 you can also specify an argument:
475 $_[KERNEL]->post( 'SimpleDBI', 'shutdown', 'NOW' );
477 This will signal SimpleDBI to shutdown.
479 NOTE: This will NOT let the outstanding queries finish!
481 Any queries running will be lost!
482 Due to the way POE's queue works, this shutdown event will take some time to propagate POE's queue.
484 If you REALLY want to shut down immediately, do this:
485 $_[KERNEL]->call( 'SimpleDBI', 'shutdown', 'NOW' );
487 SimpleDBI Notes
489 This module is very picky about capitalization!
491 All of the options are uppercase, to avoid confusion.
493 You can enable debugging mode by doing this:
495 sub POE::Component::SimpleDBI::DEBUG () { 1 }
497 use POE::Component::SimpleDBI;
498 Also, this module will try to keep the SubProcess alive. if it dies,
500 it will open it again for a max of 5 retries.
501 You can override this behavior by doing this:
503 sub POE::Component::SimpleDBI::MAX_RETRIES () { 10 }
505 use POE::Component::SimpleDBI;
506 EXPORT
508 Nothing.
510
512 DBI
513 POE::Component::DBIAgent
515 POE::Component::LaDBI
517 POE::Component::EasyDBI
519
521 Apocalypse <apocal@cpan.org>
522
524 Copyright 2007 by Apocalypse
525 This library is free software; you can redistribute it and/or modify it
527 under the same terms as Perl itself.
528perl v5.8.8 2007-10-03 POE::Component::SimpleDBI(3)