Wednesday, January 30, 2013

QTP Integration with Oracle DB

You can connect to Oracle database and execute SQL statements by following steps:

  • Oracle client has to be installed on QTP (or UFT) host.
  • Assuming the tnsnames.ora file contains the desired database information.
Using following two functions can help you to execute SQL queries.


Function executeQuery(p_db, p_sql)
 Reporter.ReportEvent micPass, "executeQuery", "The SQL query: '" &p_sql & "' is going to be executed on: '" & p_db & "'."
 If getVal(p_db)="N/A" Then
  Reporter.ReportEvent micFail,"executeQuery","DB connection information is not available."
  executeQuery = -1
  ExitAction
 End If
 
 Set localConn = CreateObject("ADODB.Connection")
 Set localRs = CreateObject("ADODB.Recordset")
 Set localErr = CreateObject("ADODB.Error")

 On Error Resume Next
 localConn.open getVal(p_db)
 
 Global_DBErr = ""
 For Each localErr In localConn.Errors
  if Err.Number<>0 then
   Global_DBErr = Global_DBErr & Err.Number & vbCr &  " " & Err.Description
   Reporter.ReportEvent micFail,"Step 1","DB connection failed: " & Global_DBErr
   ExitAction
  end if
 Next
 
 On Error Resume Next
 localRs.open p_sql, localConn
 
 For Each Local_Err In localConn.Errors
  if Err.Number<>0 then
   Global_DBErr = Global_DBErr & Err.Number & vbCr &  " " & Err.Description
   Reporter.ReportEvent micFail,"Step 1","SQL Execution Failed: " & Global_DBErr
   ExitAction
  End if
 Next
 
 if localRs.eof then
  v_retVal = -1
 else  
  v_retVal = localRs(0).value
 end if

 set localConn = Nothing
 set localRs = Nothing
 set localErr = Nothing

 Reporter.ReportEvent micPass, "executeQuery", "Query result: [" & v_retVal & "]"
 executeQuery = v_retVal
End Function


Function getVal(p_key)
 v_retVal = "N/A"
 Select Case p_key
  Case "DB1" v_retVal = "Provider=MSDAORA.1;Password=DB_PASWORD1;User ID=LOGIN_USER1;Data Source=DB1;Persist Security Info=True"
  Case "DB2" v_retVal = "Provider=MSDAORA.1;Password=DB_PASWORD2;User ID=LOGIN_USER2;Data Source=DB2;Persist Security Info=True"
 End Select
 getVal = v_retVal
End Function

How you make use of these functions is as following:

 v_sysdateFromDB = executeQuery(Parameter("p_db"), "select sysdate from dual")

No comments: