Forum Discussion

ghuff2's avatar
ghuff2
Contributor
9 years ago

ADO recordset is closed when being returned from a function

At times I need to check if a record has been inserted into an SQL database. Sometimes I need to wait for the record to be inserted into the database (we sync certain records between two databases, i...
  • leandroaraujoso's avatar
    leandroaraujoso
    9 years ago

    Hello ghuff2,

     

    The answer is very simple but painful, you just can't. The ADO connection closes automatically when the function ends. 

     

    What I did in order not to rewrite a lot of code:

     

    1 - I wrote a class that called Database;

    2 - Database has the methods Close(), Open() and ExecuteQuery() that incapsulates the ADO methods for those actions;

      

    Each time I have to query a database I do the following:

     

    Database.Open();

    var rs = Database.ExecuteQuery(sqltext);

    //Do something with the recordset

    while (!rs.EOF){

    //Do something

    }

    Database.Close();

     

    Another option is to create a complex type using either dotNet or JavaClasses and fill the type with the recordset content and then return your type filled with data. This is a option but it is a bit slow.

     

    Regards,

     

    Leandro de Araújo Souza

leandroaraujoso's avatar
leandroaraujoso
Contributor
9 years ago

Hello ghuff2,

 

The answer is very simple but painful, you just can't. The ADO connection closes automatically when the function ends. 

 

What I did in order not to rewrite a lot of code:

 

1 - I wrote a class that called Database;

2 - Database has the methods Close(), Open() and ExecuteQuery() that incapsulates the ADO methods for those actions;

  

Each time I have to query a database I do the following:

 

Database.Open();

var rs = Database.ExecuteQuery(sqltext);

//Do something with the recordset

while (!rs.EOF){

//Do something

}

Database.Close();

 

Another option is to create a complex type using either dotNet or JavaClasses and fill the type with the recordset content and then return your type filled with data. This is a option but it is a bit slow.

 

Regards,

 

Leandro de Araújo Souza

  • ghuff2's avatar
    ghuff2
    Contributor
    9 years ago

    Ah, that's too bad. Guess things can't be easy all the time.

     

    Thanks for the suggestions, I'll try to do it with a Class as well.