Perl DBI Error Msg: Can't call method "selectcol_arrayref" on an undefined value

Question

my $dblinks = '';
$dblinks = $dbh->selectcol_arrayref("select db_link from db_links where ticket=\'LOW\'");
my $success = 0;
for my $dblink (@$dblinks) {
  $success = eval {
    my ($ret) = $dbh->selectrow_array("select 1 from "
      . $dbh->quote_identifier($dblink, 'SYSIBM', "SYSDUMMY1") );
    $ret;
  };

  if ($success) {
    &Logging (3, $I, "connect_${G_CONNECT_COUNT}", "Connect success for $dblink");
  } else {
    # Read thru the selectcol_array, check for an oracle error
    $l_msg="$dblink Result doesn't match 1";
    @l_errstr=();
    &ConnectFailed ($p_host, $p_db, $p_ars, $p_ars_sev, $l_msg, $p_cid, @l_errstr);
    # Raise a Ticket with Oracle message
    &Logging (3, $I, "connect_${G_CONNECT_COUNT}", "Connect failed for $dblink");

  }
    $l_dbh->commit();
    $l_dbh->do(qq{alter session close database link  "$dblink"});

}

Answer 1

Update:

Simple, really, you assign the handle returned by the connect call to $l_dbh but invoke a method on $dbh. You must use strict.

Original answer:

The database handle $dbh is not defined which means the connection failed. You should either check return values of your calls, or specify { RaiseError => 1} in the connect call to find out the reason.

Further, there is no reason to prefix every sub invocation with &: Use ConnectFailed( ) instead of &ConnectFailed( ), unless you know the effect of prefixing a sub invocation with & and desire to have that effect.

From perldoc perlsub:

A subroutine may be called using an explicit & prefix. The & is optional in modern Perl, as are parentheses if the subroutine has been predeclared. The & is not optional when just naming the subroutine, such as when it's used as an argument to defined() or undef(). Nor is it optional when you want to do an indirect subroutine call with a subroutine name or reference using the &$subref() or &{$subref}() constructs, although the $subref->() notation solves that problem.

... If a subroutine is called using the & form, the argument list is optional, and if omitted, no @_ array is set up for the subroutine: the @_ array at the time of the call is visible to subroutine instead. This is an efficiency mechanism that new users may wish to avoid. (emphasis added).

Answer 2

You don't show where $dbh is assigned. Presumably you do so earlier. If you do not, then I beseech you to add these two litle lines to all your code files:

use strict;
use warnings;

...and they will save you from a world of hurt.

Earlier on when you create the db handle, you should check if something bad happened:

my $dbh = DBI->connect($data_source, $username, $password)
        or die $DBI::errstr;

There is no real point in continuing with your program if you cannot get a db handle, is there? If you don't die, you should at least return from this function/method/area of code that is responsible for handling the DB.

There other issues with your code, such as using eval {} blocks everywhere and calling functions with &, but this has been covered quite amply in earlier questions on this site so I would encourage you to do a search.

Answer 3

my ($ret) = $dbh->selectrow_array("select 1 from " . $dbh->quote_identifier($dblink, 'SYSIBM', "SYSDUMMY1") );

Im not an expert at DBI, but this here looks a little wierd to me. Why the single quotes around SYSIBM and double quotes around SYSDUMMY1? This probably wouldn't solve your problem, but it's a good practice. It's not manditory to Use Strict. That's a suggestion.

$dblinks = $dbh->selectcol_arrayref("select db_link from db_links where ticket=\'LOW\'");

This here other thing that looks really odd is the escape Slashes. I would rewrite that too. It might not work, but it sure looks good.

$sql = qq{select db_link from db_links where ticket=LOW};

$dblinks = $dbh->selectcol_arrayref($sql, undef);