maybe this could go on “Getting Started” channel too. I am able to connect to a db (mysql), retrieve/write data, everything is fine.
But now my application is growing a little, and I would start to write a better code: how would you suggest to plan multiple db connections? I mean, don’t want to have
Dim ydb As New MySQLCommunityServer
[...]
record=db.SQLSelect(sql)
[...]
on every Action of every button involved in db transaction. I guess it would be better to have something like a App global Method opening the db connection(s) once, and a Global Property (say my_db) I can use to interact with the DB, like
record=App.my_db.SQLSelect(sql)
I tried something like this, with the only result of a Nil object error when accessing my_db.
Is there here around someone who can write a few lines on how would be the best strategy to code this? I have already read/searched on every tut/howto/video…never found something simply explaining the basic idea.
There are two general approaches to this. The first, as Paul suggested, is to have global db object in your application object or even possibly in a global module. Initialize it in the app.open event and never worry about it again.
There is one drawback to this. If the app has a long run time (meaning it never quits) you may run into issues where the connection is dropped by the server. Shorter lifespans and you don’t need to worry about it. There is also some concern if you are doing database work in threads because each thread should have its own database connection.
The second option is to create a global connection method and every time you’re going to use the database you open a new connection. This method creates a new connection and returns the database object to the caller method.
It’s not without drawbacks either since this new connection takes a non-zero amount of time. But it’s guaranteed to work in threads and there’s no worry about timeouts. The only real thing you have to worry about then is complex DB operations that might span multiple methods (think parent-child records) have to work on the same connection so you will need to pass the connection to children methods rather than making a new connection in those. This is really important if you are using DB transactions as they are per connection.
I have absorbed every line!
I think I’ll use Paul approach because of the relative semplicity (i.e. speed) of the db queries, but I’ll give a try on both solutions because…it’s interesting, that’s all.
