in reply to Leashing DBI

My problem with DBI is the opposite of Masem's problem...

Masem hides his SQL statements by defining them elsewhere, whereas my problem is with the surrounding DBI code. Every time I want to look something up I have to do something like: 

$sth=$dbh->prepare("select whatever from thetable where col=?");
$sth->execute($val);
my @list;
while(my $hashref=$sth->fetchrow_hashref) {
  push @list,$hashref;
}
$sth->finish;
return \@list;

[download]
DBI provides a few methods such as selectall_arrayref or whatever... but these methods are not cached, and they only return a few data types (there is no selectall_hashref). What I would like to do is this: 
my $hashref=$dbh->getall_hashref("select * from abc where col=?",$val)
+;
my $arrayref=$dbh->getrow_arrayref("select * from abc where col=?",$va
+l);
etc...

[download]
All the statements would be compiled with prepare_cached. Does anyone know off hand if any of those DBIx modules support this sort of thing?

Replies are listed 'Best First'.
(jeffa) 2Re: Leashing DBI
by jeffa (Bishop) on Jun 27, 2001 at 08:39 UTC
    What do you mean selectall_arrayref is not cached?!? 
    my $res = $DBH->selectall_arrayref("
    SELECT employees.name, skills.name
    FROM employees,skills,employee_skills
    WHERE employees.id = ?
    AND skills.id = employee_skill.skill_id
",undef,$emp_id);

    [download]
    Correct me if i am wrong, but that statement is cached.

    At any rate, please read (dkubb) Re: (2) Outputing data from mySQL query into format for use with HTML::Template if you are tired of building that hash data structure. :)

    Jeff 

    R-R-R--R-R-R--R-R-R--R-R-R--R-R-R--
L-L--L-L--L-L--L-L--L-L--L-L--L-L--
[reply]
[d/l]
[select]
Re: Re: Leashing DBI
by buckaduck (Chaplain) on Jun 27, 2001 at 22:14 UTC
    This may or may not be useful, depending on what you want to do with the data once you get it. But you may want to look at the module Data::Table: 
    my $table = Data::Table::fromSQL($dbh,
                                 "select whatever from table where col
+ = ?",
                                 $val);

    [download]
    And the query result is stored in a Data::Table object. From there it can be extracted or manipulated at will. In a CGI program you can print it out as an HTML table easily: print $table->html;

    buckaduck

[reply]
[d/l]
[select]

In Section Meditations