How to Call a Stored Procedure in MySQL

Introduction

By setting up a sample table and procedure in MySQL, you can automate the insertion of records into your database directly from a Joget process. This approach is useful for automating database operations without manual intervention.

How does it work?

To implement this in your Joget workflow, follow these steps:

Create a Sample Table

Run the following to create a temporary table in the database to observe the changes.

CREATE TABLE IF NOT EXISTS `app_fd_demo` (
  `id` varchar(255) NOT NULL,
  `dateCreated` datetime DEFAULT NULL,
  `dateModified` datetime DEFAULT NULL,
  `c_message` longtext,
  PRIMARY KEY (`id`)
)

Prepare the stored procedure

Next, create a stored procedure named jogetaddrecord. This procedure will insert a new record into the app_fd_demo table each time it is called. Use the following SQL code:

DELIMITER //
CREATE PROCEDURE jogetaddrecord(IN inputParam VARCHAR(255))
BEGIN
    INSERT INTO app_fd_demo VALUES (now(), now(), now(), inputParam);
END //
DELIMITER ;

Calling the stored procedure in Joget DX8

In Joget Workflow, you can call this stored procedure from a process using the SQL Query Tool. Map the tool to the process, and add the following SQL command to execute the procedure:

call jogetaddrecord("hello");

Observe database change

After running the process in Joget, check your database to verify that the new record has been inserted. Use the following query:

mysql> select * from app_fd_demo;
+---------------------+---------------------+---------------------+-----------+
| id | dateCreated | dateModified | c_message |
+---------------------+---------------------+---------------------+-----------+
| 2016-06-29 11:57:19 | 2016-06-29 11:57:19 | 2016-06-29 11:57:19 | hello |
+---------------------+---------------------+---------------------+-----------+
1 row in set (0.00 sec)