Element "RelDBUpdateProcess"

Using RelDBUpdateProcess Element you can update any Relational Database as long as there is a suitable JDBC driver is available.

The Element should be placed into a ProcessForm or ProcessBlock Element.

Parameters

Connection Name

The element requires a Relational Database datasource as a reference. If there is no such a data source definition yet you can define it using Edit Data Sources dialog (Ctrl + F7). In a reference data source you have to specify the connection information only (Database Url, JDBC Driver, Username, Password). You don't need to define other properties i.e. Query since it will not be used.

Update Query (SQL)

In this property you can define a SQL statement to update Database. It must be a valid Update Query that your database supports. INSERT, DELETE, UPDATE even DROP.

You can also use SELECT queries that returns a resultset which contains only one record. For example to retrieve the last inserted row identity.

Example:

INSERT INTO TABLE abc (F1, F2) VALUES(V1, V2);

selecT @@IDENTITY AS v:ROW_ID;

Please note that the selecT statement above has a special meaning. See Retrieving Information from database with selecT.

Stored Procedures

In addition to regular update queries you can also make stored procedures calls in this parameter. The call definition must be enclosed within '{' and '}' characters.

Example:

{call my_stored_procedure} 

Dynamic Statements

You can use MScript functions to make your SQL statements dynamic. With MScript functions you can refer to the values of the input fields that exist in the enclosing ProcessBlock element and conditionally form your SQL statement.

Example:

In the figure below you see elements that are configured to delete the selected records from the "products" table.

See a more detailed example.

Completion Codes

This Process returns the following completion code