Setting a DB connection to run any queries from QTP

Setting a DB connection to run any queries from QTP

How to set a DB connection and to get a value from an “adodb” Data Base

Public Function DBSelect

set conn = createobject(“adodb.connection”)

conn.open “DSN=DSN_SERVERhere; UserID=userID; Password=password;”

set rs = createobject(“adodb.recordset”)

‘get the date value from DB for an Event

rs.open “SELECT searchColumnHere FROM tableName WHERE columnName=’value’”, conn

eventsInDB = rs(“searchColumnHere”)

‘eventsInDB = FormatNumber(eventsInDB, 2) – use this if you need to format the output nr

rs.close

DBSelect = eventsInDB ‘here we are defining the output value

End Function

Or, if the query is defined somewhere outside, for e.g.:

getCol1data = UCASE(“SELECT ‘searchColumnHere’ FROM tableName WHERE columnName=’value’”) ‘”searchColumnHere” is the code name of  the column which’s value should be outputed. for e.g: CLBVAS (use from your select)

then the function can be wrote like this (I’ve added it within a class):

Public Function DBQuery

 DBQuery = New Query

End Function

Class Query

 Public Function DBSelect(sqlSelect, searchColumnHere)

  set conn = createobject(“adodb.connection”)

  conn.open “DSN=DSN_SERVERhere; UserID=userID; Password=password;”

  set rs = createobject(“adodb.recordset”)

  ‘get the date value from DB for an Event

  rs.open sqlSelect, conn

  eventsInDB = rs(searchColumnHere)

  ‘eventsInDB = FormatNumber(eventsInDB, 2) – use this if you need to format the output number

  rs.close

  DBSelect = eventsInDB ‘here we are defining the output value

 End Function

End Class

now the function is called like this:

DBQuery.DBSelect getCol1data “searchColumnHere”  ‘the “getCol1data” has the value assigned to it upper

or:

DBSelect getCol1data “searchColumnHere”  ‘when there is no class defined