Beefy Boxes and Bandwidth Generously Provided by pair Networks
Do you know where your variables are?

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 all is quiet...

How do I use this? | Other CB clients
Other Users?
Others making s'mores by the fire in the courtyard of the Monastery: (4)
As of 2017-09-26 04:43 GMT
Find Nodes?
    Voting Booth?
    During the recent solar eclipse, I:

    Results (292 votes). Check out past polls.