Forum Discussion

Priyanka_Naveen's avatar
Priyanka_Naveen
New Contributor
15 years ago

How to return a recordset from a function and process it accordingly.

I want to return a database recordset from a function and read records one by one from the returned recordset. But as the control reaches

the statement "RecSet.Open ", script execution stops abruptly.



Sub sub_CallFunction

'    -------------------------------------------------------------------------------------

Dim RecSet, varServiceAgentCount                 ' Database Integration

MsgBox(aqObject.GetVarType(RecSet))        ' Returns 0, i.e. "vbEmpty" Type

Set RecSet = CreateObject("ADODB.Recordset")          

Set RecSet = fn_ExecuteQuery("select count(*) from user_information ui "_

                                      & " where ui.is_clientmanager_user='Y' and ui.account_dead='N'")

MsgBox(aqObject.GetVarType(RecSet))        ' Returns 9, i.e. "vbObject" Type

Set colMethods = aqObject.GetMethods(RecSet)  

While colMethods.HasNext

    Log.Message(colMethods.Next.Name)

  WEnd

' Returns following methods:

' AddNew, CancelUpdate, Close, Delete, GetRows, Move, MoveNext, MovePrevious, MoveFirst, MoveLast, Open, Requery, Update,  

' UpdateBatch, CancelBatch, NextRecordset, Supports, Find, Cancel, GetString, CompareBookmarks, Clone, Resync,Seek, Save

 

Set colProperties = aqObject.GetProperties(RecSet)

While colProperties.HasNext

    Log.Message(colProperties.Next.Name)

  WEnd

' Returns following properties: 

' Properties, AbsolutePosition, ActiveConnection, BOF, Bookmark,CacheSize, CursorType, EOF, Fields, LockType, MaxRecords,

' RecordCount, Source, ' AbsolutePage, EditMode, Filter, PageCount, PageSize, Sort, Status, State, CursorLocation,

' MarshalOptions, DataSource, ActiveCommand, StayInSync, DataMember, Index

RecSet.Open                                                 ' Script execution stops: ADODB.Recordset.

                                                                     ' The connection cannot be used to perform this operation. It is either closed or invalid in this context 

RecSet.MoveFirst

while Not RecSet.EOF

    Call Log.Message(RecSet.Fields(0).Value)

Call RecSet.MoveNext 

wend  

Next

End Sub


Function fn_CalledFunction

'    ----------------------------------------------------------------------------------------

Set AConnection = ADO.CreateADOConnection

  AConnection.ConnectionString = "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;Password=g8PBgbADQksh7WFSy+MDIbdZQyQ4g90h;Initial Catalog=ClientManager;Data Source=d1950SQL1"

  AConnection.LoginPrompt = False ' Suppress the login dialog box 

  AConnection.Open

  Dim RecSet

  Set RecSet =  AConnection.Execute_(varQuery)

  MsgBox(RecSet.Fields(0).Value)                                 ' Returns total number of records

  Set fn_ExecuteQuery =  RecSet

End Function

2 Replies

  • tristaanogre's avatar
    tristaanogre
    Esteemed Contributor
    15 years ago
    In your code where you're creating the record set and executing a query, I don't see anywhere where you're assigning the connection string to the record set, unless I'm missing something.



    Here is an excerpt from a code unit that I wrote in JScript that returns a record set based upon a given SQL query string.  See if this helps.  Obviously, you'd have to adapt syntax and such for VBScript



    var lDatabaseName;

    var lSQLServerName;

    var lConnectionString;

    var lConnectionObject;





    function SQLUtilities_Initialize()

    {

    if (!Project.Variables.VariableExists("DatabaseName")) 

            {

            Project.Variables.AddVariable("DatabaseName", "String")    

            }

        if (!Project.Variables.VariableExists("SQLServerName")) 

            {

            Project.Variables.AddVariable("SQLServerName", "String")    

            }

        lDatabaseName = Project.Variables.DatabaseName;

        lSQLServerName = Project.Variables.SQLServerName;    

        if (lConnectionObject == null) 

            {lConnectionObject= Sys.OleObject("ADODB.Connection")};

        

        

    }





    function RefreshSettings()

    {

        SQLUtilities_Initialize()

        lDatabaseName = Project.Variables.DatabaseName;

        lSQLServerName = Project.Variables.SQLServerName; 

    }





    function GetConnectionString()

    {

        RefreshSettings();

        lConnectionString = 'Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=';

        lConnectionString = lConnectionString + lDatabaseName;

        lConnectionString = lConnectionString + ';Data Source=' + lSQLServerName;

        return lConnectionString;

    }




    function ReturnQueryRecordSetFromString(QueryString)

    {

        try

        {

            RefreshSettings();

            lConnectionObject.ConnectionString = GetConnectionString();

            lConnectionObject.Open()        

            var RecordSet = lConnectionObject.Execute(aqConvert.VarToStr(QueryString));

            return RecordSet;

        }

        catch (e)

        {

            Log.Error("Error running QueryString.  See Additional Information for QueryString value: " + e.description, aqConvert.VarToStr(QueryString))

            return aqObject.EmptyVariant;

        }    

    }
  • irina_lukina's avatar
    irina_lukina
    Super Contributor
    15 years ago

    Hi Priyanka,


    It is not obvious from your code how you connect to the database. I cannot see the lines that connect to the database in your code. Maybe the problem is related to the connection settings?

    Below there are some helpful links that contain information on working with databases from TestComplete tests:

    1. Working With ADO Command Object

    This help topic contains an example on using the ADO Command object.

    2. www.connectionstrings.com

    Contains connection strings examples.