Re: DBI returning mysql columns in strange order.

[dragonchild]

I am shocked that no-one suggested the obvious solution, which also happens to be the better solution in the long run.

Change the SELECT statement to specify what you're looking for!

This would require some rewriting and quite a lot of rethinking the design, but it would be more maintainable and extensible. If you want, I can provide an example of how I would write this snippet using structured design principles.

[mpeppler]

Indeed. select * from ... is really bad form (although expedient when you are in a hurry, I'll admit).

That said the fundamental problem of the OP's code, as has been noted in one of the previous responses, is the reliance on the order of hash keys - I'd like to stress that one should retrieve column names via $sth->{NAME} (or the NAME_lc/NAME_uc variations) as that will correctly list all the column names in the proper order.

Michael

[neilwatson]

There is a method to my madness. I choose select * from ... because the code is meant to work on a collection of tables whose columns are all different.

My application involved having a group of small tables. Then I would have this code query and display any table that was selected. I'm trying to be lazy^TM

[mpeppler]

Well - as long as you are aware of the pitfalls of that technique...

Michael

[EdwardG]

With respect, this is not a good enough reason to use SELECT * - I'll try to explain why.

The main danger is that at some future time, someone (perhaps not you) will add a BLOB column to one of your tables. If this happens, your code as is could blithely start dragging this data across a network. I don't know MySQL so well, but in SQL Server this could be anything up to about 2GB, perhaps more. Plenty enough to provoke the wrath of most Network Administrators.

You might be lucky - your code could fail due to some exceeded limit, or you might be unlucky - your code could work, and quietly start a mammoth data transfer.

If you really have your heart set on your current approach, you might want to consider thinking about a maximum limit to the amount of data returned in a column, and then explicitly set this limit with code similar to this:

    $dbh->{LongReadLen}=1024*200;  # Max size of column data
    $dbh->{LongTruncOk}=0;         # Not OK to truncate data

[jdtoronto]

Yes, that is an obvious way, but even then not necessarilly if you retrieve using a HASHREF.

my $select = qq~SELECT field1 field2 field3 FROM $ref;~;
my $sth = $dbh->prepare( $select );
$sth->execute();
my ($field1, $field2, $field3) = $sth->fetchrow_array;

Will get things EXACTLY where you want them, so would

my @inarray = $sth->fetchrow_array();

Or the alternative of having DBI return and arrayref.

But is the idea fo specifying the specific fields extensible and maintainable? My logic would be that if the table structure changes then wildcard of fields does not need to be changed. If you use the HASHREF methods of collecting the data from DBI then you only need be concerned about the actual fields when you look into the HASH for the values.

Of course I stand to be corrected on this, but I would think that having to change the code as well as the SQL is less maintainable than just having to change the code.

jdtoronto

Just like him: All opinions are purely mine and all code is untested, unless otherwise specified

[dragonchild]

Please stand corrected. *grins*

sub display_table
{
    my ($dbh, $table_name, $columns, $is_ordered) = @_;

    my $sql = 'SELECT ';
    $sql .= join ',', @$columns;
    $sql .= "\n  FROM $table_name";
    $sql .= "\n ORDER BY " . join(',', 1 .. @$columns)
        if $is_ordered;

    print "$sql\n";

    my $sth = $dbh->prepare_cached($sql)
        or die "Cannot prepare '$sql':\n" . $dbh->errstr;

    $sth->execute
        or die "Cannot execute '$sql':n" . $dbh->errstr;

    my $values = $sth->fetchall_arrayref;

    $sth->finish;

    return $values;
}

[mpeppler]

That's certainly a valuable technique.

Unfortunately this breaks down if you have low-level code that executes stored procedures. While the caller will (or should) know what result sets and columns will be coming back, the code that actually executes the proc won't.

For example, I have a system that is table-driven where each database call is described by a small auto-loaded perl subroutine. The higher level code calls this subroutine with appropriate parameters, and then the low-level "execute this request" code runs and fetches all the data. Admittedly the low-level code doesn't have to know about the column names, but it still fetches that information (usually for debugging/trace purposes).

Michael

PS. I'm not disagreeing with you - just pointing out that (as usual) there are exceptions :-)