Beefy Boxes and Bandwidth Generously Provided by pair Networks
Perl: the Markov chain saw

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
Domain Nodelet?
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 chanting in the Monastery: (4)
As of 2022-06-25 07:06 GMT
Find Nodes?
    Voting Booth?
    My most frequent journeys are powered by:

    Results (81 votes). Check out past polls.