[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3279
  • Last Modified:

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

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
hlinap
Asked:
hlinap
  • 6
  • 6
1 Solution
 
developmentguruCommented:
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
 
hlinapAuthor Commented:
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
 
developmentguruCommented:
 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
Free recovery tool for Microsoft Active Directory

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.

 
hlinapAuthor Commented:
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
 
developmentguruCommented:
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
 
hlinapAuthor Commented:
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
 
developmentguruCommented:
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
 
developmentguruCommented:
 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
 
hlinapAuthor Commented:
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
 
developmentguruCommented:
 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
 
hlinapAuthor Commented:
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
 
hlinapAuthor Commented:
Thx!
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 6
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now