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)

In reply to Re: DBI: pass $dbh from a sub by periapt
in thread DBI: pass $dbh from a sub by poqui

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.