How to connect Database from UFT/QTP ? Know How.

By TestNBug in UFT QTP Basics 3 Comments
ConnectingUFT/QTP to database

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

 

Connecting Database:

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

 

Observation:

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.

Step 1:

Create an ADODB connection object which is required step for connecting to database as below.
Set objConnection = CreateObject(“ADODB.Connection“)

Step 2:

Create Recordset object which is for getting a value from database.
Set objRecordSet = CreateObject(“ADODB.Recordset“)

Step 3:

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.

 
connecting-database in UFT QTP

Click on “View connection Properties”
 
Click on View Connection properties in UFT QTP Database conncetion

User Name and Server details are required from this dialogue box
 
Properties for connection database to UFT QTP 

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.

Step 4:

Enter the required SQL query to be executed.

Step 5:

Execution of the query with the statement below.
objRecordSet.open sqlQuery, objConnection
 

Testing the Connection:

The above mentioned sample script has been executed and the value from the database is retrieved.
 
succesful Connection of UFT QTP

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.

Related Articles.