SQL Transactions

You can add standard transaction handling for SQL commands invoked in a mashup script or for Presto database mashables created in version 3.0 or later using these statements:

<sqlBeginTransaction>: opens a connection to the default datasource or the datasource specified in the statement and sets AutoCommit to false.

<sqlCommit>: sends a commit message to the default datasource or the datasource specified in the statement and closes the database connection.

<sqlRollback>: sends a rollback message to the default datasource or the datasource specified in the statement and closes the database connection. The Presto Server also automatically sends a rollback for any SQL invocation failures for the specified datasource within the bounds of the transaction.

Note:

The Presto Server does not support distributed transactions. These statement only affect the invocations for a specific datasource. All other statement are unaffected, including SQL commands to other datasources.

...
<sqlBeginTransaction name="hr"/>
<sqlUpdate name="hr" statement="insert into
dept(dept_id, name, region)
values('15', 'Analysis', 'SouthWest')"
outputvariable="$deptInsert"/>
<sqlUpdate name="hr" statement="insert into
jobs(job_id, title, dept, grade)
values('344', 'Analysis Manager', 'Analysis', 'G12')"
outputvariable="$jobInsert"/>
<sqlUpdate name="hr" statement="insert into
jobs(job_id, title, dept, grade)
values('345', 'Technical Advisor', 'Analysis', 'G15')"
outputvariable="$jobInsert"/>
<sqlCommit name="hr"/>
...

For a working sample mashup, see the DatabaseSample (sql.emml). See Mashup Samples for a list of Presto mashup samples and where to find them.