Mike,
> Bill et al,
>
> I noticed my MySQL remote databases timing out and just
> re-engineered my GetHandle routine to verify that it's a good, active
connection
> (otherwise get a new one):
Thanks for the sample code. When I'm working in the area again I'll take a
closer look to see what I can learn from it.
The ongoing - but now very occasional/rare - problem I get is losing the
connection at some point later in the session due to a timeout. The message
is like "MySQL server has gone away". I've gotten some relief from this
problem by triggering activity on the server side every few seconds (timer
pop), and now it rarely happens, but we don't connect for extended periods
anyway. So far, it's only used for a few internal applications.
The underlying problem, I think, is that Inmotion's servers aren't setup for
this type of connection. When I last looked into it I noticed a timeout
control variable in MySQL that I can't change and Inmotion has set to a low
value - I believe because they favor in and out connections for php/mysql
queries coming from web pages and not long running client/workstation
connections.
I guess from this perspective, I've been getting away with a very low cost
solution, and the real answer is to pay the going rate for a dedicated
server. Developing for the Android, for one thing, will press this issue.
I've been reluctant to build Internet dependencies into my apps, trying to
make it so they will run with or without the Internet, but it's getting
harder by the day.
FWIW, here's the code I use today to make the connection:
PARAMETERS cMySQLdb && name of the MySQL database to be
connected
* sample call: omysql_utils.connect_inmotion('mysqldb2')
this.cserv = 'mydomain.com'
this.uid = 'myuserid'
this.upw = 'mypassword'
this.udb = cMySQLdb
* added OPTION=3 to surpress ODBC login screen - not sure but may have other
effects to supress timeouts?
this.cConstr = "DRIVER={MySQL ODBC 3.51
Driver};SERVER="+this.cserv+";UID="+this.uid+";PWD="+this.upw+";DATABASE="+t
his.udb+";OPTION=3"
This.nConnectionHandle = SQLStringConnect(this.cConstr, .T.)
if This.nConnectionHandle < 0
AError(gaError)
MessageBox("Could not connect to "+this.udb+" on the MySQL Server. Error
message returned is: " + Chr(13) + ;
gaError[1,2], 16, "Connect_InMotion")
IF ATC("and no default driver specified",gaError[1,2]) <> 0
=MESSAGEBOX("See
http://www.h2officesolutions.com/h2os_custguide/How_to_install_the_MySQL_ODB
C_driver.htm for ;
instructions on obtaining and installing the ODBC driver",0,m_product)
endif
STORE .t. TO z_esc
ENDIF
IF z_esc
=lsmmsg("MYSQL_UTILS:Connect_inmotion: Connection attempt to
"+TRIM(cMySQLdb)+' FAILED ')
else
=lsmmsg("MYSQL_UTILS:Connect_inmotion: Connected MySQL database:
"+TRIM(cMySQLdb))
ENDIF
* Z_ESC is returned to caller
RETURN
>
> FUNCTION GetHandle(tlForce as Logical) as Integer
> * Returns connection handle to caller.
> LOCAL liHandle as Integer, lcDNS as String,
> loException as Exception,
> lcDSNFile as String, llEncrypt as Logical, liRetryCnt as Integer
> IF tlForce OR this.nHandle <= 0 THEN
> TRY
> =SQLSETPROP(0, 'DispLogin', 3 )
> && suppress login info
>
> =SQLSETPROP(0,"ConnectTimeOut",SQLTIMEOUT) && wait N seconds before
> timeout (mjb 08-04-06)
> IF PEMSTATUS(this,"cDSNFile",5)
> THEN && mjb 11-09-06 new code for
> increased flexibility
> lcDSNFile = this.cDSNFile
> llEncrypt = this.lEncrypt
> ELSE && older standard code
> IF FILE("dsn.ini") THEN
> && mjb 02-26-06 defer to dsn.ini before dns.ini
> lcDSNFile = "dsn.ini"
> llEncrypt = .F.
> ELSE
> lcDSNFile = "dns.ini"
> llEncrypt = .T.
> ENDIF && FILE("dsn.ini")
> ENDIF && PEMSTATUS(this,"cDSNFile",5)
>
> IF FILE(lcDSNFile) THEN
> lcDSN = FILETOSTR(lcDSNFile)
> IF llEncrypt THEN
> SET LIBRARY TO
> cipher50.fll
> lcDSN =
> encrypt(lcDSN,MYKEY)
> ENDIF
> liHandle =
> SQLSTRINGCONNECT(lcDSN)
> IF
> SQLEXEC(liHandle,[select @@version as cVersion],"curVersion") =
> 1 THEN
>
> this.cDBCVersion = curVersion.cVersion
> USE IN
> curVersion && done with temp cursor
> ELSE && attempt reconnection
> liRetryCnt = 1
> DO WHILE
> liHandle < 0 AND liRetrCnt <= RETRY_COUNT
> WAIT
> WINDOW TIMEOUT .5 "Re-attempting (" +
> ALLTRIM(STR(liRetryCnt)) + ") to connect to SQL database..."
>
> liHandle = SQLSTRINGCONNECT(lcDSN)
> IF
> SQLEXEC(liHandle,[select @@version as cVersion],"curVersion")
> = 1 THEN
>
> this.cDBCVersion = curVersion.cVersion
>
> USE IN curVersion && done with temp cursor
> ELSE
>
> MESSAGEBOX(this.cSystemName + " is unable to establish
> connection to the database.",16,"Unable to connect.")
>
> liHandle = -99
> ENDIF
>
> liRetryCnt = liRetryCnt + 1
> ENDDO
> ENDIF
> ELSE
> liHandle = -1
> ENDIF
>
> CATCH TO loException
> liHandle = -1
>
> MESSAGEBOX(loException.Message,16,"Problem getting handle. " +
> ADMINMSG)
> ENDTRY
> this.nHandle = liHandle
>
> ELSE
> * mjb 02-04-08 verify that it's good
> IF SQLEXEC(this.nHandle,[select
> @@version as cVersion],"curVersion2")
> = 1 THEN
> liHandle = this.nHandle
> ELSE && force new handle via recursive call
> WAIT WINDOW NOWAIT
> "Reattempting to connect..."
> liHandle = this.GetHandle(.T.)
> ENDIF
> ENDIF && tlForce OR this.nHandle = 0
>
> RETURN liHandle
> ENDFUNC && GetHandle() as Integer
>
>
> so in the caller, it just knows that the connection will be
> good when it
> comes back if it's positive:
>
> liHandle = this.GetHandle()
> if liHandle > 0 then
> liResult = SQLEXEC(liHandle,[select iid from table where
> iactive=1],"curTemp")
> if liResult > 0 then ...
> endif
> else && problems getting connection
> endif
>
> Actually I've got a routine called ExecuteAndFill that does the above
> stuff, but you get the idea from that stub code.
>
>
>
> --
> Mike Babcock, MCP
> MB Software Solutions, LLC
> President, Chief Software Architect
> http://mbsoftwaresolutions.com
> http://fabmate.com
> http://twitter.com/mbabcock16
>
[excessive quoting removed by server]
_______________________________________________
Post Messages to: [email protected]
Subscription Maintenance: http://leafe.com/mailman/listinfo/profox
OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message:
http://leafe.com/archives/byMID/profox/6C875889CAD24D8CAD183F577C22EBD1@bills
** All postings, unless explicitly stated otherwise, are the opinions of the
author, and do not constitute legal or medical advice. This statement is added
to the messages for those lawyers who are too stupid to see the obvious.
