Using Connect action for database

Use the Connect action to establish a connection with the database server that you want to use to automate database-related tasks. This action supports Microsoft Access, Microsoft SQL Server, MySQL, Oracle, PostgreSQL Server, and SQLite database servers, as well as the use of CSV, Microsoft Excel, and TXT files as databases.

Note: If your current office installation is any of the following:

Microsoft 365 Apps for Enterprise
Office 2016, 2019, or 2021 (Consumer Version 2009 or later)

Then you do not need to install the Microsoft Access Database Engine as an additional component in your system.

However, if you have both Microsoft 365 Apps and the Microsoft Access Database Engine are installed on your system, then you can uninstall the Microsoft Access Database Engine and repair Microsoft 365 Apps.

Microsoft recommends that you do not install the Microsoft Access Database Engine driver separately because both the Microsoft Access Database Engine 2016 and Microsoft 365 Apps use the same major version identifier (16.0). To learn more about the Microsoft Access Database Engine driver installation requirement, see Unable to use the Access ODBC or OLEDB provider

Specify the details of a database server and associate it with a session name. Use the session name provided in this action in the other actions so that you do not have to provide the details of the database server in those actions.

Procedure

To establish a connection with a database server, follow these steps:

Double-click or drag the Connect action from the Database package in the Actions palette.
Enter a unique name for the session in the Session name field.

Select the Default or User defined connection option.

When you select User defined connection mode and use action Read from with encoding ANSI to save the retrieved data in a CSV file, you will see that the retrieved records from the Oracle database display garbled character.

To know more about this issue, see Fullwidth hyphen is garbled when exporting data to csv by Database: Read from action with encoding ANSI (A-people login required)

If you select the Default option, you can directly enter the connection string for any of the supported database types.
If you have configured your database and shared repository in the secondary site, use the JDBC connection to connect to the replica database. For example, jdbc:sqlserver://AGL-ADDRESS:1433;databaseName=DB_NAME;user=username;password=password;applicationIntent=ReadOnly
You can establish a connection with the Snowflake database using ODBC drivers. For more information about downloading the ODBC driver, see Snowflake using ODBC Driver
You can enter the connection string to connect to the database. For example, Driver={SnowflakeDSIIDriver};Server={account}.aws.snowflakecomputing.com;Database=DB_NAME;uid=username;pwd=password;
Use this option to connect to an Excel, CSV, or TXT file. See Connection Strings by database type.
Note: To access any database through ODBC, you must first install a 32-bit ODBC driver on your device.
To ensure a more secure automation, use a Credential Vault variable for the connection string.
Select the Use ODBC 64-bit driver for connection option to connect to the database.
Note:
- Ensure that 64-bit access ODBC driver is installed on your system to access any database and perform operations by using database actions.
- The following action does not support the 32-bit or 64-bit OBDC drivers because the listed actions are for database transactions that are carried out after making a connection with the database:
  - Begin database transaction
  - End database transaction
  - Manage stored procedure
- Using Excel as a database, you can process more rows with 64-bit drivers compared to 32-bit ones.

If you select the User defined option, select the database type from the available options and complete the following fields based on the selection:


Database Type	Options
Microsoft SQL Server, PostgreSQL Server	Server name: Enter the name of the database server you want to connect to. Database name: Enter the database name. Username: Enter the username you want to use to access the database server. To ensure a secure user name, select a Credential Vault variable. Otherwise, enter a value. Password: Enter the password for the username you have provided. To ensure a secure password, select a Credential Vault variable. Otherwise, enter a value. Instance name: Enter a name for this connection instance. Timeout in seconds (optional) field: Specify the amount of time between 1-9999 you want the system to wait before canceling a bot. The automation will time out and fail if the database connection is not established the specified time. If you do not enter a value in the Timeout in seconds (optional) field, then the default timeout specified in the driver library is used. Note: Microsoft SQL Server supports this option only. Connect to Microsoft SQL Server with Windows authentication
MySQL	Enter the same options as in the Microsoft SQL Server database type. Also, enter the port number. The default port number is 3306.
Microsoft Access, SQLite	Select the database file path from: Bots folder local device existing file variable
Oracle	Server name (Optional): Enter the name of the Oracle server you want to connect to. Optional:Select an option to specify the Oracle instance you want to use: System id (SID): Enter the system ID. Service name: Enter the service name. TNS Name: Enter the TNS Name available in the tnsname.ora configuration file. Specify the file path of the tnsname.ora configuration file, if you have not set the system property with environment variable. Note: You can connect to the Oracle Database with the Server name or Oracle instance. If you are connecting using the server name, you must specify the System id (SID) or Service name in the Oracle instance field to identify the Oracle database and its instance. However, if you are connecting using the TNS name, you do not require to enter any value in the Server name field. You should only enter the value of the TNS name and file path. Username: Enter the username you want to use to access the Oracle server. To ensure a secure user name, select a Credential Vault variable. Otherwise, enter a value. Password: Enter the password for the username you have provided. To ensure a secure password, select a Credential Vault variable. Otherwise, enter a value. Port: Enter the port number. The default port number is 1521.

Note: If you establish a connection by using a JDBC driver, for Microsoft SQL Server, the Database package already includes the JDBC driver. However, for other database servers, you must provide the corresponding JDBC driver jar file after downloading it from the appropriate resources.

If you establish a connection by using an ODBC driver, you need not provide a jar file.

Select a driver file from the Bots folder, the local device, or a file variable.
Click Save.

Next steps

Choose from the following:

Use the Read from action to retrieve records from the database.
Using the Read from action
If you are automating a task that involves making changes to the database, insert the Begin database transaction action.
This action ensures that all records are updated or deleted in their entirety, and prevents accidental updates or deletions of incomplete data if the bot encounters an error during run time.
Database package

Automation 360