For example:

package Mylibrary;
use strict;
use DBI;

my $database="somedb:localhost";
my $username="username";
my $password="password";

sub connect
{
    my $dbh = DBI->connect("DBI:mysql:$database", 
                           "$username","$password")
              or error_fatal("blah");
    return $dbh;
}

sub get_current_vote
{
    my ($dbh) = @_;
          # if we're not connected (or weren't passed
          # a connection), connect
    $dbh ||= connect();

    my $sth = $dbh->prepare("SELECT current_vote FROM 
                    vote_count;");    
    $sth->execute() or die $dbh->errstr();

    my $current_vote = ($sth->fetchrow_array())[0];
    return $current_vote;
} 

1;
[download]

# my funky script
use strict;
use DBI;
use Mylibrary;

   # connect to library:
my $dbh = Mylibrary::connect();
   # get current vote:
my $current_vote = Mylibrary::get_current_vote($dbh);
[download]

Update: Call the file with the Mylibrary package in it Mylibrary.pm, and either store it in the same directory with all the scripts that use it or add a use lib line in your scripts like the following:

# my funky script
use strict;
use DBI;
use lib '/path/to/my/library/';
use Mylibrary;
[download]

Hi,

To add my $0.02... if you're going the OO way, you can add a DESTROY {} routine which checks for open DB connections and properly closes them before going out of scope. This is especially useful when using commit/rollback (you should do a rollback in this case of course!).

sub DESTROY {
  $self = shift;
  # assume database handle is stored in blessed object
  if ($self->{dbh})
    $self->{dbh}->rollback();
    $self->{dbh}->disconnect() || croak $self->{dbh}->errstr;
  }
}
[download]

I like this "trick" very much, because it saves me if some unforeseen error occurs and of course it serves the virtue of laziness.

--
Cheers, Joe

Cheers

That makes a lot of sense, I'll go with that...

One question though... the "use Mylibrary" will use a file I assume, called Mylibrary.pm ..?
If so, where does this file need to be located ?
or can I specify it with a path prepended to the package name?

Cheers
lagrenouille

After the initial configuration (i.e. putting your user/pass in a customized VoteDBI.pm package), you can replace all the username/password/connect code above with a single line, such as:

my $db = VoteDBI->Connect('vote_table') or die;
[download]

p.s. As shipped, the module is MySQL specific, but I have a hacked version that works with postgresql. A couple lines changes and you can probably get it working with your favorite database, too.

-Blake

It's a module that serves as a "wrapper" for DBI, which lets me do "use DbiFunc;" instead of "use DBI" in all the DB related scripting I do (using oracle on solaris for a wide range of distinct projects, table spaces, DB accounts).

In addition to solving the problem about keeping the account name/password/environment settings all in one place, it also provides a small number of easy-to-use (and easy-to-remember) methods that make the DB transactions a lot quicker to code, and cover virtually all the kinds of actions I typically need. I hope it gives a useful example of the approach, at least.

lagrenouille

use Config::Properties

Contents of a properties file.
dbdriver = DBI:mysql
dbname = DatabaseName
dbhost = localhost
dbport = 3306
dbuser = root
dbpass = rotorooter

You would not believe how awesome that module is. I have 3 different databases to access (At work). I use the same script to access, and disconnect for each of them. I simply feed the script a fh reference to the proerties file, or the properties file name, and the script does the rest.

If you like, I will post the code here for you.

Kristofer Hoch