Win32::ODBC => generating a list of hashes from the query result set 
Author Message
 Win32::ODBC => generating a list of hashes from the query result set

Dear All,

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

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

> for my $row (0..$#a) {
>     print "Row $row \t";
>     for my $col ( keys %{ $a[$row] } ) {
>         print $col." => ".$a[$row]{$col}."\t";
>     }
>     print "\n";
> }
> exit;
> sub insert {
> my $dsn = "dsn";
> my $login = "uid";
> my $password = "pwd";
> my $query = "select text_id from table where text_id<11";
> 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( \%a ) ) {

>                     foreach my $Column ( keys %a ) {
>                          print $Column."\t".$a{$Column}."\n"; } } }
> else {
>                 my $err = $db->Error;
>                 warn "Sql() ERROR\n";
>                 warn "\t\$query: $query\n";
>                 warn "\t\$err:   $err\n": }

> $db->Close();

> }

and I get the following error message:

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

Tel:      +31 30 600 40 75
Fax:      +31 30 600 40 90
GSM:      +31 651 194 291

Internet: http://www.*-*-*.com/



Sun, 16 May 2004 10:32:34 GMT  
 Win32::ODBC => generating a list of hashes from the query result set



Quote:
> Dear All,

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

[snipped some code]

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


                                  ^^^



Cheers,
Bernard



Sun, 16 May 2004 10:41:17 GMT  
 Win32::ODBC => generating a list of hashes from the query result set
Hi Bernard,

Quote:

[cut]

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

>                                   ^^^



> Cheers,
> Bernard

I know, I know... Rookie, RTFM... Now I got rid of the error message,
but

Quote:
> for my $row (0..$#a) {
>     print "Row $row \t";
>     for my $col ( keys %{ $a[$row] } ) {
>         print $col." => ".$a[$row]{$col}."\t";
>     }
>     print "\n";
> }

prints
Row 1<cr><lf>
...

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

Thanks,

Zoltan



Sun, 16 May 2004 11:00:37 GMT  
 Win32::ODBC => generating a list of hashes from the query result set


Quote:
> Hi Bernard,


> [cut]

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

>>                                   ^^^



> I know, I know... Rookie, RTFM... Now I got rid of the error message,
> but

>> for my $row (0..$#a) {
>>     print "Row $row \t";
>>     for my $col ( keys %{ $a[$row] } ) {
>>         print $col." => ".$a[$row]{$col}."\t";
>>     }
>>     print "\n";
>> }

> prints
> Row 1<cr><lf>
> ...

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

I can't help you with that since I don't know how %a is filled.
I see it used only in the FetchRow method so that must be the place,
but I've never used DBI and don't know anything about it. Hold tight,
though, I'm sure you'll get more help in a little while from someone
else.

Cheers,
Bernard



Sun, 16 May 2004 11:06:49 GMT  
 Win32::ODBC => generating a list of hashes from the query result set
Hi again,

Quote:

> I can't help you with that since I don't know how %a is filled.
> I see it used only in the FetchRow method so that must be the place,
> but I've never used DBI and don't know anything about it. Hold tight,
> though, I'm sure you'll get more help in a little while from someone
> else.

> Cheers,
> Bernard

I do appreciate your help. I've gone one step further.

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";

Quote:
}

exit;

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";
                    }
             }

Quote:
}

else
{
                my $err = $db->Error;
                warn "Sql() ERROR\n";
                warn "\t\$query: $query\n";
                warn "\t\$err:   $err\n";

Quote:
}

$db->Close();

Quote:
}

returns this (incorrect) result set:

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,

Zoltan



Sun, 16 May 2004 12:30:39 GMT  
 Win32::ODBC => generating a list of hashes from the query result set


Quote:
> Hi again,


>> I can't help you with that since I don't know how %a is filled.
>> I see it used only in the FetchRow method so that must be the place,
>> but I've never used DBI and don't know anything about it. Hold tight,
>> though, I'm sure you'll get more help in a little while from someone
>> else.

> I do appreciate your help. I've gone one step further.

> 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";
> }

> exit;

> 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;

                      ^^^^^^^^^

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

Cheers,
Bernard



Sun, 16 May 2004 13:02:04 GMT  
 Win32::ODBC => generating a list of hashes from the query result set
$a[$row] is not a hash ref, instead it is column name or value, in this
case 'text_id'.

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.

--
  Simon Oliver



Sun, 16 May 2004 13:15:23 GMT  
 Win32::ODBC => generating a list of hashes from the query result set
Gurus,

Thanks for your suggestions.

Quote:

> $a[$row] is not a hash ref, instead it is column name or value, in this
> case 'text_id'.

> 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.

> --
>   Simon Oliver

The solution was


Now it works.

Have a nice day,

Zoltan Kandi, M. Sc.
Product & Application Specialist

Tellabs Netherlands BV
Perkinsbaan 17
3439 ND Nieuwegein

Tel:      +31 30 600 40 75
Fax:      +31 30 600 40 90
GSM:      +31 651 194 291

Internet: http://www.tellabs.com



Sun, 16 May 2004 13:55:00 GMT  
 Win32::ODBC => generating a list of hashes from the query result set

Quote:
> I can't help you with that since I don't know how %a is filled.
> I see it used only in the FetchRow method so that must be the place,
> but I've never used DBI and don't know anything about it. Hold tight,
> though, I'm sure you'll get more help in a little while from someone
> else.

It's not DBI - it's Win32::ODBC.  I use DBI where possible - if you are
going to learn a database module you might as well learn a one that is
cross-platform, unless you need a specific Win32::ODBC function of
course.

Anyway....

Problem is in sub insert:

Quote:
>              while( $db->FetchRow( \%a ) ) {


Should read:

             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'],

Quote:
}

Of course DBI does all this much better. As soon as you have executed
your query you can retrieve the column names via the $sth->{NAMES}
attribute and get the complete recordset as a list of lists via the
$sth->fetchall_arrayref method.  Here's a complete rewrite using DBI:use
strict;
use warnings;
use diagnostics;
use DBI;

my $data = insert();



        print "Row $row \t";

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

Quote:
}

exit;

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;

Quote:
}

Personally, I wouldn't call it 'insert' (it suggests data will be
inserted into the table), perhaps 'get_data' is better.  And I would
make it take arguments:

sub get_data {

        $dsn ||= 'my_default_dsn';

--
  Simon Oliver



Sun, 16 May 2004 14:00:34 GMT  
 Win32::ODBC => generating a list of hashes from the query result set


Wed, 18 Jun 1902 01:00:00 GMT  
 Win32::ODBC => generating a list of hashes from the query result set
Quote:
> your query you can retrieve the column names via the $sth->{NAMES}

Sorry, that should read '$sth->{NAME}'

--
  Simon Oliver



Sun, 16 May 2004 14:04:57 GMT  
 Win32::ODBC => generating a list of hashes from the query result set


Wed, 18 Jun 1902 01:00:00 GMT  
 Win32::ODBC => generating a list of hashes from the query result set

Quote:

>I wrote a sub to accumulate all results of an SQL query in in a list of
>hashes

Why don't you use DBI + DBD::ODBC? DBI has a built-in for that. Like
this:

        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.

--
        Bart.



Sun, 16 May 2004 14:56:44 GMT  
 Win32::ODBC => generating a list of hashes from the query result set

Quote:
> I can't help you with that since I don't know how %a is filled.
> I see it used only in the FetchRow method so that must be the place,
> but I've never used DBI and don't know anything about it. Hold tight,
> though, I'm sure you'll get more help in a little while from someone
> else.

It's not DBI - it's Win32::ODBC.  I use DBI where possible - if you are
going to learn a database module you might as well learn a one that is
cross-platform, unless you need a specific Win32::ODBC function of
course.

Anyway....

Problem is in sub insert:

Quote:
>              while( $db->FetchRow( \%a ) ) {


Should read:

             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'],

Quote:
}

Of course DBI does all this much better. As soon as you have executed
your query you can retrieve the column names via the $sth->{NAME}
attribute and get the complete recordset as a list of lists via the
$sth->fetchall_arrayref method.  Here's a complete rewrite using DBI:

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

my $data = insert();



        print "Row $row \t";

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

Quote:
}

exit;

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;

Quote:
}

Personally, I wouldn't call it 'insert' (it suggests data will be
inserted into the table), perhaps 'get_data' is better.  And I would
make it take arguments:

sub get_data {

        $dsn ||= 'my_default_dsn';

--
  Simon Oliver



Sun, 16 May 2004 16:43:00 GMT  
 Win32::ODBC => generating a list of hashes from the query result set

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;

--
  Simon Oliver



Sun, 16 May 2004 16:43:36 GMT  
 
 [ 15 post ] 

 Relevant Pages 

1. Win32::ODBC => generating a list of hashes from the query result set

2. generating sets from lists of lists

3. problem with setting default selection in $query->scrolling_list using CGI.pm

4. Win32::ODBC -- getting results

5. Win32 ODBC BETA 21.12.1999 Returns No Results

6. Win32 ODBC BETA 21.12.1999 Returns No Results

7. looping through an sql query using win32::odbc

8. SQL Pass-thru query with Win32::ODBC?

9. Win32::ODBC/MSAccess query

10. looping through an sql query using win32::odbc

11. Win32::ODBC - Delete & Drop Query

12. (newbie?) query on Hash-of-lists

 

 
Powered by phpBB® Forum Software