QTP - Accessing Databases

Accessing Databases:

As such QTP doesn't provide any built-in support to connect to database, however using VBScript testers will be able to connect and interact with databases using ADODB objects

ADODB has 4 properties or methods with which we will be able to work with the databases.

• ADODB.Connection - Used to establish a connection to the Database
• ADODB.Command - Used to execute a SQL command(Queries or Stored Procedures)
• ADODB.Fields - Used to fetch a particular column from a record set after executing a query/stored proc
• ADODB.Recordset - Used to fetch data from a database

How to connect to Database?

Databases can be connected using Connection strings. Each database differ the way we connect to the them, however the connection strings can be build with the help of http://www.connectionstrings.com/

Now Let us see how to connect to the database with the following parameters.

• Database Type - MSSQL SERVER
• Server Name - SQLEXPRESS
• Database Name - Trial
• User Id - sa
• password - Password123

The Output of the Query is shown in the SQL Server Management Studio as follows:

 Dim objConnection 
 'Set Adodb Connection Object
 Set objConnection = CreateObject("ADODB.Connection")     
 Dim objRecordSet 
 
 'Create RecordSet Object
 Set objRecordSet = CreateObject("ADODB.Recordset")     
 
 Dim DBQuery 'Query to be Executed
 DBQuery = "Select NAME from dbo.EMPLOYEE where AGE = 29"
 
 'Connecting using SQL OLEDB Driver
 objConnection.Open "Provider=sqloledb.1;Server=.\SQLEXPRESS;User Id=sa;Password=Password123;Database=Trial"
 
 'Execute the Query
 objRecordSet.Open DBQuery,objConnection
 
 'Return the Result Set
 Value = objRecordSet.fields.item(0)    
 msgbox Value
 
 ' Release the Resources
 objRecordSet.Close        
 objConnection.Close  
 
 Set objConnection = Nothing
 Set objRecordSet = Nothing

RESULT

Upon Executing the above script the output is shown in the message box as shown below: