in reply to Code factory

First thing I see wrong is the use of SELECT *. Don't do that. It's slow. It hurts maintainability. It was meant for interactive use, and has no buisness being in a program.

As for your actual question, I see two ways of doing this. Either create a closure and insert it into the main:: package space (or your current package, as the case may be), or create a subroutine which takes in SQL and a list of params for $sth->execute() and have it return the hashref. The closure version is a bit more advanced, but I suspect it will be more efficent. An example of using closures for this is in the Camel on pages 338-339.

----
I wanted to explore how Perl's closures can be manipulated, and ended up creating an object system by accident.
-- Schemer

Note: All code is untested, unless otherwise stated

Replies are listed 'Best First'.
Re: Code factory
by tadman (Prior) on Jul 07, 2003 at 18:15 UTC
    Everyone busts on SELECT * as if it eats babies for breakfast and feasts on the blood of virgins in the afternoon. Come on! It might be a bit lazy, but if used carefully, it's not always that evil.

    In this specific example, the most data you're going to get is a single row, so SELECT * isn't quite as unruly as you'd expect. It's probably not going to return ten thousand columns. Granted, one should only request what they're looking for, but when you're not sure, you might as well just snatch it all than be caught short later. Memory today is measured in megabytes, not kilobytes, so you can be a little more laisez-faire about these things.

    Additionally, this is all put into a HASH, so, even if the columns are re-ordered, there's no real risk of damage to the program, provided all the requisite columns are still there. This, of course, is not guaranteed, so one must be careful about column changes, and test code thoroughly.

    I agree an unconditional SELECT * where you do combine that with a bind_param_array is playing with fire, but this code isn't quite that risky.
[reply]
      If someone changes the name of a column without changing the code, and you have that column listed in the select, you will get an error message right away. If you select * and put it in a hashref, you might never get an error message at all from that situation. You'd just wonder why "last_name" was empty on all your reports for the last few months.
[reply]
      Granted, one should only request what they're looking for, but when you're not sure, you might as well just snatch it all than be caught short later.

      Wrong. If you're not sure about the column names you are supposed to be getting from a table, you need to look it up and be sure before you "finish" writing the perl script (actually, before you code beyond the line that specifies the text of the query statement). If necessary, use additional queries in the perl script to look up the "meta-data" provided by the database server -- the stuff that describes the user tables -- to figure out column names and data types. (But that should never really be necessary, unless you're actually writing a tool specifically to probe a database schema.)

      I have seen a number of my colleagues get burned (and I've been burned after some of them left our shop) because someone added a column to a table, and suddenly their logic for handling the results of "select * ..." was broken in "mysterious, incomprehensible" ways.

[reply]
        Yeah, I can see your point, though using a hashref is better in this situation than an array. Eek, an array could put all the right data in all the wrong places.
[reply]
Re: Re: Code factory
by Anonymous Monk on Jul 08, 2003 at 18:28 UTC
    A note on select *. In some db's, you can do subselects with select * that increase maintainability. You can get things like: 
    select
 drink.id, drink.name
from
 ( select * from beverage where type = ? ) drink

    [download]
    now mind you, it may not be as efficient as explicitly doign every single column, but if the sub query produces negligible data-size difference (in bytes) then you can definitely get away w/ it and have virtually no performance difference.

    Granted, this query can be rewritten so that it doesn't use a subquery, but subqueries are a feature that is needed sometimes. This is just merely an example showing only the features of a subselect and select * :)

[reply]
[d/l]

In Section Seekers of Perl Wisdom