Connection to a database

[ktab]

First of all, hello to all

For years i used to write macros in vba in excel. Recently it just happened to deal with vb6, and now, as a newbie in this, i want to ask the following.
I'm developing an application, as soon as the main form loads (MDI form), the app connects to db. I use ADO connection. The connection to the db is closed by the time the app closes, and never before this. Is that right? Should i connect/disconnect from db as soon as i finish with procedures?
Also, when setting recordsets, right before closing procedure, I set them back to nothing. I guess that's what it has to be done?

Thank you in advance.
(I hope i posted in the right session.. or is it for VB6 forum?)

[si_the_geek]

Welcome to VBForums

It sounds like your usage of recordsets is fine, but the connection is debatable - there are several commonly held views on it. Some people say it is fine to do it the way you do, and others recommend always closing & re-opening it.

As a general rule, what you are doing is fine unless the program can be open (and not actually being used) for long periods of time, as the database system may disconnect you after a certain amount of time - leading to errors in your program.


(You posted in the right place - the VB6 forum is meant for questions that don't fit in more specific sections).

[ktab]

So the problem that may occur is disconnection from db..
I guess that disconnection problem, may happen if the app is idle for some time right? I think that i belong into this category, as I'm sure that my app will be open for long, an occasionaly idle.

What if I set a function (boolean) to check connectivity before executing code, and if false then reconnect? How may i check if my connection is ok or down? What or with what should i use?

[si_the_geek]

There is a way to check the current status of the connection (using the .State property), but unfortunately it isn't worthwhile for this - as it doesn't notice if there has been a disconnection by the database!

What you could do is run a test query (eg: "SELECT 0 FROM tablename WHERE False") to see if you get an error, and if so then re-connect (simply with connection.Open). However, that would slow your program down a bit, so it is probably better to not use a function at all - and instead just add extra code to your error handlers (if that particular error occurs, reopen the connection, but only a certain amount of retries).

[ktab]

Or just open_connection [procedure] close_connection and be sure that you are disconnected 100%
Thank you very much for your advices. You were really helpful. See you next time (I'm sure I'm gonna post again in the forum... as newbie)

[dee-u]

One of the most common problems I encounter is a disconnected LAN or that the computer where the database is located shut-offed so what I did was to actually make a list of the most common description from such errors then retry as si_the_geek as suggested but before trying I am actually pinging the target computer so I could check if it is already 'on-line', if not then I wouldn't bother reconnecting. And the retry attempt is on the user, my program asks the user if he/she wants to retry reconnecting, if yes then it will try to reconnect, if no then close gracefully.

[ktab]

Could you give me a sample code of ping to an ip address? That's a good idea.
What i use now, is connect/disconnect to db before each procedure that i use the database. I do this (connect/disconnect) through 2 public subs. I could add this ping before try to connect to check connectivity in these subs.

[dee-u]

There should be examples if you do a search. You mean to say you are opening the connection, execute queries then close the connection?

[ktab]

Exactly. And that was my original question. If it's better to handle the procedures this way or just stay connected all the time the app is running

[dee-u]

I'll just keep an open connection and handle errors if I encounter them.

[ktab]

Is it faster (the app) like that? Is it more "appropriate"? I started dealing with VB6 the past 1 month and i want to start the right way.

[dee-u]

Yes it should be faster than opening and closing. That was actually my method several years ago and it was rather slow, that is upon executing every sql statement I had in place a function to determine if I could access the database or not but I have changed it since then.

[erickwidya]

If it's better to handle the procedures this way or just stay connected all the time the app is running

-> what DB that u using? if Access then this option is better because it ur open and close all the time the DB will fragmented (or something like that)

if u using SQL SERVER then u should consider open and closed the connection whenever u need it

i've read some article regarding this but i forgot where i put it..sorry, can't elaborate more

[ktab]

I use access mdb. I'll google about what you say, (db gets fragmented)

[GaryMazzone]

For VB6 the perfered way to connect to the DB was connect once at application startup and leave it open while the application was running. On exiting the app close the connection and set all the objects to nothing. That changed with VB.Net and the recommeded action was to connect and disconnect at each call.

[Blakk_Majik]

Thanks for that tidbit Gary. I was wondering why all the 6.0 C++ COM objects we were using just opened connections to the database and just stayed open. Using these objects with our .Net apps has caused (and is still causing)us several problems.

[ktab]

Thank you GaryMazzone. Now, another question, is mdb or try to deal with mySQL? The app will be multi-using and i think that access might not be for that. Or not?

[GaryMazzone]

If you are going for multi-user I would recommend not using Access (more then 5 I always change to a real DB). Why mySQL? What about SQK Server Express versions?

[ktab]

The app will have maximum 3 users... Sould i stay with mdb?
Also, why not mySQL? Is the transaction with the db more difficult? I know that SQL express is also freeware, i just prefer to support open source. What differences are there between these 2? (i know that the OP (me :P ) is offtopic, but now that i have the chance, i ask)

[GaryMazzone]

Personal choice I guess... I perfer Oracle,then SQL Server and don't like mySQL. In earlier versions it was missing functionality and it was stuff I needed. I just never looked back.

3 users is not to many for use. How much data would be the next question.... and is it only used on a local LAN?

[ktab]

A local LAN, 3 PC's that's the maximum number of simultaneous users. How much data? There are 5 tables, 500 rows is the biggest one.
It's not a big db, and the querries execute fast, under my current connection. I just want a second opinion.

[techgnome]

Gary - might want to take another look.... it's got some newly added features... finally supports inner selects (which had been a deal killer for me in the past) as well as stored procedures.

ktab - unless you need the raw power of a full SQL Server, either mySQL or SQL Server Express should be more than sufficient. Depending on how often you make calls to the database, you might want to go for a middle ground approach to the connection issue. Often the conventional wisdom is to open once, hold open, close on exit, or to open, execute, close... it's that second one that I want to address a little bit.

Often in that situation people will setup their subs so that calling the sub will open the DB, exwecute the SQL, then close it.... but then they will either call that sub several times, or call several subs similar all at the same time... clearly that is going to lead to performance issues with all the opening and closing going on. A better approach would be to create subs that do the executing, and have them accept a connection parameter.... so you can open the conenction, call the subs (passing in the opened connection), then when all the calls are done, close the connection. That way you onle incurr the overhead of connection opening once.

-tg

[ktab]

techgnome, this is what i am currently doing. When i have a branch of procedures that deal with my db, i have set a boolean, and as soon as procedures are done, connection close. But i have to admit, that i'm seriously thinking on following dee-u's idea, and before any procedure that executes SQL, ping (i've made a test boolean function with this) and if true then go on with executing SQL.
So, possibly I will convert the whole thing to open connection while app opens, handle disconnections with ping function before procedures, and close connection on exit..