# 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

{% hint style="info" %}
**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.
{% endhint %}

### Connect

<figure><img src="/files/35M7mao79dwvezRjjhVc" alt=""><figcaption><p>Connect Autom View</p></figcaption></figure>

**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: <mark style="color:green;">The name of the SQL Server.</mark>
  * Database Name: <mark style="color:green;">The name of the database.</mark>
  * Username: <mark style="color:green;">The username for the database connection.</mark>
  * Password: <mark style="color:green;">The password for the database connection.</mark>
  * Port: <mark style="color:green;">The port number for the SQL Server.</mark>
  * Instance: <mark style="color:green;">The instance name of the SQL Server (if applicable).</mark>

<figure><img src="/files/cCB0SDDLNOyPAeWdVwAH" alt=""><figcaption><p>Microsoft SQL</p></figcaption></figure>

* **For MySQL Server:**
  * Server Name: <mark style="color:green;">The name of the SQL Server.</mark>
  * Database Name: <mark style="color:green;">The name of the database.</mark>
  * Username: <mark style="color:green;">The username for the database connection.</mark>
  * Password: <mark style="color:green;">The password for the database connection.</mark>
  * Port: <mark style="color:green;">The port number for the SQL Server.</mark>

<figure><img src="/files/jvbItdbVbSXAw0Slk56i" alt=""><figcaption><p>My SQL</p></figcaption></figure>

* **For PostgreSQL Server:**
  * Server Name: <mark style="color:green;">The name of the SQL Server.</mark>
  * Database Name: <mark style="color:green;">The name of the database.</mark>
  * Username: <mark style="color:green;">The username for the database connection.</mark>
  * Password: <mark style="color:green;">The password for the database connection.</mark>
  * Port Number: <mark style="color:green;">Port number of the database.</mark>

<figure><img src="/files/acZmKAPv3WtChrdnd0hh" alt=""><figcaption><p>PostgreSQL</p></figcaption></figure>

* **For Oracle Server:**
  * Server Name: <mark style="color:green;">The name of the SQL Server.</mark>
  * Username: <mark style="color:green;">The username for the database connection.</mark>
  * Password: <mark style="color:green;">The password for the database connection.</mark>
  * Port: <mark style="color:green;">The port number for the SQL Server.</mark>
  * Instance: <mark style="color:green;">The instance name of the SQL Server (if applicable).</mark>

<figure><img src="/files/IF7w1v59ln5YUcWjEXOH" alt=""><figcaption><p>Oracle</p></figcaption></figure>

**Input Fields:**

* Database Type: <mark style="color:green;">Select the type of database.</mark>
* Database Connection Details:
  * Server Name: <mark style="color:green;">The name of the database server.</mark>
  * Database Name: <mark style="color:green;">The name of the database.</mark>
  * Username: <mark style="color:green;">The username for the database connection.</mark>
  * Password: <mark style="color:green;">The password for the database connection.</mark>
  * Port: <mark style="color:green;">The port number for the database server.</mark>
  * (Optional) Instance: <mark style="color:green;">The instance name for SQL Server or Oracle.</mark>

**Output Fields:**

* None

{% hint style="danger" %}
**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.

<img src="/files/NsdHeYrPw9727fcULoT9" alt="" data-size="original">
{% endhint %}

### Disconnect

<figure><img src="/files/9DgqIr1NW8YvvifVWw4c" alt=""><figcaption><p>Disconnect Autom View</p></figcaption></figure>

**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:**

* <mark style="color:green;">None required.</mark>

**Output Fields:**

* None

### Insert/Update/Delete

<figure><img src="/files/2ddnypahRo9o4DDVWn7x" alt=""><figcaption><p>Insert/Update/Delete Autom View</p></figcaption></figure>

**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: <mark style="color:green;">The SQL query to be executed.</mark>
* Timeout: <mark style="color:green;">Timeout duration to stop the query if it exceeds the specified time.</mark>

**Output Fields:**

* None

### Select

<figure><img src="/files/36Q2Gr1P4WV76XSlcAbl" alt=""><figcaption><p>Select Autom View</p></figcaption></figure>

**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: <mark style="color:green;">The SQL query to be executed.</mark>
* Timeout: <mark style="color:green;">Timeout duration to stop the query if it exceeds the specified time.</mark>
* Assign to Variable: <mark style="color:green;">Variable to store the output of the select query.</mark>

**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**](/userguide/installation-setup/mate-agent-installation.md)

* 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](https://www.oracle.com/database/technologies/instant-client/downloads.html).
  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`:

    ```bash
    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](#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**](/userguide/use-cases.md)

### [FAQ](/userguide/faq.md)


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://docs.autommate.com/userguide/libraries/integration/database.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.