Beefy Boxes and Bandwidth Generously Provided by pair Networks
Think about Loose Coupling
 
PerlMonks  

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->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
}
Comment on DBI bind output vars by column name
Select or Download Code
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
    Nice...
    I would also suggest looking at Re^2: fetchrow_array as well.

    -InjunJoel
    "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.

Back to Snippets Section

Log In?
Username:
Password:

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

How do I use this? | Other CB clients
Other Users?
Others studying the Monastery: (7)
As of 2014-08-21 06:53 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    The best computer themed movie is:











    Results (128 votes), past polls