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

Hi Monks!
I am trying to write a small module to have my sql connection separated from the rest of my applications just in case information like password or username need to be changed.
I am having issues, and I am wondering if someone would have something similar that could help me with what I have.
Here is the module:
#--------------------------------------------------------------
# my module.
#--------------------------------------------------------------
package ModTest;
require Exporter;

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

use Carp qw( confess );
use DBI;

sub connect_mysql {

  my $hostname = "mysql.page.com";   
  my $username = "myusername";   
  my $password = "mypassword";   
  my $database = "mydatabase";

 my $dbh = \DBI->connect("dbi:mysql:database=$database;host=$hostname"
+,$username,$password) 

#bles $dbh;                                                         ||
+ confess "Connect to $db failed: $DBI::errstr";
return $dbh;

}

1;

[download]

Here is my test Perl script to test the connection:

#!/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 my_table";
my $st = $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}";
}

$dbh ->disconnect();

[download]

Thanks for the help!

Replies are listed 'Best First'.
Re: SQL Module Connection Help!
by CountZero (Bishop) on Nov 04, 2010 at 07:06 UTC
    Have you condidered saving your connection info into a configuration file?

    A configuration file is even easier to edit if the connection data changes.

    Suggested modules are (amongst many) YAML, JSON or Config::Any.

    CountZero

    A program should be light and agile, its subroutines connected like a string of pearls. The spirit and intent of the program should be retained throughout. There should be neither too little or too much, neither needless loops nor useless variables, neither lack of structure nor overwhelming rigidity." - The Tao of Programming, 4.1 - Geoffrey James

[reply]
Re: SQL Module Connection Help!
by JavaFan (Canon) on Nov 03, 2010 at 23:50 UTC
    I am having issues,
    And you assume the people here have divine knowledge, and know all about your "issues"? 
    my $dbh = \DBI->connect(...)

    [download]
    Why another reference?
[reply]
[d/l]
Re: SQL Module Connection Help!
by kcott (Archbishop) on Nov 03, 2010 at 23:54 UTC

    Your problem may lie with the backslash in: my $dbh = \DBI->connect(...

    Try getting your connection code working in a standalone script first then move it out to a module.

    The basic idea of putting the connection code in a separate module is fine - it's done at lots of sites.

    -- Ken

[reply]
[d/l]
      Hi this line is reversed our @EXPORT  = qw( mysql_connect);, it should be our @EXPORT  = qw( connect_mysql);. Removed the my $dbh = \DBI->connect(.., now is my $dbh = DBI->connect(...
      Getting this now:
      Software error:

Can't call method "prepare" on an undefined value at test.pl line 12.

      [download]
[reply]
[d/l]
[select]

        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.

        -- Ken

[reply]
[d/l]
[select]
        Here, the module:
        #--------------------------------------------------------------
# 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]

        Test code:
        #!/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]

        Does anyone have any more suggestions?
        Thanks!
[reply]
[d/l]
[select]

Back to Seekers of Perl Wisdom