An example:
Master Method calls Method 1:
Method 1: Opens the connection and reports True if the connection is established.
Method 2: Does the querry and handles errors. This Method is returning the RecordSet back to the “Master Method”.
Can i close an open DB connection right after the “Master Method” has received the RecordSet and will the RecordSet then be destroyed once the “Master Method” ends?
Whether or not the current Xojo implementation allows you to close the db before you are through with the recordset, I wouldn’t do it. It’s just not “proper” coding.
Open the DB
Get a RecordSet
Deal with the RecordSet
Close the RecordSet
Close the DB
Make no assumptions about the validity of the recordset in the absence of a valid database connection.
Thank you @Tim Hare.
In my example, “Close the RecordSet” would be done automatically once the “Master Method” ends. Because the RecordSet is no property of any other object. Is this correct?
Having the RecordSet go out of scope may not be the best way to close it or the database connection depending on the database engine and how the Xojo framework handles that situation. A better practice would be to close the RecordSet yourself and also close the database connection yourself as soon as you’re finished with each. Resource utilization will be kept down and database administrators will be kept happy.
I remember not having closed thr recordsets in a application of mine. This worked fine while using sqlite and cubesql, but not at all when we switched to MS SQLServer using ODBC connectivity. I had to explicitely nil out a recordset before re-using it then.
I am using onboard Plugin for the mySQL connections and MBS for MSSQL connections.
In both cases, i never close the RecordSets. I am monitoring the memory usage of my App often and never saw any issues.
I am using 6 mySQL connections and 1 MSSQL connection in my App. All connections querry the Server thousands of times throughout a day from 5-15 machines (LAN and VPN).
[quote=211973:@Sascha S]Thank you @Tim Hare.
In my example, “Close the RecordSet” would be done automatically once the “Master Method” ends. Because the RecordSet is no property of any other object. Is this correct?[/quote]
That is correct. Objects are reference counted and are closed and destroyed when the last reference is released.
Thank you for the claryfication @Tim Hare
I would add, if you intend to reuse the recordset, you do have to explicitly close it, especially, as noted, with MS SQL, where each db connection can only have one active recordset at a time. The old recordset isn’t dropped until after the new one is created, so you will run into a collision. But if you just let it go out of scope, you don’t have to close it. The framework will do that for you.
I looked into that code ( it is about 5 years old) and found a comment explaining that setting the recordset to Nil after using it eliminated a problem with a ‘results pending’ error from the ODBC connection.
I don’t have access to the setup of those days and I don’t know if the error still would occur today…
I believe that is still an idiosyncrasy of MS SQL. But I would recommend closing the recordset (setting it to NIL is the same as closing it if you only have one reference, but better practice is to explicitly close it) before you reuse the variable.