QTP & QC->DB->Code for funcitons to connect to Database and exexute query.

'**********************************************************************************************************
'Function Name:   fun_OpenDBConnection(ip_objDBConnection, ip_strConnectionString)
'Description :        Used to connect to the database based upon the connection string passed
'Parameters:        ip_objDBConnection: Connection object which will be used for db connection
'        ip_strConnectionString : Actual connection string
'Return  value       0 : Indicates the successful deleteion of the file
'        Non Zero Vale: Indicates the failure and contains the error description for the failure  
'Example:            fun_OpenDBConnection(objDBConnection, "Driver={Microsoft ODBC for Oracle};Server=NALXTST2;Uid=ab4p_app01;Pwd=ab4p_app01_bibtb;")
'**********************************************************************************************************
Public Function fun_OpenDBConnection(ip_objDBConnection, ip_strConnectionString)
   On Error Resume Next
   ip_objDBConnection.ConnectionString= ip_strConnectionString
   ip_objDBConnection.Open
    If Err.Description <> "" Then
     '<<< Return the error code along with the description if function is unable to make connection >>>
     fun_OpenDBConnection = Err.Description & VBNewLine &  Err.source
     Err.Clear
     Exit Function
   Else
    '<<< Return 1 if function is able to make connection successfully >>>
    fun_OpenDBConnection = ip_objDBConnection.State
   End If  
End Function


'**********************************************************************************************************
'Function Name:   fun_ExecuteQuery(ip_objDBConnection,ip_strQuery)
'Description :        Used to execute the query to retrieve the record set
'Parameters:        ip_objDBConnection: Connection object which will be used for db connection
'        ip_strQuery : Actual query to be executed
'Return  value       Error text with error description
'          Recordset as a result of query execution
'Example:            fun_ExecuteQuery(objDBConnection, "Select * from MyTable")
'**********************************************************************************************************
Public Function fun_ExecuteQuery(ip_objDBConnection,ip_strQuery)
   On Error resume next
   Dim objRes
   '<<< Get the result set by executing the passed query >>>
    Set objRes = ip_objDBConnection.Execute(ip_strQuery)
    If Err.Description <> "" Then
     fun_ExecuteQuery = "ERROR: " & VBNewLine & Err.Description & VBNewLine &  Err.source
     Err.Clear
   Else
    '<<< ---------- Converting the Recorset to the array ---------- >>>
    Dim intTotalRows , intTotalColumns
    intTotalRows = 0
    intTotalColumns = objRes.Fields.Count - 1
    '<<< Calculating the total rows returned by the recordset >>>
    While objRes.EOF = False
      objRes.moveNext
      intTotalRows = intTotalRows + 1
    WEnd
    '<<< Forming the Array based upon t he total Rows and Total Coulns returned by the query >>>
    Dim arrCollection()
    ReDim arrCollection(CInt(intTotalRows) - 1, intTotalColumns)
   
    '<<< Get the recordset cursor to the first point >>>
    objRes.MoveFirst
    Dim intRowIndex, intColumnIndex
    intRowIndex = 0
    While objRes.EOF = False
      For intColumnIndex = 0 To objRes.Fields.Count -1  
       If  CStr(objRes.fields(intColumnIndex).Value) <> "" Then
          arrCollection(intRowIndex, intColumnIndex) = objRes.fields(intColumnIndex).Value   
       End If
       Next
      objRes.moveNext
      intRowIndex = CInt(intRowIndex) + 1
    WEnd
    '<<< Return the newly formed array >>>
    fun_ExecuteQuery = arrCollection
    objRes.Close
   End If
End Function


Example Code:-
 Set objDatabaseCon = CreateObject("ADODB.Connection")
 Set objRecordset = CreateObject("ADODB.RecordSet")

strConString="Driver={Microsoft ODBC for Oracle};Server=NALXTST2;Uid=ab4p_app01;Pwd=ab4p_app01_bibtb;")

strConnectionState =  fun_OpenDBConnection(objDatabaseCon, strConString)
    '<<< Exit the component if the connection status is not returned as 1 => connection was not successfll >>>
    If CStr(strConnectionState) <> "1" Then
     Fun_ReportFailure "Connecting to database " & vbNewLine & strConString, "Unable to make connection :" & vbNewLine & strConnectionState, Nothing
     ExitComponentIteration
    End If

    strQuery = "select * from bridge.pl2pom_logical_msg where system_id in (select system_id from bridge.pl2pom_xml_msg where correlation_id ='" & Trim(arrAllocCollection(3)) & "')"

    '<<<Collecting the result of the query in to an array>>>
    arrRecordSet =  fun_ExecuteQuery(objDatabaseCon, strQuery)
    '<<<----- Validate the return type from the function call ------ >>>
    If Not IsArray(arrRecordSet) Then
     If Instr(arrRecordSet, "ERROR") Then
      Fun_ReportFailure "Execute Query", "Unable to execute the following query due to the error :" & arrRecordSet & vbNewLine & " Query: " & strQuery, Nothing
     Else
      Fun_ReportFailure "Execute Query", "Unable to execute the following query : " & strQuery, Nothing
     End If
     ExitComponentIteration
    Else
     If UBound(arrRecordSet) < 0 Then
      Fun_ReportFailure "Execute Query", "Unable to retrieve any rows by executing the following query : " & VBNewline & strQuery, Nothing 
      ExitComponentIteration
     End If
    End If