Subroutines #3

#0 | #1 | #2 | #3 | #4

Doing more with subroutines

If you are writing several scripts that need to access a database, you will note that logging in to a database server requires the same steps - the only things that might change are the user name, password, and database name.

This sounds like a good time to use a subroutine. Since I use a database regularly, here is the process I used to develop my subroutine.

First we need to determine what information is needed to access a database. That's easy, although you may need your administrator to do some of this. If you can do this yourself, all the better.

For this example, you will need to have the DBI module installed.

To access a typical database we need the following information:

  1. database name
  2. user name to access the database
  3. password for that user

So, the database has to exist already; a user and password has to be set up to access it. Do this before trying to continue.

If this is in place, then let's get started:

sub DBconnect
{
	# connect to database
	my ($dsn,$dbhost,$dbname,$dbuser,$dbpasswd,); # set up some variables
	$dbhost     = "localhost";
	$dbname     = "KB";
	$dbuser     = "myUsername";  # user name
	$dbpasswd   = "myPassword"; # password
	$dsn        = "DBI:mysql:$dbname:$host_name"; # data source name
	$dbh        = DBI->connect ($dsn, $dbuser, $dbpasswd) 
        or die "Could not connect to server $dsn: $DBI::err ($DBI::errstr)\n";
} # end sub DBconnect

First, we set up any variables that will be used in the subroutine. In this case, we need 5, so that's easy enough. The $dbhost is set to 'localhost' since this is the usual arrangement - the script typically runs on the same server as the database. If not, this will need to be schanged to point to the right URL.

Then the user and password are set up. Next, we put all of them together to set up a Data Source Name.

Note that in this case, we are connecting to a mysql database. If this is not the case, you will have to determine the correct method of connecting to your particular database, using the DBI documentation.

Finally, we connect to the database using the above definitions. We make sure we check the result of this step with the "or die ..." statement.

Now, to see if it actually works!

If all goes well here (no error statements on the screen or in the log file), we can now create some SQL statements to add data or select data from the database.

Here is how a simple SELECT statement looks, using my database:

#!/usr/bin/perl
use strict;
use warnings;
use DBI;
DBconnect();
my ($sth,$dbh,@records);
$sth=$dbh->prepare(qq{select title from resources where bid>0 order by title limit 10});
$sth->execute();
while (@records=$sth->fetchrow_array())
{
    print "$records[0]\n";
}
$sth->finish();
$dbh->disconnect;
################################
sub DBconnect
{
	# connect to database
	my ($dsn,$dbhost,$dbname,$dbuser,$dbpasswd,$host_name);
	$host_name="localhost";
	$dbname="KB";
	$dsn = "DBI:mysql:$dbname:$host_name"; # data source name
	$dbuser = "myUsername";  # user name
	$dbpasswd = "myPassword"; # password
	$dbh = DBI->connect ($dsn, $dbuser, $dbpasswd, { RaiseError => 1,PrintError=>0 })
       or die "Could not connect to server $dsn: $DBI::err ($DBI::errstr)\n";
} # end sub DBconnect

Passing arguments to your subroutines