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

I am trying to open a database connection in a subroutine, and pass the handle ($dbh) as the result of a successful open.
my $dbh;
sub connectdb {# connect to database using 3 parameters; return databa
+se handle
    my ($dbstring, $dbdepot, $password) = @_;
    print "attempt to connect to database $dbstring :$dbdepot\n";
    system "time /T";
    my $dbhi = DBI->connect($dbstring,$dbdepot,$password,
        { AutoCommit => 0,
          RaiseError => 1,
          PrintError => 1 })
        or warn "can't connect to database $dbstring :$dbdepot :", $DB
+I::errstr, "\n";
    if (defined ($dbhi)) {
        print "success: $dbhi \n";
        return $dbhi;
    } else {
        print "Failure\n";
        return -1;
    }
}

while (($dbh = connectdb($dwqa1,$depot,$pswd) eq -1) && ($sleepcount <
+ $sleeplimit)) {
    sleep ($sleepdur);
    ++$sleepcount;
}
my $sth = $dbh->prepare("select '$depot', t.table_name, column_name fr
+om user_tables t, user_tab_columns c where c.table_name = t.table_nam
+e");
$sth->execute();

[download]


Here is the output:
attempt to connect to database dbi:Oracle:xxxxx :IC_xxxxx 8:53a success: DBI::db=HASH(0x1c48904) Issuing rollback() for database handle being DESTROY'd without explicit disconne ct() at C:\Perl\dbitest.pl line 45.
Can't call method "prepare" without a package or object reference at C:\Perl\dbi test.pl line 51.

Is it that the object only exists inside the sub where it was created? If so, how do I make it live outside the sub?

Replies are listed 'Best First'.
Re: DBI: pass $dbh from a sub
by nobull (Friar) on Jul 29, 2004 at 16:52 UTC
    You have a misplaced parenthasis:

    ($dbh = connectdb($dwqa1,$depot,$pswd) eq -1)

    Should read

    ($dbh = connectdb($dwqa1,$depot,$pswd)) eq -1.

    Note that the use of '-1' as the failure code from a subroutine that should normally return an object is somewhat affected. Usually a subroutine that fails should simply return(). (This will return undef in a scalar context and an empty list in a list context).

    If you did this you could just say:

    !( $dbh = connectdb($dwqa1,$depot,$pswd) )

[reply]
[d/l]
[select]
Re: DBI: pass $dbh from a sub
by dsb (Chaplain) on Jul 29, 2004 at 16:13 UTC
    Well the first line of your output indicating a successful connection is from your own instructions.

    The rollback is probably because you have AUTOCOMMIT disabled and since you neither commit nor explicitly destroy the DBI object, the database cancels the changes.

    That last error does seem like it's because of the scope of $dbh so try and declare the variable, or establish the connection outside of the loop.

    dsb
    This @ISA my cool %SIG
[reply]
Re: DBI: pass $dbh from a sub
by periapt (Hermit) on Jul 29, 2004 at 16:38 UTC
    I believe it is a scoping problem. That is, the connection is destroyed when connectdb goes out of scope even though you pass the value of $dbhi back to the calling routine.

    I've never tried this but you might try returning a reference to $dbhi (return \$dbhi). That might preserve the connection following the end of sub.

    Update: OK, this was a bad suggestion. It doesn't work. The suggestion below, however does work.

    What I usually do, though, is pass connectdb a reference to my db handle and assign the connection to that reference. Just replace $dbhi with $$dbhi in your subroutine.

    Update: Here is some code for the above suggestion.
    #calling function
# $cfg is a scalar that will hold a reference to a hash
# OpenODBC is a generic routine to establish an ODBC connection 
# via Win32::ODBC

$rtncd = InitParams(\$cfg,\$db01);
#~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~# database connections
                               #   connection 1
                               # open database connections
    ($rtncd, $$db01) = OpenODBC($$cfg->{obtsdsn},$$cfg->{dbf01}); 
    if($rtncd){
        $$cfg->{logstr}  =
            "$$cfg->{county};$$cfg->{procdte};$$cfg->{fname};".
            "$$cfg->{module};1;error;[]ODBC error, ($rtncd) ";
        $$cfg->{logstr} .= ($rtncd == 2)
            ? "Could not connect with database $$cfg->{dbf01};"
            : "Could not establish connection via $$cfg->{obtsdsn};" ;
        $$cfg->{logstr} .= "ODBC;InitParams(); ; ; "; 
        UpdtMetaLog($$cfg->{logdir},$$cfg->{logfile},$$cfg->{logstr});
    }

sub OpenODBC{
# Usage  &OpenODBC([DSNNAME],[DatabaseName])
# opens a database connection using the basic ODBC driver
# returns 0 if connection established, 2 if connection fails, 
#         3 if database not available
    require Win32::ODBC;
    my $dsn   = $_[0];
    my $dbf   = 'USE '.$_[1];
    my $uid   = '***********';
    my $pwd   = '***********';
    my $db    = '';
    my $rtncd = 0;
                                   # Establish ODBC Connection
    $rtncd = 2 unless($db = new Win32::ODBC("DSN=$dsn;UID=$uid;PWD=$pw
+d"));
                                   # Establish the data base to use
    $rtncd || ($db->Sql("$dbf") && ($rtncd = 3));

    return ($rtncd,$db);

}   # end OpenODBC()

    [download]

    PJ
    unspoken but ever present -- use strict; use warnings; use diagnostics; (if needed)
[reply]
[d/l]
      \$dbhi gives me the same error message.
      $$dbhi says "not a scalar reference".
[reply]
Re: DBI: pass $dbh from a sub
by mifflin (Curate) on Jul 29, 2004 at 16:20 UTC
    The warn on the DBI->connect call is useless because you are telling DBI to RaiseError.
    Wrap that call with an eval to trap errors.
[reply]
      The DBI->connect method does not follow the RaiseError property. It returns undef on an error and sets the $DBI::errstr. The return value needs to be tested, although it is more useful to use die instead of warn.
[reply]
        The DBI->connect method does not follow the RaiseError property. It returns undef on an error...
        It used to be that way, but hasn't been for quite a while. Check the changes log.
[reply]
        It doesn't behave that way for me.
        The code... 
        use DBI;
eval {
   $dbh = DBI->connect('dbi:Oracle:dev', 'bad', 'pass', {RaiseError =>
+ 1, PrintError => 0}) || warn "warning";
   print "after connect\n";
};
if ($@) {
   print "Ugh!\n";
}

        [download]
        will not print out "warning" or "after connect"
        it prints out "Ugh!"
        # perl testit
Ugh!

        [download]
[reply]
[d/l]
[select]
        But I avoided "die" because I don't want my program to end at that point; doesn't "die" exit the program?
[reply]

Back to Seekers of Perl Wisdom