1DBIx::Connector(3)    User Contributed Perl Documentation   DBIx::Connector(3)
2
3
4

Name

6       DBIx::Connector - Fast, safe DBI connection and transaction management
7

Synopsis

9         use DBIx::Connector;
10
11         # Create a connection.
12         my $conn = DBIx::Connector->new($dsn, $username, $password, {
13             RaiseError => 1,
14             AutoCommit => 1,
15         });
16
17         # Get the database handle and do something with it.
18         my $dbh  = $conn->dbh;
19         $dbh->do('INSERT INTO foo (name) VALUES (?)', undef, 'Fred' );
20
21         # Do something with the handle more efficiently.
22         $conn->run(fixup => sub {
23             $_->do('INSERT INTO foo (name) VALUES (?)', undef, 'Fred' );
24         });
25

Description

27       DBIx::Connector provides a simple interface for fast and safe DBI
28       connection and transaction management. Connecting to a database can be
29       expensive; you don't want your application to re-connect every time you
30       need to run a query.  The efficient thing to do is to hang on to a
31       database handle to maintain a connection to the database in order to
32       minimize that overhead. DBIx::Connector lets you do that without having
33       to worry about dropped or corrupted connections.
34
35       You might be familiar with Apache::DBI and with the DBI's
36       "connect_cached()" constructor. DBIx::Connector serves a similar need,
37       but does a much better job. How is it different? I'm glad you asked!
38
39       •   Fork Safety
40
41           Like Apache::DBI, but unlike "connect_cached()", DBIx::Connector
42           create a new database connection if a new process has been
43           "fork"ed. This happens all the time under mod_perl, in POE
44           applications, and elsewhere. Works best with DBI 1.614 and higher.
45
46       •   Thread Safety
47
48           Unlike Apache::DBI or "connect_cached()", DBIx::Connector will
49           create a new database connection if a new thread has been spawned.
50           As with "fork"ing, spawning a new thread can break database
51           connections.
52
53       •   Works Anywhere
54
55           Unlike Apache::DBI, DBIx::Connector runs anywhere -- inside of
56           mod_perl or not. Why limit yourself?
57
58       •   Explicit Interface
59
60           DBIx::Connector has an explicit interface. There is none of the
61           magical action-at-a-distance crap that Apache::DBI is guilty of,
62           and no global caching. I've personally diagnosed a few issues with
63           Apache::DBI's magic, and killed it off in two different projects in
64           favor of "connect_cached()", only to be tripped up by other
65           gotchas. No more.
66
67       •   Optimistic Execution
68
69           If you use "run()" and "txn()", the database handle will be passed
70           without first pinging the server. For the 99% or more of the time
71           when the database is just there, you'll save a ton of overhead
72           without the ping.
73
74       DBIx::Connector's other feature is transaction management. Borrowing an
75       interface from DBIx::Class, DBIx::Connector offers an API that
76       efficiently handles the scoping of database transactions so that you
77       needn't worry about managing the transaction yourself. Even better, it
78       offers an API for savepoints if your database supports them. Within a
79       transaction, you can scope savepoints to behave like subtransactions,
80       so that you can save some of your work in a transaction even if part of
81       it fails. See "txn()" and "svp()" for the goods.
82

Usage

84       Unlike Apache::DBI and "connect_cached()", DBIx::Connector doesn't
85       cache database handles. Rather, for a given connection, it makes sure
86       that the connection is just there whenever you want it, to the extent
87       possible. The upshot is that it's safe to create a connection and then
88       keep it around for as long as you need it, like so:
89
90         my $conn = DBIx::Connector->new(@args);
91
92       You can store the connection somewhere in your app where you can easily
93       access it, and for as long as it remains in scope, it will try its
94       hardest to maintain a database connection. Even across "fork"s
95       (especially with DBI 1.614 and higher) and new threads, and even calls
96       to "$conn->dbh->disconnect". When you don't need it anymore, let it go
97       out of scope and the database connection will be closed.
98
99       The upshot is that your code is responsible for hanging onto a
100       connection for as long as it needs it. There is no magical connection
101       caching like in Apache::DBI and "connect_cached()".
102
103   Execution Methods
104       The real utility of DBIx::Connector comes from the use of the execution
105       methods, "run()", "txn()", or "svp()".  Instead of this:
106
107         $conn->dbh->do($query);
108
109       Try this:
110
111         $conn->run(sub { $_->do($query) }); # returns retval from the sub {...}
112
113       The difference is that the "run()" optimistically assumes that an
114       existing database handle is connected and executes the code reference
115       without pinging the database. The vast majority of the time, the
116       connection will of course still be open. You therefore save the
117       overhead of a ping query every time you use "run()" (or "txn()").
118
119       Of course, if a block passed to "run()" dies because the DBI isn't
120       actually connected to the database you'd need to catch that failure and
121       try again.  DBIx::Connector provides a way to overcome this issue:
122       connection modes.
123
124       Connection Modes
125
126       When calling "run()", "txn()", or "svp()", each executes within the
127       context of a "connection mode." The supported modes are:
128
129       •   "ping"
130
131       •   "fixup"
132
133       •   "no_ping"
134
135       Use them via an optional first argument, like so:
136
137         $conn->run(ping => sub { $_->do($query) });
138
139       Or set up a default mode via the "mode()" accessor:
140
141         $conn->mode('fixup');
142         $conn->run(sub { $_->do($query) });
143
144       The return value of the block will be returned from the method call in
145       scalar or array context as appropriate, and the block can use
146       "wantarray" to determine the context. Returning the value makes them
147       handy for things like constructing a statement handle:
148
149         my $sth = $conn->run(fixup => sub {
150             my $sth = $_->prepare('SELECT isbn, title, rating FROM books');
151             $sth->execute;
152             $sth;
153         });
154
155       In "ping" mode, "run()" will ping the database before running the
156       block.  This is similar to what Apache::DBI and the DBI's
157       "connect_cached()" method do to check the database connection, and is
158       the safest way to do so. If the ping fails, DBIx::Connector will
159       attempt to reconnect to the database before executing the block.
160       However, "ping" mode does impose the overhead of the "ping" every time
161       you use it.
162
163       In "fixup" mode, DBIx::Connector executes the block without pinging the
164       database. But in the event the block throws an exception, if
165       DBIx::Connector finds that the database handle is no longer connected,
166       it will reconnect to the database and re-execute the block. Therefore,
167       the code reference should have no side-effects outside of the database,
168       as double-execution in the event of a stale database connection could
169       break something:
170
171         my $count;
172         $conn->run(fixup => sub { $count++ });
173         say $count; # may be 1 or 2
174
175       "fixup" is the most efficient connection mode. If you're confident that
176       the block will have no deleterious side-effects if run twice, this is
177       the best option to choose. If you decide that your block is likely to
178       have too many side-effects to execute more than once, you can simply
179       switch to "ping" mode.
180
181       The default is "no_ping", but you likely won't ever use it directly,
182       and isn't recommended in any event.
183
184       Simple, huh? Better still, go for the transaction management in "txn()"
185       and the savepoint management in "svp()". You won't be sorry, I promise.
186
187       Rollback Exceptions
188
189       In the event of a rollback in "txn()" or "svp()", if the rollback
190       itself fails, a DBIx::Connector::TxnRollbackError or
191       DBIx::Connector::SvpRollbackError exception will be thrown, as
192       appropriate.  These classes, which inherit from
193       DBIx::Connector::RollbackError, stringify to display both the rollback
194       error and the transaction or savepoint error that led to the rollback,
195       something like this:
196
197           Transaction aborted: No such table "foo" at foo.pl line 206.
198           Transaction rollback failed: Invalid transaction ID at foo.pl line 203.
199
200       For finer-grained exception handling, you can access the individual
201       errors via accessors:
202
203       "error"
204           The transaction or savepoint error.
205
206       "rollback_error"
207           The rollback error.
208
209       For example:
210
211         use Try::Tiny;
212         try {
213             $conn->txn(sub {
214                 # ...
215             });
216         } catch {
217             if (eval { $_->isa('DBIx::Connector::RollbackError') }) {
218                 say STDERR 'Transaction aborted: ', $_->error;
219                 say STDERR 'Rollback failed too: ', $_->rollback_error;
220             } else {
221                 warn "Caught exception: $_";
222             }
223         };
224
225       If a "svp()" rollback fails and its surrounding "txn()" rollback also
226       fails, the thrown DBIx::Connetor::TxnRollbackError exception object
227       will have the savepoint rollback exception, which will be an
228       DBIx::Connetor::SvpRollbackError exception object in its "error"
229       attribute:
230
231         use Try::Tiny;
232         $conn->txn(sub {
233             try {
234                 $conn->svp(sub { # ... });
235             } catch {
236                 if (eval { $_->isa('DBIx::Connector::RollbackError') }) {
237                     if (eval { $_->error->isa('DBIx::Connector::SvpRollbackError') }) {
238                         say STDERR 'Savepoint aborted: ', $_->error->error;
239                         say STDERR 'Its rollback failed too: ', $_->error->rollback_error;
240                     } else {
241                         say STDERR 'Transaction aborted: ', $_->error;
242                     }
243                     say STDERR 'Transaction rollback failed too: ', $_->rollback_error;
244                 } else {
245                     warn "Caught exception: $_";
246                 }
247             };
248         });
249
250       But most of the time, you should be fine with the stringified form of
251       the exception, which will look something like this:
252
253           Transaction aborted: Savepoint aborted: No such table "bar" at foo.pl line 190.
254           Savepoint rollback failed: Invalid savepoint name at foo.pl line 161.
255           Transaction rollback failed: Invalid transaction identifier at fool.pl line 184.
256
257       This allows you to see you original SQL error, as well as the errors
258       for the savepoint rollback and transaction rollback failures.
259

Interface

261       And now for the nitty-gritty.
262
263   Constructor
264       "new"
265
266         my $conn = DBIx::Connector->new($dsn, $username, $password, {
267             RaiseError => 1,
268             AutoCommit => 1,
269         });
270
271       Constructs and returns a DBIx::Connector object. The supported
272       arguments are exactly the same as those supported by the DBI. Default
273       values for those parameters vary from the DBI as follows:
274
275       "RaiseError"
276           Defaults to true if unspecified, and if "HandleError" is
277           unspecified. Use of the "RaiseError" attribute, or a "HandleError"
278           attribute that always throws exceptions (such as that provided by
279           Exception::Class::DBI), is required for the exception-handling
280           functionality of "run()", "txn()", and "svp()" to work properly.
281           Their explicit use is therefor recommended if for proper error
282           handling with these execution methods.
283
284       "AutoInactiveDestroy"
285           Added in DBI 1.613. Defaults to true if unspecified. This is
286           important for safe disconnects across forking processes.
287
288       In addition, explicitly setting "AutoCommit" to true is strongly
289       recommended if you plan to use "txn()" or "svp()", as otherwise you
290       won't get the transactional scoping behavior of those two methods.
291
292       If you would like to execute custom logic each time a new connection to
293       the database is made you can pass a sub as the "connected" key to the
294       "Callbacks" parameter. See "Callbacks" in DBI for usage and other
295       available callbacks.
296
297       Other attributes may be modified by individual drivers. See the
298       documentation for the drivers for details:
299
300       DBIx::Connector::Driver::MSSQL
301       DBIx::Connector::Driver::Oracle
302       DBIx::Connector::Driver::Pg
303       DBIx::Connector::Driver::SQLite
304       DBIx::Connector::Driver::mysql
305       DBIx::Connector::Driver::Firebird
306
307   Class Method
308       "connect"
309
310         my $dbh = DBIx::Connector->connect($dsn, $username, $password, \%attr);
311
312       Syntactic sugar for:
313
314         my $dbh = DBIx::Connector->new(@args)->dbh;
315
316       Though there's probably not much point in that, as you'll generally
317       want to hold on to the DBIx::Connector object. Otherwise you'd just use
318       the DBI, no?
319
320   Instance Methods
321       "dbh"
322
323         my $dbh = $conn->dbh;
324
325       Returns the connection's database handle. It will use a an existing
326       handle if there is one, if the process has not been "fork"ed or a new
327       thread spawned, and if the database is pingable. Otherwise, it will
328       instantiate, cache, and return a new handle.
329
330       When called from blocks passed to "run()", "txn()", and "svp()",
331       "dbh()" assumes that the pingability of the database is handled by
332       those methods and skips the "ping()". Otherwise, it performs all the
333       same validity checks. The upshot is that it's safe to call "dbh()"
334       inside those blocks without the overhead of multiple "ping"s. Indeed,
335       it's preferable to do so if you're doing lots of non-database
336       processing in those blocks.
337
338       "run"
339
340         $conn->run(ping => sub { $_->do($query) });
341
342       Simply executes the block, locally setting $_ to and passing in the
343       database handle. Returns the value returned by the block in scalar or
344       array context as appropriate (and the block can use "wantarray" to
345       decide what to do).
346
347       An optional first argument sets the connection mode, overriding that
348       set in the "mode()" accessor, and may be one of "ping", "fixup", or
349       "no_ping" (the default). See "Connection Modes" for further
350       explication.
351
352       For convenience, you can nest calls to "run()" (or "txn()" or "svp()"),
353       although the connection mode will be invoked to check the connection
354       (or not) only in the outer-most block method call.
355
356         $conn->txn(fixup => sub {
357             my $dbh = shift;
358             $dbh->do($_) for @queries;
359             $conn->run(sub {
360                 $_->do($expensive_query);
361                 $conn->txn(sub {
362                     $_->do($another_expensive_query);
363                 });
364             });
365         });
366
367       All code executed inside the top-level call to "txn()" will be executed
368       in a single transaction. If you'd like subtransactions, nest "svp()"
369       calls.
370
371       It's preferable to use "dbh()" to fetch the database handle from within
372       the block if your code is doing lots of non-database stuff (shame on
373       you!):
374
375         $conn->run(ping => sub {
376             parse_gigabytes_of_xml(); # Get this out of the transaction!
377             $conn->dbh->do($query);
378         });
379
380       This is because "dbh()" will better ensure that the database handle is
381       active and "fork"- and thread-safe, although it will never "ping()" the
382       database when called from inside a "run()", "txn()" or "svp()" block.
383
384       "txn"
385
386         my $sth = $conn->txn(fixup => sub { $_->do($query) });
387
388       Starts a transaction, executes the block, locally setting $_ to and
389       passing in the database handle, and commits the transaction. If the
390       block throws an exception, the transaction will be rolled back and the
391       exception re-thrown.  Returns the value returned by the block in scalar
392       or array context as appropriate (and the block can use "wantarray" to
393       decide what to do).
394
395       An optional first argument sets the connection mode, overriding that
396       set in the "mode()" accessor, and may be one of "ping", "fixup", or
397       "no_ping" (the default). In the case of "fixup" mode, this means that
398       the transaction block will be re-executed for a new connection if the
399       database handle is no longer connected. In such a case, a second
400       exception from the code block will cause the transaction to be rolled
401       back and the exception re-thrown. See "Connection Modes" for further
402       explication.
403
404       As with "run()", calls to "txn()" can be nested, although the
405       connection mode will be invoked to check the connection (or not) only
406       in the outer-most block method call. It's preferable to use "dbh()" to
407       fetch the database handle from within the block if your code is doing
408       lots of non-database processing.
409
410       "svp"
411
412       Executes a code block within the scope of a database savepoint if your
413       database supports them. Returns the value returned by the block in
414       scalar or array context as appropriate (and the block can use
415       "wantarray" to decide what to do).
416
417       You can think of savepoints as a kind of subtransaction. What this
418       means is that you can nest your savepoints and recover from failures
419       deeper in the nest without throwing out all changes higher up in the
420       nest. For example:
421
422         $conn->txn(fixup => sub {
423             my $dbh = shift;
424             $dbh->do('INSERT INTO table1 VALUES (1)');
425             eval {
426                 $conn->svp(sub {
427                     shift->do('INSERT INTO table1 VALUES (2)');
428                     die 'OMGWTF?';
429                 });
430             };
431             warn "Savepoint failed\n" if $@;
432             $dbh->do('INSERT INTO table1 VALUES (3)');
433         });
434
435       This transaction will insert the values 1 and 3, but not 2.
436
437         $conn->svp(fixup => sub {
438             my $dbh = shift;
439             $dbh->do('INSERT INTO table1 VALUES (4)');
440             $conn->svp(sub {
441                 shift->do('INSERT INTO table1 VALUES (5)');
442             });
443         });
444
445       This transaction will insert both 4 and 5.
446
447       Superficially, "svp()" resembles "run()" and "txn()", including its
448       support for the optional connection mode argument, but in fact
449       savepoints can only be used within the scope of a transaction. Thus
450       "svp()" will start a transaction for you if it's called without a
451       transaction in-progress. It simply redispatches to "txn()" with the
452       appropriate connection mode. Thus, this call from outside of a
453       transaction:
454
455         $conn->svp(ping => sub {
456             $conn->svp( sub { ... } );
457         });
458
459       Is equivalent to:
460
461         $conn->txn(ping => sub {
462             $conn->svp( sub { ... } );
463         })
464
465       Savepoints are supported by the following RDBMSs:
466
467       •   PostgreSQL 8.0
468
469       •   SQLite 3.6.8
470
471       •   MySQL 5.0.3 (InnoDB)
472
473       •   Oracle
474
475       •   Microsoft SQL Server
476
477       •   Firebird 1.5
478
479       For all other RDBMSs, "svp()" works just like "txn()": savepoints will
480       be ignored and the outer-most transaction will be the only transaction.
481       This tends to degrade well for non-savepoint-supporting databases,
482       doing the right thing in most cases.
483
484       "mode"
485
486         my $mode = $conn->mode;
487         $conn->mode('fixup');
488         $conn->txn(sub { ... }); # uses fixup mode.
489         $conn->mode($mode);
490
491       Gets and sets the connection mode attribute, which is used by "run()",
492       "txn()", and "svp()" if no mode is passed to them.  Defaults to
493       "no_ping". Note that inside a block passed to "run()", "txn()", or
494       "svp()", the mode attribute will be set to the optional first
495       parameter:
496
497         $conn->mode('ping');
498         $conn->txn(fixup => sub {
499             say $conn->mode; # Outputs "fixup"
500         });
501         say $conn->mode; # Outputs "ping"
502
503       In this way, you can reliably tell in what mode the code block is
504       executing.
505
506       "connected"
507
508         if ( $conn->connected ) {
509             $conn->dbh->do($query);
510         }
511
512       Returns true if currently connected to the database and false if it's
513       not. You probably won't need to bother with this method;
514       DBIx::Connector uses it internally to determine whether or not to
515       create a new connection to the database before returning a handle from
516       "dbh()".
517
518       "in_txn"
519
520         if ( $conn->in_txn ) {
521            say 'Transacting!';
522         }
523
524       Returns true if the connection is in a transaction. For example, inside
525       a "txn()" block it would return true. It will also work if you use the
526       DBI API to manage transactions (i.e., "begin_work()" or "AutoCommit".
527
528       Essentially, this is just sugar for:
529
530         $con->run( no_ping => sub { !$_->{AutoCommit} } );
531
532       But without the overhead of the code reference or connection checking.
533
534       "disconnect_on_destroy"
535
536         $conn->disconnect_on_destroy(0);
537
538       By default, DBIx::Connector calls "$dbh->disconnect" when it goes out
539       of scope and is garbage-collected by the system (that is, in its
540       "DESTROY()" method). Usually this is what you want, but in some cases
541       it might not be. For example, you might have a module that uses
542       DBIx::Connector internally, but then makes the database handle
543       available to callers, even after the DBIx::Connector object goes out of
544       scope. In such a case, you don't want the database handle to be
545       disconnected when the DBIx::Connector goes out of scope.  So pass a
546       false value to "disconnect_on_destroy" to prevent the disconnect.  An
547       example:
548
549         sub database_handle {
550              my $conn = DBIx::Connector->new(@_);
551              $conn->run(sub {
552                  # Do stuff here.
553              });
554              $conn->disconnect_on_destroy(0);
555              return $conn->dbh;
556         }
557
558       Of course, if you don't need to do any work with the database handle
559       before returning it to your caller, you can just use "connect()":
560
561         sub database_handle {
562             DBIx::Connector->connect(@_);
563         }
564
565       "disconnect"
566
567         $conn->disconnect;
568
569       Disconnects from the database. Unless "disconnect_on_destroy()" has
570       been passed a false value, DBIx::Connector uses this method internally
571       in its "DESTROY" method to make sure that things are kept tidy.
572
573       "driver"
574
575         $conn->driver->begin_work( $conn->dbh );
576
577       In order to support all database features in a database-neutral way,
578       DBIx::Connector provides a number of different database drivers,
579       subclasses of DBIx::Connector::Driver, that offer methods to handle
580       database communications. Although the DBI provides a standard
581       interface, for better or for worse, not all of the drivers implement
582       them, and some have bugs. To avoid those issues, all database
583       communications are handled by these driver objects.
584
585       This can be useful if you want more fine-grained control of your
586       transactionality. For example, to create your own savepoint within a
587       transaction, you might do something like this:
588
589         use Try::Tiny;
590         my $driver = $conn->driver;
591         $conn->txn(sub {
592             my $dbh = shift;
593             try {
594                 $driver->savepoint($dbh, 'mysavepoint');
595                 # do stuff ...
596                 $driver->release('mysavepoint');
597             } catch {
598                 $driver->rollback_to($dbh, 'mysavepoint');
599             };
600         });
601
602       Most often you should be able to get what you need out of "txn()" and
603       "svp()", but sometimes you just need the finer control. In those cases,
604       take advantage of the driver object to keep your use of the API
605       universal across database back-ends.
606
607       "driver_name"
608
609         my $driver_name = $conn->driver_name;
610
611       Returns the name of the DBI driver (to be) used to connect to the
612       database.
613
614       "dsn"
615
616         my $dsn = $conn->dsn;
617
618       Returns the DBI Data Source Name originally passed to "new()" as the
619       first argument.
620

See Also

622       •   DBIx::Connector::Driver
623
624       •   DBI
625
626       •   DBIx::Class
627
628       •   Catalyst::Model::DBI
629

Authors

631       This module was written by:
632
633       •   David E. Wheeler <david@kineticode.com>
634
635       It is based on documentation, ideas, kibbitzing, and code from:
636
637       •   Tim Bunce <http://tim.bunce.name>
638
639       •   Brandon L. Black <blblack@gmail.com>
640
641       •   Matt S. Trout <mst@shadowcat.co.uk>
642
643       •   Peter Rabbitson <ribasushi@cpan.org>
644
645       •   Ash Berlin <ash@cpan.org>
646
647       •   Rob Kinyon <rkinyon@cpan.org>
648
649       •   Cory G Watson <gphat@cpan.org>
650
651       •   Anders Nor Berle <berle@cpan.org>
652
653       •   John Siracusa <siracusa@gmail.com>
654
655       •   Alex Pavlovic <alex.pavlovic@taskforce-1.com>
656
657       •   Many other DBIx::Class contributors
658
660       Copyright (c) 2009-2013 David E. Wheeler. Some Rights Reserved.
661
662       This module is free software; you can redistribute it and/or modify it
663       under the same terms as Perl itself.
664
665
666
667perl v5.34.0                      2021-09-27                DBIx::Connector(3)
Impressum