Visual Basic 6 and Mysql ODBC Driver error

[lexer]

Hi

I've got a Visual Basic program that insert Data of calls from a Meridian PBX in a Mysql DB (I'm using wampserver), I've got the Mysql ODBC 3.51.24 Driver. The program works fine when receive constantly data calls from the PBX, At night there are not calls, At the morning when arrive new calls from the PBX a the ODBC driver fails an shows the following message (see attached file):

Run-time error '-2147467259 (80004005)':
[Mysql][ODBC 3.51 Driver][mysqkd-5.0.51a-community-nt] Mysql server has gone away.


If I stop the VB program and run again It starts receiving calls.
The VB Mysql ODBC driver doesn't fail If It's constantly receiving data from the PBX, When The program doesnt receive data from the PBX in long period (8 hours for example) and after that period It receives a call I get the above error.
Any ideas?

Thanks,

 

[vb5prgrmr]

Sounds...(Could be wrong)...like your connection is closing due to inactivity but your program thinks it is still valid. Put some sort of inactivity time test in and if last activity greater than 30 minutes then close and reopen all connections. Just a thought...

Good Luck

 

[lexer]

Thanks vb5prgrmr, In general I do the following in my VB program:

'****First: Open The connection and the database
Set Cnx = New ADODB.Connection
Set Cnx = New Connection
Cnx.ConnectionString = "Driver={MySQL ODBC 3.51 Driver};Server=localhost;Port=3306;Database=calls; PWD=;UID=root"
Cnx.Open
'*****Second I receive and process the data from the PBX
'*****Third insert the data on Mysql database
Cnx.Execute "Insert Into register(Ext,date,hour,elapse, number) values('" & Ext & "' ,'" & date & "','" & hour & "','" & elapse & "','" & number' )"


First step is execute one time (open connection) just when the program is run, Second and Third steps are execute for every call from the PBX, I never Close and open again the mysql conection.
Should I have to open and close the mysql connection for every call that I receive?

 

[gmmastros]

Should I have to open and close the mysql connection for every call that I receive?

There are pro's and con's with that approach. In my app, I chose to open the connection/use the connection/close the connection every time I "touch" the database. This has worked out very well for me. When you do things this way, you would ordinarily think that it would be slower because you are doing more "work". In reality, connection pooling kicks in and subsequent connections to the database are very fast. This is many times faster than using the database, so the time it takes to connect is negligible.

If you choose NOT to do things this way, I would encourage you to put some error handling in your application. Trap the error, and attempt to re-connect. This would effectively be the same as closing the app and re-opening it.




-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[vb5prgrmr]

I would say no. You do not need to open and close it everytime. As I suggested, while you are busy use it but if you fall into a lull 15-30 minutes, then you may want to close and reopen.

However, I like gmmastros's suggestion. Trap the connection error, close your end, and then reopen.

Good Luck

 

[TLowder]

To me it sounds like a MySQL setup, settings, startup variable issue. I use MySQL and establish the connection when my program opens and the connection stays connected for long periods of time. I've never "timed" the connection but its something along the line of weeks or months, not hours. Its possibly something like your max connections are being reached which maybe drops the previous connections. Or some setting that may have to do with disconnecting after a length of time of inactive connection (like vb5programmer was saying about inactivity). I really don't know for sure what to look at but rather than change your program to compensate for the problem, I would try to find find the source of the problem which I think is a database setup issue. Possibly try the MySQL forum. Also how many connections are we talking about? Is it every connection that gets the error in the morning? Are the connections via lan? What database version? All of these questions are factors to think about, sort of retorical questions in the sense that I probaly can't help any further but you should think about these when trying to find the problem. I don't know about or what Meridian PBX is, so I don't know exactly what your setup is. Also your ODBC driver is not the newest.

Tom

 

[lexer]

Thanks for your help, I changed my VB application and Now I open and close the Mysql connection for every call I receive and It works fine.
I have another problem, The VB program can send data to Mysql just when the wampserver is ready, I put the VB application and the wampserver on the PC's start up, But The wampserver (Mysql) takes several seconds for being ready to accept data from VB, I would like to use an error handling in my VB aplication for checking when Mysql is ready for accepting data.
How can I do the error handling, Using "on error" ?

 

[vb5prgrmr]

By checking the state property of your connection and waiting until the state of the connection is open.

Good Luck