In this article, I will show you how to connect MySQL Database in UFT using VBScript and perform read and write operations in MySQL Database Table. Nowadays MySQL database is widely used by developers as well as automation testers / SDETs.So you must know how to establish the connection of UFT with the MySQL database.
Generally, all of us have Microsoft office package got installed on our machines. Whenever we install MS Access Database and Excel as part of the MS Office package, their respective drivers to connect them using ADODB connection automatically gets installed in our machines. However, that is not the case with MySQL or any other databases. So, if you want to connect to the MySQL database in UFT using VBScript, first you will have to meet the prerequisites.
Prerequisite to Connect MySQL Database with VBScript
In order to connect the MySQL database with VBScript, we need a MySQL ODBC connector driver to be installed in our machine. You can get the latest version from https://dev.mysql.com/downloads/connector/odbc/.Once the MySQL connector driver gets installed on your machine you are good to proceed further.
You can check whether the MySQL connector driver is already installed on your machine. To check it go to Control Panel > System and Security > Administrative Tools and Open Data Sources (ODBC). I have mysql-connector-odbc-5.1.13-winx64 installed on my machine.
Read MySQL Database in UFT
The following is the Employee table.We will read and update this MySQL database table in UFT.
Since I have mysql-connector-odbc-5.1.13-winx64, I am using MySQL ODBC 5.1 Driver in the connection string. If you download the latest version which is currently mysql-connector-odbc-8.0.26-winx64, you may have to use MySQL ODBC 8.0 Unicode Driver. Anyway, you can check the exact value in the Data Sources (ODBC).
Code for reading MySQL Database Table
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 |
Set con = CreateObject("ADODB.Connection") Set ResultSet = CreateObject("ADODB.Recordset") 'ConnectString = "Driver={MySQL ODBC 5.1 Driver};Server=SERVER NAME ;Database=YOUR DATABASE NAME ;User=DB USER NAME;Password=DB PASSWORD;Option=3;" ConnectString = "Driver={MySQL ODBC 5.1 Driver};Server=localhost;Database=mytestdb;User=root;Password=;Option=3;" con.Open ConnectString SqlQuery = "SELECT EmpID,FirstName,LastName,Department FROM Employee ORDER BY EmpID" Set ResultSet = con.Execute(SqlQuery) 'The loop will iterate and display all EmpID,FirstName and LastName of the Employee Table Do Until ResultSet.EOF MsgBox "Emp ID = " + CStr(ResultSet("EmpID")) + " , First Name = " & CStr(ResultSet("FirstName")) + " , Last Name = " & CStr(ResultSet("LastName")) ResultSet.MoveNext Loop con.Close Set ResultSet = Nothing Set con = Nothing |
Update MySQL Database in UFT
In the following example, we will update the first name of the employee for Employee ID 104.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
Set con = CreateObject("ADODB.Connection") Set ResultSet = CreateObject("ADODB.Recordset") 'ConnectString = "Driver={MySQL ODBC 5.1 Driver};Server=SERVER NAME ;Database=YOUR DATABASE NAME ;User=DB USER NAME;Password=DB PASSWORD;Option=3;" ConnectString = "Driver={MySQL ODBC 5.1 Driver};Server=localhost;Database=mytestdb;User=root;Password=;Option=3;" con.Open ConnectString 'SQL Query to update First Name for the Employee having Empoyee ID 104 SqlQuery = "Update Employee Set FirstName='James' where EmpID=104" con.Execute (SqlQuery) con.Close Set ResultSet = Nothing Set con = Nothing |
Output: The first name got updated to James from John.
Conclusion
In this tutorial, we have seen how to read and update MySQL Database using VBScript in UFT.If you have any queries please mention them in the comment box and please don’t forget to share this article.
