DBI bind output vars by column name

Well, I've been working on bettering my DBI skills and in reading thru the docs I could not find a built-in way to bind output columns by column name! There are methods for column number. I thought, "I can't be the first person to have wanted this," but a quick search for something simple got me nowhere, and I wrote this. I would be delighted to know if anyone may find this useful, or better yet, could improve it or offer help in making it better/faster/etc...

# This is my means of a sort of 'bind params by column name'.
sub bind_result_columns
{
    my $sth = shift;
    my $RESULT_MAP = shift;

    my $bound_fields = {};
    
    while (my ($node_field, $db_col) = each %$RESULT_MAP) {
        $db_col = lc $db_col;
        if (defined $sth->{NAME_lc_hash}{$db_col}) {

            $sth->bind_col(
                $sth->{NAME_lc_hash}{$db_col} + 1,
                \$bound_fields->{$node_field}
                );
        }
        else {
            die "DB column $db_col not returned from your SQL query!\n
+";
        }
    }
    return $bound_fields;
}
[download]

So, here's a usage example...

my $QUERY = "select READ_COMMUNITY,IP_ADDRESS,DEVICE_GROUP,MANAGED fro
+m SNMP_NODES";

# Do your connect, prepare, execute, etc...
my $dbh = DBI->connect($DB_DSN, $DB_USER, $DB_PASS, $DB_OPTIONS)
    or die "Couldn't connect to DB: " . $DBI::errstr;

my $sth = $dbh->prepare($QUERY)
    or die "Couldn't prepare statement: " . $dbh->errstr;

$sth->execute();


### Map the node definition fields to the SQL result columns  ###
my $RESULT_MAP = {
    'read_community' => 'READ_COMMUNITY',
    'address'        => 'IP_ADDRESS',
    'on_off'         => 'MANAGED',
    'group'          => 'DEVICE_GROUP',
};

my $bound_fields = bind_result_columns($sth, $RESULT_MAP);

while ($sth->fetch) {
    # Do stuff with the data now...
    print Dumper $bound_fields;   # DEBUG
}
[download]

Back to Cool Uses for Perl