How to execute an ADO query asynchronously?

[kibokochui]

Is this a valid code (running in a COM+ component)?

VB class (MTSTransactionMode = 1 - NoTransactions):

Private Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)

Implements ObjectControl

Private ObjCtx As ObjectContext
Private ASPReq As ASPTypeLibrary.Request
Private ASPRes As ASPTypeLibrary.Response
Private ASPSer As ASPTypeLibrary.Server

Private Sub ObjectControl_Activate()

On Error Resume Next

Set ObjCtx = GetObjectContext()
Set ASPReq = ObjCtx("Request")
Set ASPRes = ObjCtx("Response")
Set ASPSer = ObjCtx("Server")

End Sub

Private Function ObjectControl_CanBePooled() As Boolean
ObjectControl_CanBePooled = False
End Function

Private Sub ObjectControl_Deactivate()

On Error Resume Next

Set ObjCtx = Nothing
Set ASPReq = Nothing
Set ASPRes = Nothing
Set ASPSer = Nothing

End Sub

Public Sub RunQueryAsync()

On Error GoTo Run_Err

Dim cx As Connection
Dim rs As Recordset

Set cx = New Connection
cx.CursorLocation = adUseServer
cx.ConnectionString = "Provider=sqloledb;server=..."
cx.Open

' Execute long query
Set rs = cx.Execute("exec pa_longquery", , adAsyncExecute)

Do Until rs.State <= 1
' If client is not connected then cancel query and exit
If Not ASPRes.IsClientConnected Then
cx.Cancel ' Cancel query
Err.Raise vbObjectError + 512, "", "The client has disconnected"
End If
' Wait
Sleep 100
Loop

' Return data to client
ASPRes.Write rs.GetString

' Ok
ObjCtx.SetComplete

Run_Exit:
If rs.State <> 0 Then rs.Close
Set rs = Nothing
cx.Close
Set cx = Nothing

Exit Sub

Run_Err:
On Error Resume Next
ASPRes.Write "¡¡ERROR!! " & Err.Description
ObjCtx.SetAbort
Resume Run_Exit

End Sub

Pros, contras, performance, ideas....

Thanks.

[Aaron Bertrand [MVP]]

You can use an asynchronous flag with ADO, however it doesn't make much
sense if that query needs to return data to the client.
[url]http://www.aspfaq.com/2194[/url]

--
Aaron Bertrand
SQL Server MVP
[url]http://www.aspfaq.com/[/url]




"kibokochui" <No@mail> wrote in message
news:O1bKr4Y4DHA.1644@TK2MSFTNGP10.phx.gbl...

> Is this a valid code (running in a COM+ component)?
>
> VB class (MTSTransactionMode = 1 - NoTransactions):
>
> Private Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
>
> Implements ObjectControl
>
> Private ObjCtx As ObjectContext
> Private ASPReq As ASPTypeLibrary.Request
> Private ASPRes As ASPTypeLibrary.Response
> Private ASPSer As ASPTypeLibrary.Server
>
> Private Sub ObjectControl_Activate()
>
> On Error Resume Next
>
> Set ObjCtx = GetObjectContext()
> Set ASPReq = ObjCtx("Request")
> Set ASPRes = ObjCtx("Response")
> Set ASPSer = ObjCtx("Server")
>
> End Sub
>
> Private Function ObjectControl_CanBePooled() As Boolean
> ObjectControl_CanBePooled = False
> End Function
>
> Private Sub ObjectControl_Deactivate()
>
> On Error Resume Next
>
> Set ObjCtx = Nothing
> Set ASPReq = Nothing
> Set ASPRes = Nothing
> Set ASPSer = Nothing
>
> End Sub
>
> Public Sub RunQueryAsync()
>
> On Error GoTo Run_Err
>
> Dim cx As Connection
> Dim rs As Recordset
>
> Set cx = New Connection
> cx.CursorLocation = adUseServer
> cx.ConnectionString = "Provider=sqloledb;server=..."
> cx.Open
>
> ' Execute long query
> Set rs = cx.Execute("exec pa_longquery", , adAsyncExecute)
>
> Do Until rs.State <= 1
> ' If client is not connected then cancel query and exit
> If Not ASPRes.IsClientConnected Then
> cx.Cancel ' Cancel query
> Err.Raise vbObjectError + 512, "", "The client has disconnected"
> End If
> ' Wait
> Sleep 100
> Loop
>
> ' Return data to client
> ASPRes.Write rs.GetString
>
> ' Ok
> ObjCtx.SetComplete
>
> Run_Exit:
> If rs.State <> 0 Then rs.Close
> Set rs = Nothing
> cx.Close
> Set cx = Nothing
>
> Exit Sub
>
> Run_Err:
> On Error Resume Next
> ASPRes.Write "¡¡ERROR!! " & Err.Description
> ObjCtx.SetAbort
> Resume Run_Exit
>
> End Sub
>
> Pros, contras, performance, ideas....
>
> Thanks.
>
>

[kibokochui]

In my case, querys always return data to the client.
I want to execute them asynchronously to offer to the user the possibility
of cancelling them.

Thanks.

"Aaron Bertrand [MVP]" <aaron@TRASHaspfaq.com> escribió en el mensaje
news:OTIDEgb4DHA.2168@TK2MSFTNGP12.phx.gbl...

> You can use an asynchronous flag with ADO, however it doesn't make much
> sense if that query needs to return data to the client.
> [url]http://www.aspfaq.com/2194[/url]
>
> --
> Aaron Bertrand
> SQL Server MVP
> [url]http://www.aspfaq.com/[/url]
>
>
>
>
> "kibokochui" <No@mail> wrote in message
> news:O1bKr4Y4DHA.1644@TK2MSFTNGP10.phx.gbl...

> > Is this a valid code (running in a COM+ component)?
> >
> > VB class (MTSTransactionMode = 1 - NoTransactions):
> >
> > Private Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
> >
> > Implements ObjectControl
> >
> > Private ObjCtx As ObjectContext
> > Private ASPReq As ASPTypeLibrary.Request
> > Private ASPRes As ASPTypeLibrary.Response
> > Private ASPSer As ASPTypeLibrary.Server
> >
> > Private Sub ObjectControl_Activate()
> >
> > On Error Resume Next
> >
> > Set ObjCtx = GetObjectContext()
> > Set ASPReq = ObjCtx("Request")
> > Set ASPRes = ObjCtx("Response")
> > Set ASPSer = ObjCtx("Server")
> >
> > End Sub
> >
> > Private Function ObjectControl_CanBePooled() As Boolean
> > ObjectControl_CanBePooled = False
> > End Function
> >
> > Private Sub ObjectControl_Deactivate()
> >
> > On Error Resume Next
> >
> > Set ObjCtx = Nothing
> > Set ASPReq = Nothing
> > Set ASPRes = Nothing
> > Set ASPSer = Nothing
> >
> > End Sub
> >
> > Public Sub RunQueryAsync()
> >
> > On Error GoTo Run_Err
> >
> > Dim cx As Connection
> > Dim rs As Recordset
> >
> > Set cx = New Connection
> > cx.CursorLocation = adUseServer
> > cx.ConnectionString = "Provider=sqloledb;server=..."
> > cx.Open
> >
> > ' Execute long query
> > Set rs = cx.Execute("exec pa_longquery", , adAsyncExecute)
> >
> > Do Until rs.State <= 1
> > ' If client is not connected then cancel query and exit
> > If Not ASPRes.IsClientConnected Then
> > cx.Cancel ' Cancel query
> > Err.Raise vbObjectError + 512, "", "The client has

disconnected"

> > End If
> > ' Wait
> > Sleep 100
> > Loop
> >
> > ' Return data to client
> > ASPRes.Write rs.GetString
> >
> > ' Ok
> > ObjCtx.SetComplete
> >
> > Run_Exit:
> > If rs.State <> 0 Then rs.Close
> > Set rs = Nothing
> > cx.Close
> > Set cx = Nothing
> >
> > Exit Sub
> >
> > Run_Err:
> > On Error Resume Next
> > ASPRes.Write "¡¡ERROR!! " & Err.Description
> > ObjCtx.SetAbort
> > Resume Run_Exit
> >
> > End Sub
> >
> > Pros, contras, performance, ideas....
> >
> > Thanks.
> >
> >

>
>

[Aaron Bertrand [MVP]]

> In my case, querys always return data to the client.

> I want to execute them asynchronously to offer to the user the possibility
> of cancelling them.

That's not what asynchronous is for. Checks to Response.isClientConnected
can allow you to cancel your query (e.g. issue a rollback or kill the spid).

--
Aaron Bertrand
SQL Server MVP
[url]http://www.aspfaq.com/[/url]

[kibokochui]

Yes (in the code of the first message I use Response.isClientConnected). But
how can I use Response.isClientConnected if I do not execute the
Connection.Execute call asynchronously?
If I use Connection.Execute synchronously ASP request must wait until query
terminates (and it can be long) and then I can use
Response.isClientConnected to decide if return query result to client or
not, but what I want to do is cancel query call, not cancel the return of
data to the client.

Thanks!

"Aaron Bertrand [MVP]" <aaron@TRASHaspfaq.com> escribió en el mensaje
news:ut2v9Vp4DHA.3548@TK2MSFTNGP11.phx.gbl...

> > In my case, querys always return data to the client.
> > I want to execute them asynchronously to offer to the user the

possibility

> > of cancelling them.

>
> That's not what asynchronous is for. Checks to Response.isClientConnected
> can allow you to cancel your query (e.g. issue a rollback or kill the

spid).

>
> --
> Aaron Bertrand
> SQL Server MVP
> [url]http://www.aspfaq.com/[/url]
>
>

[Mike Collier]

>Re: How to execute an ADO query asynchronously?
[url]http://81.130.213.94/myforum/forum_posts.asp?TID=60&PN=1[/url]

--
Mike Collier BSc( Hons) Comp Sci
FREE ado browser tool when you register online.
[url]www.adoanywhere.com[/url]
forum [url]http://www.adoanywhere.com/forum[/url]