Working with database in UFT/QTP is a very good feature available. But, most of the times, connecting to database is a huge task due to
improper details. In this post, we will know how to connect to database successfully.
5 Steps for Successful Connection:
Interacting with the database requires five basic steps as listed below:
- Create ADODB connection object
- Create Recordsetobject
- Connect to DB using provider and server
- Write the SQL Query
- Execute the query
Using VB script ADO connection objects we connect to a database. The ADO objects we have are:
- ADODB.Connection: To connect to the database
- ADODB.Recordset – To get data from a database
- ADODB.Command – To execute SQL Queries
- ADODB.Fields – To get a particular column data from a record set
Sample Script – Retrieving value from database:
'Create ADODB connection object Set objConnection = CreateObject("ADODB.Connection") 'Create Recordsetobject Set objRecordSet = CreateObject("ADODB.Recordset") 'Connect to DB using provider and server objConnection.open "provider=sqloledb;Server=SDWA-160-PC;User Id=SDWA-160; Password=; Database=sample;Trusted_Connection=Yes" 'Write the SQL Query sqlQuery="Select * from emp" 'Execute the query objRecordSet.open sqlQuery, objConnection 'Display output value = objRecordSet.fields.item(0) msgbox Value objRecordSet.Close objConnection.Close Set objConnection = Nothing Set objRecordSet = Nothing
If you observe closely all the 5 steps are covered in the above program. Now, to setup a database connection we will look at each of these steps.
Create an ADODB connection object which is required step for connecting to database as below.
Set objConnection = CreateObject(“ADODB.Connection“)
Create Recordset object which is for getting a value from database.
Set objRecordSet = CreateObject(“ADODB.Recordset“)
Authentication using Id, Password and details.
- Provider: For SQL server database the provider is “sqloledb”
- Server: Open the SQL Server in your machine and Right click on the required Database/ click on properties.
Finally the Step 3 looks like
objConnection.open “provider=sqloledb; Server=SDWA-160-PC;User Id=SDWA-160; Password=; Database=sample; Trusted_Connection=Yes”
Hence, Connection setup completed and ready for execution.
Enter the required SQL query to be executed.
Execution of the query with the statement below.
objRecordSet.open sqlQuery, objConnection
Testing the Connection:
This is all about the connecting to database in UFT/QTP. If you have any questions or need any clarifications, Please post in the comments section below.