Perl and MySql - checking tables for column names

[bizactuator]

I had to take over code from someone that died.

They had this in the code:

    my @_mb = $dbh->tables;
    my $_tablesAffected = 0;
    foreach $_k (@_mb) {
        # Ran code in here...
    }

It appears the $dbh->tables is no longer working.

They were running through the tables in that foreach, pulling the tables in:

    my $_sql = "select * from $_k LIMIT 0";
    my $sth2 = $dbh->prepare($_sql);
    $sth2->execute();
    my @_mb2 = @{$sth2->{NAME}};
    foreach $_k2 (@_mb2) {
        # then looking for column names with username:

        if($_k2 =~ /username/i) {
            # do what they are looking for
        }
    next;
    }

So since that is no longer working, how can they run through the database like that, looking for the tables with the column they are looking for?

It is for people who want to change their username to something else, the programming programmed everything together by the username, because they said they would never allow changes, but they had so many requests, they changed their mind, so now, they have to be changed, so need to look through the tons and tons of tables, in case it is somewhere.

The server was upgraded and it quit working, I think it is because that $dbh->tables no longer works.

Can someone point to me on how to use the new catalog in that to make it work again?

[Corion]

The DBI documentation suggests using the catalog functions like ->table_info to retrieve information about the tables in your database. Maybe you can use that instead of ->tables.

If you tell us maybe something more about how ->tables fails with your database and the driver you use to connect to it, maybe there is something we can do to help you better. But without being able to reproduce your situation, some generic advice is all we can give.

[bizactuator]

Yeah, no errors, just nothing in the array... I put in debugging code, but no errors returned, just nothing. So, if I put it in the code, how does it output the response? For example, if I do it like this:

my @_mb = $dbh->table_info;
    my $_tablesAffected = 0;
    foreach $_k (@_mb) {
        # Ran code in here...
    }

What would the @_mb have it in? just table names? So that way I could loop through it like it already does:

  foreach $_k (@_mb) {
    my $_sql = "select * from $_k LIMIT 0";
    my $sth2 = $dbh->prepare($_sql);
    $sth2->execute();
    my @_mb2 = @{$sth2->{NAME}};
    foreach $_k2 (@_mb2) {
        # then looking for column names with username:

        if($_k2 =~ /username/i) {
            # do what they are looking for
        }
    next;
    }
  }

or is there a bunch of fields in the @_mb array? Thx, -Richard

[Corion]

Maybe now is a good moment to go through the Basic debugging checklist. Consider step 2, printing the contents of your variables instead of guessing at what they might contain, and step 4, using Data::Dumper.

Also, you haven't told us what database server and what version you are using, and what ->table_info returns. From your usage of LIMIT 0, I assume that it is some kind of MySQL but I prefer not to guess.

[kschwab]

It wouldn't be portable to databases other than mysql, but you can query directly for table names:

SELECT table_name FROM information_schema.tables;

And, then to get column names for a specific table

SELECT column_name FROM information_schema.columns WHERE table_schema 
+= 'your_database' AND table_name = 'your_table';