Re: Topics in Perl Programming: Table-Mutation Tolerant Database Fetches with DBI

[chipmunk]

I think the more apropos question would be:

How does one write SQL statements such that they can work without modification in the face of labeling, destructive, or positional field mutations?

If you can do that, I would argue that you don't have to worry about the Perl/DBI code.

[jreades]

Yes, because it's probably working by mind control.

Uh, more seriously, the only way that you could reliably (and this probably needs several levels of qualification) avoid this breaking your code would be to interrogate the database prior to each select (and this still wouldn't save you in the event that the database is changed in the time between your first 'interrogation' select and your second 'data' select).

What you'd need to do is either:

1. Use DBI to return a list of the table's field names (this would help you determine if your hashref query would break) -- I'm not sure if this is even possible, but the $dbh->table_info looks like it might offer something of value
2. Create a table of 'meta' information -- one whose field names might be something along the lines of table_name, column_name, column_type and so on. If you are writing a complete interface for an extensible system then this would probably be a much better way to go because you can force people to use your scripts to modify the tables and, at the same time, update the meta information in the meta table. That way, your scripts could turn to a reliable source for information about the tables they are querying while still permitting the tables to change.

Obviously, I lean towards the latter solution, but it requires that you be able to restrict modifications to the tables to the tools that you provide.

[repson]

You could use idea 1 and use the sql statement 'describe table' then you could checksum that output and if the checksum changes, die and don't run until the program is updated. This could apply if other people are likely to change column names and you don't want your program to try to run in an unknown situation.

[Dominus]

I think that my example shows that that won't always help.

[twotone]

Assuming you have already prepared and executed statement handle $sth, $sth->{NAME} will return an array ref of the column names, so

my @columns = @{$sth->{NAME}};

gives you an array of your column names.