Web app database connect method

Targets Web
1

Hi All,

I have a web app that runs as cgi on Linux connecting to MySql. For database access, I have written my own methods under session to access the database, e.g. session.LoadTable, session.GetRecord … etc

For each method when it is called, it creates a MySqlDatabase instance, connects, read data, and return data (no explicit close at the moment, assume Xojo will close it for me). In other words, if I have to load a table to a WebListBox (say 5 records), and click into detail records one by one, there will be 6 times for the routine (DB connect, read, return).

I wanna ask if this a a good approach or should I create a global DB instance and use it throughout the entire session?

Thanks for advises.

2

Hi Tony,

You should have one DB instance for each session, but you should always check if the connection is still alive before performing any db access method (select/update/delete).

Regards,

3

Thanks Olivier, but what is the advantage of using one DB instance for each session, and on the other hand, what is the disadvantage of mine? Does my approach consume more server resources? I know my approach is a little bit slower (connects DB many times), but I think this is more stable relatively as the app and DB is disconnected when the app is idle.

4

I pass my existing Session.db as a parameter through to the method that connects to the main database. The method is then able to check if the database connection is nil, is connected, has timed out, etc and returns either nil (if it cannot connect at all) or the database if it was able to re-connect or build the database connection from scratch.

5

so david, it is ok to use a global one but just check if it is disconnect, timeout etc and reconnect before using??

6

Not global in App, but one defined in the Session module.

For a desktop app, defining the database in App is OK, but for a Web App it is best to place the database in Session, so if one user does a db.Close command it doesn’t affect the other logged in users.

7

[quote=370304:@David Cox]Not global in App, but one defined in the Session module.

For a desktop app, defining the database in App is OK, but for a Web App it is best to place the database in Session, so if one user does a db.Close command it doesn’t affect the other logged in users.[/quote]

sorry i mean on session… so what other thing to check for the connection other than disconnect and timeout???

8

I do this inside the method after passing in the currentDB:

Dim db As New ???Database

if currentDB <> nil then
  if currentDB.IsConnected then
    rs = currentDB.SQLSelect("SELECT 1") 'dummy command to keep alive!
    Return currentDB
  end if
  
  'if lost connection, see if we can reconnect!
  if currentDB.Connect then
    if currentDB.IsConnected then
      Return currentDB
    end if
  end if
end if

<<< Insert here new database settings
db.host = "???"
db.Port = ???
db.Username = "???"
db.Password = "???"
db.DatabaseName = "???"

if db.Connect then
    Return db
  else
  Return nil
end if
9

I am pretty sure i have some of those code in my application in my db class for multiuser. but NOT all of them.