ADODB.Connection issue

[LordBoling]

Hi Everyone.

So I am making a program that pulls orders down from my website and then enters them into an in house inventory management system.

Unfortunately after making the first few queries the database connection seems to disconnect and I get this error:

[MySQL][ODBC 5.1 Driver][mysqld-5.1.70-cll]MySQL server has gone away

err.windescription is: Unspecified error

err.number is: 80020009

This is the code that I use to connect to the database:

Global $OSC = ObjCreate("ADODB.Connection")
$OSC.ConnectionString="Provider=MSDASQL;Driver={MySQL ODBC 5.1 Driver};Server=OurServer;Database=DB;Uid=User;Pwd=ThePassword;"
$OSC.Open() 

I also tried this to see if it would work:

Global $OSC = ObjCreate("ADODB.Connection")
$OSC.ConnectionTimeout=100000
$OSC.CommandTimeout=100000
$OSC.ConnectionString="Provider=MSDASQL;Driver={MySQL ODBC 5.1 Driver};Server=OurServer;Database=db;Uid=User;Pwd=ThePassword;wait_timeout=100000"
$OSC.Open()

I run my queries like this:

$rs = $OSC.Execute("SELECT some, columns FROM some,tables WHERE some.customers_id = tables.customers_id AND orders_id="&$OscNum)
If IsObj($rs) And $rs.EOF=False Then
   ;There is an order so get the details from last query
   Global $order_info = $rs.GetRows()
EndIf

To try to fix the issue I also tried running them by calling this in place of the first line in the above example:

Func mysqlQuery(ByRef $DB,$query)
   If Not $DB.State() Then $DB.Open()
   $result=$DB.Execute($query)
   Return $result
EndFunc

I then process the information gained by this query and display it in a GUI for the user to verify. The user will accept the information and the automation begins. There are then a few more queries that run, however depending on the time it took to verify the information one of them always fails. If the user decides to walk away while the GUI is waiting then accepts it when they return the very next query fails. If they accept it immediately the last query usually fails.

All the fixes above seem to produce the exact same results.

I also tried opening and closing the database connection around every query which got rid of the error:

$OSC.Open()
$rs = $OSC.Execute("SELECT some, columns FROM some,tables WHERE some.customers_id = tables.customers_id AND orders_id="&$OscNum)
If IsObj($rs) And $rs.EOF=False Then
   ;There is an order so get the details from last query
   Global $order_info = $rs.GetRows()
   $OSC.Close()
EndIf

However it appeared to take about five seconds to open and close the database for each query, which made the program significantly longer. I would employ that if needed but was hoping there was a better solution.

Does anyone have any suggestions? Thank you.

PS: I am not sure if ADODB is COM related or not. I thought it was but if this is not the right section and can be better answered in another section of the forum, please let me know. Thanks.

Edited July 5, 2013 by LordBoling

[LordBoling]

Okay, so I did some checking on this and unfortunately there is no solution. My server does not allow for the connection timeout to be adjusted within the connection parameters. I will have to either open and close the connection for every query or adjust my actual server settings. Just thought I would post this in case anyone else has this issue.