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

In reply to Reading a MySQL table into an array by davies

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.