Database Action

1. Overview

In Mitra, database actions allow you to execute INSERT, UPDATE, and DELETE operations directly on database records using components like labels, tables, and lists.

2. How to Add a Database Action

• Select the Component: Navigate to the desired component and go to the "Interactions" area.

• Choose the Interaction: Select the "Database Action" option. You can either create a new database action or select an existing one.

3. Configuring a Database Action

When configuring a database action, the interface presents the following elements:

• Data Dictionary: The right-hand panel displays the dictionary containing all available tables and columns for manipulation via INSERT, UPDATE, and DELETE commands.

• Filter and Input Variables: Contextual screen variables and configured input variables are accessible. These enable you to dynamically use screen-applied filters or user-provided input data directly in your database actions. For more information on variables, refer to the “SQL VIEW” documentation.

4. Testing the Script

After configuring your database action, test the script to ensure it works as expected. This involves verifying the DML (Data Manipulation Language) commands (INSERT, UPDATE, or DELETE) for correctness. Once testing is complete, confirm the action configuration, and the script will be activated for the component interactions.

5. Examples

5.1. Deleting Salespeople

Scenario: Configure a database action to delete salespeople directly from a list.

• Component: Salespeople list.

• Interaction: When clicking a "Delete" button, the database action executes the following script:

DELETE FROM CAD_1005 WHERE ID = :

Here, :ID_VENDEDOR is a variable capturing the ID of the selected salesperson in the list. Mitra's variable system ensures the delete is applied correctly.

5.2. Creating Salespeople from Inputs

Scenario: Add inputs on the screen (name, city, and salary), and when clicking the "Save" button, execute an INSERT based on these input values.

• Component: Name, city, and salary inputs.

• Interaction: When clicking the "Save" button, the database action executes the following script:

INSERT INTO CAD_1005 (ID, DESCR, FOTO_PERFIL, SALARIO)
SELECT (MAX(ID)+1) AS ID, :INPUT_2673, :INPUT_2689, :INPUT_2676
FROM

Variables like :INPUT_2673, :INPUT_2689, and :INPUT_2676 are populated with values from the screen inputs.

5.3. Task Update

Scenario: Update a task's status to "Completed" from a list of tasks, each with a "Complete" button.

• Component: Task list with a "Complete" button.

• Interaction: When clicking the "Complete" button, the database action executes the following script:

UPDATE CAD_1017 SET ID_STATUS = 2 WHERE ID = :

Here, :ID_TAREFA retrieves the ID of the selected task in the list.

5.4. DML Sequence

Scenario: Execute multiple DML commands within a single database action. For example, mark a task as completed and simultaneously register it in a "Completed Tasks" list.

• Component: A task list and a completed tasks list.

• Interaction: When clicking the "Complete" button, the database action executes the following scripts:

5.4.1. UPDATE

First, update the task's status to "Completed" in the original task list:

UPDATE CAD_1017 SET ID_STATUS = 2 WHERE ID = :

ID_TAREFA ensures the correct task is updated.

5.4.2. INSERT

Next, copy the task to the completed tasks list:

INSERT INTO CAD_1019 (ID, DESCR) 
  SELECT ID, DESCR FROM CAD_1017 WHERE ID = :

The task's details are copied from the original list. The variable :ID_TAREFA ensures the correct task is added to the completed tasks list.

This approach enables you to configure and execute multiple scripts within a single database action, ensuring they are performed in the defined sequence.