Re: [OT] Inspired by closing comments from the UBB thread. (fwd)

[Daniel <db...@newsfactor.com>]

Nicely put Nick. There's already a Structured Query Language,
And there's an easy to use abstraction called DBI up on CPAN.
Feel free to use in application code thusly:

my $statement = qq~	
	SELECT field1, field2
	FROM table
	WHERE id = ?
~;
my $ref;
my $sth = $dbh->prepare($statement);
foreach my $question (@questions) {
	$sth->execute($question);
	$ref = $sth->fetchrow_hashref;
	$sth->finish;
	&display_data($ref);
}

At the end of the day you're gonna have a $dbh somewhere and it's gotta 
receive some SQL to be useful. Hide it where you want to, I'll put it 
real close to where the data is going to be used (unless the data needs 
to be used from many different access points in which case all that 
nasty :-) SQL goes into a OO module that understands how to provide:
my $handle = new foobar $dbh;
my $arrayref = $handle->gimme_foobar_data;
).

--
Daniel Bohling
NewsFactor Network


> 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 =>
>>$@);
>>

Re: [OT] Inspired by closing comments from the UBB thread. (fwd)

[Tim Bunce <Ti...@pobox.com>]

On Wed, Aug 01, 2001 at 05:29:10AM -0700, Daniel wrote:
> Nicely put Nick. There's already a Structured Query Language,
> And there's an easy to use abstraction called DBI up on CPAN.
> Feel free to use in application code thusly:
> 
> my $statement = qq~	
> 	SELECT field1, field2
> 	FROM table
> 	WHERE id = ?
> ~;
> my $ref;
> my $sth = $dbh->prepare($statement);
> foreach my $question (@questions) {
> 	$sth->execute($question);
> 	$ref = $sth->fetchrow_hashref;
> 	$sth->finish;
> 	&display_data($ref);
> }

Umm, these days I'd write loop that as:

  foreach my $question (@questions) {
  	&display_data( $dbh->selectrow_arrayref($sth, undef, $question) );
  }

:-)

Since ValueClick's been mentioned I'll point out that I now have the
task of exploring how to migrate all the embedded SQL code that Nick
mentioned from MySQL over to Oracle :-)  [Hi Nick!]

I'm not a big fan of heavy abstractions and I'm pretty comfortable
with how much of the code is structured, in general.

I'm hoping that a mixture of new DBD::Oracle and DBI features, possibly
a DBD::Oracle::mysql subclass, and a sprinkling of DBIx::AnyDBD will
prove sufficient.

Combining that with using Oracle's ODBC gateway to make MySQL tables
appear live within Oracle should enable a smooth migration without a
sharp 'big bang' transition.

Of course, all this is just theory at the moment.

Tim.