DBI::mySQL - philosophical question?

jfrm has asked for the wisdom of the Perl Monks concerning the following question:

A long time ago, brothers, I wrote a script and I didn't know what I was doing. It's grown big. I don't know much more now but I do know I should convert it to 'use strict' so I am. And a right PITA it has been (apologies for swearing in a temple). I'm almost there but one question remains. The script uses:

require DBI;
require DBD::mysql;
[download]

And here there and everywhere, there are statements like:

$statement = ....
$sth = $dbh->prepare($statement) 
$rv = $sth->execute 
@info = $sth->fetchrow_array;    
$rc = $sth->finish;
[download]

I've converted these to have 'my' in front of each instance - I figured this was better practice than a single my ($statement,$sth,$rv,$rc) at the top of each perl file to cover all the subfunctions (I'm sure you'll tell me if I'm wrong). Anyway, previously, I connected to the database at the top:

our $database;
$dbh = DBI->connect("DBI:mysql:$database");
[download]

and then disconnected at the very end.

$dbh->disconnect;
[download]

And multiple database calls happened using this single connection.

Now that I'm converting to 'use strict', each subfunction is naturally enough forcing me to declare the $dbh within it. So I presume my options are:
1. declare $dbh as a global variable, use 'our' within the subfunctions and carry on with a single connection.
2. connect and disconnect to the database for each separate database query.

So my 'elegance' genie is telling me that I shouldn't use 'our' unless necessary so should go for 2. But my 'performance' genie is telling me that it's going to make everything take longer if I connect and disconnect over and over so I should opt for 1. Please oh fellow bowl carriers, your collective wisdom would be appreciated especially if you know of a magical option 3 which is the correct path for the righteous...

Comment on DBI::mySQL - philosophical question? Select or Download Code

Replies are listed 'Best First'.
Re: DBI::mySQL - philosophical question? by shotgunefx (Parson) on Jan 29, 2004 at 01:02 UTC
You could pass the $dbh into the functions as a parameter. Declare my $dbh at the beginning of the script if you don't need to share it with other packages/files Make it a package var. There is nothing wrong with this when it makes sense. Connections are slow, it would be ridiculous to keep establishing db connections for the sake of dogma. -Lee "To be civilized is to deny one's nature."	[reply]
Re: DBI::mySQL - philosophical question? by dws (Chancellor) on Jan 29, 2004 at 01:34 UTC
Two items to add to the advice above: 1. The traditional (pre `our`) way to declare a package global is `use vars qw($dbh);` [download] 2. You don't need to `require DBD::mysql`, as that gets done automagically via `connect()`	[reply] [d/l] [select]
Re: DBI::mySQL - philosophical question? by chromatic (Archbishop) on Jan 29, 2004 at 01:00 UTC
Pass a database handle as a parameter to those functions.	[reply]
Re: DBI::mySQL - philosophical question? by Joost (Canon) on Jan 29, 2004 at 01:08 UTC
Now that I'm converting to 'use strict', each subfunction is naturally enough forcing me to declare the $dbh within it. So I presume my options are: declare $dbh as a global variable, use 'our' within the subfunctions and carry on with a single connection. connect and disconnect to the database for each separate database query. My $0.02: I'm not sure why you would need `our` for every function, unless they are all in a different file. Never disconnect and then connect again if you can help it! Especially if you want to use different (non-mysql) databases on remote machines this can take a long time. I'd say that the most "clean" way would be to pass the database handle ($dbh) as a parameter to the function. This will at least make it possible to move the functions (and the calling code) to other packages and/or files without hassle (at the cost of having slightly more characters to type for each call). HTH, Joost. "What should it profit a man, if he should win a flame war, yet lose his cool?"	[reply] [d/l]