Execute a stored procedure

This example shows how to run a stored procedure when you are using a database connection. The example assumes that you are familiar with the following concepts:

• Creating integrations using Application Integration
• Connector task
• Integration Connectors
• Database stored procedures

All stored procedures in a database connection are exposed to you as actions in the Connector task. An action is a first class function that is made available to the integration through the connector interface. Actions let you make changes to an entity or entities, and vary from connector to connector. However, it is possible that a connector doesn't support any action, in which case the Actions list will be empty.

The following connectors support stored procedures:

• BigQuery
• Cloud SQL - MySQL
• Cloud SQL - PostgreSQL
• Cloud SQL - SQL Server
• MySQL
• Oracle DB
• PostgreSQL
• SQL Server
• MariaDB
• AlloyDB
• Snowflake
• Redshift

Example

Consider you have a MySQL database which has the following stored procedure that gets a customer's information from the customers table:

CREATE PROCEDURE get_customer_info
(IN p_customer_id INT, OUT p_name VARCHAR(50), OUT p_email VARCHAR(255))
BEGIN
    SELECT name, email INTO p_name, p_email
    FROM customers
    WHERE id = p_customer_id;
END

This stored procedure returns the name and email for the specified customer. It takes in the customer ID through the p_customer_id input variable and returns the name and email in the p_name and p_email output variables, respectively.

Now suppose you want to get the name and email id of the customer with customer_id=1001, you must do the following tasks:

1. Create a connection to your MySQL database.
2. Open or create a new integration.
3. Add the Connectors task to your integration.
4. In the Configuration section, click Configure task to open the Configure connector task pane.
5. Configure the Connectors task to use the connection you created in step 1.
   1. In the Connection column, select the required connection from the list of available connections.

      After you select a connection, the Type column appears with the values Entities and Actions. All the stored procedures will be listed in Actions.

   2. Select Actions > get_customer_info.
   3. Click Done to complete the connection configuration and close the pane.
6. Click the Connectors task element, and then click connectorInputPayload in the Task Input section.
7. Specify the following JSON payload in the Default Value field:

   {
   "customer_id": 1001
   }

8. Click the Test button in the integration editor toolbar to run the integration.

   If the integration runs successfully, the connectorOutputPayload vairable will have a JSON payload similar to the following:

   {
   "name": "John",
   "email": "[e-mail]"
   }