Re: [cgiapp] Lazy DBI
In case it is of interest I have solved the problem like this.
package GSystem::Common::DBH;
use strict;
use warnings;
use Log::Log4perl qw(get_logger);
use DBI;
our $dbh = undef;
sub dbh {
my $log = get_logger('GSystem::Common::DBH');
my $dbusr = user;
my $dbpwd = pwd;
my $dsn = GSystem;
$dbh = DBI-connect(DBI:ODBC:$dsn, $dbusr, $dbpwd, {RaiseError = 0,
PrintError = 0}) || $log-logdie (Could not connect to database)
unless defined $dbh;
return $dbh;
}
1;
So when I want a dbh I use
my $dbh = GSystem::Common::DBH::dbh;
and I (think) I either get a new or existing dbh.
I would be interested to know of any flaws in my logic...
Mark
# CGI::Application community mailing list
####
## To unsubscribe, or change your message delivery options, ##
## visit: http://www.erlbaum.net/mailman/listinfo/cgiapp##
####
## Web archive: http://www.erlbaum.net/pipermail/cgiapp/ ##
## Wiki: http://cgiapp.erlbaum.net/ ##
####
Re: [cgiapp] Lazy DBI
On Feb 19, 2008 5:02 AM, Mark Knoop [EMAIL PROTECTED] wrote: I would be interested to know of any flaws in my logic... This is okay for CGI. It would not be good for any persistent environment like FastCGI, PerlEx, or mod_perl. It those environments it will break if your database connection times out from inactivity or if you restart your database server (it won't reconnect on the next request). This is a pre-sorting-mod_perl-out workaround. Thanks for the tip though. Mark # CGI::Application community mailing list #### ## To unsubscribe, or change your message delivery options, ## ## visit: http://www.erlbaum.net/mailman/listinfo/cgiapp## #### ## Web archive: http://www.erlbaum.net/pipermail/cgiapp/ ## ## Wiki: http://cgiapp.erlbaum.net/ ## ####
Re: [cgiapp] Lazy DBI
I would be interested to know of any flaws in my logic...
I did something like that, but went further. I started out with the
goal not to connect to the DB until necessary, and ended up with a
goal not to even test if I've already connected to the DB, or if the
handle is still alive, etc. I didn't want to have all those if
(!$dbh) { connect } or those DBI pings before every operation.
I reversed the logic so that it uses my module to execute prepared
statement handles within an eval. If it fails, it disconnects,
reconnects, and tries to execute the prepared statement handle again
(up to n times). So, the idea is, I default to just processing as if
I'm always connected to the DB. I let this DB handler catch errors,
retry connections and executions, etc.
I went one step further and let it lazy prepare statement handles
too. If the eval'ed execution fails, my logic determines the statement
isn't prepared. It prepares it, and then does the retry.
So, I just process without getting pedantic about have I connected to
the DB yet? Did I prepare a statement? Can I ping the DB? I
default to expected, and let the module handle the exceptions.
I guess the use of eval for every DBI statement execution can hurt
performance more than all the pedantic testing to know what's already
been done But, I found I always had to use an eval anyway. I found
some rare conditions seemed to be possible that could cause an
execution to die. If I wanted to really be sure I caught every failure
and handled it gracefully, I had to do the eval anyway.
Mark
# CGI::Application community mailing list
####
## To unsubscribe, or change your message delivery options, ##
## visit: http://www.erlbaum.net/mailman/listinfo/cgiapp##
####
## Web archive: http://www.erlbaum.net/pipermail/cgiapp/ ##
## Wiki: http://cgiapp.erlbaum.net/ ##
####
Re: [cgiapp] Lazy DBI
On Feb 19, 2008 5:02 AM, Mark Knoop [EMAIL PROTECTED] wrote: I would be interested to know of any flaws in my logic... This is okay for CGI. It would not be good for any persistent environment like FastCGI, PerlEx, or mod_perl. It those environments it will break if your database connection times out from inactivity or if you restart your database server (it won't reconnect on the next request). If you get rid of the our $dbh and call connect_cached() instead of connect(), it will accomplish the same thing, but also work without changes when you switch to a persistent environment. - Perrin # CGI::Application community mailing list #### ## To unsubscribe, or change your message delivery options, ## ## visit: http://www.erlbaum.net/mailman/listinfo/cgiapp## #### ## Web archive: http://www.erlbaum.net/pipermail/cgiapp/ ## ## Wiki: http://cgiapp.erlbaum.net/ ## ####
Re: [cgiapp] Lazy DBI
Just my 2cents... Apache::DBI. Entirely forget about passing it around.. that's too much work. Create an ini file or something, and put your connection options in there, maybe even your own lib to connect to various db's, but call the connect from various libs whenever needed, and let Apache::DBI handle caching the connections. Apache::DBI is simply a more elegant solution... only problem is that it assumes you're running apache (not an problem for me). On Thu, Feb 14, 2008 at 5:59 AM, Mark Knoop [EMAIL PROTECTED] wrote: Hi Still getting up to speed on CGI::Application while redeveloping my app along with your valuable help. I am looking at the DBI plugin and would like to benefit from the lazy loading idea. The thing is the actual database calls will be made from other objects. So I can put dbh_config in cgiapp_init but then how do I best get the dbh when I need it? I have been considering the various param options with your help via another thread and am still considering my options but however I do that I don't really want to be passing the CGI::Application into every new object just so I can get to the dbh. But (I may be wrong) if I create a dbh ref to pass around then I create a connection and so lose out on the lazy loading. Any advice on the best approach to this? Thanks Mark # CGI::Application community mailing list #### ## To unsubscribe, or change your message delivery options, ## ## visit: http://www.erlbaum.net/mailman/listinfo/cgiapp## #### ## Web archive: http://www.erlbaum.net/pipermail/cgiapp/ ## ## Wiki: http://cgiapp.erlbaum.net/ ## #### # CGI::Application community mailing list #### ## To unsubscribe, or change your message delivery options, ## ## visit: http://www.erlbaum.net/mailman/listinfo/cgiapp## #### ## Web archive: http://www.erlbaum.net/pipermail/cgiapp/ ## ## Wiki: http://cgiapp.erlbaum.net/ ## ####
Re: [cgiapp] Lazy DBI
On Thu, Feb 14, 2008 at 6:27 AM, Joshua Miller [EMAIL PROTECTED] wrote: Just my 2cents... Apache::DBI. Entirely forget about passing it around.. that's too much work. Create an ini file or something, and put your connection options in there, maybe even your own lib to connect to various db's, but call the connect from various libs whenever needed, and let Apache::DBI handle caching the connections. Apache::DBI is simply a more elegant solution... only problem is that it assumes you're running apache (not an problem for me). I was just starting an email that says the same thing. It just works, and no code changes need to be made (assuming you followed some pretty basic guidelines). Sean # CGI::Application community mailing list #### ## To unsubscribe, or change your message delivery options, ## ## visit: http://www.erlbaum.net/mailman/listinfo/cgiapp## #### ## Web archive: http://www.erlbaum.net/pipermail/cgiapp/ ## ## Wiki: http://cgiapp.erlbaum.net/ ## ####
Re: [cgiapp] Lazy DBI
On Thu, Feb 14, 2008 at 6:27 AM, Joshua Miller [EMAIL PROTECTED] wrote: Just my 2cents... Apache::DBI. Entirely forget about passing it around.. that's too much work. Create an ini file or something, and put your connection options in there, maybe even your own lib to connect to various db's, but call the connect from various libs whenever needed, and let Apache::DBI handle caching the connections. Apache::DBI is simply a more elegant solution... only problem is that it assumes you're running apache (not an problem for me). I was just starting an email that says the same thing. It just works, and no code changes need to be made (assuming you followed some pretty basic guidelines). Ah am using IIS as we are on Windoze and I am not as familiar with administering Apache (and we can't afford a sysadmin just yet). I am planning to migrate and go down the mod_perl route but that is down the list after CGI::Appifying my code and implementing some other essential features can only handle so much new stuff at once! Will bear your comments in mind for the future though. # CGI::Application community mailing list #### ## To unsubscribe, or change your message delivery options, ## ## visit: http://www.erlbaum.net/mailman/listinfo/cgiapp## #### ## Web archive: http://www.erlbaum.net/pipermail/cgiapp/ ## ## Wiki: http://cgiapp.erlbaum.net/ ## ####
Re: [cgiapp] Lazy DBI
On 02/14/2008 06:03 AM, Mark Knoop wrote: Ah am using IIS as we are on Windoze and I am not as familiar with administering Apache (and we can't afford a sysadmin just yet). I am planning to migrate and go down the mod_perl route but that is down the list after CGI::Appifying my code and implementing some other essential features can only handle so much new stuff at once! Will bear your comments in mind for the future though. consider something like Rose::DB then. It handles the persistent DBI connections, is Apache/mod_perl-aware, and makes it easy to config your connections. -- Peter Karman . [EMAIL PROTECTED] . http://peknet.com/ # CGI::Application community mailing list #### ## To unsubscribe, or change your message delivery options, ## ## visit: http://www.erlbaum.net/mailman/listinfo/cgiapp## #### ## Web archive: http://www.erlbaum.net/pipermail/cgiapp/ ## ## Wiki: http://cgiapp.erlbaum.net/ ## ####
