11:50 p.m.
To be more clear, the parser will accept the existing syntax, but any toString of the
resulting command (such as what happens in Designer) would cause output to be in the
expected form.
Another approach to getting the update counts would be to introduce another reserved
variable, such as ROWCOUNT. Then something like "ROWS_UPDATED = INSERT ...;"
would get parsed into "INSERT ...; ROWS_UPDATED = VARIABLES.ROWCOUNT;". We
could add a validation to 7.1.1 that treats ROWCOUNT as reserved for forward compatibility
and then there is no potential of a subtle breaking change. ROWCOUNT could also be used
for other purposes, such as Oracle's SQL%rowcount that can also tell you the position
of the cursor for non-update statements.
----- "Steven Hawkins" <shawkins(a)redhat.com> wrote:
Hello all,
In looking at expanding our support for stored procedure handling in
our procedure language there are a couple of other cleanups that would
be good to discuss.
1. We should not allow the direct usage of stored procedures as
subqueries. Instead of "WHERE col = (EXEC foo())", we should use
"WHERE col = (SELECT * FROM (EXEC foo()) x)". I propose that we
deprecate this usage and allow the parser to automatically convert to
the latter syntax.
2. To be more concise/closer to standard syntax we should allow
dynamic SQL statements to have the form "EXECUTE [(STRING|IMMEDIATE)]
...". The choice to use the STRING keyword in 5.5 was due to not
having IMMEDIATE as a reserved word.
Our assignment syntax has several issues. I propose that the parser
continue to allow the existing syntax, but with the following changes.
3. The plsql assignment operator := will also be accepted. We do not
yet need to make this the preferred syntax, but it may be a good idea
as it disambiguates an assignment from the equality predicate boolean
expression.
4. We should deprecate the direct assignment of commands to the
variable. For example:
a = select ...;
b = insert into ...;
c = update ...;
d = delete ...;
The proper syntax for query expressions is a scalar subquery, thus "a
= select ...;" should be "a = (select ...);".
For nearly every other database, DML statements do not project the
rows updated. They also do have the possibility of providing a proper
result set, such as with the PostgreSQL/Oracle insert returning
clause. Both of these are reasons why you don't typically see syntax
such as "b = insert into...;". The more common method for getting the
update count is from an implicit variable. One possibility is to use
our existing "ROWS_UPDATED" as that implicit variable. So rather than
"ROWS_UPDATED = insert into ...; x = ROWS_UPDATED;", you would just
have "insert into ...; x = ROWS_UPDATED;". For most update procedures
this obviates the need to explicitly set the ROWS_UPDATED value as it
will be set implicitly by the last DML statement. However this could
potentially be a breaking change in an existing procedure, such as
"...; ROWS_UPDATED = ...; insert into ...; END". Another option is to
use more explicit syntax, like PostgreSQL, with "GET DIAGNOSTICS x =
ROWCOUNT"
This should also change the meaning of "e = exec foo...;". As per
TEIID-1237, what we would like is "e = exec foo...;" to be a command
statement with the side affect of setting e to the return value of the
procedure. This syntax would only be valid if the procedure has a
return parameter (currently it would be valid if the procedure has a
resultset that has only a single column or no result set and only one
out/inout or return parameter). The other change would be that the
this statement would also set the implicit return cursor to the result
set of the procedure if it has one. Since these would be potentially
breaking changes (albeit in very narrow circumstances) we could put a
validation in 7.1.1 that only allows that assignment if the procedure
returns a result value and if there is a subsequent command statement.
Otherwise we would have to use a slightly less intuitive syntax, like
SQL Server, such as "exec e = foo...;".
Any thoughts?
Steve