Web App DB Connection Advice

Does anyone have any advice for database connection for WE? Is it best to open a connection when a user logs in and leave it open until the user leaves the app or is it best to open and close the connection with each query. I’m using CubeSQL on a Ubuntu server. Right now I’m opening/closing for each query and it seems fast enough but I’m wondering if that’s creating a lot of work for the server.

Any Suggestions?

The recommendation is that you connect each user in the session.open event that way you can create transactions per user/session.

Thanks Wayne… I’m creating the connection as a property of the session. I’ll change my code to leave the connection open until the user ends the session.

Much Appreciated!

Johnny, note that most hosting companies will kill your database connection after a short time. In my case MySQL connections were dropped after 15 seconds of inactivity. You may well create the database as a Session variable, and give it all the required username/password details, but I would re-kindle the database connection each time you want to access it, otherwise you may get timeout errors in code that is correct. If you have a dedicated server, you can set your own longer database timeouts, but you will still want to code for timeouts.

Personally, I create all the variables as Session variables, but create the Database and RecordSet anew for every Method that needs database data. I do this because I look up databases in threads so I need the RecordSets to not clash.

Thanks David.

That’s what I’m doing now. I wasn’t sure with WE if it would be best to leave the users connection open or close it after each query. With PHP I always close the connection after I get finished with the query results. With this app the end user will probably have their browser open during the entire work day. Timeouts wouldn’t be good. Thanks for the advice. I may have to do some testing.

I am using cubesql on a windows server with a xojo web app on the same server. My connections timeout and siconnect. I had to put a timer on each session to issue a ping command. Before that i just let the web app reconnect but it introduced a time delay.

In another project, not Xojo, I kept sending a very simple query to obtain and compare the user-record every 15 seconds as long as there was no business activity to the database. Never had issues with connection that had to be open whole day.

its back up :slight_smile:

http://www.valentina-db.com/

i just noticed this replay went to wrong post, and i was wondering what happend … lol … wondering where is my post… lol

Most database administrators would prefer this approach. Imagine the work created for the server if connections are kept open artificially by generating traffic periodically to simulate actual use by a user. Simple is good. Open as needed and close immediately after that work is complete.