Performing DML operations using stored procedures

By default, the CloudIO platform performs direct DML operations on a table. However, you should always perform DML operations through an API or stored procedures instead of direct table updates, as they act as an additional security layer between the application and the table.

To use API or stored procedures,

1. While defining the datasource details, select the Use API checkbox.

2. In API name, enter the PL/SQL package name or the stored

procedure prefix for MySQL/MS SQL Server.

Note: To download the boilerplate code, in the upper right corner, from the Actions list, click PL/SQL spec.

The following stored procedures can be used to perform DML operations on a table:

Stored procedure	Description
POST_QUERY	Will be invoked if the Call Post Query checkbox is selected while defining datasource details. Refer Defining datasource details.
INSERT_ROW	Will be invoked when creating a new record. Note: There are some parameters that are IN OUT type. That is because they have either the Out Param or the Primary Key checkboxes selected while defining the attribute properties. When a parameter is IN OUT, then the values set in the procedure will be reflected in the user interface.
UPDATE_ROW	Will be invoked when updating an existing record
DELETE_ROW	Will be invoked when deleting a record
VALIDATE_ROW	Will be invoked on changing a value of a field on the user interface of which the Server Validate On Change property is enabled.
POST	Will be invoked in the PL/SQL table mode, where all the dirty records (Insert/Update/Delete) will be passed at once as a PL/SQL table.

Note: All the procedures except POST are called once for every record.

If you want to invoke an existing PL/SQL API, use the auto generated package as a wrapper package to call any existing APIs.

Note: Whenever a new parameter is added, or an existing parameter name or data type is changed, ensure that you download boilerplate code again and check if the parameters in the database are in the right sequence as in the boilerplate code.

• Do not re-compile the boilerplate code in the database once you have implemented the business logic, as it may override any changes that you may have added to the database.

• Use the downloaded code only for reference purposes.