when trying to retrieve multiple values from database in qtp, it is not returning any values even after connection is established.
Question ID: 105956
2
0

i am trying to retrieve multiple values from database and save it in datatable. it is not returning any values .

Marked as spam
Posted by (Questions: 1, Answers: 3)
Asked on February 26, 2015 11:21 am
274 views
Answers (4)
2
Private answer

You have to get a recordset out of the database and iterate through it to enter the values into the datatable.

This page (Using Database Functions (microfocus.com)) has some good examples on it for a variety of things. The last one on the list “Function represent_values_of_RecordSet( myrs)” can be modified to insert the data into the DataTable instead of reporting it as they do in this function. You would use the other functions on that page to connect to the DB (db_connect) and get the recordset (db_execute_query).

Don’t forget to dismiss your objects and disconnect from the database.

Marked as spam
Posted by (Questions: 16, Answers: 807)
Answered on March 16, 2023 4:42 pm
0
Private answer

I am using below code:-
Function FetchValue_DataSource_AllValue_DataTable(strDataSource,strSqlQuery)

On Error resume Next
Dim oCon, oRes

err.clear
Set oCon = CreateObject(''ADODB.Connection'')
Set oRes = CreateObject(''ADODB.Recordset'')
oCon.Open strDataSource
If oCon.State = 1 Then
Set oRes = oCon.Execute(strSqlQuery)
oRes.Open strSqlQuery, oCon,3,3,1
If oRes.EOF = ''False'' Then
oRes.MoveFirst
intmaxCount = oRes.RecordCount
intColCount = oRes.Fields.Count
For j = 0 To intColCount -1 Step 1
datatable.LocalSheet.AddParameter oRes.Fields(j).Name,''''
Next
For i = 0 to intmaxCount-1
datatable.LocalSheet.SetCurrentRow(i+1)
For j = 0 To intColCount -1 Step 1
strDTColName = oRes.Fields(j).Name
strDBColName = oRes.Fields(j).Name
' If Instr(1,strDTColName, Then strDTColName = ''COUNT''
If strDTColName = ''COUNT(*)'' Then strDTColName = ''COUNT''
If IsNumeric(Mid(strDTColName,1,1)) Then strDTColName = ''_'' & strDTColName
datatable.Value(strDTColName,dtLocalSheet) = oRes.Fields(strDBColName).Value
Next
oRes.MoveNext
Next
Else
result= ''Null''
end If
Else
Call Sub_WriteToResultsLog(1,''Not able to connect with DB, Please Verify Manually'','''')
result= ''Null''
End If

If err.number <> 0 Then result= ''Null''

FetchValue_DataSource_AllValue_DataTable = result

If oRes.State = 1 Then oRes.Close
If oCon.State = 1 Then oCon.Close

Set oRes = Nothing
Set oCon = Nothing

On Error Goto 0

End Function

Marked as spam
Posted by (Questions: 1, Answers: 3)
Answered on February 27, 2015 6:16 am
0
I don't see anything obviously wrong. One thing you could try is commenting out the ''On Error'' statements, then run the code again to see if any exceptions surface.
( at February 27, 2015 5:24 pm)
0
Private answer

hi,
yes i am sure that query is finding data. When i am using that query in sql Developer it is returning appropriate data. Actually the problem is when i am trying to fetch multiple values it is hot returning anything. And when i am trying to fetch single value, it is returning expected value.
Also, the problem is with 32 bit machine. Same code for fetching multiple vales is working in 64 bit machine.

I have installed oracle client 11g in my machine.

Marked as spam
Posted by (Questions: 1, Answers: 3)
Answered on February 27, 2015 6:12 am
0
Private answer

Post a copy of your query code. Are you absolutely sure your query is finding data? Have you tested the query through another interface? What version of the product are you using?

Marked as spam
Posted by (Questions: 1, Answers: 101)
Answered on February 26, 2015 5:43 pm
EyeOnTesting

Welcome back to "EyeOnTesting" brought to you by Orasi Software, Inc.

X
Scroll to Top