I have a VFP9 app using mostly remote views and sql passthough in a couple of places.

My problem is that now my users are getting a 'connection is busy' error..and sadly I haven't been able to reproduce the error - so I'm trying to figure out where to look. Everything was working fine while using just the remote views to connect to an Oracle database, I've made sure to use 'share connection' in all my views - as I've had this problem before by not doing that.

So now I've created an sqlexec command that issues a select statement to the oracle database, and now the users get the 'connection is busy' error.

Plus - here is the crazy part - is that this sqlexec command issues a select statement to the oracle database. The oracle database has a dblink to a DB2 database that uses another ODBC driver on the Oracle server for that...thus passing the sql statement on to DB2 - which is the dataset I get returned.

What I'm doing is
When my program fires up - I create a connection string and handle - then issue a command to the oracle database (this is needed to get the DB2 dblink to work)

lnConnectionString = blahblahblah
PUBLIC plnConnectionHandle
plnConnectionHandle = SQLSTRINGCONNECT(lnConnectionString)

lnEResult = SQLEXEC(plnConnectionHandle, 'alter session set global_names = false', 'curHolder', aCountInfo)
IF lnEResult ! 1
lnYesNo = MESSAGEBOX('Unable to set global names session in Oracle!', 16, 'Backend Error...')
ENDIF


..then when my program is running - if I need to use the connection to query data I do this:


lcSQL = very simple SQL statement just to test the connection

lnEResult = SQLEXEC(plnConnectionHandle, lcSQL1, 'curConnectionTest', aCountInfo)
IF lnEResult ! 1
lnDisconnect = SQLDISCONNECT(plnConnectionHandle)

lnConnectionString = blahblahblah

plnConnectionHandle = SQLSTRINGCONNECT(lnConnectionString)

lnEResult = SQLEXEC(plnConnectionHandle, 'alter session set global_names = false', 'curHolder', aCountInfo)

IF lnEResult ! 1
lnYesNo = MESSAGEBOX('Unable to set global names session in Oracle!', 16, 'Backend Error...')
RETURN 0
ENDIF

ENDIF

lcSQL2 = select statement I want to run to get my data

lnQResult = SQLEXEC(plnConnectionHandle, lcSQL2, tlcOutputCursor, aCountInfo)

RETURN lnQResult


Does anyone see why I would be getting the connection busy error? Is there a way I can use the above connection with all my remote views?..thus sharing the connection? geeze I just don't undertand..aaaahhh! help!