当前位置: 动力学知识库 > 问答 > 编程问答 >

Perl DBI dynamic fetchrow while loops

问题描述:

I'm trying to pass table names to a sub that gets all the field names of that table, stores them into an array, and then uses that array in conjunction with the fetchrow of another sql query to display the data in those fields. Here's the code I have now:

Examples of sub calls with table names as the parameter:

shamoo("reqhead_rec");

shamoo("approv_rec");

shamoo("denial_rec");

shamoo sub:

sub shamoo

{

my $table = shift;

print uc($table)."\n=====================================\n";

#takes arg (table name) and stores all the field names into an array

$STMT = <<EOF;

select first 1 * from $table

EOF

my $sth = $db1->prepare($STMT);$sth->execute;

my ($i, @field);

my $columns = $sth->{NAME_lc};

while (my $row = $sth->fetch){for $i (0 .. $#$row){$field[$i] = $columns->[$i];}}

$STMT = <<EOF;

select * from $table where frm = '$frm' and req_no = $req_no

EOF

$sth = $db1->prepare($STMT);$sth->execute;

$i=0;

while ($i!=scalar(@field))

{

#need code for in here...

}

}

I am looking for a way to turn this nto something that doesn't have to be explicitly defined....


my ($frm, $req_no, $auth_id, $alt_auth_id, $id_acct, $seq_no, $id, $appr_stat, $add_date, $approve_date, $approve_time, $prim);

while(($frm, $req_no, $auth_id, $alt_auth_id, $id_acct, $seq_no, $id, $appr_stat, $add_date, $approve_date, $approve_time, $prim) = $sth->fetchrow_array())

网友答案:

Use fetchrow_hashref:

sub shamoo {
    my ($dbh, $frm, $req_no, $table) = @_;

    print uc($table), "\n", "=" x 36, "\n";

    #takes arg (table name) and stores all the field names into an array
    my $sth = $dbh->prepare(
        "select * from $table where frm = ? and req_no = ?"
    );

    $sth->execute($frm, $req_no);

    my $i = 1;
    while (my $row = $sth->fetchrow_hashref) {
        print "row ", $i++, "\n";
        for my $col (keys %$row) {
            print "\t$col is $row->{$col}\n";
        }
    }
}

You may also want to set FetchHashKeyName to "NAME_lc" or "NAME_uc" when you create your database handle:

my $dbh = DBI->connect(
    $dsn,
    $user,
    $pass,
    {
        ChopBlanks       => 1,
        AutoCommit       => 1,
        PrintError       => 0,
        RaiseError       => 1,
        FetchHashKeyName => "NAME_lc",
    }
) or die DBI->errstr;
网友答案:

I wonder if this method would work for an empty table.

The safest method to get the column metadata is not to look at the keys of the returned hashref (which might not exist) but rather play by the rules and use DBI provided attributes of $sth itself:

$sth->{NAME}->[i]
$sth->{NAME_uc}->[i]
$sth->{NAME_lc}->[i]

See the Metadata section of the DBI man page for details.

分享给朋友:
您可能感兴趣的文章:
随机阅读: