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
}
-
Are you posting in the right place? Check out Where do I post X? to know for sure.
-
Posts may use any of the Perl Monks Approved HTML tags. Currently these include the following:
<code> <a> <b> <big>
<blockquote> <br /> <dd>
<dl> <dt> <em> <font>
<h1> <h2> <h3> <h4>
<h5> <h6> <hr /> <i>
<li> <nbsp> <ol> <p>
<small> <strike> <strong>
<sub> <sup> <table>
<td> <th> <tr> <tt>
<u> <ul>
-
Snippets of code should be wrapped in
<code> tags not
<pre> tags. In fact, <pre>
tags should generally be avoided. If they must
be used, extreme care should be
taken to ensure that their contents do not
have long lines (<70 chars), in order to prevent
horizontal scrolling (and possible janitor
intervention).
-
Want more info? How to link
or How to display code and escape characters
are good places to start.