Data - SQL

Use Data - SQL to import records from a SQL Server database or a MySQL database into a data file. The data file is used in a botflow Automated steps that you can design for each bot that will run. to access the imported records from database tables and views (queries) and while using custom SQL statements. This topic describes how to create and manage Data - SQL server connections.

Jump to:

The first time you use Data - SQL or SQL to connect to a MySQL database, you will be prompted to install the MySQL Connector, if it is not already installed.

Note: 

You can install and use a MySQL version up through 8.0.28 (not 8.0.29), but your MySQL Connector must be version 8.0.17. If you have already installed a newer version of MySQL, download the MySQL Connector 8.0.17 files and copy the MySQL.data.dll and Renci.SshNet.xml files into the C:\Program Files (x86)\MySQL\Connector NET 8.0\Assemblies\v4.5.2 folder and copy the Renci.SshNet.dll file into the C:\Project Files (x86)\Nintex\RPA folder. Or, follow these instructions to install MySQL Connection version 8.0.17:

  1. Go to the connector download.
  2. Select Product Version 8.0.17 and the 393.4M download.
  3. Open the MySQL Installer.
  4. When the installation asks you to upgrade to version 8.0.29, click Cancel.
  5. Click Add.
  6. Select Connector/J 8.0.17 - X86 from the Available Products list and move it to the Products/Features To Be Installed list. If this connector is greyed out, you have a newer version already installed; click Cancel, click Remove to remove the newer version, click Add, and then select the correct connector.
  7. Click Next and then Execute to complete the installation.
  8. Navigate to C:\Program Files (x86)\MySQL\Connector NET 8.0\Assemblies\v4.5.2.
  9. Select and copy Renci.SshNet.dll.
  10. Navigate to C:\Program Files (x86)\Nintex\RPA and paste Renci.SshNet.dll.

If you are using MySQL version 8.0.17, follow these instructions to download and install MySQL Connector version 8.0.17: 

  1. Go to the connector download.
  2. Select Product Version 8.0.17 and the 393.4M download.
  3. Open the MySQL Installer.
  4. Accept the license agreement terms and click Next.
  5. Select Custom and click Next.
  6. Select Connector/NET 8.0.17 - X86 from the Available Products list and move it to the Products/Features To Be Installed list.
  7. Click Next and then Execute to complete the installation.
  8. Navigate to C:\Program Files (x86)\MySQL\Connector NET 8.0\Assemblies\v4.5.2.
  9. Select and copy Renci.SshNet.dll.
  10. Navigate to C:\Program Files (x86)\Nintex\RPA and paste Renci.SshNet.dll.

You can import Execute SQL and Stored Procedure actions and save them as

  • None

  • Data. This action creates an Access database file at a specified location and displays data for immediate modification. The data types that are used in these output Access database files are dependent on the SQL data types that are used within the imported SQL database tables.

  • File. This action creates an Access database file at a specified location. The data types that are used in these output Access database files are dependent on the SQL data types that are used within the imported SQL database tables.

  • List. This action creates a list-like structure in Nintex RPA LE.

The table below displays how the mapping between SQL and Access database data types have changed between different versions of Nintex Bot:

SQL data types Nintex Bot 14.6.2 Nintex Bot 15.1.10 Nintex Bot 17.3.0 Nintex Bot 17.4.0 and above
BigInt Number   Number Number
Bit Yes/No Yes/No Yes/No Yes/No
Char Short Text Short Text Short Text Long Text
Date Short Text Short Text   Date/Time
DateTime Date/Time Date/Time Date/Time Date/Time
DateTime2 Short Text Short Text Short Text Date/Time
DateTimeOffset Short Text Short Text   Date/Time
Decimal Number Number Number Number
Float Number Number Number Number
Int Number   Number Number
Money Currency Short Text Short Text Long Text
NChar Short Text Short Text Short Text Long Text
NMoney Long Text   Short Text Long Text
Numeric Number Number Number Number
NVarChar Short Text Short Text Short Text Long Text
NVarCharMax Long Text     Long Text
Real Number Number Number Number
SmallDateTime Date/Time Date/Time Date/Time Date/Time
SmallInt Number   Number Number
SmallMoney Currency Short Text Short Text Long Text
SQLVariant Short Text Short Text Short Text Long Text
Text Long Text   Short Text Long Text
Time Short Text Short Text   Long Text
TinyInt Number   Number Number
UniqueId Number Short Text   Long Text
VarChar Short Text   Short Text Long Text
VarCharMax Long Text     Long Text

Note: All text data with a Long Text Access database data type is represented to allow text data to have more than 255 characters.

Create a Data - SQL connection

  1. Click Add Item () on the Botflow Pane.
  2. Click Data - SQL.
  3. Select the SQL type from the Type drop-down list:
    • SQL. Select to use with a SQL database.
    • MySQL. Select to use with a MySQL database. This option requires that MySQL is installed.
  4. In the Server field, complete one of the following tasks:
    • Type the required server name.
    • Click the Expression Builder to build the server name using variable or other token values.
    • For SQL, click the down button to retrieve a list of available SQL Servers.
  5. Select Port to type a port number or accept the default number (3306).
  6. Type a Timeout value or use the up and down buttons to incrementally change the value by one second.
  7. For SQL, select the authenticate type from the Authentication drop-down:
    • Windows. This type uses your Windows log in credentials.
    • SQL. This type uses the authentication credentials set for the SQL server.

    For MySQL, type the username and password associated with the selected MySQL server.

  8. Click Connect to connect to the SQL database server or MySQL database server and retrieve a list of available databases.
  9. Select an available database from the Database drop-down list.
  10. Select the record source from the Records drop-down list:
    • Table. Retrieves database table records.
    • SQL. Retrieves database records based on an entered SQL query records statement.
    • View. Retrieves database query view records.
  11. Type a timeout value in the Timeout field or use the up and down buttons to incrementally change the value by one second.
  12. Select a table or table view or type a SQL statement depending on your records selection.
  13. Page through the Data Wizard to import the records into a data file and display that data in the Data view in the Botflow Pane.

Manage a Data - SQL connection

  1. Navigate to the Data - SQL server connection on the Botflow Pane.
  2. Right-click a data field in the SQL - data information.
  3. Use the Context Menu to:
    • Edit. The Field screen displays so that you can edit the existing value information.
    • Copy Value. This action copies the field value from the selected field.
    • Paste Value. This action pastes the field value into the selected field.
    • Clear. This action clears the field value from the selected field.