Questions on proper database usage / OOP.

I’m relatively new to Xojo, databases, and OOP in general. I’m creating a Windows Desktop app and using a SQLite database. I can connect to the database and create a recordset. Can also create and update records so far. What I’m not clear on is how to handle the database connection(s) and recordsets properly along the lines of OOP and database interactions in general. For example, I have just a single database and many different forms that will display/use various records. I’ll be creating new records, editing records, deleting records on the forms. Here are my questions:

  1. Do I need to just connect to the database once in my application (connection remains active?) OR make a connection each time I need to perform a database CRUD task or to generate a recordset? Would I make a database connection when each form opens (Load)?
  2. Do I need to close the database connection and/or recordset after each CRUD task?

Thank you.

  1. yes open once and close once (unless the database is remote)
  2. no

and use PREPARED STATEMENTS
and ALWAYS check error status after each operation

You might like to check out ARGen by Bkeeney Software https://bkeeney.com/allproducts/argen/argen-3-0-video/

It’s a huge time saver and gives you plenty of source code. A real joy to work with because it let’s you really concentrate on your own project.

Just my 2cents… Personally I think that one should learn how to do things (like SQL) the long-handed way first, and not rely on special tools from the git-go. Once you are familiar with how SQL works, what it can and cannot do, how it does what it does, then and only then should you include time-saving tools such as these. Its like teaching a child arithmetic by starting with a calculator.

One of the reasons I say this… is what do you do in the future if you need to program advanced SQL in an enviorment that doesn’t have these (or similar tools) and you have to actually “write code”.

Note: I am not saying these tools are bad things, I’m just saying you should learn the foundations first…

Totally agree. I see too many people using ActiveRecord/ARGen as a crutch. You really need to learn how databases work in Xojo first, BEFORE using any of the tools. Figure out what’s the big pain for you and find the tool that eliminates the pain.

We get a lot of ActiveRecord questions from people new to Xojo that they wouldn’t ask if they knew a bit more about how databases work. ActiveRecord is not the only solution and it’s not the perfect solution for every developer - it’s a solution that we’ve found to be helpful. We developed ActiveRecord because we found ourselves doing the exact same code over and over (and over) again until we finally said enough was enough and created some automation behind it.

I’m glad developers find ARGen useful but I wish more would learn how to do database code in Xojo, first, before turning to ARGen.

What we’ve done for DB server connections is keep them active if they’ve been used recently. If they haven’t been used for five minutes or so we close the connection. If it’s not connected when you do the next db operation it will connect again. Some DB servers don’t like their connection to be active all the time as it can interfere with backups. This approach requires a bit of work since you’ll have to keep track of db operations and how long since the last db interaction.

Edit: You’ll also need one db connection per thread. So all of your windows will be on the main thread. Any additional threads that touch the db should have their own connection.

Is there a recommended location to place the code for opening the DB connection ?

(Apologies if this is a thread intrusion, it seems relevant to the OP question)

We generally call a DBOpen function in the App.Open event. DBOpen and the database property are in a module that handles all of the database operations.

Thankyou Bob.