Good questions Mark! I have re-read the Xojo documentation several times, and it does seem difficult to quite pin down the exact “do’s & don’ts”.
I do now have a cgi web app (using Sqlite DB) running on Apache server which has now been up 24/7 since early September, and all Sessions now close reliably. “Exactly” what I did to fix it still remains a bit of a mystery!
Like you, I originally built the DB connect routines into the Session.Open event, and stored the DB objects and Recordset objects as Properties of Session, so that they would remain in scope throughout the Session. I also stored a SMTPSecureSocket object as a Session Property.
Anyway, I have now redesigned the app, so that Session does not contain any properties other than String and Integer variables.
What I have done now, is to create a Funtion (stored as a Method in Session called, let’s say, “DBConnect”) which returns a connected DB object to any calling Method (located in any WebPage or WebContainer). So, whenever a Read or Write to a DB is needed, anywhere in the app, I create a Method in that container that starts with a line like this:-
Dim myDB as SQLiteDatabase = Session.DBConnect
Sometimes I keep record pointers as String or Integer variables within the Session object, so that I can simulate “Find previous / next” records, or to “re-find” the current record prior to updating it, for example.
Once record processing has been completed, I check myDB for any errors, and finally I close the recordset object and close the DB object. I stick religiously to this format in every Method that needs DB access.
I now use similar technique for SMTPSecureSocket.
Runtime speed seems perfectly fast, with Record updates, or Recordset retrievals almost instant, with the server in Holland and the browser in Cyprus.
One other thing that I found caused me trouble with stuck sessions, was storing a reference to a web object as a property in another object. For example, I had a WebDialogue with a Yes button and a No button. The Dialogue had a property called “ButtonClicked” and the type of property was WebButton. In each button’s Action event my code was like:-
ButtonClicked = Me
The Dialogue’s Dismissed Event had code like:-
If ButtonClicked.Name = "cmdYes" then
do something else
However, I subsequently changed this arrangement, and the ButtonClicked property is now just a String, NOT a WebButton.
The Button’s Action event was changed like:-
ButtonClicked = Me.Name
I have no real idea, which of the above cured my “stuck sessions” - but I did not see totally trouble-free operation until I had implemented ALL of the above.
It would be really helpful if Xojo could produce some kind of “Best Practice” document specifically aimed at cgi WebApps design.