"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:
- Use TTable components. These don't have the error as they open a new connection each time. Not a very pretty solution :(
- 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.
- Use multiple connections. Also not practical for me.
- Replace the BDE with something else that doesn't have the problem. In progress but...
- 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.
To set the rowset size with the BDE, do the following:
- Click on your TDatabase component
- In the object inspector, expand Params
- Put in a Key of "ROWSET SIZE"
- Put in the desired value
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 :(