I use pool (as recommended by Erel) . Without it - had many issues... With it - no probs...
Here is how I do it in ABM (supplied by alwaysbusy). Never a problem - yet this is my own server - NOT Hostgator (who knows what wrenches they toss into the cogs).
Init MySQL - set driver and pool size ONCE.
Sub AppStart (Args() As String) ' in main sub
' change if you want to connect to a mySQL server using connection pool
DBM.InitializeMySQL("jdbc:mysql://localhost/harris?characterEncoding=utf8&useSSL=false", "mhwxxxxx", "b12xxxxx", 150) ' 150 is pool size
' DBM code module
Sub InitializeMySQL(jdbcUrl As String ,login As String, password As String, poolSize As Int) 'ignore
Try
pool.Initialize("com.mysql.jdbc.Driver", jdbcUrl, login, password)
Catch
Log("Last Pool Init Except: " & LastException.Message)
End Try
' change pool size... default is 10 (or maybe less)
Dim jo As JavaObject = pool
jo.RunMethod("setMaxPoolSize", Array(poolSize))
' The get and close methods for MySQL pooling
Sub GetSQL() As SQL
Return pool.GetConnection
End Sub
Sub CloseSQL(mySQL As SQL)
mySQL.Close
End Sub
' ABM update method...
Sub SQLUpdate(SQL As SQL, Query As String) As Int
Dim res As Int
Try
SQL.ExecNonQuery(Query)
res = 0
Catch
Log(" UPD ERR: "&LastException)
res = -99999999
End Try
Return res
End Sub
End Sub
Typical call for a query (update and select)....
' update query....
Sub SetShift(pk As String , sid As Long)
Dim sql As SQL = DBM.GetSQL
DBM.SQLUpdate(sql,"UPDATE loadmast SET sourceid = "&sid &" WHERE pk = "&pk)
' Log(" setting shift id: "&sid&" pk: "&pk)
DBM.CloseSQL(sql)
End Sub
' Simple Select query using ResultSet...
Sub GetDriverName(drv As Int) As String
Dim SQL As SQL = DBM.GetSQL
Dim res As ResultSet
Dim nam As String = "Not Found"
res = SQL.ExecQuery("Select * from emp where PK = "&drv)
Do While res.NextRow
nam = res.GetString("Last_name")&", "&res.GetString("First_name")
Loop
res.Close
DBM.CloseSQL(SQL)
Return nam
End Sub
You can Try and Catch to your hearts content... which is always a good idea.
The point is - init once - open and close with every query - and get your own server and screw Hostgator - seems there lies the issue.
Thanks