MS Sql HSTMT Connection Busy Errors

[Bruce]

If you are using MsSql with your web server, and you have more than one user using the server, then you will likely run into the STMT Connection Busy error.

see here for a fuller explanation;
http://www.softvelocity.net/community/blogs/clarion_news/archive/2005/12/15/410.aspx

In our experience here we've found that turning MARS on does completely eliminate the error. We're using Ms SQL 2005, and Clarion 6.3.9058. You may have similar success with earlier builds of Clarion, we haven't back-checked to see exactly which build works the best.

The key issue though is "turning MARS on". Despite what the SV post above says, it does not appear to be automatic. In essence, the _first_ file opened in the database must have the driver setting
/MULTIPLEACTIVERESULTSETS=TRUE

If you are using FM3 then the first file that connects is none of the ones in your dictionary, but rather the FM3 connection file. In order to set the driver string for that, you'll need a recent build of FM3.

The new driver setting is on the AutoUp tab of the FM3 global extension. (see attached pic.)

Cheers
Bruce




[attachment deleted by admin]

[charl99]

Hmmmmm,

Just one problem here...

I am using the dictionary as a DLL and the main application (the one running on a server on Windows!) crash when I do as you suggest.  Is there a way to override this for the web app?

Thanks

Charl

[Alberto]

Hi,

In our experience you ned to use:

/BUSYHANDLING=2 /MULTIPLEACTIVERESULTSETS=TRUE

Cheers,
Alberto

[Rhys Daniell]

We found that the /MULTIPLEACTIVERESULTSET parameter doesn't work, at least with the version of Clarion and the SQL client that we are using.

To get MARS support we had to add this to the connect string:

mars_Connection=Yes

[kevin plummer]

what version of Clarion and SQL are you using?

[Rhys Daniell]

6.3/9057, MSSQL 2005

[Bruce]

This might be a difference between 9057 and 9058.

Cheers
Bruce

[Stu]

Does this logic still apply for Clarion 7 + FM3 + MSSQL 2005?

* Also, if you put the MULTIPLE.. line into the FM3 Connect String, should you still set each file in your dictionary (in Clarion 7) with the MARS option?

[Stu]

I have implemented the FM3 Connect string, and put the same driver string into all files in the dictionary.

In my webserver I have a form for say CONTACT. On the form I have a child browse for CONTACT_ATTRIBUTES, which has a "Cascade on Delete" relationship with CONTACT.

I pre-populate the CONTACT_ATTRIBUTES file on Insert with say 20 records.

On Cancel, I get the following error:



[attachment deleted by admin]

[kevin plummer]

Hi Stu,

Is your Cascade delete set in your dict?

In SQL I tend to move all that logic to the server. Not sure if this will fix your prob but worth a try.

Kev

[Stu]

Kev,

Good call. I haven't moved any of that to the Server. Might be a good place to start.

Got any initial tips for moving cascading of delete to SQL server?

[kevin plummer]

Generally what I do is save a copy of all child records as the form open.

If the user clicks cancel then I delete the current child records and add back in the originals. If it is a new form then there are no original records to add back in but I have deleted the current records so there are no orphaned child records.

Of course if the user decides to click on a new menu item then the parent is not saved but the children are. I'm not sure if even a cascading delete would clean them up. So what I do to avoid this and users accidentally loosing work is to convert a parent insert into an update. So in my example above if there are no original records I would delete the parent record as well.

There may be a much easier way to do all this but it seems to work for me.

Cheers,

Kev

[Stu]

Kev,

Ahhh, thought you meant the SQL Server side.

Yeah, I've been adding routines to remove the files on cancelling of the form. Also have a function that runs on program startup which removes all blank records.

[kevin plummer]

I did mean you should think about moving the constraints to server side and it may fix your prob.

The alternative is to just drop the constraint and use your function. This approach seems to work for me.

Kev

[Stu]

Thanks Kevin.