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, it syncs every 5 minutes). To do that I wrote the following function:
def wait_record_in_db(conn_str, query_str, timeout=300):
conn = open_db_conn(conn_str) #This function is definitely working.
results = conn.execute_(query_str)
while results.EOF and timeout > 0:
Delay(20000)
results = conn.execute_(query_str)
timeout -= 20
return resultsBasically it just checks if the query returns any records, and if not waits until it does or the time limit has been reached. This way I can check if the record is in the database every 20 seconds and then return the results, instead of just hard-coding a 5 minute wait.
The problem is that the recordset that this function returns is closed for some reason. When I try to check if the returned recordset has any records in it like this:
results = wait_record_in_db(conn_str, query_str)
if results.EOF:
Log.Error('No record found')
else:
Log.Checkpoint('Record found')I get the following runtime error:
ADODB.Recordset
Operation is not allowed when the object is closed.
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
