QTP - 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 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
Upon Executing the above script the output is shown in the message box as shown below: