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
