Database

Introduction

This document provides detailed instructions for integrating the Database Library with Automate, a powerful workflow automation platform. The Database Library action enables users to connect to various databases such as SQL Server, Oracle, MySQL, and PostgreSQL, and perform a range of actions within them. These actions include executing transactions, queries, inserts, updates, deletes, and selections. Integrating the Database Library with Automate allows users to efficiently manage database operations directly from their workflows.

What is the Database Library?

The Database Library in Automate offers a set of actions to interact with databases seamlessly. Users can connect to databases like SQL Server, Oracle, MySQL, and PostgreSQL, and execute transactions and queries with ease. This integration empowers users to perform database operations within their Automate workflows, enhancing efficiency and productivity.

Automate Integration with Database Library

Automate provides a set of pre-built actions within the Database Library to facilitate seamless integration with various databases. These actions include "Connect," "Disconnect," "Insert/Update/Delete," and "Select," enabling users to perform essential database operations directly within their automation workflows.

Using the Integration

To utilize the integration with the Database Library in Automate, follow the steps outlined below:

How To Use Actions

Oracle Database Query Syntax

When executing queries on an Oracle database, users should ensure that the query is written without a semicolon at the end of the line.

Connect

Connect Autom View

Description: Establishes a connection to the selected database.

Purpose: This action enables the system to connect to various types of databases for data retrieval and manipulation.

Usage:

• Select the Database Type: Choose from Microsoft SQL Server, MySQL, PostgreSQL, or Oracle.

• For Microsoft SQL Server:

  • Server Name: The name of the SQL Server.

  • Database Name: The name of the database.

  • Username: The username for the database connection.

  • Password: The password for the database connection.

  • Port: The port number for the SQL Server.

  • Instance: The instance name of the SQL Server (if applicable).

Microsoft SQL

• For MySQL Server:

  • Server Name: The name of the SQL Server.

  • Database Name: The name of the database.

  • Username: The username for the database connection.

  • Password: The password for the database connection.

  • Port: The port number for the SQL Server.

My SQL

• For PostgreSQL Server:

  • Server Name: The name of the SQL Server.

  • Database Name: The name of the database.

  • Username: The username for the database connection.

  • Password: The password for the database connection.

  • Port Number: Port number of the database.

PostgreSQL

• For Oracle Server:

  • Server Name: The name of the SQL Server.

  • Username: The username for the database connection.

  • Password: The password for the database connection.

  • Port: The port number for the SQL Server.

  • Instance: The instance name of the SQL Server (if applicable).

Oracle

Input Fields:

• Database Type: Select the type of database.

• Database Connection Details:

  • Server Name: The name of the database server.

  • Database Name: The name of the database.

  • Username: The username for the database connection.

  • Password: The password for the database connection.

  • Port: The port number for the database server.

  • (Optional) Instance: The instance name for SQL Server or Oracle.

Output Fields:

• None

Time Zone Synchronization

When using the database library to connect to an Oracle database, users must be aware of any time zone differences between the Mate Agent and the Oracle Client instance. If a discrepancy exists, users should specify the Oracle Client's time zone in the Connect Action to ensure proper synchronization between the Mate Agent and the Oracle Client instance.

Disconnect

Disconnect Autom View

Description: Closes the connection to the database.

Purpose: This action terminates the active connection to the database, freeing up resources.

Usage:

• No additional input required.

Input Fields:

• None required.

Output Fields:

• None

Insert/Update/Delete

Insert/Update/Delete Autom View

Description: Executes an insert, update, or delete query in the database.

Purpose: This action allows for the modification of data within the connected database.

Usage:

• Enter the SQL Query: The SQL query to be executed.

• Specify the Timeout: Duration to stop the query if it exceeds the specified time.

Input Fields:

• SQL Query: The SQL query to be executed.

• Timeout: Timeout duration to stop the query if it exceeds the specified time.

Output Fields:

• None

Select

Select Autom View

Description: Executes a select query in the database.

Purpose: This action retrieves data from the connected database based on the provided query.

Usage:

• Enter the SQL Query: The SQL query to be executed.

• Specify the Timeout: Duration to stop the query if it exceeds the specified time.

• Assign to Variable: Variable to assign the output of the select query.

Input Fields:

• SQL Query: The SQL query to be executed.

• Timeout: Timeout duration to stop the query if it exceeds the specified time.

• Assign to Variable: Variable to store the output of the select query.

Output Fields:

• None

These actions facilitate the interaction with databases by establishing connections, executing queries, and managing data retrieval and manipulation tasks within the specified database environment.

Additional Tips

Connecting to Oracle Database using Autom Mate's Mate Agent

In this scenario, the Mate Agent is installed within the same firewall as the Oracle Database, allowing secure communication between the agent and the database. This document provides detailed steps to configure the connection between the Mate Agent and the Oracle Database. The agent itself is not installed on the Oracle server but can still access it through the network.

Step 1: Ensure the Mate Agent is Installed and Configured on the On-Premises Server

• The Mate Agent must be installed on a server that resides within the same firewall as the Oracle database. This allows the agent to communicate securely with Oracle over the internal network.

  Make sure the server where the Mate Agent is installed meets all hardware and software requirements. Follow the standard installation process, including configuring the network and system services, and setting up dependencies.

Step 2: Download the Correct Version of Oracle Instant Client

• The Oracle Instant Client must be installed on the same server as the Mate Agent to enable the communication between the agent and Oracle. This client allows database interactions like running SQL queries.

  Steps:

  1. Visit Oracle's Instant Client download page.
  2. Download the version compatible with your Oracle Database and server operating system (Linux, Windows, etc.).

  Note: Ensure you choose the correct version (32-bit or 64-bit) for your server's architecture.

Step 3: Install the Oracle Instant Client

• Once downloaded, install the Oracle Instant Client on the same server where the Mate Agent is running.

  On Linux:

  1. Unzip the client package.

  2. Move the files to a directory like /opt/oracle/instantclient.

  3. Ensure the required files (sqlplus, tnsping, etc.) are executable.

  On Windows:

  1. Extract the Instant Client files to a location such as C:\Oracle\InstantClient.

  2. Verify that sqlplus.exe and tnsping.exe are available.

Step 4: Configure the tnsnames.ora File for Oracle Connection

• The tnsnames.ora file defines how the Oracle Instant Client connects to the database. It specifies the database host and connection details.

  Steps:

  1. Locate or create the tnsnames.ora file:

     • On Linux: /etc/oracle/tnsnames.ora or /opt/oracle/instantclient/network/admin/tnsnames.ora

     • On Windows: C:\Oracle\InstantClient\network\admin\tnsnames.ora

  2. Add the following configuration:

     textCopy codeORCL =
       (DESCRIPTION =
         (ADDRESS = (PROTOCOL = TCP)(HOST = [your_database_host])(PORT = 1521))
         (CONNECT_DATA =
           (SERVER = DEDICATED)
           (SERVICE_NAME = [your_service_name])
         )
       )

  Replace [your_database_host] and [your_service_name] with the actual details of your Oracle Database.

Step 5: Set Environment Variables for Oracle Connectivity

• To ensure the Mate Agent can interact with the Oracle Instant Client, environment variables need to be set on the server.

  On Linux:

  • Add the following lines to .bashrc or .bash_profile:

    bashCopy codeexport ORACLE_HOME=/opt/oracle/instantclient
    export LD_LIBRARY_PATH=$ORACLE_HOME:$LD_LIBRARY_PATH
    export PATH=$ORACLE_HOME:$PATH

  On Windows:

  • Add the Oracle Instant Client directory to the Path environment variable:

    1. Open Control Panel > System > Advanced system settings > Environment Variables.

    2. Edit Path and add the Oracle Instant Client path (C:\Oracle\InstantClient).

    3. Add a new environment variable ORACLE_HOME with the value pointing to the Instant Client location.

  These variables allow the server to locate and use the Instant Client tools.

Step 6: Test the Oracle Connection

• After configuring the tnsnames.ora and environment variables, test the connection from the server where the Mate Agent is running.

  Steps:

  1. Create an Autom and add Database - Connect action inside of it.
  2. Configure the Connect action for Oracle Database.

  3. Run the Autom to see if the connectivity is working.

  If successful, a "Success" message will appear. If there are errors, verify your tnsnames.ora configuration and environment variables.

Step 7: Configure Mate Agent to Use Oracle

• With the Oracle connection established, configure the Mate Agent to interact with the Oracle database. This can be done through the Autom Mate platform by adding the Oracle connection details, credentials, and any necessary permissions.

  Follow the instructions in the Autom Mate user guide for connecting databases to the Mate Agent. The database credentials and connection information must match what is set in the tnsnames.ora file.

Conclusion

By following these steps, you can successfully establish a connection between the Mate Agent and Oracle Database within the same firewall. This setup allows Autom Mate to perform automated workflows that involve Oracle database operations, such as querying data, executing SQL commands.

Use Cases

FAQ