Re: RFC: SQL::Preprocessor - a SQL filter for Perl
From: Dean Arnold (darnold_at_presicient.com)
Date: 07/26/04
- Next message: Michael Peppler: "Re: Drivers that support server-prepare-statements?"
- Previous message: Reinhard Pagitsch: "Re: DBD::Oracle debugging"
- In reply to: Tim Bunce: "Re: RFC: SQL::Preprocessor - a SQL filter for Perl"
- Next in thread: Jeff Zucker: "Re: RFC: SQL::Preprocessor - a SQL filter for Perl"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]
Date: Mon, 26 Jul 2004 01:20:36 -0700 To: Tim Bunce <Tim.Bunce@pobox.com>
Tim Bunce wrote:
> On Sun, Jul 25, 2004 at 01:31:51PM -0700, Dean Arnold wrote:
>
>>A couple years ago, someone posted an RFC and some
>>questions about building an Inline::SQL module. Alas,
>>while I was pretty enthused about it, it never
>>seemed to get off the ground.
>
>
> I don't see the point. What would be benefits be?
> I've yet to see a convincing argument that:
>
>
>> EXEC SQL DECLARE CURSOR mycursor AS
>> SELECT * FROM mytable FOR UPDATE;
>
>
> is better than:
>
> $mycursor = $dbh->prepare("SELECT * FROM mytable FOR UPDATE");
>
>
> There IS a good case for a functional interface to handle
> simple cases. But I don't think EXEC SQL is a step forward!
Yeah, I admit the prefix has always been an eyesore, but I've
been coming from the pragmatic direction of "what can I key on
to find a SQL statement" and still head in a direction similar to
existing implementations (I guess I've been fishing in the
COBOL/C/etc. pond too long).
>
> There are many DBIx::* modules competing for that niche but none
> have significant mind-share. (Party, I think, because they either
> try to do too much, or too little, or they're just plain odd.)
>
And many more that try to do away with SQL altogether...with
IMHO limited success (at least for non-trivial queries).
>
> Here's something to ponder:
>
> CONNECT "dbi:Driver:foo", "user", "pass";
>
> $id = 42;
> SELECT "foo, bar FROM table WHERE id = ", \$id, INTO \$foo, \$bar;
> print "foo=$foo, bar=$bar\n";
>
> SELECT "foo, bar FROM table", USING {
> print "foo=$_->{foo}, bar=$_->{bar}\n";
> };
>
> SELECT "foo, bar FROM table", INTO \$foo, \$bar, USING {
> print "foo=$foo, bar=$bar\n";
> };
>
> SELECT "* FROM mytable
> RETURNING LINEGRAPH(*), IMAGEMAP
> WHERE WIDTH=500 AND HEIGHT=500
> AND FORMAT='PNG'
> AND LOGO='myimage.png'
> AND X_AXIS='Date' AND Y_AXIS='Stock Price'
> AND MAPNAME='stockmap'
> AND SIGNATURE='GOWI Systems, Inc.'
> AND SHOWPOINTS=1 AND POINT='opencircle'",
> INTO \$chart, \$map;
>
> No source-code filtering required, just "plain" perl, albeit
> a little advanced in places.
I pondered that, but it somehow seems a bit crusty with those
quotes/double quotes/escapes in unexpected places (for a SQL hacker).
Plus (unfortunately), theres a few SQL and Perl introductory
keywords that collide (e.g., SELECT, DELETE) unless we enforce
case sensitivity...which probably isn't too much to ask for a bolt-on
like this. Then that EXEC SQL isn't needed. Hmmm...
However, I think (even assuming case sensitivity is applied) that a filter may still
be a needed (maybe combined with your function-to-keyword mapping approach).
If introductory keywords were keyed via a filter,
then all those quotes/escapes go away (assuming a common SQL statement terminator).
And, for the odd SQL variant, 'EXEC SQL' (or a similar keyword)
might remain a viable (tho optional) prefix, if there's a dbms that has
some sort of "abracadabra some statement".
Would implicit statement variables (e.g., $SQLSTATE, $SQLCODE, etc)
fit into your design ? And result data implicitly dumped into $_ or @_ ?
I guess eval { } would accomodate the dynamic SQL instance ?
As well as placeholders (tho perl interpolation may obviate that need, albeit at reduced
dbms performance).
>
> I'd be happy to see someone implement something along those lines.
> (If someone's interested I'll expand on the ideas implied above.)
>
> Tim.
>
I'm trying 8^/.
FWIW:
I've been implementing a clone of a report writer interface that essentially
does the SQL to DBI translation (not unlike dbish). In the process, I realized
that exposing the perlish underside might be valuable...which led me down the Filter path.
I guess I need to review what SQLJ is doing to see what (if anything) its doing
differently than its ancestors....
- Dean
- Next message: Michael Peppler: "Re: Drivers that support server-prepare-statements?"
- Previous message: Reinhard Pagitsch: "Re: DBD::Oracle debugging"
- In reply to: Tim Bunce: "Re: RFC: SQL::Preprocessor - a SQL filter for Perl"
- Next in thread: Jeff Zucker: "Re: RFC: SQL::Preprocessor - a SQL filter for Perl"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]
Relevant Pages
|
