I'm keeping this on dbi-dev pro tem. Greg - I suggest you ask the
question on dbi-users.
On Tue, 8 Mar 2005 11:01:48 +0000, Tim Bunce <[EMAIL PROTECTED]> wrote:
> On Mon, Mar 07, 2005 at 11:47:32PM -0000, Greg Sabino Mullane wrote:
> > > While I don't have any particular objections, doesn't
> > > Pg (and most other DBMSs supporting savepoints) have
> > > SQL syntax to implement them ? If so, isn't just
> > >
> > > $dbh->do(<savepoint-sql>)
> > >
> > > sufficient ?
> >
> > Sure, but they also have syntax to implement BEGIN and ROLLBACK.
It isn't wholly clear why we have $dbh->commit an $dbh->rollback; both
could be simulated with $dbh->do on most databases. However, there is
some minor performance advantage on some systems. For example, IBM
Informix Dynamic Server (IDS) recognizes those statements in ESQL/C
and the SQLI protocol used to transfer commands between client code
(such as Perl + DBI) and the server sends a two byte command for
ROLLBACK instead of sending the string. Further, the two byte command
does not have to be parsed and executed - so there is a small, but
barely measurable, performance benefit to using the $dbh->rollback
statement.
> > I see the savepoints as an extension of the same sort of
> > transaction processing the DBI dos there. Certainly, drivers are
> > welcome to implementit any way they want (including allowing
> > savepoints via "do") but the advantages include being able to
> > track the savepoints closely, even allowing things like returning
> > an array of the current savepoints to the application. It also
> > abstracts the savepoint manipulation away from knowing the exact
> > SQL for your particular DBMS, one of the goals of DBI after all. :)
>
> I'd be interested to see how varied the syntax is between databases.
The SQL 2003 (1999) syntax is in section 16 of ISO/IEC 9075-2:2003:
16 Transaction management
16.1 <start transaction statement>
Start an SQL-transaction and set its characteristics.
<start transaction statement> ::= START TRANSACTION [
<transaction mode> [ { <comma> <transaction mode> }...] ]
<transaction mode> ::= <isolation level> | <transaction access
mode> | <diagnostics size>
<transaction access mode> ::= READ ONLY | READ WRITE
<isolation level> ::= ISOLATION LEVEL <level of isolation>
<level of isolation> ::= READ UNCOMMITTED | READ COMMITTED |
REPEATABLE READ | SERIALIZABLE
<diagnostics size> ::= DIAGNOSTICS SIZE <number of conditions>
<number of conditions> ::= <simple value specification>
16.2 <set transaction statement>
Set the characteristics of the next SQL-transaction for the SQL-agent.
NOTE 402 - This statement has no effect on any SQL-transactions
subsequent to the next SQL-transaction.
<set transaction statement> ::= SET [ LOCAL ] <transaction
characteristics>
<transaction characteristics> ::= TRANSACTION <transaction mode>
[ { <comma> <transaction mode> }... ]
16.3 <set constraints mode statement>
If an SQL-transaction is currently active, then set the constraint
mode for that SQL-transaction in the current SQL-session. If no
SQL-transaction is currently active, then set the constraint mode for
the next SQL-transaction in the current SQL-session for the SQL-agent.
NOTE 404 â This statement has no effect on any SQL-transactions
subsequent to this SQL-transaction.
<set constraints mode statement> ::= SET CONSTRAINTS <constraint
name list> { DEFERRED | IMMEDIATE }
<constraint name list> ::= ALL | <constraint name> [ { <comma>
<constraint name> }... ]
16.4 <savepoint statement>
Establish a savepoint.
<savepoint statement> ::= SAVEPOINT <savepoint specifier>
<savepoint specifier> ::= <savepoint name>
16.5 <release savepoint statement>
Destroy a savepoint.
<release savepoint statement> ::= RELEASE SAVEPOINT <savepoint specifier>
16.6 <commit statement>
Terminate the current SQL-transaction with commit.
<commit statement> ::= COMMIT [ WORK ] [ AND [ NO ] CHAIN ]
16.7 <rollback statement>
Terminate the current SQL-transaction with rollback, or rollback all
actions affecting SQL-data and/or schemas since the establishment of a
savepoint.
<rollback statement> ::= ROLLBACK [ WORK ] [ AND [ NO ] CHAIN ]
[ <savepoint clause> ]
<savepoint clause> ::= TO SAVEPOINT <savepoint specifier>
(If you want to see the hyperlinked HTML from which I cut'n'pasted the
syntax, tell me. Tools and all available on request.)
As it happens, IDS does not yet support savepoints; it's on my list of
things I'm looking at for a future release. But if we implement them,
it will be using the standard syntax first, and any IBM proprietary
variations second.
> I'd also be interested to see a proposal for an API along with some
> real-world examples.
>
> My position is that I'm open to being persuaded, but you'll have
> to put in the leg work. The best place for that at this stage is
> dbi-users as I'd like you to be able to show wider support for the
> proposals from the user community.
>
> Tim.
>
--
Jonathan Leffler <[EMAIL PROTECTED]> #include <disclaimer.h>
Guardian of DBD::Informix - v2003.04 - http://dbi.perl.org
"I don't suffer from insanity - I enjoy every minute of it."
