SQL Operation

Using transactions with SQL Operation

Using transactions in SQL is very usefull. Having the possibility to rollback in case an error occured is really interesting.

It is possible to use transactions easily in Stambia, with the SQL Operation process action.

This articles shows an example of how to do this in a process.

The SQL Operation Action

The use of transactions with SQL Operations is done through specific parameters.

The most commons are:

SQL Transaction Type

Used to indicate the type of the transaction.

Available types: COMMIT,NOCOMMIT,AUTOCOMMIT,AUTONOMOUS

SQL Transaction Name

Name of the transaction

To do multiple operations using the same transaction, the same SQL Transaction Name has to be put on each of the actions.

By default the actions have AUTOCOMMIT type. More informations on the types can be found in the Designer's help.

Example

Here is a little example in which we want to do some operations, and rollback everything if an error occured, or commit otherwise.

Process overview:

Overview

All the actions are on same transaction: T1 .

The three first ones are using NOCOMMIT Transaction Type.

The last one uses the COMMIT type, in order to send a commit after the action.

With this settings, all the actions will be executed within the same JDBC Connection without doing any commit, except the last one.

So, if everything worked during the process, the last action is executed and the commit done.

But if an error occured, the process will end with errors and stop, the Commit action will then not be executed, so no commit will be performed.

In case of a process ending with errors, the runtime is doing automatically a rollback. As all the other actions here have NOCOMMIT type, the last commit on which the rollback will be done is before the execution.

Example of execution:

error

Here one of the actions failed. A rollback will be executed, and the operations performed by the two successfull one's will not be saved.

Sql Operation and SQL_ACTION_TYPE

The SQL Operation action can be used in different ways.

You can use it to execute standalone DDL or DML SQL queries / statements. And you can also use it to link data through bindings.

This articles explains how the SQL_ACTION_TYPE parameter works, what are the different options for it and how to use it.

DDL_DML option

For executing standalone queries which you don't want to use the result, you have to use this parameter.

It is usefull if you want to execute an insert, delete, update, create table, etc and you don't need to reuse the result of the query / statement.

Example :

DDL DML

SELECT and UPDATE, INSERT, DELETE options

These options are used for bindings.

Select

The select option is used to execute a select query (set in the Expression Editor of the action) and bind the result to another action.

Example :

selectBind

The select is the source of the bind, and we use the result here to delete the row(s) returned by the select.

Update, Insert, Delete

These options have to be the target of a bind link.

You can then use the result of the bind in the query(ies) of the action. It can be used to delete, update or insert using the result of a select for example.

The syntax is :{column}:

Exemple for a delete :

deleteBind

The bind is from the select of the last example. Here, we are using the CUS_ID returned by the select query with :{CUS_ID}:.

Note, if your select contains 1000 rows, the delete will be executed for the 1000 rows, so make sure your select is correct to not lose data.