Custom module problems

[cormanaz]

Howdy Monks. I have some subroutines I am using a lot so I decided to try to put them into a custom-made module. Here they are:

package sqlsupport;
use strict;
use DBI;

require Exporter;
our @ISA = qw(Exporter);
our @EXPORT_OK = qw();

sub connectdb {
    my $database = shift;
    my $driver   = "mysql";
    my $server   = "localhost";
    my $url      = "DBI:$driver:$database:$server";
    my $user     = "perl\_interpreter";
    my $password = "crawdad";
    my $dbh = DBI->connect( $url, $user, $password ) or die "connectdb
+ can't connect to mysql: $!\n";
    return $dbh;
}

sub dosql {
    my @results = ();
    my ($dbh,$sqlstatement,$response)= @_;
    my $sth = $dbh->prepare($sqlstatement);
    my @row;
    $sth->execute || die "Could not execute MySQL statement: $sqlstate
+ment";
    if ($response) {
    while (@row=$sth->fetchrow_array) { push(@results, [ @row ]); }
    }
    $sth->finish();
    return @results;
}


1;

I am told by this place that I can put my module into a special directory and reference it like so:

use lib qw(/myPerl/myModules/);
use sqlsupport;

When I do that, I get no error on the use statements, but perl says it can find either of the subroutines. I have not gone through the whole make, make build, etc. process because I thought that is only necessary if you are inserting the module into the local lib structure. Maybe not, tho.

Your advice appreciated.

Steve

[davidrw]

you're not exporting anything by default (see the Exporter docs).. do either one of these:

# this will export the functions by default
our @EXPORT = qw(connectdb dosql);

# This will make them available upon request ...
our @EXPORT_OK = qw(connectdb dosql);
# ... and the calling code will need to be like:
use sqlsupport qw/onnectdb dosql/;

[freddo411]

I am told by this place that I can put my module into a special directory and reference it like so:

use lib qw(/myPerl/myModules/); 
use sqlsupport;

In addition to the need for export and the sage advice on capitalization, you'll want to understand that "use <Module>" is looking for either a file "Module" or more commonly "Module.pm". By convention, the package inside of the file "Module.pm" is "Module". The later is the 'best practices' way to do it. Note that you don't specify the .pm extention in the use directive, nor do you specify it in the package name. Since use actually calls require to do some its work, this is explained in the docs for require.

You can read the 'use' docs here.

Cheers

--Freddo411

[Fletch]

Also be aware that all-lowercase package / module names are reserved for use by Perl as pragmas (directives to the compiler or runtime). While it's not currently in use and probably not likely to be used, SqlSupport would be a better name than sqlsupport.

[strat]

Btw: if you write your dosql the following way, you can use sql parameters and get better error handling/messages, e.g.

my $sql = q~SELECT a,b,c,d FROM table WHERE e=? and f=?~;

my $data = dosql($dbh, $sql, 1, $value1, $value2);

sub dosql {
    my( $dbh, $sqlStatement, $response, @params )= @_;

    # some databases already check the sql or even do
    # something when the prepare is executed
    my $sth = $dbh->prepare($sqlstatement)
      or die "Couldn't prepare SQL statement: $DBI::errstr\n\t$sqlStat
+ement";

    $sth->execute(@params)
      or die "Could not execute MySQL statement: $DBI::errstr\n\t$sqls
+tatement";

    my @results = ();
    if( $response ) {
      while( my @row = $sth->fetchrow_array ) {
        # since @row is only valid within the while loop
        # you can use \@row instead of [ @row ]
        push( @results, \@row );
      } # while
    } # if

    $sth->finish();

    return \@results;
} # dosql

[greenFox]

See also the very excellent Simple Module Tutorial