Solved

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

Posted on 2007-12-07
12
3,032 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
  • 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
 
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
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
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 250 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

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
Many companies are looking to get out of the datacenter business and to services like Microsoft Azure to provide Infrastructure as a Service (IaaS) solutions for legacy client server workloads, rather than continuing to make capital investments in h…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
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…

707 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now