SQL Database

Stored Procedures

Stored procedures have a singe operation called Execute. This operation can return output parameters or a result set. A stored procedure that returns a result set can control a loop. Any mandatory parameters to the stored procedure must be mapped.

Output parameters that start with the at (@) symbol are returned once per call, while other parameters return a result set.

Table Type Database Object

This table shows the different types of standard SQL operations. Access to these operations are provided by table type database object.

SQL operation	Details
Create	Creates a record in a table. All fields with NOT NULL attribute are mandatory and must be mapped.
Update	Updates a record in a table. Primary key fields are mandatory and must be mapped. Any number of non-key fields can be mapped.
Delete	Deletes a record from database. Primary key fields are mandatory and must be mapped.
Get	Retrieves a single record from a table. Primary key fields are mandatory and must be mapped.
List	Retrieves one or more records from database based on the mapped fields. This operation can control a loop.

Use the Database widget wizard to view the database tables.

• Tables with no primary keys and typically views are listed but no transactions are available.

• Tables with no additional columns apart from the PK, the UPDATE transaction is not available.

Database operations can fail and, in this case, will provide an exit function similar to NOK handling for M3 APIs. The action taken is controlled by Exit Type parameter which can have these values:

• IGNORE means that the error is ignored.

• LOOP means that the current loop operation in progress is stopped.

• MAPPING means that the current mapping operation is stopped.