I wrote a sub to accumulate all results of an SQL query in in a list of
hashes as follows:

Can't use string ("text_id") as a HASH ref while "strict refs" in use at
        LoH_db.pl line 7 (#1)
    (F) Only hard references are allowed by "strict refs".  Symbolic
    references are disallowed.  See perlref.

Uncaught exception from user code:
        Can't use string ("text_id") as a HASH ref while "strict refs"
in use at
 LoH_db.pl line 7.

The query should return

text_id 1
text_id 2
text_id 3
text_id 4
....
text_id 400

and the foreach loop after the push statement prints these results
correctly.

The reason why I wrote such a complicated sub is that I want it to be
universal, to be able to return any query results depending on the
$query, which will be ultimately passed from the caller.

Perlref has been read but I did not get any smarter.
I still think I'm referencing something wrong while fetching the results

error, please?

TIA and best regards,

Zoltan Kandi, M. Sc.
Product & Application Specialist

Tellabs Netherlands BV
Perkinsbaan 17
3439 ND Nieuwegein

I'm still (de)referencing something wrong, I'm afraid...

Thanks,

use strict;
use warnings;
use diagnostics;
use Win32::ODBC;

print "Data from insert2(), creating array from DB\n";

{
    print "{ ";
    foreach my $Column ( keys %$Row )
    {
      print "$Column => $Row->{$Column}";
    }
    print " }\n";

sub insert2
{
my $dsn = "dsn";
my $login = "uid";
my $password = "pwd";
my $query = "select text_id from descriptions";
my %a;

my $db = new Win32::ODBC("DSN=$dsn;UID=$login;PWD=$password;") or die
"Can not connect: " . Win32::ODBC::Error();

if( ! $db->Sql( $query ) )
{
             print "Data read from the DB\n";
             while( $db->FetchRow() )
             {
                    undef %a;
                    %a = $db->DataHash();

                    foreach my $Column ( keys %a )
                    {
                         print $Column."\t".$a{$Column}."\n";
                    }
             }

Data read from the DB
text_id 1
text_id 2
text_id 3
text_id 4
text_id 5
text_id 6
text_id 7
text_id 8
text_id 10
Data from insert2(), creating array from DB
{ text_id => 10 }
{ text_id => 10 }
{ text_id => 10 }
{ text_id => 10 }
{ text_id => 10 }
{ text_id => 10 }
{ text_id => 10 }
{ text_id => 10 }
{ text_id => 10 }

I'm clueless, but it does not seem to be a WIn32::ODBC specific issue,
since the hash %a is retrieved correctly from the DB. Might it still be
a (de)referencing problem?

Have a nice day,

I can't test your exact code, but I have a feeling that you
should get rid of that undef.

The problem is here:

which should be:

So instead of appending an array ref, you are appending the row
(col1,val1,col2,val2...), hence $a[$row] has a column name rather than
an array reference.

Thanks for your suggestions.

Now it works.

Have a nice day,

Zoltan Kandi, M. Sc.
Product & Application Specialist

Tellabs Netherlands BV
Perkinsbaan 17
3439 ND Nieuwegein

Anyway....

Problem is in sub insert:

             while( $db->FetchRow ) {
                    my %a = $db->DataHash;

Also, personally I would not return an array from sub insert, but a
reference to the array.

Your code is really quite inefficient - why loop though the data-set,
store it in an array and then loop through it again when it can be done
in one loop.  Also, you are storing the column names multiple times.  If
you really need to keep a store of the query and want to work with
column names you could try creating a hash of lists instead od a list of
hashes.  The hash of lists would have column names as the keys and
column values as the list elements:

%data = {
        text_id  => [1, 2, 400],
        text_val => ['a', 'b', 'oj'],

my $data = insert();

        print "Row $row \t";

        print "$columns->[$col] => $data->[$row][$col]\t";
    }
    print "\n";

sub insert {
        my $dsn = "dsn";
        my $login = "uid";
        my $password = "pwd";
        my $query = "select text_id from table where text_id<11";
        my $dbh = DBI->connect("dbi:ODBC:$dsn", $login, $password, {RaiseError
=> 1});
        my $sth = $dbh->prepare($query);
        $sth->execute();
        my $rows = $sth->fetchall_arrayref;

        $sth->finish;
        $dbh->disconnect;
        return $rows;

sub get_data {

        $dsn ||= 'my_default_dsn';

        use DBI;
        my $dbh = DBI->connect($dsn, $uid, $pwd, {RaiseError => 1});
        END { $dbh->disconnect if $dbh }
        my $st = "select * from table where text_id<11";

You can trich fetchall_arrayref into returning an array (ref) of
hashrefs, but this looks simpler. Newer DBI versions also provide the
method fetchall_hashref.

Anyway....

Problem is in sub insert:

             while( $db->FetchRow ) {
                    my %a = $db->DataHash;

Also, personally I would not return an array from sub insert, but a
reference to the array.

Your code is really quite inefficient - why loop though the data-set,
store it in an array and then loop through it again when it can be done
in one loop.  Also, you are storing the column names multiple times.  If
you really need to keep a store of the query and want to work with
column names you could try creating a hash of lists instead od a list of
hashes.  The hash of lists would have column names as the keys and
column values as the list elements:

%data = {
        text_id  => [1, 2, 400],
        text_val => ['a', 'b', 'oj'],

use strict;
use warnings;
use diagnostics;
use DBI;

my $data = insert();

        print "Row $row \t";

        print "$columns->[$col] => $data->[$row][$col]\t";
    }
    print "\n";

sub insert {
        my $dsn = "dsn";
        my $login = "uid";
        my $password = "pwd";
        my $query = "select text_id from table where text_id<11";
        my $dbh = DBI->connect("dbi:ODBC:$dsn", $login, $password,
{RaiseError
=> 1});
        my $sth = $dbh->prepare($query);
        $sth->execute();
        my $rows = $sth->fetchall_arrayref;

        $sth->finish;
        $dbh->disconnect;
        return $rows;

sub get_data {

        $dsn ||= 'my_default_dsn';

That is a solution, but not very efficient because it creates an
unnecessary copy of the hash.

The solution is to create a new hash with every iteration of the loop
via 'my %a'.  You then store a reference to each new hash:

        while( $db->FetchRow ) {
                my %a = $db->DataHash;