Beefy Boxes and Bandwidth Generously Provided by pair Networks
The stupid question is the question not asked
 
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 rifling through the Monastery: (5)
As of 2014-09-17 22:24 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    How do you remember the number of days in each month?











    Results (100 votes), past polls