Connect is Busy - OK or Revert?

[stanlyn]

Hi,

When I first load a form, I set some values and then it runs a cursorfill() event and shown me the records in a grid. So far so good...

Now if I click another record in the grid, I'm presented with a "Connection is Busy" dialog with options of OK, Revert and Help. I can select either OK or Revert and it continues on as if its all ok...

There has been no known updates or changes to the cursor, and was wondering why it is thinking that data was changed.

Any ideas?
Stanley

 

[Olaf Doschke]

What happens behind the scenes? Are you querying detail data of the selected record for a child grid?

And how's your Buffering set? Tablebuffering, Rowbuffering? CA init does SET MULTILOCKS ON for purpose, to enableTABLEUPDATE() at all. The default buffer mode for updatable CA cursors should be 3 (optimisitic row buffering) meaning "Optimistic record locks which wait until pointer moves, and then lock and update."

And the latter is using the connection in the moment you change from one to another record, and if there is no idle connection you get the "connection busy" message.

Then what helps perhaps is to go for tablebuffering mode and do not trigger single recor updates implicitly by moving away from one record. In each new session doing CURSORSETPROP("Buffering",5,0) sets tablebuffering for all cursors, tables, views etc. in the datasession.

How's your Ca set? Fetch all? Fetch top 100 records? fetch as needed?

Is the connection you make synchrounous or asynchronous?

I need this to reproduce.

All I can say for now is, I would rather limit the result set by WHERE and not by "fetch as needed", I haven't tried but have the assumption this will keep a connection busy, you better fetch all records, not meaning full table, but all records of the resultset, which then frees the connection for usage again. Otherwise, while the query is idle, the connection is still occupied and therefore "busy". Just an assumption.

Knowing your setup in more detail I could make some tests and see what really happens.

Bye, Olaf.

 

[JRB-Bldr]

What Olaf says is correct.

However one routine that I added on Remote operations in order to eliminate (or at least minimize) the "Connection is busy" message was WaitForUpdate() as follows:

USE MyRemoteTable IN 0 ALIAS MyTable
SELECT MyTable
=WaitForUpdate()  && Wait until ready to continue
<now do whatever>
.
.

FUNCTION WaitForUpdate
cAlias = ALIAS()
=CURSORSETPROP("FetchSize",-1,cAlias)
LabelHand = CURSORGETPROP("ConnectHandle",cAlias)
DO WHILE SQLGETPROP(LabelHand,"ConnectBusy")
   cWaitMsg = "Waiting For Remote Operation to Complete..."
   WAIT WINDOW cWaitMsg  NOWAIT
   =INKEY(3,"H")
ENDDO

RETURN .T.

Good Luck,
JRB-Bldr

 

[stanlyn]

Olaf,

>> Are you querying detail data of the selected record for a child grid?
1. Yes


>> how's your Buffering set?
2. Rowbuffering


?? meaning "Optimistic record locks which wait until pointer moves, and then lock and update."

3. Yes, I understand that, but what I don't understand is that there is nothing to update. I only moved the record pointer.

4. So, with row buffering on, is it doing a "lock and update" every time the record pointer changes, regardless of whether a record was changed?

?? And the latter is using the connection in the moment you change from one to another record,
5. What do you mean by "the latter"?

?? if there is no idle connection you get the "connection busy" message.
6. I'm the only one using the connection, and it has already received the cursor, so what could be keeping the connection busy? BTW, the max records to fetch is 150, so that happens very quickly and should not be keeping it busy...

?? Fetch top 100 records? fetch as needed?
7. Top 100 with a max of 150. How and when does the "fetch as needed" kick in? I was under the impression that "fetch as needed" kicks in when trying to move past the end of the previous batch, and until it kicked in, it should not be busy. Is this correct?


?? Is the connection you make synchrounous or asynchronous?
8. The CAs "AllowSimultaneousFetch" property is .F., while the VFP IDE's remote date settings has it as Synch. Is there a property on either the de or ca for this? Are these settings are same as each other or unrelated?

Thanks,
Stanley










SELECT SCOPE_IDENTITY()

 

[stanlyn]

Hi jrbbldr,

Thanks for the udf...

>> USE MyRemoteTable
1. Are you really refering to the sql remote table here or the local cursor?

2. How often were you getting bit by this?

3. Were you returning large results?

4. Is this "connection busy" a common problem?

Thanks,
Stanley

 

[Olaf Doschke]

I would simply suggest to move away from record buffering to table buffering, then you don't have this message at all, as there are no single updates.

I don't use rowbuffering, it's useless. Even if you want more often updates you can do TABLEUPDATE() of one row only. Therefore there is no advantage in rowbuffering. You can then check ConnetBusy before issuing Tableupdate of the single row, you can even check GETFLDSTATE() to see, if a tableupdate is needed at all, obviously VFP is doing more than needed here.

From what you experience it was enough to touch the row to make it cause an implicit update. Just turn this off using tablebuffering mode: First line of form load set to CURSORSETPROP("Buffering",5,0) would be sufficient for that or set buffering for each CA directly after the cursorfill.

I can't tell you why the connection is busy, and have not done many things with fetchasneeded and other advanced settings of the connection in all detail. May also depend on several other things. It's odd, assuming you're the only user testing in debugmode, that you already get so much busystates. Maybe a hint on some flaw of the LAN hardware, too.

Bye, Olaf.

 

[stanlyn]

Olaf,

>> CURSORSETPROP("Buffering",5,0) in the form's load event.

1. Will this override all of the CAs buffering settings where they are all set to "row" in the builder?

2. A related problem has surfaced. I have a single CA (1 out of 3) that is not reliable. Normally I would call a cursorfill() method and then a "select 'cursor'". On this one CA, it errors with table not found... If I put "set step on" immediately before it, it works simply letting the debugger open up and clicking resume. It never errors when doing it this way... Take the "set step on" line out and the problem returns. There are no issues with the other 2 CAs. VFP9sp2 and MS Sql Server 2008r2 is used.

Any ideas,
Stanley

 

[JRB-Bldr]

>> USE MyRemoteTable
1. Are you really referring to the sql remote table here or the local cursor?

The MyRemoteTable in the example is the name of a VFP Remote View's cursor, but it was pre-defined to populate that View Cursor from a table within an 'alien' database such as MS SQL Server, Informix, etc.

2. How often were you getting bit by this?

It depended on the circumstances.
* How busy was the 'alien' server
* How much network traffic was going on
* How large the record set involved was
* etc.

3. Were you returning large results?

Most of the time reasonably large, but not always.

As I said, if the 'alien' server itself was busy doing other things, then its response to an additional query, etc. was slower than usual. If VFP was 'ready' to move on before the cursor action was complete then the warning message was seen.

NOTE - Once I changed things over to SQL Pass-Thru (instead of Remote Views) and had the Connection configured as Non-Asynchronous, these issues went away since VFP waited on the completion of the action before attempting to move on.

4. Is this "connection busy" a common problem?

Again, with Remote Views my client was experiencing them from time to time (more often than desired) depending on the circumstances.

Good Luck,
JRB-Bldr

 

[stanlyn]

JRB-Bldr,

>> Once I changed things over to SQL Pass-Thru (instead of Remote Views) and had the Connection configured as Non-Asynchronous, these issues went away since VFP waited on the completion of the action before attempting to move on.

1. I've just recently done several large conversions where it literally took days to export vfp data into SQL with SPT and never once did I see a connection busy, connection anything, sql, or vfp issue. It was rock solid, and now on super trival things I'm getting these errors, which is making me wonder what way is the most reliable, and what direction should I be learning and implementing?

2. Is there anything I can do to make these CAs as reliable as what I experience with SPT?

>> If VFP was 'ready' to move on before the cursor action was complete then the warning message was seen.

3. What commands and how are they sent to the server to make vfp wait until the remote server can respond?


Thanks,
Stanley

 

[foxyland]

@stanlyn: you wrote:
3. What commands and how are they sent to the server to make vfp wait until the remote server can respond?

Not an elegant way, but you can do this:

USE MyRemoteTable IN 0 ALIAS MyTable
SELECT MyTable
Goto bottom
<now do whatever>

hth,
Foxy

 

[Olaf Doschke]

The symtoms you see could all have a root in an asynchronous connection.

Eg the effect of set step on is not by the debugger, but simply by waiting. Even if the resultset is there in split seconds, if you don't wait for it, you get alias not found, as the result simply isn't there immediately, but almost immedately.

IIRC connections you create wit SQLStringConnect are synchronous by default, meaning you wait for results before continuing. Then stop using SQLSetprop("Asynchronous",.T.), or perhaps explicitly set it .F.

While it sounds like a good idea to let SQL Server query in parallel to what else you want to do, if you want to make use of that feature you also need to program in a way to wait for results, eg you can't directly use a result, but you could already trigger all other CAs and then in the end wait for all of them to finish. But that also contributes to busy connections.

You'll be fast enough and have to worry less about programming, if you avoid asynchronous execution of SQL Server in parallel to your fox code.

Bye, Olaf.

 

[Olaf Doschke]

How many users do you have in parallel? Because the effort needed to program in an asynchronous way doesn't pay, if you have many users, the server will be busy anyway with many users, so a single user doesn't ever profit much of asynchronous SQL Execution in comparison to the effort of programming for that case.

If your form needs the data to work anyway, you need to wait, anyway, then you can use synchronous connections, anyway. You don't gain much, the LAN bandwidth also doesn't scale.

You can use an asynchronous connection to start a lengthy query, which may even not have a resultset, but rather creates a temp table and propagets it with data or updates a statisic or anything that runs on SQL Server and has no direct effect on your fox app.

Bye, Olaf.

 

[JRB-Bldr]

What commands and how are they sent to the server to make vfp wait until the remote server can respond?

The FUNCTION WaitForUpdate above that I posted will hold off VFP execution until the backend server is done with the SQL request.

Good Luck,
JRB-Bldr

 

[stanlyn]

>> You can use an asynchronous connection to start a lengthy query, which may even not have a resultset, but rather creates a temp table and propagets it with data or updates a statisic or anything that runs on SQL Server and has no direct effect on your fox app.

So, it looks like I have complete control of using either asynchronous and/or synchronous functionality. Does this mean that they can be mixed, sending one asynchronous, immediately followed by an synchronous command and neither command will be held up. I understand that if I need the results before I continue, I'd use synchronous mode. If so, I can see a lot of usefullness with this.

So how and where do I add the SQLSetprop("Asynchronous",.T./.F.) commands? Just before the Cursorfill()? Does it have to be sent in a select statement, or as a SPT command?

Thanks,
Stanley

 

[Olaf Doschke]

If you don't do that right now, I'd ask myself how your connection get's asynchronous, as that's not the default.

So I'd look for "SQLSetProp" via code references search. Perhaps it's done in builder code or some old code you recycled from your "SPT era", which is doing the connection.

The full function call would need to include the handle and it can be done right after making the connection.

Yes, it can be switched, as a connection is not synchronous or asynchronous by it's creation and throughout it's lifetime. Just out of a feeling I would rather suggest you create two connections, one synchronous, one asynchronous and store their handles in two descriptive properties or public variables. eg gnSyncSQLHandle, gnAsyncSQLHandle or whatever.

This uses up two connections instead of one, but I have the feeling you get unpredictable results, if you start an asynchronous query, then switch to synchronous right away, even while the asynch query still runs. Will that dealy the switch to synchronous mode or not? You can of course test, but if you don't have limited connections as with SQLEXPRESS, it's quite easy to have one synchronous and one asynchronous connection, isn't it? And to use a CA one or the other way you set it's Datasource to the one or the other handle, that's all.

SQLSetProp() doesn't have to be sent to SQL Server, it's a foxpro function, not T-SQL, you do it in VFP. It is one of the SQL Passthrough functions, as is SQLStringConnect() or SQLConnect(), all the SQLxyz() functions are the family of SPT.

So here Foxpro decides, if it wants to wait for SQL Server or continue, it's not a job of SQL Server to make the connection synchronous or asynchronous, SQL Server just delivers to the ODBC driver in any case, what is done on the VFP side is of no interest to SQL Server, that's between VFP and the ODBC driver.

Bye, Olaf.

 

[stanlyn]

>> If you don't do that right now, I'd ask myself how your connection get's asynchronous, as that's not the default.

1. I haven't made any specifix settings related to asynchronous, and actually thought I was using defaults. In vfp.tools.options.remote data, I have asynchronous execution and display warnings are both UN-Checked, while Batch processing and automatic transactions are Checked. Would the settings on this page apply to the CAs, SPT, remote views, or all?


>> you recycled from your "SPT era"

2. None recycled...


3. I searched the whole project via code reference for "SQLSetProp"... None found... I also looked for any properties that maybe related to asynchronous/synchronous in both the de and the ca... none found...

Is there a command that I can run that tells me the current mode?


>> all the SQLxyz() functions are the family of SPT.

4. Does all the spt commands apply to CAs?


>> And to use a CA one or the other way you set it's Datasource to the one or the other handle, that's all.

5. I'm a little confused here as I'm not sure how to configure a CA for either of these 2 modes as I stated earlier, I see no properties to set, only a datasource name and nothing about synchronous, actually I see only 2 properties related to the connection string and they are datasource and datasourcetype.

In spt I use:
Public gcConnString
gcConnString = Alltrim(TASK.conn_string)

ConnectionHandle = Sqlstringconnect((gcConnString ))

and in the DE I only have a type of ODBC and a datasource of:
This.DataSource = sqlstringconnect([Driver={SQL Server};Server=DAS; Integrated Security=SSPI; Database=DEEDROOM"])


6. I should be able to use any of the sqlexec commands mixed in with the ca stuff and the odbc driver will handle it correctly???


Thanks,
Stanley

 

[Olaf Doschke]

3. SQLGetProp()

4. Does all the spt commands apply to CAs?

No, the common thing of CA and SPT is using an ODBC Connection, if you use DataSourceType ODBC. Well, and both create cursors, obviously.

5. I already said Asynchronous or Synchronous is a property of the connection, not the CA. The CA only uses the connection by it's handle, you simply store the handle in the CA DataSource property, that's what the builder code does:
This.DataSource = sqlstringconnect(connectionstring)

But we already decided not to do that but to open a connection in main.prg and store it in goApp.nConnectionhandle, or something like that and to use it for all CAs. Now you can do two connecton and you can decide what connection to use before CursrFill(). The CA doesn't connect, it does only use a connectionhandle. SQLStringConnect() connects using a connectionstring, or you can use SQLConnect() using a DSN you configure in Windows.

You write it yourself in code, and you don't see the woods for the trees, this is actually the same thing. This.Datasource is just the ConnectionHandle.

You don't have to literrally stay with that line, you can also do

Public gnConnectionHandle
gnConnectionHandle = SqlStringConnect(Alltrim(TASK.conn_string))

Ca.Datasource = gnConnectionHandle

What is so hard to see that? The result of SQLStringConnect always is a connnectionhandle, so you set Datasource to this handle, not to the command.

If you set variable = 2*3, what is in the variable? 6, isn't it? not "2*3", it's evaluated and the result is stored.

The connection string is obsolete once you have the handle, it doesn't need to be stored, the handle needs to be stored.

Bye, Olaf.

 

[JRB-Bldr]

Spring-boarding off of Olaf's example code above....

gnConnectionHandle = SqlStringConnect(Alltrim(TASK.conn_string))

[B]* --- Config To Run SQL Operations [U]Synchronously[/U] ---
=SQLSETPROP(gnConnectionHandle, 'asynchronous', .F.)[/B]

Ca.Datasource = gnConnectionHandle

Good Luck,
JRB-Bldr

 

[Olaf Doschke]

To answer the default-settings question:

In vfp.tools.options.remote data, I have asynchronous execution and display warnings are both UN-Checked, while Batch processing and automatic transactions are Checked. Would the settings on this page apply to the CAs, SPT, remote views, or all?

Good observation, you found the defaults, I checked: They do apply to SQLConnect() and SQLStringConnect() and that applies to CA and SPT.

Remote views have their own section in that tab with other defaults, but that doesn't matter here, does it?

If changing the options doesn't work for you, this might be due to VFP not being able to store it in the correct registry branches.

As said: If all else fails explicitly set asynchronous to .F., like JRB-Bldr proposes. Do it right after establishing the connection, and you're done with it.

And at that point, you could simply do two connections:

gnConnectionHandleAsynch = SqlStringConnect(Alltrim(TASK.conn_string))
gnConnectionHandleSynch = SqlStringConnect(Alltrim(TASK.conn_string))

* --- Config To Run SQL Operations Synchronously ---
=SQLSETPROP(gnConnectionHandleAsynch, 'Asynchronous', .T.)
=SQLSETPROP(gnConnectionHandleSynch, 'Asynchronous', .F.)

* --- Later, when initing a CA ---
CA.Datasource = gnConnectionHandleSynch && preferred, but could also set to gnConnectionHandleAsynch, if wanting to execute asynchronously.

Bye, Olaf.

 

[stanlyn]

>> What is so hard to see that? The result of SQLStringConnect always is a connnectionhandle, so you set Datasource to this handle, not to the command. If you set variable = 2*3, what is in the variable? 6, isn't it? not "2*3", it's evaluated and the result is stored. The connection string is obsolete once you have the handle, it doesn't need to be stored, the handle needs to be stored.

Much more clear... Thanks Olaf for driving that home for me...



Yesturday, in an effort to answer my own question about how to tell what mode I in, I placed this code just before a cursorfill() and it returned 'Synchronous Mode'. I placed this code several places and it always returned 'Synchronous Mode'.

lnDS = thisform.DE_AppMain.DataSource
lSet=SQLGETPROP(lnDS, "Asynchronous")
IF lSet = .T.
=MESSAGEBOX("Asynchronous Mode",0,"Asynchronous ")
ELSE
=MESSAGEBOX("Synchronous Mode",0,"Synchronous")
ENDIF

1. So if Synchronous mode causes vfp to wait until it finishes, then it is clearly failing. After a lot of trial and error, I now have the answer on why the "connection is busy", maybe. It is actually busy. Let me explain...

Two grids on a form, "one" parent and one "many" child. No relations or anything to connect them. I populate the child grid in the parent's AfterRowColChange event by:

gcCountyPageKey = crsParent.county_pagekey

aa = 'select county_pagekey, clerk_id, qty_characters, start_time, end_time, unique_id '
bb = 'from child where county_pagekey = ?gcCountyPageKey'
dd = aa+bb

.DE_AppMain.caChild.SelectCmd = dd
dd = ''

.grdChild.RecordSource = Space(0)
.DE_AppMain.caChild.CursorFill()
.grdChild.RecordSource = 'crsChild'
.grdChild.Refresh()

I test and its in Synchronous mode...

Now, when first loading the form, the parent grid is immediately populated, and the child grid is not populated and it should be.

Changing records in the parent grid produces immediate results and you can hold down the up and down arrows and scroll the whole list of parent records with no child records showing.

Now, after about a minute, the child records starts showing up. Why, if Synchronous mode make vfp wait, why can I change the parent record while fetching the records has not finished.

There has never been any indication that the busy signal was legitimate, and of course I thought I had the logic wrong and changed it again when in fact the logic was working, its that it was showing too late.

There are indexes build on the lookup keys...

2. If I run the equivalent sql command in SSMS, those child records are returned immediately, so I was expecting the same results here.

3. Is there such a thing of cancelling the previous cursorfill() if I change to a different parent record that would trigger another cursorfill()?

4. Are all of these cursorfill() methods buffered waiting one behind the other to process, when fastly moving thru the parent grid, as everytime the record changes the AfterRolColChange event fires sending another cursorfill().

The point is that it is badly out of sync..., so bad that I thought it was not working at all...

Any ideas,
Stanley