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.