?
Solved

How can I recognize the ADO ExecSQL method finishes in eoAsyncExecute mode?

Posted on 2007-12-07
12
Medium Priority
?
3,186 Views
Last Modified: 2013-11-23
Hello Experts!!!

I need to execute a command by ADOQuery.ExecSQL. I need it to work in async mode.
How can I recognize the ExecSQL method finishes its execution?

e.g.: as the "Open" method fires OnAfterOpen event
0
Comment
Question by:hlinap
[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
  • 6
  • 6
12 Comments
 
LVL 21

Expert Comment

by:developmentguru
ID: 20427196
You can respond to OnFetchComplete.  The only other possibility I see is to use a normal ADOQuery (non async) from within a thread and have the thread notify you when it is finished.
0
 
LVL 1

Author Comment

by:hlinap
ID: 20427308
Hello developmentguru,
Unfortunatelly, OnFetchComplete doesn't occur - at least because the passed guery gives no result. I'm just using one thread, so to have another one is not good way for me, in this case.

Another way could be to use Open method, but it fails with "CommandText does not return a result set". So I tried to set execute options as eoExecuteNoRecords, but then I get "Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another". Do you know why?
0
 
LVL 21

Expert Comment

by:developmentguru
ID: 20430792
 You are using ExecSQL... not running a select query?  If you run a select query and get no results it works using the Open method with OnFetchComplete (at least against SQL Server 2005 Express).  I am running a simple example where I set up an ADOConneciton and an ADOQuery.  I set them up and connect them and run a ridiuloucly long running query.  On the ADOQuery1FecthComplete I add a '-' to my forms caption.  By the way the query execute options were set to [eoAsyncExecute, eoAsyncFetch].

  I further tested it by making sure to assign a DBGrid's DataSource property to nil prior to executing my query and then set it to the DataSource attached to my query in the OnAfterFetch.  The grid shows all of the column results but no rows.

  If you are running a stored procedure then you should try using it in a select statement and then use the Open method.

  To have any more input I would need to know the database server type you are attaching to, the SQL statement, and possibly the stored procedure code itself.

Let me know.
0
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 
LVL 1

Author Comment

by:hlinap
ID: 20440045
I'm attaching to MSSQL Server 2000.
I need to run SQL statement in async mode and display a progress of fetching for each query I enter.

Also, I need to run SQL statement, e.g.:
"SELECT * INTO #tmp FROM table
SELECT * FROM #tmp"
The problem is that in this case the query doesn't fire neither OnFetchProgress nor OnFetchComplete events. Actually, data are fetched so I can display it but, unfortunatelly, I don't know when. In async mode no such event occurs that could inform me about it.

So, I recognized I should separate the qiven SQL into two parts (separated batches):
1. "SELECT * INTO #tmp FROM table" - pure DB side part
2. "SELECT * FROM #tmp" - pure fetching part
Ok, in this case the 2. statement will fire both OnFetchProgress and OnFetchComplete events.

Now, I need to run 1. statement (also in async mode):
The way should be to use ExecSQL method, but it fires (probably?) no event, whereas Open does. Open returns at least AfterOpen event. It is sufficient in this case, because 1. doesn't fetch data.
But as I said before, the Open method fails with "CommandText does not return a result set". So I tried to set execute options as eoExecuteNoRecords, but then I get "Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another".

Perhaps there is need for some "return value" - parameter to be set within ADOQuery???
In SQL Profiler I can see, for 1.:
declare @P1 int
set @P1=1
exec sp_prepexec @P1 output, NULL, N'select * into #tmp from table'
select @P1
What do you think?
0
 
LVL 21

Expert Comment

by:developmentguru
ID: 20442170
I think it sounds like you need to do a stored procedure on the server.  This will allow you to do all of the queries at once and still get the events (unless you want an event at each stage).  You did not mention which statement you need to do after your two queries... unless that is the exec sp_prepexec... that may affect what you need to do.  If all else fails you could make the last line of your statement:

select 0 result

Let me know how it goes.
0
 
LVL 1

Author Comment

by:hlinap
ID: 20448531
Think about my problem as I'm creating something like MS Query Analyzer. Stored procedures are acceptable, but not allways. User would like enter whatever query he wants.When user executes a query, I want to display a progress bar and a cancel button to him.

Everything works ok if the user passes a simple query. If he executes "SELECT * FROM table" it's ok, he can see fetch progress, he is able to cancel the query whenever he wants, etc.
But if user wans to execute "combined" query i.e. something like:
"SELECT * INTO #tmp FROM table
SELECT * FROM #tmp"
- Doesn't matter the query doesn't make sense - it's just an example - I'm not able to catch any event by ADO comps. (Unfortunatelly I have to use ADO; it's a requirement)

A "SELECT 0 result" as you recommended couldn't work, beacuse it is the very same case:
"SELECT * INTO #tmp FROM table
SELECT 0 result"
-it's a "combined" query, so no events. :(

Note:
Following block (i wrote before i have copied this from MS SQL Profiler):
"declare @P1 int
set @P1=1
exec sp_prepexec @P1 output, NULL, N'select * into #tmp from table'
select @P1"
 is a query generated by ADOQuery when I pass "select * into #tmp from table" and ADOQuery.Prepared := True;

===========================
The ideal solution should be to find a way how to catch the events for exactly this query:
"SELECT * INTO #tmp FROM table
SELECT * FROM #tmp"
===========================

Please, test this query and try to find the way.

The strange thing is that if I execute such "combined" query, I can obtain only BeforeOpen event, but no other. Even if data is actually being fetch, I can see with some utility that the data is really being downloaded. But no event raise!!! Why???

Thanks a lot!
0
 
LVL 21

Expert Comment

by:developmentguru
ID: 20449927
In my own testing, the problem appears to be with the ADO being used asynchronously with a query batch.  I was able to separate the query and get the events for the select portion.  My simple test had the ADOQuery ExecuteOptions set to [eoAsyncFetch] and the test code I did looks like this.

procedure TForm2.ADOQuery1AfterOpen(DataSet: TDataSet);
begin
  Caption := Caption + '-';
end;
 
procedure TForm2.ADOQuery1FetchComplete(DataSet: TCustomADODataSet;
  const Error: Error; var EventStatus: TEventStatus);
begin
  Caption := Caption + '.';
end;
 
procedure TForm2.Button1Click(Sender: TObject);
begin
  ADOQuery1.Close;
  ADOQuery1.SQL.Text := 'select * into #tmp from vam_icitem ';
  ADOQuery1.ExecSQL;
  ADOQuery1.Close;
  ADOQuery1.SQL.Text := 'Select * from #tmp';
  ADOQuery1.Open;
end;

Open in new window

0
 
LVL 21

Accepted Solution

by:
developmentguru earned 750 total points
ID: 20450011
 It does not give you the events for the select into #tmp because the events are generated by a client side cursor (and there is obviously not one for a sever side cursor like this).  You could get the timing of each stage by setting the execute options to [] and simply running it in a thread.
0
 
LVL 1

Author Comment

by:hlinap
ID: 20456815
Yes, I can understand if it doesn't give me the events for:
"SELECT INTO #tmp",
but if it does for:
"SELECT * from table",
so why it doesn't for:
"SELECT INTO #tmp
SELECT * from #tmp" ???
What's the problem???
0
 
LVL 21

Expert Comment

by:developmentguru
ID: 20458832
 The problem there is in ADO.  My personal guess is that the ADO engine parses the first viable query and determines if it will result in a client side cursor, if not it disables those events.  Unfortunately we as developers often use tools that do not work the way they should.  While you could wait for Microsoft to fix the issue it is almost always more expedient to find your own solution.  In this case you may be able to separate the individual queries into different calls.  I wish I had a magic solution for you.  Good luck.
0
 
LVL 1

Author Comment

by:hlinap
ID: 20463103
Yes, you had! You helped me to stop my hopeless endeavour to find the solution within ADO... :)

I'll separate queries into two parts i.e. ExecSQL and Open.
To be able recognize the first one finishes I'll set ADOQuery ExecuteOptions  := [] while running it "asynchronously" in a thread.
After this I'll run the second part in common "async" way by setting ADOQuery ExecuteOptions  := [eoAsyncExecute, eoAsyncFetch]

I hope this discusion will help also to others;)

Thanks a lot!
0
 
LVL 1

Author Closing Comment

by:hlinap
ID: 31413382
Thx!
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

A company’s centralized system that manages user data, security, and distributed resources is often a focus of criminal attention. Active Directory (AD) is no exception. In truth, it’s even more likely to be targeted due to the number of companies …
Recently I was talking with Tim Sharp, one of my colleagues from our Technical Account Manager team about MongoDB’s scalability. While doing some quick training with some of the Percona team, Tim brought something to my attention...
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
Suggested Courses

762 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