On Wed, Jun 04, 2003 at 12:22:19PM -0700, Jeff Zucker wrote:
> Tim Bunce wrote:
>
> >> 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 we're very much in the realms of inventing our own syntax here.
We're just using 'standard sql' as a guide for everyones sanity.
For that perspective, using placeholders as you propose is just fine :)
> >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?
No. I'm just saying I dislike "SELECT $cols FROM tbl1@"$dsn1""
Perhaps allow it but don't use it in examples :)
Also, "CONNECT TO $dsn AS $table" seems rather confused between
'levels'. If it's just setting up a connection why mention table name?
If I want to use 20 different tables in one db, do I need 20 separate
"CONNECT TO ..." statements? I would hope not.
So how about keeping "CONNECT TO" just for connections (I'll call
them links here as that's Oracle's terminology for connections to
remote databases):
CONNECT TO $dsn AS mylinkname
just sets up 'mylinkname' as an alias for the connection which can be used as
SELECT ... FROM [EMAIL PROTECTED]
and, if desired, also allow something like
CREATE SYNONYM foo_table_name AS [EMAIL PROTECTED]
(or some other syntax with the same effect) so
SELECT ... FROM foo_table_name
is same as saying
SELECT ... FROM [EMAIL PROTECTED]
?
[Note that CONNECT TO needs to handle username and password as well]
> It seems to me that making it a SQL statement allows for automating
> connections and processing from a file of SQL statements.
Yes, I agree. I was wearing my implementation hat :)
> >[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!)
Perhaps it's time to use a parser tool like Parse::RecDecent. Then
nested whatevers just wouldn't be a problem. You'd also get better
syntax error messages etc.
> >>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.
Sure, I see that. But how does that relate to SQL::Parser supporting
multiple data sources etc that the rest of your message is about?
> >Thank you!
>
> And thank *you* for providing such a elegant and extendable framework
> without which none of this would have been possible.
Thanks, but I'm just bumbling my way along one step at a time...
Tim.
