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
}
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'"
| [reply] |
Re: DBI bind output vars by column name
by injunjoel (Priest) on Jul 13, 2007 at 17:28 UTC
|
| [reply] |
|
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.
| [reply] |
|