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
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
