MySQL - Execute a query

Use the MySQL - Execute a query action A tool for building the processes, logic, and direction within workflows. to run queries in a database you specify. A query can be a request for data or for action.

For example, you want to generate a directory of your customers' mobile phone numbers. To achieve this, you need to retrieve the phone numbers from your company database. By using the MySQL - Execute a query, you can retrieve these data, store them in variables, and then insert in a Generate document action.

For more information about MySQL, go to MySQL.

Before you begin: 

Jump to:

Configure the MySQL - Execute a query action

  1. Add the action to the workflow and open the action configuration panel. For more information, see Add, rename, and copy actions.

  2. Select a Connection. If you do not have a connection, see Add a connection.

    3. Caution: Make sure to first configure your database's firewall to allow your workflow to connect to your database. For more information on source IP addresses you can use, see Source IP addresses for workflows.

  1. In SQL script, type concatenated queries, separated by semicolons, to run on your database. Do not use single quotations; instead, for parametrized queries, add query parameter key names and values (next step).

    SQL script example: SELECT * FROM [Customers]

    2. For more guidance on SQL keywords and syntax, see SQL Keywords Reference.

  2. To add a parametrized query execution, in Query parameters, click Add SQL query parameters.
    1. Add a Parameter name and Value.
    2. Click Add SQL query parameters to add more parameter names and values.
    Example: 

    SELECT * FROM [Customers] WHERE Name = @name

    Parameter name = @name

    Value = <Customer_Name>

  3. In Column to retrieve, type the database column that contains the values you want to retrieve. For example, "Email address". This field is required for SELECT queries.

Maintain path integrity

Caution: If a file/folder that's used in a workflow is moved, renamed, or deleted, any workflows that use that file/folder will not start or will fail. Make sure you keep the paths in your workflow configurations up to date.

Source IP addresses for workflows

To run workflows that connect to SQL databases or related systems, first configure its firewall rules to allow traffic from potential source IP addresses for these workflows. If firewall rules are in place but not configured to allow these IP addresses, then contact does not occur and the workflow fails.

For instructions on SQL Server firewall configuration, see the Configure the Windows Firewall to Allow SQL Server Access Microsoft article or, for SQL Server on Windows Azure, see the Creating Microsoft SQL Server Connection to SQL Azure from Nintex Automation CloudNintex Community article.

Potential source IP addresses for workflows:

US region EU region AU region

  • 40.112.243.0

  • 13.91.46.203

  • 40.112.255.35

  • 13.91.41.7

  • 13.91.40.30
  • 40.115.98.85

  • 40.87.138.71

  • 40.113.68.216

  • 104.41.226.28

  • 40.87.134.251

  • 23.101.230.162

  • 23.101.238.134

  • 23.101.238.167

  • 23.101.238.27

  • 23.101.235.11

MySQL - Execute a query fields and settings

Field

Description

Variable types

Connection Credentials and other settings required to pass data between the workflow and the indicated service, which could be a cloud service, business application, or content store.

The MySQL connection to use for the query.

(n/a)
SQL script

The concatenated queries, separated by semicolons, that will run on your database. Do not use single quotations; instead, for parametrized queries, add query parameter key names and values.

SQL script example: SELECT * FROM [Customers]

For more guidance on SQL keywords and syntax, see SQL Keywords Reference.

 Text, Decimal, Integer, Boolean, DateTime, Collection
Query parameters Adds a parametrized query execution. Click Add SQL query parameters and add a Parameter name and Value. Text, Decimal, Integer, Boolean, DateTime, Collection
Column to retrieve The database column that contains the values you want to retrieve. For example, "Phone numbers". This field is required for SELECT queries. Text, Decimal, Integer, Boolean, DateTime, Collection
Result

Stores the result of the action in an object with the following variables:

  • Retrieved column: A collection variable to store the values retrieved by your SQL query.

    A collection is an index-based array of values of different data types (text, integer, date/time, etc.) In Nintex Automation Cloud, a collection is specifically designed to hold multiple values for safe keeping under one variable. Depending on how many values should be stored, the size of the collection is shaped accordingly to accommodate each value.

    For example, your workflow retrieved a set of values such as Apple, 345, Kensas, and John@email.com. How would you store this random information in one place? That's where a collection comes in.

    To get each value stored in a collection, use Get item from collection.

  • Results count: An Integer variable to hold the number of values retrieved by your SQL query.

    • This variable is very useful especially if your workflow should know how many values were retrieved by the action.

 Object