26.15 Performing Database Operations
- SQLITE
- MySQL
- ORACLE and
- SQL SERVER
- PostgreSQL
- RedShitDB
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