You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-dev@db.apache.org by Bryan Pendleton <bp...@amberpoint.com> on 2009/11/03 22:44:43 UTC
Are there any SQL statements that simply aren't supported in a procedure?
As part of https://issues.apache.org/jira/browse/DERBY-3062 I'm trying
to clarify the doc regarding the behavior of the
{ NO SQL | MODIFIES SQL DATA | CONTAINS SQL | READS SQL DATA }
clause. I've cleaned the text up somewhat, and will post a patch shortly,
but some of the text seems to imply that there exist certain SQL
statements which simply can't be executed from a procedure, no matter
what level of SQL support is asserted by the above clause:
MODIFIES SQL DATA
Indicates that the stored procedure can execute any SQL statement
except statements that are not supported in stored procedures.
I tried looking around in code and tests (particularly LangProceduresTest)
but wasn't able to figure out a conclusive answer:
Are there any SQL statements that are illegal in a procedure which
specifies MODIFIES SQL DATA?
thanks,
bryan
Re: Are there any SQL statements that simply aren't supported in a
procedure?
Posted by Rick Hillegas <Ri...@Sun.COM>.
Hi Bryan,
The Developer's Guide lists the restrictions here (I haven't run any
experiments to verify that the user documentation is correct):
http://db.apache.org/derby/docs/10.5/devguide/devguide-single.html#cdevspecial29620
Hope this helps,
-Rick
Bryan Pendleton wrote:
> As part of https://issues.apache.org/jira/browse/DERBY-3062 I'm trying
> to clarify the doc regarding the behavior of the
>
> { NO SQL | MODIFIES SQL DATA | CONTAINS SQL | READS SQL DATA }
>
> clause. I've cleaned the text up somewhat, and will post a patch shortly,
> but some of the text seems to imply that there exist certain SQL
> statements which simply can't be executed from a procedure, no matter
> what level of SQL support is asserted by the above clause:
>
> MODIFIES SQL DATA
> Indicates that the stored procedure can execute any SQL statement
> except statements that are not supported in stored procedures.
>
> I tried looking around in code and tests (particularly
> LangProceduresTest)
> but wasn't able to figure out a conclusive answer:
>
> Are there any SQL statements that are illegal in a procedure which
> specifies MODIFIES SQL DATA?
>
> thanks,
>
> bryan
>
Re: Are there any SQL statements that simply aren't supported in a
procedure?
Posted by Knut Anders Hatlen <Kn...@Sun.COM>.
Knut Anders Hatlen <Kn...@Sun.COM> writes:
> Bryan Pendleton <bp...@amberpoint.com> writes:
>
>> As part of https://issues.apache.org/jira/browse/DERBY-3062 I'm trying
>> to clarify the doc regarding the behavior of the
>>
>> { NO SQL | MODIFIES SQL DATA | CONTAINS SQL | READS SQL DATA }
>>
>> clause. I've cleaned the text up somewhat, and will post a patch shortly,
>> but some of the text seems to imply that there exist certain SQL
>> statements which simply can't be executed from a procedure, no matter
>> what level of SQL support is asserted by the above clause:
>>
>> MODIFIES SQL DATA
>> Indicates that the stored procedure can execute any SQL statement
>> except statements that are not supported in stored procedures.
>>
>> I tried looking around in code and tests (particularly LangProceduresTest)
>> but wasn't able to figure out a conclusive answer:
>>
>> Are there any SQL statements that are illegal in a procedure which
>> specifies MODIFIES SQL DATA?
>
> I'm vaguely remembering exceptions being thrown either by setting the
> schema or the isolation level from within a procedure. I'll have to
> check to be sure.
Sorry, I confused this with another issue I had seen. SET SCHEMA and SET
ISOLATION work fine in a procedure. What doesn't work, is
Connection.setAutoCommit(true), but since we don't have any SQL
statement for that operation, that doesn't really answer your question.
--
Knut Anders
Re: Are there any SQL statements that simply aren't supported in a
procedure?
Posted by Knut Anders Hatlen <Kn...@Sun.COM>.
Bryan Pendleton <bp...@amberpoint.com> writes:
> As part of https://issues.apache.org/jira/browse/DERBY-3062 I'm trying
> to clarify the doc regarding the behavior of the
>
> { NO SQL | MODIFIES SQL DATA | CONTAINS SQL | READS SQL DATA }
>
> clause. I've cleaned the text up somewhat, and will post a patch shortly,
> but some of the text seems to imply that there exist certain SQL
> statements which simply can't be executed from a procedure, no matter
> what level of SQL support is asserted by the above clause:
>
> MODIFIES SQL DATA
> Indicates that the stored procedure can execute any SQL statement
> except statements that are not supported in stored procedures.
>
> I tried looking around in code and tests (particularly LangProceduresTest)
> but wasn't able to figure out a conclusive answer:
>
> Are there any SQL statements that are illegal in a procedure which
> specifies MODIFIES SQL DATA?
I'm vaguely remembering exceptions being thrown either by setting the
schema or the isolation level from within a procedure. I'll have to
check to be sure.
--
Knut Anders