DBI::mysql column names as hash keys?

Discussion in 'Perl Misc' started by Tony, Feb 5, 2004.

  1. Tony

    Tony Guest

    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? :)
     
    Tony, Feb 5, 2004
    #1
    1. Advertising

  2. Tony <> wrote:

    > 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";


    --
    Tad McClellan SGML consulting
    Perl programming
    Fort Worth, Texas
     
    Tad McClellan, Feb 5, 2004
    #2
    1. Advertising

  3. Tony

    gnari Guest

    "Tony" <> wrote in message
    news:...
    > 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
     
    gnari, Feb 5, 2004
    #3
  4. Tony

    kz Guest

    "Tony" <> wrote in message
    news:...
    > 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
     
    kz, Feb 6, 2004
    #4
  5. Tony

    Tony Guest

    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.




    :(
     
    Tony, Feb 8, 2004
    #5
  6. Tony

    gnari Guest

    "Tony" <> wrote in message
    news:...
    > 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
     
    gnari, Feb 8, 2004
    #6
  7. Tony <> wrote:

    > 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;


    --
    Tad McClellan SGML consulting
    Perl programming
    Fort Worth, Texas
     
    Tad McClellan, Feb 8, 2004
    #7
  8. Tony

    Tony Guest

    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";
    };
     
    Tony, Feb 9, 2004
    #8
  9. Tony <> wrote:

    > printf "no results\n";



    Why not just print() ?

    Done too much C programming?


    --
    Tad McClellan SGML consulting
    Perl programming
    Fort Worth, Texas
     
    Tad McClellan, Feb 9, 2004
    #9
  10. Tony

    gnari Guest

    "Tony" <> wrote in message
    news:...
    > 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
     
    gnari, Feb 9, 2004
    #10
  11. Tony

    Tony Guest

    very useful insights gnari

    thanks very much
     
    Tony, Feb 10, 2004
    #11
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. rp
    Replies:
    1
    Views:
    560
    red floyd
    Nov 10, 2011
  2. Paul Vudmaska

    dbi:mysql mysql has gone away

    Paul Vudmaska, Apr 21, 2004, in forum: Ruby
    Replies:
    0
    Views:
    120
    Paul Vudmaska
    Apr 21, 2004
  3. Jerome Hauss
    Replies:
    0
    Views:
    184
    Jerome Hauss
    Oct 13, 2004
  4. Xeno Campanoli
    Replies:
    16
    Views:
    278
    Martin DeMello
    Aug 25, 2005
  5. samasama
    Replies:
    4
    Views:
    130
    samasama
    Sep 20, 2006
Loading...

Share This Page