Since you asked, my opinion is that what you describe would not be
useful. Primarily for the reason pointed out already by a number of people
-- lack of flexibility. Most, if not all, database servers accept highly
customizable performance params to a query, and most even moderately
evolved applications make use of SQL queries that are significantly
more complex than a single-where-clause select.
At ValueClick we built a wrapper module (DB.pm :) that delivered a $dbh
into the API, handling everything up to that point with minimal
fuss. From that point on, some standard things were collected in a utility
class, but most modules created their own $sth, usually with bind
variables, with SQL statements nicely formatted in the source using a here
doc ... it was highly manageable and functional, and most of all it was
flexible. Not all applications are fast-developing, but my experience is
that it pays to develop as if yours were ... rapid access to tweak the SQL
fetching data into the application is very desirable, IMHO.
The point is not that you can't abstract it all away as you show in your
code below, it's that by the time you have covered all eventualities
(sorts, groups, selects from multiple tables, et al.), your interface is
so complicated you are basically paraphrasing the SQL in some new language
of your invention. And that, if I am not mistaken, is the purpose of SQL
in the first place!
There is such a thing as over-abstraction, IMHO, and having played with
this a lot, I have found that this type of effort would be such.
Hope this helps,
~~~~~~~~~~~
Nick Tonkin
On Wed, 1 Aug 2001, Joe Breeden wrote:
> Woooie!?!
>
> I didn't expect the firestorm this post would generate. From what I hear
> people are either embedding SQL or writing their own utility module to
> essentially do something along the line of:
>
> $s->StartDBI ( DSN => 'somedsn_pointer') ;
> eval {
> $s->SelectSQL ( NAME => 'sql_select',
> TABLE => 'sometable',
> FIELDS => ['field1', 'field2', 'field3'],
> WHERE => 'field1=?',
> VALUES => $some_value_for_field1);
> while ( my $return = $s->SQLGetArray( NAME => 'sql_select')) {
> #do something $return - maybe complete a template object?
> }
> };
> $s->EndDBI ( DSN => 'somedsn_pointer', QUERIES => 'sql_select', RESULTS =>
> $@);
>
> Where the different calls do the things hinted at in their name (i.e.
> StartDBI opens the DSN and connects to the database in question, SelectSQL
> would prepare the SQL select statement and execute it via DBI). This allows
> the us to pass a native Perl structure which is reformatted to work with
> DBI. We also get back scalars, arrays, or hashes that are easy to work with.
> This is what we do here where I work. I still consider this embedded SQL
> because a change to the table or even to the server could cause the program
> to break in a lot of places. I think what I had in mind was some way to put
> this type of processing into a layer where all the SQL related items are
> essentially in a template file somewhere maybe a SQL::Template type thingy.
>
> If this is something that people feel would be a worthwhile endeavor, let me
> know and maybe when there's have a little free time in the Fall one could
> write a CPAN module that has this functionality.
>
> We had the conversation awhile back about adding redundant and unnecessary
> crap to CPAN and I want to make sure something like this would be a good
> thing or not.
>
> Thanks,
>
> --Joe Breeden
>
> --------------------------
>
