Thursday, June 12, 2008

" Connection is busy with results for another hstmt"

One of the problems with working on the same program for years, is that you always end up paying for your sins. In my case the sin in question is still using the bde and odbc.

"Connection is busy with results for another hstmt" is a common error when connecting to ODBC databases. It occurs because an OBDC connection can only have one active cursor at a time. By default, odbc only retrieves the first 20 records for a query. If the query contains more than 20 records, the rest are retrieved on demand. That's all very well, but when you open a second query using same connection/session you get the dreaded hstmt error.

I thought I had beaten it years ago, but my latest set of changes have resulted in the error reappearing.

There are a few possible solutions to this error:
  1. Use TTable components. These don't have the error as they open a new connection each time. Not a very pretty solution :(
  2. Put a FetchAll after each Open. This forces the retrieval of all records. Not practical in my case as I have around 250 tquery components to check.
  3. Use multiple connections. Also not practical for me.
  4. Replace the BDE with something else that doesn't have the problem. In progress but...
  5. Cheat. Set the odbc rowset size to a larger number. If you set it to 200, then the first 200 records will be returned. If you set it to -1 then ALL records will be returned. This could have dire effects on performance if your queries return a lot of records.
This time round I discovered, and went with option 5.

To set the rowset size with the BDE, do the following:
  1. Click on your TDatabase component
  2. In the object inspector, expand Params
  3. Put in a Key of "ROWSET SIZE"
  4. Put in the desired value
Changing the rowset size should work with other odbc connection components as well.

Update: As Otto has pointed out, the error can also be resolved, if you are using sql server 2005 or later, by using the SQL Native Client drivers. See Multiple Active Record Sets for more details. Doesn't help me though :(

11 comments:

Ottar Holstad said...

When using BDE to connect to a MS SQL Server, via ODBC, this can (sometimes?) be solved by using SQL Native Client instead of SQL Server Driver for ODBC.

Sean said...

Thanks. That will work with sql 2005, but not with sql 2000.

See http://msdn.microsoft.com/en-us/library/ms345109.aspx

Ottar Holstad said...

hmmmm... it's possible to install only SQL Native Client and connect to a an older SQL Server, and I had the impression that the support-staff at my company solved this problem for a customer by doing that

Sean said...

According to the MARS article, multiple active record sets are implemented in the database for sql server 2005.

For older versions of sql server, it is implemented in the oledb native driver by opening more connections. This may have side effects.

If you have sql server 2005+, the native driver is the way to go. For older versions of sql server, it may be worth trying too.

Wilfred Villarruz said...

Thanks Sean. This saved my day.

zeycus said...

It saved my day too, thanks a lot!

For me, the fetchall() approach is good, so far no huge tables.

n3wjack said...

Since this seems to be one of the first hits on Google when you look up this error message, I'll add the solution that worked for me here as well. :)

In my case the problem was caused because 2 datasets where creating cursors over the same database connection.

The fix in my case was to change the properties of the dataset I created myself to use clientside cursors (CursorLocation) and a static CursorType (I don't need to update the data using this dataset).

David said...

Thanks for the great advice Sean. I'm currently having this problem with a classic ASP application. How would I go about using option 5? (Increasing rowset size)

Sean said...

@David,

I am not sure how to do it with asp. Perhaps you can add Rowset to the connection string,

JayArun said...

Hi, i have the same problem,in my app there are 6 database components to connect databse via dbe to MS Sql server, i tried with rowset size, 3 of my datbase component is working fine, but other 3 are still have the connection busy problem,
I tried with Sql native client also, it solve connection problme, but produces some other uncoverable errors.

Lucas Costa Mendes said...

Thanks man, you have helped me a lot with your post, I changed the config of Connection, the mode property, from fmOnDemand to fmAll, and it works fine.