Reading a MySQL table into an array

davies has asked for the wisdom of the Perl Monks concerning the following question:

Dearly beloved brethren,

In my personal pilgrimage to enlightenment, I am trying to convert a VB6 programme to Perl. This programme reads Excel files, writes them to MySQL, hacks the database around and produces more Excel files. Because Excel and VB6 use different connections to MySQL, I cannot make the final output tables (that are to be passed from MySQL to Excel) temporary files, because the Excel connection would be unable to see them. However, MessWare crashes far more often than I like. When it does this, it may leave tables on the database that are no use to monk or beast. I therefore want some code available that reads all the database tables, compares them to a list, and deletes them if they are not in the list.

Now, I know how to read data from a MySQL table and put it into a Tk listbox. This was taken from one of the man pages or docs, and works perfectly in a variety of situations. My plan, therefore, was thus:
1) Create a table listing the tables that should exist (including itself!).
2) Read the list of tables that actually exist into an array.
3) Run through the array, comparing each table to the table of table names, deleting the table if it isn’t in the table of tables.
(1) is easy and complete. But (2) has got me stumped. I get either one table name only, or the right number of tables, but all called 1. I have created a module called SQL, which gets called from any number of other modules. Strict, warnings and diagnostics are in every file. The relevant parts of the SQL module are:

package SQL;
use DBI;

our $dbh;
our $sth;

sub xqt
{
    my $sCommand = shift;
    $sth         = $dbh->prepare($sCommand);
    $sth->execute ();
}
[download]

The following code produces just the last table name:

use SQL;

my @tables;
my $temp;

SQL::Connect "debloat", "localhost";
SQL::xqt "SHOW TABLES";
my $i = 0;
while ($temp = $SQL::sth->fetchrow_arrayref)
{
    $tables[$i] = $$temp[$i];
}

foreach (@tables)
{
    print "$_\n";
}
[download]

This produces a list of "1"

use SQL;
my @tables;

SQL::Connect "debloat", "localhost";
SQL::xqt "SHOW TABLES";
my $i = 0;
while (my @ary = $SQL::sth->fetchrow_array ())
{
    $tables[$i] = @ary;
    $i++;
}

foreach (@tables)
{
    print "$_\n";
}
[download]

I find it hard to believe that this is a complex problem, but it’s doing my hubris no good to realise that it’s too complex for me. I don’t know if it’s connected, but I’d be extra grateful if some kind soul would clear up my incomprehension on @ary. Mummy always told me that a Perl variable starting with @ was an array. However, in the code I have that populates listboxes correctly, @ary seems to work as a scalar. This code appears within the SQL module:

sub PopList
{
    my $cboList    = shift(@_);
    my $sCommand   = shift(@_);
    xqt $sCommand;
    while (my @ary = $sth->fetchrow_array ())
    {
        $cboList->insert('end', @ary);
    }
}

sub PopAgent
{
    my $cboList    = shift(@_);
    PopList $cboList, "SELECT * FROM agent ORDER BY agent"
}
[download]

I have tried several solutions involving $ary[$i] and similar constructs, but without useful results. This isn’t urgent, except that the brick walls of my hermitage are starting to show damage from me beating my head against them.

TIA and, as Dave Allen used to say, “may your God go with you”.

John Davies

Update: My system configuration ~~is in my scratchpad~~. Update2: WAS in my scratchpad. I didn't think it was relevant, and so it proved. But it's in my private scratchpad if anyone REALLY needs it. /update2. /update

READMORE tags added by Arunbear

Comment on Reading a MySQL table into an array Select or Download Code

Replies are listed 'Best First'.
Re: Reading a MySQL table into an array by rnahi (Curate) on Jul 30, 2005 at 11:59 UTC
It seems to me that you are : going into an unnecessarily complex path mixing up instructions to deal with simple arrays and bi-dimensional ones. A simpler solution should be: `my $tables = $dbh->selectcol_arrayref("SHOW_TABLES") or die $DBI::errstr;` [download] This one, and many more are shown in DBI Recipes. Be sure to understand the various idioms before using them. One introduction to what to get from a database is shown in The fine art of database programming. And, don't forget to "use strict" and warnings.	[reply] [d/l]
Re^2: Reading a MySQL table into an array by rvosa (Curate) on Jul 30, 2005 at 15:25 UTC
`use SQL; my @tables; my $temp; SQL::Connect "debloat", "localhost"; SQL::xqt "SHOW TABLES"; my $i = 0; while ($temp = $SQL::sth->fetchrow_arrayref){ $tables[$i] = $$temp[$i]; } foreach (@tables) { print "$_\n"; }` [download] If this is the exact same code you're using it would seem that you only get one entry because you don't update $i++.	[reply] [d/l]
Re^3: Reading a MySQL table into an array by davies (Monsignor) on Jul 30, 2005 at 15:41 UTC
Sorry - I was trying to cut my code down to the minimum & cut too much! I'll check this out (as I said in another reply, my intention is to understand), but I have deleted some of my code after following RNahi's advice and getting a working solution. Regards, John Davies	[reply]
Re^2: Reading a MySQL table into an array by davies (Monsignor) on Jul 30, 2005 at 15:23 UTC
Thanks. I couldn't immediately see how to do what I want with your solution, but the DBI recipes reference gave me everything I needed. I'll keep trying with your advice, though, as the purpose is to understand (the live DB & VB front end has been supporting about 20 users for about 18 months). I'm still rather mixed up about the array instructions, but I expect that I'll be able to sort myself out with more reading. I am using strict, warnings and diagnostics in all my files. If I didn't mention it in my somewhat rambling post, my apologies. Regards, John Davies	[reply]