Beefy Boxes and Bandwidth Generously Provided by pair Networks
Your skill will accomplish
what the force of many cannot

DBI bind output vars by column name

by Hercynium (Hermit)
on Jul 10, 2007 at 03:41 UTC ( #625755=snippet: print w/replies, xml ) Need Help??
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->{NAME_lc_hash}{$db_col} + 1,
        else {
            die "DB column $db_col not returned from your SQL query!\n
    return $bound_fields;
So, here's a usage example...

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


### 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
Replies are listed 'Best First'.
Re: DBI bind output vars by column name
by rob_au (Abbot) on Jul 10, 2007 at 04:35 UTC
    That's fairly neat - There are some other similar DBI receipes and tricks from gmax in the node DBI recipes.


    perl -le "print unpack'N', pack'B32', '00000000000000000000001000000000'"

Re: DBI bind output vars by column name
by injunjoel (Priest) on Jul 13, 2007 at 17:28 UTC
    I would also suggest looking at Re^2: fetchrow_array as well.

    "I do not feel obliged to believe that the same God who endowed us with sense, reason and intellect has intended us to forego their use." -Galileo
      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.
Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: snippet [id://625755]
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others avoiding work at the Monastery: (13)
As of 2016-10-25 19:33 GMT
Find Nodes?
    Voting Booth?
    How many different varieties (color, size, etc) of socks do you have in your sock drawer?

    Results (327 votes). Check out past polls.