Hi all! I am about to embark on the development of my first web app. I’ve been doing desktop and iOS for several years and am quite comfortable with these, but web is new to me
I started working through the examples and am going to be reading through the web tutorial to get started
I have a couple really basic questions on web:
For the database, you cannot use a local SQLite db and must use MySQL or alternative, right? I am quite comfortable with MySQL as I have actually been using this in parts of my desktop apps. If my assumption is correct, my question is do you need to make a new connection to the db every time you make a call to it? I have been doing this in my desktop app. Seems redundant, but I recall MySQL seems to lose connection after some time, and therefore, I needed to reconnect to it each time I wanted to read or write to it. Is this still the case for web? Is it even necessary for desktop, or am I just adding in too much effort with a new connect each time?
I currently have my MySQL db hosted on SiteGround. I will probably be opting for Xojo Cloud when the time comes to deploy. Does a Xojo Cloud subscription also come with MySQL db access where I can eventually get rid of SiteGround and just pay for one host, or will I still need my own db host?
In the couple of examples I worked through, I did not see any module for global properties and methods. Do your global properties in web go into the Session? For example, my app is a study guide app. The user will choose a test. I take various variables from the user’s selection and use them on different screens, such as the test name, test time, etc. Would properties such as these go into the Sessions properties, and similarly for any methods I would like to call across webpages?
I found the Web Tutorial and see there is also a tutorial video to watch. Are there other resources for getting started?
You could certainly use SQLite by hosting the file on the server alongside the web app, but it would not be ideal for a high-transaction app. SQLite is not multi-user and there could be delays due to file locking during writes that you’d have to account for. Based on your later comments, MySQL is probably best for your needs.
Yes, Xojo Cloud also includes MySQL and PostgreSQL DB servers, which you can activate from the control panel.
Things that are global to the session/user go in the Session object. You can also put things global to the entire web app (and thus all users) in global modules, but usually you want stuff to be session-global.
This is helpful Paul, thank you for the tips! It’s good to know about the availability of MySQL access with Xojo Cloud. On that note, and per one of my above questions on the matter, each time you make a call to a MySQL table, do you need to reconnect each time you want to read/write? I have been doing this in my desktop apps and not sure if that is necessary
This is my call each time I read/write
mDb = New MySQLCommunityServer
mdb.Host = app.kSiteGroundHost
mdb.UserName = app.kSiteGroundUN
mdb.Password = app.kSiteGroundPW
mdb.DatabaseName = app.kSiteGroundDBnameReg
If mDb.Connect Then
/// code
End If
I took a peek through the Web documentation and didn’t find a lot discussing Sessions. I think understanding the scope of Sessions within the running App is a key point for using Xojo Web. It does seem like you know how to ask great detailed questions, so I think you’ll be able to find help if you get stuck
Creating a MySQL connection for each request is costly and can actually lead to problems (I have seen folks exhaust their maximum connections doing this). ActiveRecord was designed to pool connections and serve them by Session identifier. In other words, each Session gets its own connection to use.
It is true that Xojo doesn’t gracefully handle MySQL disconnecting when idle, but you can work around this by sending a keep-alive request every so often. How frequently you need to depends on what your MySQL idle settings are.
If you are looking for cost saving measures, my app Lifeboat can help you host your Xojo Web apps, websites, and databases all on the same VPS instance.
Lifeboat is the perfect companion, I would say a must have, when developing Web apps with Xojo. There is Xojo cloud but @Tim_Parnell 's solution is a lot more practical.
Remember that Xojo apps don have multithreading capabilities, if you need to scale your app, the only way is to run multiple instances and SQLite can be corrupted if used from more than one instance. Better to use MySQL.
As long as you dont forget to disconnect, it is not a big deal having a new connection, but this increses the latency and resources used. Try with a “sesion global” connection and put some keep alive code there.
It is expensive and with a tiny Transfer cap. Better to use a VPN, you can use the Lifeboat app there
I agree with @Tim_Parnell and @Ivan_Tellez on this. Rather than create a new connection for each request (which can be expensive and/or problematic), use a per-session connection. You can then have keep-alive or reconnect logic to ensure the connection is available.
Just wanted to let you know that the SQLite documentation has a question / answer about this very issue
(5) Can multiple applications or multiple instances of the same application access a single database file at the same time?
of interest:
SQLite allows multiple processes to have the database file open at once, and for multiple processes to read the database at once. When any process wants to write, it must lock the entire database file for the duration of its update. But that normally only takes a few milliseconds. Other processes just wait on the writer to finish then continue about their business.
I suggest reading their whole answer in full before deciding to use SQLite over a server oriented DBMS if this is something you’re concerned about. The answer from their docs is very thorough.
You can do other things to make session specific things stay separate if keeping them in Session makes it too complicated. One technique is to use a full fledged class for settings. You can put all of the settings you need in there, create an instance when a session starts up and assign it to a property on the Session. It looks something like this:
Class mySettungs
Property Username as String
Property LastLogin as DateTime
End Class
Session
Property Settings as MySettings
Event Opening
Settings = new MySettings
End Event
End Session
As a beginner, the most important thing to remember is that unlike Mobile, Console and Desktop apps, with Web apps there can be multiple users accessing your app at the exact same time, that resources (like a database) are shared and that all user initiated code runs in a thread. You need to be careful about long running processes to the point that if something is going to take a while to return data, you should push that task off to a WebThread and periodically yield to allow other sessions to do things. When it’s done, you can send the info to the browser. Otherwise you’ll find that the other sessions essentially have to wait for the current operation to finish.
It’s a slightly different mindset and the quicker you wrap your head around this, the easier it will be.
Lots of helpful tips. Thanks all. I wasn’t aware that the connection could be called once initially and then just check if it’s not, then reconnect. I’ll most likely clean up the MySQL calls in the desktop app after I get in a good place with the web app
So this logic is accurate?
make the initial connection in the session open and store this in a session property
each time the app will be calling to the db, check If db.Connect = false then reconnect with a new connection and do the planned code, else do the planned code since we still have a connection
BTW… today was my first real day of web app coding, and it seems to be going well! The considerations you guys shared has been very helpful thus far. Helping me visualize how web differs from desktop and mobile. So thank you! There’s still some learning curves, but I’m getting the hang of it
Calling db.connect will create a new connection. What you’ll want to do is run a really short query like SELECT 1 periodically to see if the query can run. If not then you do db.connect.
This is a good reason to encapsulate your db code into one place. Basically write your own SelectSQL method that checks for a connection first and reconnects before doing the actual query.
This is very helpful. Thank you both. Does the code below look appropriate? I’m slightly concerned (not sure if I should be) that after the reconnection is attempted, if it fails for some reason, is the below approach set up appropriately so it won’t crash? When you call the reconnect, is the connection time quick enough to then run the code right away? It worked in my testing but not sure if I am on a really good connection and someone else would not be. For you web app gurus, how do you handle a no connection to the db? In other words, do you redirect them to a new page that says “we’re having trouble and cannot connect you”? Just trying to conceptually think this logic through
dim rs as RowSet = Session.mdb.SelectSQL("SELECT 1")
if rs = nil then
session.ConnectToMySQL 'reconnect to the db
end if
dim sqlExam as String = "SELECT Question FROM RDexam" 'build the query
dim rsExam as RowSet
'use a try/catch to make sure the query can run
Try
rsExam = Session.mDb.SelectSQL(sqlExam)
Catch e As DatabaseException
MessageBox("DB Error: " + e.Message)
Return
End Try
If rsExam <> Nil Then 'if not nil, will display the data
label1.Text = "Question: " + rsExam.Column("Question").StringValue
End If
Reawakening this thread. Thought I had the “check connection” method working, but I just ran into an “Error number 2013: Lost connection to MySQL server during query”, so I must be wrong somewhere
Here are my steps: When wanting to make a call to the db, I call my CheckConnectionContent method in the Session with the following code:
dim rs as RowSet = mDbContent.SelectSQL("SELECT 1") ' << error came here
if rs = nil then
'reconnect to the db
ConnectToMySQLContent
end if
I must be wrong with my use of the “SELECT 1” statement. Any thoughts?
That’s unexpected, 2013 there.
It’s expected 2003 or 2002, Can’t connect remote, or Can’t connect local.
2013 occurs when something breaks in the middle of the action, as a TimeOut.
What I would try to avoid 2 queries would be just sending the SELECT directly, and IF I GOT a 2003 or 2002 (catch it), call a reconnect() and do a retry of the same query.
This isn’t a long query. It’s finding one record to display
My steps:
App opens and connects to db
I do something in the app that calls the db to read it
Walk away for a couple minutes and try another call to read the db
Error above displays
Here is my attempt at the catch…try. Guessing this is set up incorrectly
dim rs as RowSet
try
rs = mDbContent.SelectSQL("SELECT 1") ' << error here again
Catch e as DatabaseException
ConnectToMySQLContent 'reconnect to db, but maybe in the wrong place?
Return
End Try
i always reconnect then do a query with mysql since that is fast enough most of the times.
It resets the timeout guaranteed. I believe the timeout is since the connection began to be active (depending on settings)
Thanks Derk. I thought that was frowned upon to constantly make a new connection with each call. Hence why I am trying to find the best practice for making one connection, pinging if still connected. If not, reconnect. This is the part I am struggling with
BTW, I attempted replacing the SelectSQL with ExecuteSQL (thinking I was just using the wrong command), but the 2013 error still occurred after walking away and coming back (about a minute or two)
I also tried changing the connection’s timeout to 3600 (thinking if I just make it longer, it would remain connected longer), but I still got the same error after waiting only about 5 minutes
