Perl and MySql - checking tables for column names

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

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...
    }
[download]

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;
    }
[download]

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?

Comment on Perl and MySql - checking tables for column names Select or Download Code

Replies are listed 'Best First'.
Re: Perl and MySql - checking tables for column names by Corion (Patriarch) on Jan 23, 2017 at 08:56 UTC
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.	[reply] [d/l] [select]
Re^2: Perl and MySql - checking tables for column names by bizactuator (Sexton) on Jan 23, 2017 at 09:06 UTC
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... }` [download] 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; } }` [download] or is there a bunch of fields in the @_mb array? Thx, -Richard	[reply] [d/l] [select]
Re^3: Perl and MySql - checking tables for column names by Corion (Patriarch) on Jan 23, 2017 at 09:12 UTC
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.	[reply] [d/l] [select]
Re: Perl and MySql - checking tables for column names by kschwab (Vicar) on Jan 23, 2017 at 15:16 UTC
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;` [download] 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';` [download]	[reply] [d/l] [select]