Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Connection is busy with results for another hstmt?

Posted on 1999-07-09
15
Medium Priority
?
1,325 Views
Last Modified: 2010-05-18
Hi this is a bug, I don't know why it occurs.. but it will only occur in my rather large app:

Query1 is a design time query, it is currently open and I call:

Query1.Close;

This results in the following Exception E.message:

Key violation.
General SQL error.
[Microsoft][ODBC SQL Server Driver]Connection is busy with results for another hstmt

The database is MSSQL, and I am using a D4/professional alias, SQL server alias type, pointing to a sql server odbc data source.

Any ideas?
0
Comment
Question by:RBertora
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 5
  • 2
  • +1
15 Comments
 
LVL 13

Expert Comment

by:Epsylon
ID: 1387404
Maybe you should do a call to Query1.UnPrepare.....

Eps.
0
 
LVL 7

Author Comment

by:RBertora
ID: 1387405
Unprepare returns: Query1 Cannot perform this on an open dataset!


Here is the culprit procedure:

procedure TFormEvents.RefreshQuery;
var
  TimeOut : Integer;
  ok : Boolean;
  skip : Boolean;
begin
  Table1.Close;
  Table2.Close;
  Table3.Close;
  Table4.Close;
  TableAttachments.Close;


  // at this point Query1.Active is true!

  skip := False;
  try
    Query1.UnPrepare;     // the unprepare returns this error msg: Query1 Cannot perform this on an open dataset!
    Query1.Close;         // the original message returns the Key violation. err msg above
  except on E:exception do
    begin
      showmessage(E.Message);
      skip := true;
    end;
  end;

  if skip = False then
  begin
    Query1.Sql.Clear;
    Query1.Sql.Add(GlobalQueryString);
    Query1.Prepare;
    Query1.Open;
  end;


  Table1.Open;
  Table2.Open;
  Table3.Open;
  Table4.Open;
  TableAttachments.Open;
end;
0
 
LVL 13

Expert Comment

by:Epsylon
ID: 1387406
Did you know that when you change the text of a query at runtime, the query is automatically closed and unprepared.


    Query1.Sql.Clear;
    Query1.Sql.Add(GlobalQueryString);
    Query1.Active := true;

should be enough to handle it all. I just wonder why you close all the tables.....

Eps.

0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 13

Expert Comment

by:Epsylon
ID: 1387407
Any progress?
0
 
LVL 7

Author Comment

by:RBertora
ID: 1387408
Am I left work with the problem still intact... the error message should be the key to solving this one... but I can't find any help on it...

I probably don't need to close and open the lot... Still it is a weird one it may have nothing to do with the code.. more of an odbc mess that has occured because of an upgrad of another system that uses the same sql server..... Monday I'll tackle it again, I think I might just rewrite the whole form see it that makes a change.....I have had this problem before, I got rid of it by recreating the DB... but I can't do this indefinately.. the software is now in beta and this has to be sorted.. I have moved the db onto a diff sql server.. I'll recreate it and hopefully I won't see this msg again... but what is a hstmt???????

0
 
LVL 13

Expert Comment

by:Epsylon
ID: 1387409
A hStmt holds a return value containing the ODBC statement handle created by the ODBC driver manager after a request has been made.

I think you should not bother about those details since you can't do much with it in Delphi.

By the way, I think there is a good chance that closing all the tables is causing the trouble.

Eps.
0
 
LVL 7

Author Comment

by:RBertora
ID: 1387410
I figured out the answer to the question:

A Dbgrid is bound to the query. The problem never arose before because the grid never had more than a few records... ie not scrollable.

The problem was that using the BDE/odbc connection, SQL server leaves an open / lock type connection to the table and it cannot close it until it has read the entire table.. traditionally the dbgrid only reads as much data as it needs to display.

for some reason I need to perform a Query1.FetchAll before the Query1.Close can go through....

I do however suspect that this is because I am using the Professional BDE version and not Enterprise.. I donot remember this being the case when I used the client server version...

Oh I still dislike this solution, so if any one knows of a better way to drop/close the open hstmt then please let me know.

-Bert.
0
 
LVL 13

Expert Comment

by:Epsylon
ID: 1387411
I hope I understand what you are saying.

Maybe you can just 'disconnect' the DBGrid from the query by setting DBGrid.Enable to false before changing the query and back to true again after....

Eps.
0
 
LVL 7

Author Comment

by:RBertora
ID: 1387412
Ha, nice Idea I did try it but it didn't work.. and judging by your question you seem to understand... I will try the enterprise BDE version soon / tommorrow perhaps.. to make sure that it is the fact that I am using professional that makes this difference.
0
 
LVL 3

Accepted Solution

by:
Pegasus100397 earned 600 total points
ID: 1387413
RBertora,

  Had the same problem awhile back. As I remember it you need to go into EITHER (or both) the ODBC properties or the BDECFG and tell it "the maximum number of rows to return at once" or something like that. Set it to something like 500. The default is ALWAYS the entire dataset, not "groups" of records like you would think.

Don't be concerned about a lower number, it just means (in the example above) that 500 records will be returned in one "fetch" and that, if needed or scrolled past, another "batch" will be sent automatically.

This will definitely increase the responsiveness of your application! I have a SQL Server database that has over 142 MILLION records and trust me, my users would NOT want to wait for the entire record set to be returned (plus the network types would get upset as I have over 200 people using the application!) hehehehe

Good Luck with your project!
Pegasus
0
 
LVL 7

Author Comment

by:RBertora
ID: 1387414
Hmm, my data is not large enough to see if your answer works, but I take it on good faith that you are correct.
Thanks Pegasus.
0
 
LVL 7

Author Comment

by:RBertora
ID: 2030655

Yes! I agree this is the bugger:

Key violation.
General SQL error.
[Microsoft][ODBC SQL Server Driver]Connection is busy with results for another hstmt

I posted 2 questions asking for help on this one but noooo one could help me,

http://www.experts-exchange.com/jsp/qShow.jsp?ta=delphi&qid=10180672 
and the second one I deleted cause no one could help me...

BUT eventually I figured it out. I never see this error message again. Good riddens
(I would have paid plenty points for an answer.. )
So if youre keen to get an answer for that let me know.

As for autoincrement fields I am managing fine with them, although also took me a while
to sort out... I also use text fields fine.. but must admit I have no blobs...

Rob.
0
 
LVL 1

Expert Comment

by:logosapience
ID: 2622737
Hi, all

I saw your the accepted answer but the problem is that I don't know here to find this setting (in odbc - not present, in BDECFG - it's max rows but when I set it to 500 and make a select * from table, I only get the 500 first records of the table).

Where can I find the right setting ?

Thanks.
0
 
LVL 1

Expert Comment

by:logosapience
ID: 2622742
Hi, all

I saw your the accepted answer but the problem is that I don't know here to find this setting (in odbc - not present, in BDECFG - it's max rows but when I set it to 500 and make a select * from table, I only get the 500 first records of the table).

Where can I find the right setting ?

Thanks.
0
 
LVL 7

Author Comment

by:RBertora
ID: 2622777
logosapience : I had accepted that answer without properly testing it, I had same problem as you only 500 rows being returned... what a pain.

I eventually redesigned all my code such if a form had an open table or query, it remained closed for as long as possible. ie

In design time, close all your tables that are open, then:
  formonactivate: open table
  formonclose: close table
-that ensured that the table is closed when the form is closed.

every where else I closed queries the second I didn't absolutely need them open.
......
that got rid of that anoying error message.
Rob.
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Objective: - This article will help user in how to convert their numeric value become words. How to use 1. You can copy this code in your Unit as function 2. than you can perform your function by type this code The Code   (CODE) The Im…
In my programming career I have only very rarely run into situations where operator overloading would be of any use in my work.  Normally those situations involved math with either overly large numbers (hundreds of thousands of digits or accuracy re…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…
Suggested Courses

704 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question