where do my return values go? (SQL-Query)

[Tim.Schmidt]

Hi everybody,

I use

Code:

objDatabase.execute(querystr)

in a fucntion, whereas the querystr is a select statement, which is tested to work properly.

when assigning it to a variable

Code:

functionname = objDatabase.execute(querystr)

I am using the name of the function as the variable, to pass the value returned to an if clause.

Code:

if functionname() = "1" then

QTP gives me an error msg:

"Wrong number of arguments or invalid property assignment."
and quotes the line I am using the function in. (shown above)

Am I doing something wrong? Did i forget anything?
Any help would be great

[somisays]

Hi,
Can you give more information on SQL query....
What is your SQL query ?This depends on what SQL query returns.
If you want to connect database read the following post
https://www.learnqtp.com/forums/Thread-DatabaseExample

[Tim.Schmidt]

The Connect works, the query works, all tested.

the query will either return a "0" or a "1", or nothing ofc.

[somisays]

if functionname() = "1" then

Hi,
In the above code why you are using() ?
Because you already got the returnvalue in functionname ?
Take out the () out and try.
I think if you post the code then we can help you...

[Tim.Schmidt]

that was just an example.

Actually its

if CheckStat(AppID) = "1" then

(for AppID insert number from 1-12, it is used in the query later)

Full Code for the Function:

Code:

Function CheckStat (AppID) Dim objDB, strupdate Set objDB = CreateObject("ADODB.Connection") objDB.ConnectionString = "..." objDB.Open strupdate = "select mailstat from systemstatus where systemid='"&AppID&"' and testtime=(select max(TESTTIME) from systemstatus where systemid='"&AppID&"')" CheckStat = objDB.Execute(strupdate) objDB.close End Function

[Tim.Schmidt]

Hi again,

I tested some stuff.

Code:

msgbox (objDB.Execute(Querystr))

doesnt work at all I presume (type mismatch), just smth I wanted to try to make sure he gets the right value from the database.

back to topic:

anybody have any idea how to resolve this issue?

Maybe Options/Preferences that need to be configured?

the issue is rather urgent, any help would be very good!

[surya_7mar]

Below is the code using which you can retieve data from the DB and stores in a Sample Array

Code:

Function GetDBQueryData_Fun (sClient, sQuery) Dim sDataSource sDataSource = envDBInstance Dim strConnection, oDbConn, rs, strSQL strConnection = "Provider=OraOLEDB.Oracle;Data Source="&sDataSource&";User Id="&sClient&";Password="&sClient&";" Set oDbConn = CreateObject("ADODB.Connection") oDbConn.Open strConnection If oDbConn.State <> 1 Then Reporter.ReportEvent micFail, "Unable to Connect", "Unable to Connect to the DB using the Schema " & sClient ExitComponentIteration End if Set oResultSet = CreateObject("ADODB.recordset") oResultSet.open sQuery, oDbConn, 3,3 If oResultSet.EOF Then oResultSet.close Set oResultSet = Nothing oDbConn.Close Set oDbConn = Nothing GetDBQueryData_Fun = Array("") Exit Function End If oResultSet.MoveFirst Dim arrOutput() Dim iIter, sDBRow,i iIter = 0 Do Until oResultSet.EOF sDBRow = "" For i = 0 to oResultSet.Fields.Count - 1 If i = 0 Then sDBRow = oResultSet.Fields(i) Else sDBRow = sDBRow&"<NextCol>" & oResultSet.Fields(i) End If Next ReDim Preserve arrOutput(iIter) arrOutput(iIter) = sDBRow iIter = iIter + 1 oResultSet.MoveNext Loop oResultSet.close Set oResultSet = Nothing oDbConn.Close Set oDbConn = Nothing GetDBQueryData_Fun = arrOutput End Function

[Tim.Schmidt]

Do I really need all that code??

I just want 1 value returned, so I dont need a whole array.

There is no way around the RecordSet?

[somisays]

Dear Tim,
How many columns you are retrieving in the SQL query ?

[Tim.Schmidt]

Hi somisays,

I retrieve 1 Column, with 1 Value (line).

nothing more.