SQL

Use SQL to import database table records from a SQL Server database or 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. This topic describes how to create and manage SQL server connections.

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.

Create a SQL server connection

  1. Click Add Item () on the Botflow Pane.
  2. Click SQL.
  3. Select a connection letter from the Letter drop-down list or accept the default letter.
  4. 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.
  5. 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.
  6. Select Port to type a port number or accept the default number (1433).
  7. Type a Timeout value or use the up and down buttons to incrementally change the value by one second.
  8. 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 credentials set for the SQL server connection.

      • Credential. Select the credential required for the SQL server connection.

      • Username. Displays the username stored in the credential required for the SQL server connection.
      • Password. Displays the password stored in the credential required for the SQL server connection.
      • Confirm. Re-type the password, if required, for the SQL server connection.

    For MySQL, use the credentials set for the MySQL server connection.

    • Credential. Select the credential required for the MySQL server connection.
    • Username. Displays the username required for the MySQL server connection.
    • Password. Displays the password required for the MySQL server connection.
    • Confirm. Re-type the password, if required, for the MySQL server connection.
  9. Click Connect to connect to the SQL database server or MySQL database server and retrieve a list of available databases.
  10. Select an available database from the Database drop-down list.
  11. (Optional) Type a Note describing the SQL connection.
  12. Click OK to save the SQL connection to use in the botflow.

Manage a SQL server connection

  1. Navigate to the SQL server connection on the Botflow Pane.
  2. Right-click the SQL connection.
  3. Use the Context Menu to:
    • Add. The SQL screen displays so that you can create another SQL server connection.
    • Edit. The SQL screen displays with the existing SQL server connection information that can be edited.
    • Delete. This action removes the SQL server connection information from the botflow.
    • Duplicate. This action creates a copy of the SQL server connection information with the next available connection letter.