QTP and the SQL Database

How often have you landed up in a situation where you are required to extract a value from database and compare it with a value on the screen? How often has it been the "only way" of validating the integrity of data on screen?

Well I have come across such need umpteen number of times. We can use QTP to execute a SQL query and process the results within a script/ business component. It is the powerful VBScript that enables us to execute database queries within QTP. So here is an example to showcase how a value from database is retrieved using QTP. This is something I used in many of my business components that interact with database.

My recommendation is to include this as a function in the library and design a different function that stores user id and password and other database security information. In this example everything is in the same function just to give a picture to the reader.

Dim Con, RS
Dim strUserId
Dim strPassword
Dim strServer
Dim strDataBase
Dim strSQL
Dim strConnectionString
Dim dbFirstValue
  
strUserId ="userID"
strPassword = "password"
strServer= "SQLServerName"
strDataBase= "databaseName"

Set Con  = CreateObject("adodb.connection")
Set RS = CreateObject ("adodb.recordset")
       
Con.Open strConnectionString
rs.open strSQL,Con
Do While Not RS.EOF
    dbFirstValue=RS.Fields(0).Value
    reporter.ReportEvent micDone,"FirstDBValue - ",dbFirstValue
    RS.MoveNext
    Exit Do
Loop
Con.Close