DBI::mysql column names as hash keys?

[Tony]

Hello,

Given a DBI::mysql database with 2 tables and 5 columns like so:

human --> name
--> age
--> sex

dog --> breed
--> colour

How can I get variables, named like those below... without knowing the
column names beforehand???

$vars{human.name}
$vars{human.age}
$vars{human.sex}
$vars{dog.breed}
$vars{dog.colour}

I need to do this so the template toolkit can process a template,
regardless of what database columns the template refers to.

Help?

 

[Tad McClellan]

How can I get variables, named like those below... without knowing the
column names beforehand???

You are asking how to know the names without knowing the names.

Can't be done, obviously, because it is a contradiction...

$vars{human.name}

You must quote hash keys that do not match /^\w+$/.

$vars{human.age}
$vars{human.sex}
$vars{dog.breed}
$vars{dog.colour}

I need to do this so the template toolkit can process a template,
regardless of what database columns the template refers to.

But is should be possible to discover what the names are:

SHOW TABLES

will give you the names of all of the tables.

SHOW COLUMNS FROM ?

will give you all of the column names from a particular table when
you provide the table name for the placeholder.

That answers your MySQL question.


The answer to your Perl question is:

$key = $table . '.' . $column;
or
$key = "$table.$column";

 

[gnari]

Hello,
...
How can I get variables, named like those below... without knowing the
column names beforehand???

$vars{human.name}
...

by reading the DBI docs?

gnari

 

[kz]

Hello,

[snip]

Sorry for the incorrect line wrapping.

#!/usr/bin/perl
use strict;
use warnings;
use DBI();
my $DBserver= "server";
my $DBname= "dbname";
my $DBuser= "user";
my $DBpassword= "pwd";
my $dbh = DBI->connect("DBI:mysql:database=$DBname;host=$DBserver",
"$DBuser", "$DBpassword",{'RaiseError' => 1});
my %fieldnames;
my $sth = $dbh->prepare("show tables");
$sth->execute();
while (my $refv = $sth->fetchrow_hashref()) {
my $sth2 = $dbh->prepare("describe ".$refv->{"Tables_in_$DBname"});
$sth2->execute();
while (my $refv2 = $sth2->fetchrow_hashref()) {
$fieldnames{$refv->{"Tables_in_$DBname"}.".".$refv2->{Field}} =
$refv->{"Tables_in_$DBname"}.".".$refv2->{Field}; }
$sth2->finish(); }
$sth->finish();
$dbh->disconnect();
foreach my $columns (keys %fieldnames) {
print "$columns\n"; }
exit 0;

D:\>perl t2.pl
testtie.produce_id
testtie.quantity
testtie.price
test.testid
pdata.pfrozen
testtie.description
test.testdata
pdata.pvalue
pdata.pkey

or

use strict;
use warnings;
use DBI();
my $DBserver= "server";
my $DBname= "dbname";
my $DBuser= "user";
my $DBpassword= "pwd";
my $dbh = DBI->connect("DBI:mysql:database=$DBname;host=$DBserver",
"$DBuser", "$DBpassword",{'RaiseError' => 1});
my %HOA;
my $sth = $dbh->prepare("show tables");
$sth->execute();
while (my $refv = $sth->fetchrow_hashref()) {
$HOA{$refv->{"Tables_in_$DBname"}} = [];
my $sth2 = $dbh->prepare("describe ".$refv->{"Tables_in_$DBname"});
$sth2->execute();
while (my $refv2 = $sth2->fetchrow_hashref()) {
push @{$HOA{$refv->{"Tables_in_$DBname"}}},$refv2->{Field}; }
$sth2->finish(); }
$sth->finish();
$dbh->disconnect();
print "Database $DBname\n";
foreach my $table (keys %HOA) {
print "Table $table\n";
print "Fields ",join(",",@{$HOA{$table}}),"\n"; }
exit 0;

D:\>perl test.pl
Database test
Table testtie
Fields produce_id,price,quantity,description
Table pdata
Fields pkey,pvalue,pfrozen
Table test
Fields testid,testdata

There are other (simple) solutions, though, I reckon...
You would also want to read the table_info and column_info section of the
DBI documentation which will answer your questions.

HTH,

Zoltan

 

[Tony]

Thanks for Tad / Zoltan, but the question is... how do I populate
%vars with values and keys? (where the key is the table & column name)

So this...

printf $vars{"human.name"};

Will print whatever is in the "name" column, of the "human" table (eg.
"Bob"). And this has to work for each column.

It seems easy enough to get the column names returned. You just do
this:

$sth1 = $dbh->prepare("SELECT * FROM human WHERE name = ?");
$rv = $sth1->execute("Bob");
my @names = @{$sth1->{NAME}};
printf "$names[0],$names[1],$names[2]";

This prints "name,age,sex"

The next step, is to get those values to be keys in the %vars hash,
and assign the correct values.

I've read the dbi docs, but there don't seem to be many examples, so
it's rather hard to follow.

 

[gnari]

Thanks for Tad / Zoltan, but the question is... how do I populate
%vars with values and keys? (where the key is the table & column name)

So this...

printf $vars{"human.name"};

Will print whatever is in the "name" column, of the "human" table (eg.
"Bob"). And this has to work for each column.

It seems easy enough to get the column names returned. You just do
this:

$sth1 = $dbh->prepare("SELECT * FROM human WHERE name = ?");
$rv = $sth1->execute("Bob");
my @names = @{$sth1->{NAME}};
printf "$names[0],$names[1],$names[2]";

This prints "name,age,sex"

The next step, is to get those values to be keys in the %vars hash,
and assign the correct values.

it is not clear what your problem is.

my $sth1 = $dbh->prepare("SELECT * FROM human WHERE name = ?");
my $rv = $sth1->execute("Bob");
# assuming only one row returned
if (my $res=$sth1->fetchrow_hashref) {
$vars{"human.$_"} = $res->{$_} for (keys %$res);
} else {
# no row found
}
$sth1->finish();


gnari

 

[Tad McClellan]

Thanks for Tad / Zoltan, but the question is... how do I populate
%vars with values and keys? (where the key is the table & column name)

I thought that had already been answered...

So this...

printf $vars{"human.name"};

Will print whatever is in the "name" column, of the "human" table (eg.
"Bob"). And this has to work for each column.

$vars{'human.name'} = 'Bob';

It seems easy enough to get the column names returned. [ snip]
my @names = @{$sth1->{NAME}};
printf "$names[0],$names[1],$names[2]";

The next step, is to get those values to be keys in the %vars hash,

$vars{"human.$names[0]"} = 'some value';

and assign the correct values.

Use one of the fetch*() routines to get the values.

Once you get the 3 things that you need into variables, populating
the hash should be really easy:

$vars{"$table.$column"} = $value;

 

[Tony]

Thanks for pointing me in the right direction guys.

For the record, this is what I ended up with:


$sth1 = $dbh->prepare("SELECT * FROM human where name = ?");
my $rv = $sth1->execute("Bob");

if ($rv > 0) {
while ($hash_ref = $sth1->fetchrow_hashref) {

$vars{"human"} = {%$hash_ref}; # THIS WAS THE ELUSIVE PART!!!

my $tt = Template->new;
$tt->process('test.txt', \%vars, 'test.txt.out');

}
} else {
printf "no results\n";
};

 

[Tad McClellan]

printf "no results\n";

Why not just print() ?

Done too much C programming?

 

[gnari]

Thanks for pointing me in the right direction guys.

ok, some more pointers...

$sth1 = $dbh->prepare("SELECT * FROM human where name = ?");
my $rv = $sth1->execute("Bob");

if you are not doing any interpolation, many will tell you to use single
quotes
instead of double quotes. also, you probably should declare $sth1 as
a lexical here (at least judging from the posted snippet)

if ($rv > 0) {
while ($hash_ref = $sth1->fetchrow_hashref) {

$vars{"human"} = {%$hash_ref}; # THIS WAS THE ELUSIVE PART!!!

in these situations I much prefer to use a lexical for the fetch result.
then you can simplify the elusive part

while ( my $hash_ref = $sth1->fetchrow_hashref) {
$vars{human} = $hash_ref ;
...
}
this is safe because $hash_ref is lexical in innermost scope
the next pass of the loop will generate a new reference. in any
case, it is good practice to declare lexicals in as narrow scope
as is practical.
also note there is no need to quote the key if it is a simple
word, but if you want to, $vars{'human'} is better than
with double quotes (some will tell you)


gnari

 

[Tony]

very useful insights gnari

thanks very much