Basic questions for getting started with Web

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:

  1. 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?

  2. 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?

  3. 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?

  4. 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.

You might find this doc page helpful: Porting Desktop Apps to Web Apps

1 Like

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 :slight_smile:

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.

3 Likes

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.

1 Like

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.

1 Like

Just wanted to let you know that the SQLite documentation has a question / answer about this very issue :slight_smile:

(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.

I’d like to elaborate on this a little.

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

And then later you can access them like this:

Session.Settings.Username = "Fred Flintstone"

2 Likes

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.

8 Likes

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.

1 Like

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?

The docs say that SelectSQL will throw a DatabaseException. So you might need to wrap that call in a try/catch.

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.

Thanks guys. It is a timeout issue I am guessing.

This isn’t a long query. It’s finding one record to display

My steps:

  1. App opens and connects to db
  2. I do something in the app that calls the db to read it
  3. Walk away for a couple minutes and try another call to read the db
  4. 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

How do you handle lost connection issues?

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