Performing Database Operations

Opkey features a built-in database connectivity engine, which facilitates various database operations. Currently, Opkey supports the following databases: 

1. MySQL

2. ORACLE 

3. SQL SERVER

4. PostgreSQL

5. RedShiftDB

6. SQLite

Pre-Requisites to connect databases.

ConnectDatabase Keyword in Opkey takes two parameters DriverName and ConnectionString.

Here is the list of databases, their Drivers, and connection strings.

‍

*To establish Oracle Database connectivity, it's essential to install Oracle Data Access Components (compatible ODAC drivers for the installed Oracle version) on the machine from which the user intends to establish the database connection. 

For downloading compatible ODAC drivers, please Click here:

ODBC Configuration with MySQL

Follow the below steps for ODBC Configuration for MySQL Database:

1. Search for ODBC Data Source 64-bit on the Start Menu.

2. Open Run as Admin.

3. The MySQL Connector/ODBC" screen will open. 

4. Users need to complete all the details for "Data Source Name-MySQL," "Description-MySQL," "IP of the system," "Port-3306," "User-Root," "Password of MySQL," and "Database-to work on." Then, click "OK."

5. Navigate to the "System DSN" tab and click on "Add." This will bring up the option to select MySQL on the screen. 

6. Select the installed driver and then click on the "Configure" button. 

7. MySQL will be added after clicking Ok.

Connecting  MySQL database  through Opkey

Users can confirm the MySQL connection by using two Opkey keywords: "Connect Database" to establish the connection and "ExecuteQuery" to execute a query. Input the details in the keyword, and then click "OK." 

Connect Database" consists of two components: the Driver and the Connection String. 

Input "system.data.odbc" in the Driver column and "DSN=Required DSN Name Configured in ODBC" in the Connection String column. 

To use the "Execute Query," provide details for "Sample Query" and "Command Time." Each query will produce specific results. When the connection is established, both queries will be executed, and the results will be displayed as either True or False. 

ODBC Configuration with PostgreSQL database

The user should enter "system.data.odbc" as the Driver and "DriverODBC for PostgreSQL" as the Connection. (where?)

Follow the below steps to connect ODBC with PostgreSQL

1. Verify the connectivity between the agent machine and the machine where the database is installed. 

2. Confirm the version of the PostgreSQL Database. 

3. The ODBC driver needs to match the version of the PostgreSQL Database. Users should install the ODBC Connector, which can be downloaded online or obtained through their IT Department. 

To configure ODBC with PostgreSQL

Follow the below steps to configure ODBC with PostgreSQL

1. Look for "ODBC Data Source 64-bit" in the Start Menu. 

2. Open the run as admin. 

3. Go to the “System DSN" tab.

4. A window will open of “Devart ODBC Driver for PostgreSQL Configuration”.

5. Enter details of “Data Source Name- MyPostgreSQL”, “Description-MyPostgreSQL”, “Server- 184.105.178.238”, “Port-5432”, “User Id PostgresSQL”, “Password”, “Database” and “Schema”. Note:(All the details shared above is just for reference. The details that will be entered will be user specific)

6. Select the installed driver and then click on the "Configuration" button. 

7. After entering all the necessary details, the PostgreSQL configuration will be complete. 

The user needs to provide information for both the "Connect Database" and "Execute Query" components. Here are the details: 

For "Connect Database":

Driver: system.data.odbc 

Connection: DriverODBC for PostgreSQL 

Connection String – DSN= DSN Name of configured Postgres dsn

Execute Query:

Query- SELECTFROM publictest

CommandTime-60

‍You're now ready to proceed with your operations.

To read more articles on Opkey's features Click Here.