Web App with SQLite Database disconnection

Hello Guys,

I have an app that has a SQLite Database in the back end. The database connection / disconnection happens in the Session.

It seems that after a while the database closes and the user ends with the data on the form filled but cannot save as the db is closed .

What would be the best way to handle this ?

Put a timer to check if connection is still there and if not to connect again ?

Thanks .

Is it that or is the database just read-only for that user?

Have you set the SQLiteDatabase.MultiUser property to True?

[quote=342892:@Greg O’Lone]Is it that or is the database just read-only for that user?

Have you set the SQLiteDatabase.MultiUser property to True?[/quote]

Hi Greg,

Hmm, i don`t know why i had the feeling that i did that, so far it was not there, i added the code and now is more stable so far.

In the case i have the Client API and the web interface on the same SQLite Database how i should handle the connections ?

Because the API is handled on HandleSpecialURL i Have to connect on the database on the open event and not on the session event so Is it recommended to have same or should i do a clone database and sync them from time to time and handle them separately ?

Unfortunately that is the restriction so far , i cannot use anything else than the SQLite, planning to request approvals for a MySQL Database so that no more headache, but it will take some time until they might approve it.

Thanks again .

Well just did a test with leaving the screen for a while and the database closed, so i ended up with not being able to do anything anymore . The only way is to logout and login again, time when it will crash because i guess could not connect to the database .

So what is the best option in this way ? i Should connect the database on the App.Open event and use transaction style to handle the changes on the database ? or there is a way to reconnect the session ?

What i did is, i put on the save button a statement where it checks if the connection is on of off and if off to connect again but it does not seems to be working unless you Logout then Login Again.

So i assume that the Session died and it has to login to create another session .

In this case what is the best approach ?

Thanks

The Workflow is the following

User Choose the Designated forms,
It start to fill the forms, in this time it seems that if only one user is Logged in it creates a timeout after a while and before even he presses the save button it crashes as the connection is not active anymore so as far as i remember mouse events suppose to trigger the session alive part but in a way it does not do that and it dies.

As a workaround, it leaves the form as it is , then it opens another tab, log in there and then go back to the current tab and press save, and it will save the data properly .

As a note Database definition is under App.Database property not under Session.Database, that might be an issue ?

I`m just looking to EddiesElectronics to compare and see that it is different , so trying to adapt.

Thank you.

Well it seems that no matter what i do and how i do the connection dies and does not come back so the user once the Save time comes it looses the data i used same way as Eddies Electronics web has and still Connection dies, i have no idea what other option to use here

Any ideas ?

Thanks to all .

As an update, i put a web timer on that page to refresh every 10 s and if db is down to reconnect to it so it seems stable so far, i have no idea if its a good idea or not but it works so far so its good.

And … died after 25 min , with no idea why and no report on what happened, just died, it`s a standalone app .

There is no reason that a SQLite database should be “down”. That said my recommendation is that you make a separate instance of the SQLiteDatabase object pointing to the same database for each session. This will keep you out of trouble mixing queries and transactions from multiple people. As for HandleUrl or handlespecialurl, I’d make a new connection on each request.

Just out of curiosity, what are you expecting to get? If the app is crashing because of an exception, it’ll be in one of the UnhandledException event handlers as I mentioned before. That’s really the only way that you’ll be able to track this down.

[quote=342992:@Greg O’Lone]There is no reason that a SQLite database should be “down”. That said my recommendation is that you make a separate instance of the SQLiteDatabase object pointing to the same database for each session. This will keep you out of trouble mixing queries and transactions from multiple people. As for HandleUrl or handlespecialurl, I’d make a new connection on each request.

Just out of curiosity, what are you expecting to get? If the app is crashing because of an exception, it’ll be in one of the UnhandledException event handlers as I mentioned before. That’s really the only way that you’ll be able to track this down.[/quote]
Thanks Greg,

So what i did as you recommended and as i found in Eddies electronics, i moved all the database references and properties in the Session and now no more errors.

One weird thing and i guess i did a mistake somewhere , the database once opened it creates 2 files -shm and -wal , before once i close all the sessions all those disappear and remains just the main db so what i did , i copied all the files on the usb stick, i opened them from mac with a sqlite viewer altered the tables and closed the viewer, still those 3 files on the stick but i got a message that the location of the db does not allow WAL so all the previous data will not be available.

I moved all the 3 files on the server side again and started the app.

All fine here until i closed the app and i deleted the -shm and the -wal files manually, once i did that all the data that was written there previously disappeared.

SO i noticed before that once all closed the 2 files disappear and the main db gets updated seeing the Date Modified on it, so far now , all the updates are done in the -wal file and no more in the main .sqlite file.

Any advices on this ?

Is there a way from admin side to kill all the sessions and to close the database in order to sync all ?

Thanks in advance.

RIght. SQLite writes data to those files in multi-user mode, and deleting them could definitely cause data loss. It would be better to properly close the database from within your app and let it finish the cleanup. You should never delete those files manually.

I also don’t suggest editing SQLite files on USB sticks. As you found, depending on the format of the flash drive, you may run into issues like that. It would be better to copy the file to your computer, manipulate the database and then copy it back to the flash drive to take it back to your “server” (or better yet, use something like SFTP).

App.Quit would do it, but I think you’re thinking about this backwards. Typically if you have users online all the time, you would do your manipulations in a copy of the database, record the changes that are being made and then apply them to the live copy during an upgrade maintenance period.

[quote=343450:@Greg O’Lone]
App.Quit would do it, but I think you’re thinking about this backwards. Typically if you have users online all the time, you would do your manipulations in a copy of the database, record the changes that are being made and then apply them to the live copy during an upgrade maintenance period.[/quote]

Well usually the way you have to close apps is trough closing the opened window in the case of windows and standalone or killing the app, and by doing that i guess the close event never fires so that i can put there database disconnection and closing .

I did tried a user stopping alternative by putting this in a admin control and firing this ,

[code]For i As Integer = 0 To App.SessionCount-1
App.SessionAtIndex(i).MsgBox(“Please save your work, we will shutdown after 1 minute for maintenance update.” + EndOfLine + “Thank you.”)
Next

shutdownTimer.Mode = Timer.ModeSingle[/code]

and the timer event has :

[code]For i As Integer = 0 To App.SessionCount-1
App.SessionAtIndex(i).MsgBox(“We are shutting down now.”)
App.SessionAtIndex(i).Quit
Next

If App.SessionCount = 0 Then
Session.fmsDB.Close
Else
Return
End If

[/code]

But it seems that unless the user press ok on each popup the event never fires, or at least one has to press ok, this is what i found,

And the funny part is that on mac it does closes the databases properly and does all the cleaning and closing, while on the windows side it does not do that , so the way i doit now i modified the SQLite backup app from samples and i copy as you said all those files on a mac on disk and open the db, do the backup , creates a single file db and then i delete the old one and put the new one.

So still tweaking to be done but i guess i`m close.

I did put a timeout on the app so that the session gets closed for the users that stay logged in but i guess i did it wrong as it never works.

Basically i need if nothing happens after a while , 3 min let`s say to get signed out and back to login page.

Thanks again.

Which Timeout property did you set? There’s one on App and one on Session.

This is the one you want:
http://documentation.xojo.com/index.php/WebSession.Timeout

Hi Greg, sorry for delay, maybe i`m doing it wrong .

So the app uses a connection in Session then handle the transactions normally.

Now, on a admin page i have 2 buttons, Backup DB, which will backup the db to a file and Maintenance ON,

Using the maintenance ON part should kick all the users out or disconnect them and then check if no more connections to the db to properly close the db so that i can close the app for update purposes.

Now what actually happens, the db never closes, so where i should put the code in order to be able to close the db ?

The WAL file grows and grows and never dumps to the file unless i do a backup and delete the 3 files and put the backup and rename so it`s quite annoying .

I looked into the Eddies Electronics for web and it seems that the DB is initiated once in the Session Open even and then used all over the app. That is partially good for my side but what i do when i need to kill the app and properly close the db ?

I have a Thread that i call once the button is pressed and has the following code :

[code]For i As Integer = 0 To App.SessionCount-1

App.SessionAtIndex(i).Quit

Next i

me.Kill[/code]

And then on the Session Close event i have the disconnect DB code to close the db , but still i think i`m doing something wrong as the DB never closes.

Thanks again.

Ok, first of all, database connections will automatically close when the variable that holds it goes out of scope. Likewise, Sessions will automatically close when all references to them go out of scope and the web framework only keeps one reference to each session, which is deleted when the framework determines that the user has been away from the app for a sufficient amount of time.

There’s some gotchas here that you need to consider:

  1. If the user immediately refreshes their browser, another session will be created. It would be better to put your app into “maintenance mode” And not allow users to log back in, and redirect users away from your app to another url and then call Session.Quit.
    0.if you use AddHandler anywhere in your app, make sure you have corresponding RemoveHadler calls. This must be done outside of the Destructor though, because the Destructor method will not fire if the handled events haven’t been released.
  2. Web framework views very specifically only hold hard references to its immediate children and never the other way around. For instance, users will sometimes create a reference to a WebPage from a WebContainer which is contained on that page. This creates a circular reference (object A points to object B and vice versa) making it impossible for the WebPage to release, and therefore the Session which contains it. This is true for any control which has a property which refers to a WebPage or a WebSession. When you’re done with them, you need to make sure to set them Nil.

I’m a little concerned about this. The WAL file should be committed whenever you call Database.Commit. Are you committing after calling SQLExecute?

Well most of the places i have just view only so i always use the recordset to fetch data , but whenever i update or add something i always commit, but it seems that the funny part happens on Windows, due to the restrictions i`m obliged to use Windows 7 so i guess there is an issue there as well, but on mac works flawlessly on windows always that wall grows and never closes, so it is strange for me as well.

Thanks for the above advices as well, i was thinking to redirect as well to other page, as for the maintenance page, once is on and the timer fires they cannot use the app anymore it shows a static page with maintenance mode same as the database not supported, and there i put code as well to close db but still, works only on mac properly and closes the db but on windows never.

As a small update, it seems that the app does not get corrupted, the Database have some weird thing in it, if i close all the users and then restart windows, as the app is standalone webapp and set to start on startup it always crashes, so what i do, i have a backup before i restore that and it starts perfectly with the new database, or i leave the database untouched as it is with the 3 files and i just replace the whole app folder and it works properly so i have no idea what is saved from the app that keeps that reference there and does not read the db after restart, being a corporate network some times they restart the pc`s for updates so whenever this happens the app crashes.

I will try to change the way the app uses the database, maybe it will help, but i still don`t see the link between windows not working and mac working properly .

On Both cases the app data files is sitting in ApplicationData folder so normally it suppose to work without issues .

Thanks .

Where is the DB property set in session or app?

Hi Derk,

Session.