DBI bind output vars by column name

[Hercynium]

Description:

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

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
}

[rob_au]

That's fairly neat - There are some other similar DBI receipes and tricks from gmax in the node DBI recipes.

[injunjoel]

Nice...
I would also suggest looking at Re^2: fetchrow_array as well.

-InjunJoel

[Hercynium]

Thanks for the tip. Since posting that snippet and poking around gmax's stuff I've learned a few things that I know I could have used in the past (and probably will in the future)

However, the code I wrote was to solve a different sort of problem. Because of the mapping, I can pass $bound_fields directly to the node_create() method of an object to create a fully-populated object without calling a whole bunch of setters.

Also, since the script this code is in is used on several systems, querying several databases, the $RESULT_MAP is actually built from a config file to accommodate creating the same type of objects from tables with several different schemas.