At the top of your code put:

use strict;
use warnings;
[download]

Test your code (without actually running it) with:

perl -c test.pl
[download]

That should highlight this and other problems.

If you are having problems understanding the error messages, you can get more detailed output by also adding this to the top of your code:

use diagnostics;
[download]

Your current problem relates to $dbh (in $dbh->prepare) being undefined. What you did define was $sql_dbh. You've also used $dbh elsewhere in your script. Choose which one you want to use and make appropriate changes.

#--------------------------------------------------------------
# my module.
#--------------------------------------------------------------
package ModTest;
require Exporter;

our @ISA     = qw(Exporter);
our @EXPORT  = qw( connect_mysql);
our $VERSION = 1.00;

use Carp qw( confess );

use DBI;

sub connect_mysql {

  my $hostname = "mysql.yourhost.com";   
  my $username = "username";   
  my $password = "password";   
  my $database = "database";
  
 my $dbh = DBI->connect("dbi:mysql:database=$database;host=$hostname",
+$username,$password) or die "Connect to $database failed: $DBI::errst
+r";

return $dbh;

}

1;
[download]

#!/usr/bin/perl -w

use DBI;
use CGI::Carp qw(fatalsToBrowser); 
use ModTest;

print "Content-type:text/html\n\n";

my $sql_dbh = ModTest->connect_mysql();

my $sql = "SELECT * FROM table";
my $st = $sql_dbh->prepare($sql)
    or die "Preparing MySQL query failed: $DBI::errstr";

   $st->execute()
    or die "The execution of the MySQL query failed: $DBI::errstr";

while ($row = $st->fetchrow_hashref())
{
    print " $row->{first} $row->{last}";
}

$sql_dbh ->disconnect();
[download]

Just add "warn" to your connect_mysql function and to the test code right after the call to connect_mysql. Ie.

sub connect_mysql{
....
my $dbh = DBI->connect..
warn("Got dbh '$dbh'\n");
return $dbh;
}
......
my $sql_dbh = ModTest->connect_mysql();
warn("Dbh is '$dbh'\n");

And then see what is really returned to you in both places.

I have a question, in case you add another sub to this module that has nothing todo with connecting to a database, like returning a formatted date per say, it will mean that this module will be connecting to this database forever right, because this line $sql_dbh ->disconnect(); is not part of the module, but part of the test.pl script. Shouldn't be a passing value to this module to indicate that a connection to the database has been required? Otherwise keep the call on the module "disconnected"?