Perl - DBI table_info change?

[David Bandel]

On 3/4/07, altendew <andrew at shiftcode.com> wrote:
>
> I was running my nightly mysqlhotcopy backup, when it gave me an error.
>
> mysqlhotcopy has a line where it grabs all the tables from a certain
> database.
>
> for example:
> @tables = $_dbh->tables();
>
> now this is just suppost to return all the tables of the current database.
> The proble is it returns the database name as a suffix to all the tables.
>
> for example it used to display like this:
> `table1`
> `table2`
>
> not it does this
> `db`.`table1`
> `db`.`table2`
>
> Why would it change all of a sudden because now I get this error:
> Invalid db.table name 'db.db`.`table1' at /usr/bin/mysqlhotcopy line 856.

Obviously.  You'll have to ask the author of DBI why he changed what
he did.  Meanwhile, the fix is to patch mysqlhotcopy to work.  The fix
is simple, just loop through filling an array (you're probably filling
a scalar now), then just use the array's [1] scalar and forget the [0]
scalar which is what's giving you fits:

instead of this:
    my @cats;
    my $sth1 = $dbh->prepare("select distinct category from dbkdata
      where subjsec = '$subjsec' and category != '' order by date desc");
     $sth1->execute;
     if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr;}
    while ($cat = $sth1->fetchrow){
        push @cats,$cat;
     }

do this:
     my @cats;
     my $sth1 = $dbh->prepare("select distinct category,date from dbkdata
      where subjsec = '$subjsec' and category != '' order by date desc");
     $sth1->execute;
     if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr;}
    while (my @ary = $sth1->fetchrow){
       $cat=$ary[0];
        push @cats,$cat;
     }

In the first I was just grabbing category, but when I added the order
by, the select distinct failed.  So I had to add the order by column
to the select.  I didn't want to use the date, so instead of having
fetchrow grab a scalar because it was one value, I had it fill an
array and only used the first array value (ary[0]).   You'll use the
second array value (ary[1]).

Note:  if you're not the author of mysqlhotcopy, you might want to see
if the author already has fixed this.

HTH,

David A. Bandel
-- 
Focus on the dream, not the competition.
            - Nemesis Air Racing Team motto