Nick,
Thanks for the comments. Actually, we use something like the example code
now and can do select from multiple tables (TABLES => ['table1', 'table2',
'table2 as someAlias']), can do inner and outer joins, order by clauses,
binding values, just about anything we want with straight SQL. Essentially,
our Database.pm delivers $dbh and the modules create their own $sth so what
we do and what you do probably isn't very far apart.
I was shocked at how much response the thread generated so I thought that
maybe a solution was warranted and just want to give something back. I still
think the solution I've outlined is not the best, but it may a good solution
for a lot of people.
Thanks everyone for the comments. I can see from the responses this
something everyone deals with everyday and that I not alone out here
wondering if my solution is the right one or not.
--Joe Breeden
--------------------------
Sent from my Outlook 2000 Wired Deskheld (www.microsoft.com)
> -----Original Message-----
> From: Nick Tonkin [mailto:[EMAIL PROTECTED]]
> Sent: Wednesday, August 01, 2001 4:15 PM
> To: Joe Breeden
> Cc: [EMAIL PROTECTED]
> Subject: RE: [OT] Inspired by closing comments from the UBB thread.
>
>
>
> 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
> >
> > --------------------------
> >
>
