A recent chatterbox discussion made me aware that some otherwise cluefull people are not quite sure how to retrieve various perl data structures from a DBI query. Here's a simple example that illustrates the basics for Array of Arrays, Array of Hashes, and Hash of Hashes (all references). It should run with all DBDs. 

#!perl -w
use strict;
use DBI;
use Data::Dumper;
my $dbh=DBI->connect('dbi:AnyData(RaiseError=1):');
my($create,$populate,$query,$keyfield)=split /\n/,join '',<DATA>;
$dbh->do($_) for ($create,$populate);
print 'AoA ', Dumper $dbh->selectall_arrayref($query);
print 'AoH ', Dumper $dbh->selectall_arrayref($query,{Slice=>{}});
print 'HoH ', Dumper $dbh->selectall_hashref ($query,$keyfield);
__DATA__
CREATE TABLE x (id INTEGER, phrase VARCHAR(20))
INSERT INTO x (id,phrase) VALUES (1,'foo')
SELECT id,phrase FROM x
id

[download]

Replies are listed 'Best First'.
Re: Getting Perl Data Structures from DBI queries
by helphand (Pilgrim) on Apr 22, 2004 at 02:53 UTC
    This dies with the following error: 
    DBD::AnyData::db selectall_hashref failed: Field 'id' does not exist (not one of
 ID PHRASE) at index.pl line 10, <DATA> line 4.
DBD::AnyData::db selectall_hashref failed: Field 'id' does not exist (not one of
 ID PHRASE) at index.pl line 10, <DATA> line 4.

    That's on Perl, v5.8.3 built for MSWin32-x86-multi-thread.

    If I uppercase all the 'id''s, it works.

    I have to admit though, it is not what I expected from the subject. I 'thought' the example would demonstrate how to store and retrieve Perl data structures from the underlying DB.

    Scott
[reply]
      Download the latest release of SQL::Statement (v.1.08 or higher). There were bugs in capitalization on preveious versions.

      If what you are looking for is a way to store objects in a database, here is an example of one way to store an object (in this case a CGI.pm query object) in a database and then retrieve it 

      #!perl -w
use strict;
use DBI;
use CGI;

my $dbh    = DBI->connect("DBI:DBM(RaiseError=1):mldbm=Storable");

my $sql    = {
    drop   => "DROP TABLE test"
  , create => "CREATE TABLE test (name VARCHAR(10), cgi_obj BLOB)"
  , insert => "INSERT INTO test (name,cgi_obj) VALUES (?,?)"
  , select => "SELECT cgi_obj FROM test WHERE name=?"
};

populate( Barney => new CGI('species=dinosaur&color=purple') );
my $q = $dbh->selectrow_array( $sql->{select},{},'Barney');
my $rv = ( $q->param('color') eq 'purple' ) ? "ok" : "bad";
print "$rv!\n";

sub populate {
    eval { $dbh->do($sql->{drop}) };
    $dbh->do( $sql->{create} );
    $dbh->do( $sql->{insert},{},@_);
}
__END__

      [download]
[reply]
[d/l]
        Download the latest release of SQL::Statement (v.1.08 or higher). There were bugs in capitalization on preveious versions.

        Ahh, that explains it. Unfortunately, ActiveState's PPM only has v1.06, but at least we know why it failed.

        Thanks for the second example (had to update my DBI and install MLDBM to get it to run), that one is more along the lines of what I was expecting, it's appreciated.

        Scott
[reply]

Back to Cool Uses for Perl