MultipleActiveResultSets revisited

[mtabakin]

I have a C9.1 ABC NT 8.59 web server app that is used to enter "job requests" by teachers and staff to the maintenance dept. When they go to the webserver they are immediately taken to a NetWebform with multiple tabs but only the "tab of interest" such as General Maintenance, HVAC, Plumbing, Electrical, etc. will be shown based upon a parameter I pass in the URL link. The fields on the tabs are memory fields. When they click Save I do some work on some of the memory fields and then populate the appropriate fields in the SQL table "Jobs" and do an Access:JOBS.Insert().

I use MessageBox to log silent messages and would sometimes see the error: "Connection is busy with results for another hstmt........" when inserting into the Jobs table. Although the data was saved to the Jobs table.

Searching NetTalk Central and with help from the NetTalk Thursday webinar I understood I needed to add
      MultipleActiveResultSets=TRUE and BusyHandling=2 to the driver string of the first SQL file that is opened.

Since I use FM3 and its file would be the first opened, I added those driver options on the FM3 global extension. I also had the Jobs table being opened in the WebServer procedure (not sure why I had it there) but removed this opening and also removed the Jobs table from the Data/Tables schematic in the NetWebForm and manually do a p_web.Open(Jobs) before the Insert and a p_web.Close(Jobs) after the insert.

These changes went into service at the clients site over the weekend. Checking the log on Monday was encouraging because I did not see any of the "Connection is busy......" error messages. So far so good on this.

All was well until I got the report that one individual got an error when trying to save their form - but this error was not one previously reported. They reported that:

"When I hit the save button the response was a white page, 'no data received' ".

I checked the Message log and there was not corresponding "Connection is busy......." error for their entry attempt, in fact there were no error messages of any sort in the log. But once again the data did go into the SQL Jobs table correctly.

So here's my questions:

1. First, I had the BusyHandling=2 set in the dictionary as a driver option on all my SQL tables (thanks to JP's help way back when on another project). Should this be removed from the dictionary since I added it to FM3 and understand you only need to do these driver options on the first file opened?
2. Is the "no data received" being generated by the NT webserver and if so under what conditions is this error generated?
3. Is this new error possibly related to the MultipleActiveResultSets or does it indicate some other problem occurred?
4. Any ideas how to eliminate this error?

The "Connection is busy........." error and the error today are random - this was one reported error out of about 20 requests made on Monday so it cannot be reproduced. Any ideas of traps or other debugging I can add to the app to narrow down the cause would be appreciated along with any other ideas anyone has.

Thanks!

[kevin plummer]

My experience is that your app pretty much won't run without MultipleActiveResultSets=TRUE and BusyHandling=2 using MSSQL and the way you have it setup is what I have and it works nicely. The problem is that it also introduces the potential of another problem - SQL deadly embrace. This causes threads in your app to hang and generally slow it down until your users call and you restart your app. It doesn't sound like this happened from your description but worth noting. The fix for this is to wrap your file access (insert\change\delete\select) in p_web.SetSQLTimeout(TableName,net:on) and p_web.SetSQLTimeout(TableName,net:off).

[mtabakin]

Thanks Kevin for the additional info. I understand locking and don't think I'm experiencing locking but I'm a little unclear how to check for this - will have to do my searching of how to determine the SQL DB is locking.

But in the meantime let me state what I think you said and what I've gleamed from my searching on SetSQLTime.

NT wraps any file IO it handles with p_web.SetSQLTimeout(tablename,net:on) and  p_web.SetSQLTimeout(tablename,net:off ) for you BUT if you are doing IO in source code you would need to do this yourself. So if NT is doing this for all IO it handles, wrapping my source code IO would be the same as if NT were handling it. The reason I need to be clear is I don't want to degrade what appears to be working fine right now with the MultipleActivieResultSets and BusyHandling driver options - it's be two days since implementing these options and the log shows NO occurances of the "hstmt" error and I was getting at least a couple a day prior to this.

So I guess for now I'll hold off on this until I check into the locking situation or until I see some other problem happening with IO.

Thanks again.

[kevin plummer]

NT wraps any file IO it handles with p_web.SetSQLTimeout(tablename,net:on) and  p_web.SetSQLTimeout(tablename,net:off ) for you BUT if you are doing IO in source code you would need to do this yourself.

> yes but it's not a prerequisite - only if you are experiencing locking. My comments were more of a "be prepared" if you see this happening, at least you know what it is.

 it's be two days since implementing these options and the log shows NO occurances of the "hstmt" error

> correct - you won't see that error again.