Re: RFC: SQL Extensions for SQL::Statement [Long]

[Jeff Zucker]

Tim Bunce wrote:

I'd like to be able to refer to existing dbh's as well.
I'd also like to be able to treat an existing $sth as a 'table'.
Yep.

     SELECT $cols FROM tbl1@"$dsn1" ... JOIN tbl2@"$dsn2" ...
   

Both are a little ugly and prone to nested quote problems.

Ew, nested quote problems, the bane of my existence.  I guess I was 
seeing these as shortcuts for simple DSNs (disallowing nested quotes) 
and figuring more complex DSNs would be put in as placeholders 
(although, is allowing the dsn to be a placeholder "SELECT foo from 
[EMAIL PROTECTED] WHERE  ..." a violation of the basic idea that placeholders are 
for values, not structure ... it is, but an awfully handy one in this case).

I think some form of naming would be handy:

        $dbh->{sql_sources} = {
            foo => 'dbi:Driver:dbname", # qualify with table name
            bar => $dbh,                # qualify with table name
            baz => $sth,                # implies select * from itself
        };
then:
      SELECT $cols FROM [EMAIL PROTECTED] JOIN [EMAIL PROTECTED] ...
Well that hash will certainly exist and be addressable, but are you 
saying you don't like the  "CONNECT TO $dsn AS $table" syntax?  It seems 
to me that making it a SQL statement allows for automating connections 
and processing from a file of SQL statements.

[FYI, I'm rather fond of the nested select syntax used by Oracle,
mysql v4, and others:
   SELECT foo, bar FROM (SELECT ...) table_alias WHERE ... 

I love that syntax too and it's quite powerful.  I'm not sure I'm clever 
enough to be able to handle that with SQL::Parser yet though. since 
nested parens are an even worse bane than nested quotes.  (Patches and 
co-authors welcome!)

"CREATE INTO TABLE" is a new one on me. I think "CREATE TABLE ... AS SELECT"
is more common. Are you specifically choosing non-standard syntax?
Nope, I'll go with the standard whenever I can.  I forget exactly why I 
chose that one :-).  I'll go back and review my thinking.

For calling temporary & stored procedures, functions, & modules
---------------------------------------------------------------
    my $rv = $dbh->do(            # Run a procedure or function
        "CALL $procedure($args)"
    );
    print $dbh->selectrow_array(    # print results of a function or
        "SELECT function($args)"    # procedure (no FROM or WHERE clause)
    );
I'm not really sure what you're saying here, for those two.

Well the first is basically the way other stored procedures work isn't 
it, although maybe I should  use BEGIN ... END instead.  And the second 
is for getting the results of a function without bind_param 
jiggery-pokery.  I borrowed it form Postgres  -- it allows a truncated 
SELECT with no FROM clause as a way of calling functions.

    my $sth=$dbh->prepare(
        "SELECT $cols FROM @? WHERE ...",
        {},
        {format=>'AoA',source=>$data_array_ref}
    );
$data_array_ref
should be able to be an $sth.
I guess I rejected that when I first tried it because it seemed confused 
(an sth as a parameter to an sth), but it could be a very easy way to 
get nested subqueries without the parsing headache since the two sth's 
would be parsed separately.

Thank you!
 

And thank *you* for providing such a elegant and extendable framework 
without which none of this would have been possible.

--
Jeff