26.15 Performing Database Operations

OpKey provides an inbuilt database connectivity engine which helps users to perform different kind of database operations. Currently OpKey supports following databases:

SQLITE
MySQL
ORACLE and
SQL SERVER
PostgreSQL
RedShitDB

ConnectDatabse (String DriverName, String ConnectionString).

ConnectDatabse Keyword takes two parameters DriverName and ConnectionString.

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

Database	Driver	Connection String
MySQL	mysql	Server=Server-Name;Database=Database-Name;Uid=UserName;Pwd=Password;port=3306;
Sqlite Databse	sqlite	Data Source=Path of db file
Oracle Database*	Oracle.DataAccess.Client	Data Source=Server-Address; Port/Service-Name; User Id=Username; Password=Password; For Example: Data Source=svn-server:1521/orcl.ctech.com;User Id=system;Password=Password1;
SQL Server(Standard)	System.Data.SqlClient	Server=myServerAddress; Database=myDataBase; UserId=myUsername; Password=myPassword;
SQL Server (Windows Authentication)	System.Data.SqlClient	Server=myServerAddress;Database=myDataBase; Trusted_Connection=True;
PostgreSQL	System.data.odbc	DSN=MyDSN(UserShould Provide from ODBC configuration
RedShitDB	System.data.odbc	DSN=MyDSN(UserShould Provide from ODBC configuration

* For Oracle Data base connectivity, Oracle Data Access Components (compatible ODAC drivers to the installed Oracle version) needs to be installed on the machine from where the user wants to establish the database connection. For downloading compatible ODAC drivers, please Click here

To connect MySQl through ODBC

User will have to check the connectivity of the agent machine and where the database is
User will have to check the version of MySQL Database.
The ODBC driver will have to be installed of the same version as that of MySQL Database. User will have to install the ODBC Connector. It can be downloaded online or a user can check with their respective IT Department.

ODBC Configuration with MySQL

To configure ODBC for MySQL Database the user will have to follow the below steps:

Search for ODBC Data Source 64 bit on the Start Menu.
Open Run as admin
A screen will open of “MySQL Connector/ODBC”.
User will have to fill in all the details of “Data Source Name-MySQL”, “Description- MySQL” “IP of the system” “Port-3306” “User-Root” “Password of MySQL, “Database- to work on” and then click on OK.
Go to the “System DSN” tab and click on Add. It will reflect on the screen to select MySQL.
Click on the installed driver and click on the Configure button.
MySQL will be added post-clicking Ok.

How User Can Connect MySQL through OpKey

Users can verify with the help of “Two Keywords”: “Connect Database” for securing the connection and “ExecuteQuery” to run a query, fill the details and click OK.

Connect Database has two components, one is Driver, and the second is Connection String.

The user will have to put details of “system.data.odbc” on the Driver column and “DSN=Required DSN Name Configured in ODBC” on the Connection String Column.

For the Execute Query, the user will have to fill in details of “Sample Query” and “Command Time”

For respective queries, there will be respective results. When the connection gets established, both the queries will be executed which will display the results as True or False.

ODBC Configuration with PostgreSQL

User will have to enter the data for Driver as “system.data.odbc” and Connection as DriverODBC for PostgreSQL.

To connect ODBC with PostgreSQL

1.User will have to check the connectivity of the agent machine and where the database is installed.

The user will have to check the version of the PostgreSQL Database.
The ODBC driver will have to be installed of the same version as that of the PostgreSQL Database. The user will have to install the ODBC Connector. It can be downloaded online or a user can check with their respective IT Department.

To configure ODBC with PostgreSQL

Search for ODBC Data Source 64 bit on the Start Menu
Open the run as admin
Go to “System DSN’ tab
A window will open of “Devart ODBC Driver for PostgreSQL Configuration”.
User will have to 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)

Click on the installed driver and click on the Configuration button.
Post filling all the details PostgreSQL will be configured.

Connect Database and Execute Query components will have to be filled up by the user. The details would be:

Connect Database:

Driver– system.data.odbc

Connection String – DSN=DSN Name of configured postgres dsn

Execute Query:

Query- SELECTFROM publictest

CommandTime-60