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

Here's a softball: I'm trying to place the results of a SQL Select statement into an array. In this case, the Select returns only one column. When I run the script, I get this error: 
Can't locate object method "selectcol_arrayref" via package "DBI::st" 
+at C:\temp\test.pl line 29 (#1)
    (F) You called a method correctly, and it correctly indicated a pa
+ckage functioning as a class, but that package doesn't define that pa
+rticular method, nor does any of its base classes.  See perlobj.

[download]
Here is the offending line: 
@EasySetTableNames = @{$sqlCmd->selectcol_arrayref($sqlStatement)};

[download]
And the related code (with the offending line at the end): 
use strict;
use warnings;
use diagnostics;
use DBI;
#use replace;
use File::Glob ':glob';
#Variable Declarations
#############
#---Database Credentials
my $DBUserName                     = "sa";
my $DBPassword                     = "unica*03";
my $DBName                         = "dbi:ODBC:Insight";
#------Used in Database Transactions
my $dbh                         = "";
my $sqlStatement                 = "";
my $sqlCmd                         = "";
#---Variables used to gather Easy Set Table names
my @EasySetTableNames            = ();


#------Establish DB Connection
$dbh = DBI->connect( $DBName, $DBUserName, $DBPassword,{ PrintError =>
+ 0,AutoCommit => 0})
        || die "Database connection creation failed: $DBI::errstr\n";
# Get the Easy Set Tables
#---These tables in the Insight Database all start with AI_  so get at
+ list of those tables
$sqlStatement = "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHER
+E TABLE_NAME LIKE '%AI_%'";
$sqlCmd = $dbh->prepare($sqlStatement);
$sqlCmd->execute();
@EasySetTableNames = @{$sqlCmd->selectcol_arrayref($sqlStatement)};

[download]
Also, I seem to have a logical brain fart concerning how Perl handles hashes, arrays and strings. Can anyone point me to a good source.
Do you believe in miracles? Yes!

Replies are listed 'Best First'.
Re: MSSQL Select to an array: error with selectcol_arrayref
by kyle (Abbot) on Dec 13, 2007 at 21:21 UTC

    According to the DBI documentation, you should call selectcol_arrayref on a database handle ($dbh), not a statement handle. 

    $dbh = DBI->connect( ... ) || die ...;
@EasySetTableNames = @{$dbh->selectcol_arrayref($sqlStatement)};

    [download]
[reply]
[d/l]
[select]
      Okay, I replaced the line as suggested from 
      @EasySetTableNames = @{$sqlCmd->selectcol_arrayref($sqlStatement)};

      [download]
      to 
      @EasySetTableNames = @{$dbh->selectcol_arrayref($sqlStatement)};

      [download]
      and now get this error: 
      Can't use an undefined value as an ARRAY reference at C:\temp\test.pl 
+line 29 (#1)
    (F) A value used as either a hard reference or a symbolic referenc
+e must be a defined value.  This helps to delurk some insidious error
+s.

      [download]
      I thought I had defined the array, but as mentioned before I'm baffled as to how Perl handles arrays and strings and going between them.
      Do you believe in miracles? Yes!
[reply]
[d/l]
[select]

        It looks like your select statement didn't work. The only thing being used as an array reference in that statement is the result from selectcol_arrayref. I would check what you are using for an SQL statement and make sure it is valid.

        90% of every Perl application is already written. ⇒
        dragonchild
[reply]
Re: MSSQL Select to an array: error with selectcol_arrayref
by jZed (Prior) on Dec 14, 2007 at 00:01 UTC
    kyle is right that selectcol_arrayref() is a method of the database handle ($dbh), not of the statement handle ($sth). Here's one way to remember which DBI object the various methods belong to : database handle methods take data out of the database in a single step whereas statement handle methods require multiple steps - prepare, execute, fetch.

    Another way to tell is by the error message: Can't locate object method "selectcol_arrayref" via package "DBI::st". That message says it can't find that method in that package, in other words, that object has no such method. Either you misspelled method name, or you are using it on the wrong object.

[reply]

Back to Seekers of Perl Wisdom