Hi,

At July 19, 2004, 18:22, Epstein, Ed wrote:

> I have thought about the same thing myself. As far as I know though, it
> cannot be done. A Stored procedure cannot commit the transaction its a part
> of. I would like to hear what other people say.

Here's what is mentioned in the SQL-2003 standards:

[Start quote...]
PSM-106 DBL:MCI-161, point 2.5, item 5, noted the following Language
Opportunity:
In Subclause 8.1, "<routine invocation>", the prohibitions on
SQL-transaction statements and SQL-connection statements in SQL-invoked
routines might be lifted, if a way can be found to make sure that
SQL-invoked routines end SQL-sessions and SQL-transactions that they
start, don't end SQL-transactions and SQL-sessions that they didn't
start, and don't switch SQL-connections without restoring the
SQL-connection with which they started.

PSM-107 Discussion of DBL:MCI-161, point 2.5, item 5, noted the
following Language Opportunity:

In Subclause 8.1, "<routine invocation>", the prohibitions on
SQL-transaction statements and SQL-connection statements in
SQL-invoked routines might be lifted by changing "SQL-connection
statement" to "SQL-connection statement and the implementation does
not support the execution of that SQL-statement in an invoked
SQL-routine that is a procedure" in each of the two rules that make
this prohibition, and making an appropriate entry in Appendix B,
"Implementation-defined elements", saying something like "It is
implementation-defined whether or not an SQL-implementation supports
the execution of SQL-transaction statements and/or SQL-connection
statements in an invoked SQL-routine; if it does so, then the effects
are implementation-defined."
[...End quote]

--
Best regards,
Daniel Rail
Senior System Engineer
ACCRA Group Inc. (www.accra.ca)
ACCRA Med Software Inc. (www.filopto.com)