Executing long-running tasks from ASP(author: Adrian Forbes)IntroductionThis document details how to correctly use ASP to asynchronously run EXEs, long-running processes/SQL commands and anything elsenot suited to ASP automation such as the manipulation of Microsoft Office applications. ...
First we will look at using MSMQ and a lot of custom code to create a serialised method of executing tasks from ASP, then at the end we will look at using Queued Components under Win2k to do a similar job (with only a small amount of code and configuration) to asynchronously call our custom COM objects.
Requirements
This code will require an up and running IIS server, MSMQ and Visual Basic. The examples use Visual Basic but any language that can manipulate MSMQ COM objects can be used.
Overview
This technique works by using ASP to send a request to a message queue for a certain task to be completed. A Visual Basic application will then be running on a server to carry out these requests.
The upsides of this technique are that it allows us to spawn from ASP tasks that ASP is not suited for. The messages containing the requests are sent asynchronously and the reading of them is serialised by our application if desired so that only one instance of a certain stored procedure is running at any time, or only one instance of Microsoft Word etc is created at any one time. If you don't particularly need total serialisation then multiple versions of the application can be running on either the same machine or on different machines to simultaneously process the requests.
The downsides are that it requires our custom written application to be running to service the requests. This requires that the server it runs on has an interactive user. Another downside is one inherent with asynchronous processing, namely that there is no return path. ASP issues the request and then continues on with its processing without knowing if the request has succeeded, failed or even been carried out at all.
The Process in Detail
ASP is designed for running short-lived sections of code. If your site needs to execute a task that is time-consuming then running it from pure ASP might not be the best solution. By default IIS only has 10 threads with which to execute your ASP pages. If your pages are hogging threads due to large execution times then you can see that a large website can start to suffer a reduction in performance.
One alternative is to use MSMQ. This was a stand-alone application under NT4, whereas message queuing is actually built in to the operating system for Win2k. Although this article focuses mainly on Win2k, the exact same techniques and code should work under NT4 also.
MSMQ is a "store and forward mechanism" that allows one application to store a message which is then forwarded onto a second application. The advantage of this that we will be leveraging from ASP is that the messages are sent asynchronously, so the ASP code simply sends a message and then continues on with its execution. The content of the message is entirely up to you and you might well need to devise a simple protocol that lets your target application know what you want it to do.
Our example will involve ASP sending messages that will request one of a number of possible actions. These actions will be running an EXE, executing some SQL or manipulating a Word document. Running EXEs and manipulating Word documents are not something you should be doing from ASP regardless of execution time so this technique might still be of use to you if you wish to carry out those actions.
The example is programmed to all run on the same machine but can be altered if you wish to run different parts on different machines. First of all we need a message queue. Once MSMQ is installed (either via Option Pack 4 for NT4 or via Add/Remove Programs on Win2k) you can create a queue via the MMC.
For Win2k:
- Open the Computer Management application via Start->Settings->Control Panel->AdministrativeTools->Computer Management
- Expand Services and Applications
- Expand Message Queuing
- Right-click on Public Queues
- Select New->Public Queue from the context menu
- Enter aspexe in the Name box and leave the Transactional checkbox clear
- Click OK
- Expand Public Queues
- Right-click on aspexe and select Properties from the context menu
- Select the Security tab.
- For simplicity's sake select Everyone from the list of users and check the Allow box for Full Control
- Click OK
Our queue is now set up and ready to be used. If you wish to create the queue via a script then execute the following code
Set objQI = CreateObject("MSMQ.MSMQQueueInfo")
objQI.PathName = ".\aspexe"
objQI.Create False, True
Set objQI = Nothing
|
The PathName is in the format of machinename\queuename but we are using "." as the machine name to refer to the local machine. The first parameter of the Create function is False indicating we don't want a transactional queue and the second parameter is True indicating we want the queue to be world-readable.
To send a message to this queue we use the following code
' Create an instance of MSMQQueueInfo. This will let us
' manipulate queues
Set objQI = CreateObject("MSMQ.MSMQQueueInfo")
' Specify the name of the queue we want to open
objQI.PathName = ".\aspexe"
' Call the Open method which returns an instance
' of MSMQQueue which represents the actual queue
Set objQ = objQI.Open(MQ_SEND_ACCESS, MQ_DENY_NONE)
' Create a new message
Set objMSG = CreateObject("MSMQ.MSMQMessage")
' Send the message to the queue specifying no transactions
objMSG.Send objQ, MQ_NO_TRANSACTION
' Tidy up
objQ.Close
Set objMSG = Nothing
Set objQ = Nothing
Set objQI = Nothing
|
This is the basics required to send a message. In case you haven't already worked it out, the above code is quite useless as it simply sends an empty message. Still, if you have tried to run this code from VB it will let you know that the queue is working. If you've ran the code then in the Computer Management application, expand Public Queues, aspexe and click on Queue Messages. You should see the message that was sent. Delete this message by right-clicking on Queue Messages and selecting AllTasks->Purge… from the context menu. If you haven't run the code then don't worry, we'll soon be listing the complete ASP page that contains this snipet.
Now let's create something a bit more meaningful to send. Messages can contain almost anything, but we're going to use an ADO recordset. The recordset will contain one or more rows, and each row will contain two fields. One field specifies the action we want to take (we'll call it Action), and another field will specify the details of the action (we'll call it Data). So if we want to run an EXE such as notepad then the two fields will be
If we want to execute, for example, two EXEs then we will create two rows
| Action | Data |
| EXE | notepad.exe |
| EXE | calc.exe |
As we add different types of Action we can mix them all up in one recordset, we'll see that in action later.
This is the code we will run to create the recordset.
' Create a recordset to store our data
Set objRS = CreateObject("ADODB.Recordset")
' Add our fields
objRS.Fields.Append "Action", adVarChar, 20
objRS.Fields.Append "Data", adVarChar, 255
' Open the recordset and add our data
objRS.Open
objRS.AddNew
objRS("Action") = "EXE"
objRS("Data") = "notepad.exe"
|
Now let's put it all together in an ASP page
<%@ Language=VBScript %>
<HTML>
<HEAD>
<META NAME="GENERATOR" Content="Microsoft Visual Studio 6.0">
<title>Basic aspexe send</title>
</HEAD>
<BODY>
<!-- File: BasicSend.asp -->
<!-- Author: Adrian Forbes -->
<%
' Define the constants we are going to use
MQ_SEND_ACCESS = 2
MQ_DENY_NONE = 0
MQ_NO_TRANSACTION = 0
adVarChar = 200
' Create an instance of MSMQQueueInfo. This will let us
' manipulate queues
Set objQI = CreateObject("MSMQ.MSMQQueueInfo")
' Specify the name of the queue we want to open
objQI.PathName = ".\aspexe"
' Call the Open method which returns an instance
' of MSMQQueue which represents the actual queue
Set objQ = objQI.Open(MQ_SEND_ACCESS, MQ_DENY_NONE)
' Create a new message
Set objMSG = CreateObject("MSMQ.MSMQMessage")
' Create a recordset to store our data
Set objRS = CreateObject("ADODB.Recordset")
' Add our fields
objRS.Fields.Append "Action", adVarChar, 20
objRS.Fields.Append "Data", adVarChar, 255
' Open the recordset and add our data
objRS.Open
objRS.AddNew
objRS("Action") = "EXE"
objRS("Data") = "notepad.exe"
objMSG.Label = "aspexe"
objMSG.Body = objRS
' Send the message to the queue specifying no transactions
objMSG.Send objQ, MQ_NO_TRANSACTION
' Tidy up
objRS.Close
Set objRS = Nothing
objQ.Close
Set objMSG = Nothing
Set objQ = Nothing
Set objQI = Nothing
%>
<p>Your message has been sent</p>
</BODY>
</HTML>
|
This code does the job, but as you can see we are hard coding everything so it will only ever send a request to execute notepad. Next we'll create the application that will read these requests and execute them, after that we'll come back to this code and refine it.
The process that carries out these requests will be a Visual Basic application. So create a new Standard EXE project. Add a reference to
Microsoft ActiveX Data Objects Library
Microsoft Message Queue Object Library
What versions you have installed will affect the exact names of the references. For example I am using
Microsoft ActiveX Data Objects 2.6 Library
Microsoft Message Queue 2.0 Object Library
This application has to do two things. First it has to respond to new messages appearing on the queue, second it has to get that message, read the recordset inside it and execute the instructions detailed within. Let's get it receiving new message events first of all.
To do this we need to declare two global objects.
Option Explicit
' This object holds a reference to the queue
Private m_objQueue As MSMQ.MSMQQueue
' This object notifies us when a message has arrived
Private WithEvents m_objEvent As MSMQ.MSMQEvent
|
The queue object is a reference to our queue, and the event object is used to notify us of new messages as they arrive. When the application loads we want to open our queue and specify that we wish to know of events.
Private Sub Form_Load()
Dim objQI As MSMQ.MSMQQueueInfo
' Open the queue for receive access
Set objQI = New MSMQ.MSMQQueueInfo
objQI.PathName = ".\aspexe"
Set m_objQueue = objQI.Open(MQ_RECEIVE_ACCESS, MQ_DENY_NONE)
' Create a new event object
Set m_objEvent = New MSMQ.MSMQEvent
' Tell the queue to use our event object to notify us of
' new messages
m_objQueue.EnableNotification m_objEvent
' Tidy up
Set objQI = Nothing
End Sub
|
Now when a message arrives it raises an event which we programme our m_objEvent object to respond to.
Private Sub m_objEvent_Arrived(ByVal Queue As Object, _
ByVal Cursor As Long)
Dim objQ As MSMQ.MSMQQueue
Dim objMSG As MSMQ.MSMQMessage
Dim objRS As ADODB.Recordset
' This event is fired when a new message is in the queue
' This time we are passed a reference to the queue as a
' parameter of the event
Set objQ = Queue
' Get the current message from the queue
' Note that we are specifying a timeout on the
' ReceiveCurrent call (1000 milliseconds which is
' 1 second) as there is no guarantee that the
' message is still there. For example it might
' have already been read by another process
Set objMSG = objQ.ReceiveCurrent(ReceiveTimeout:=1000)
If Not objMSG Is Nothing Then
' There was a message in the queue so let's process it
' Get the recordset from the message
Set objRS = objMSG.Body
' Tidy up
Set objMSG = Nothing
End If
' Once we have finished we need to re-create our
' event notification
Set m_objEvent = Nothing
Set m_objEvent = New MSMQ.MSMQEvent
m_objQueue.EnableNotification m_objEvent
End Sub
|
In the above code we get the recordset from the message but do nothing with it. Here is the code we'll be using to process the recordset
' Process the recordset
While Not objRS.EOF
Select Case UCase(objRS("Action"))
Case "EXE"
On Error Resume Next
Shell objRS("Data")
On Error GoTo 0
End Select
objRS.MoveNext
Wend
objRS.Close
Set objRS = Nothing
|
As we expand the functionality to execute SQL commands etc, we will simply add to our Select Case statement. Below is the code in its entirety. Save your project using any name you wish and compile it into an EXE.
Option Explicit
' This object holds a reference to the queue
Private m_objQueue As MSMQ.MSMQQueue
' This object notifies us when a message has arrived
Private WithEvents m_objEvent As MSMQ.MSMQEvent
Private Sub Form_Load()
Dim objQI As MSMQ.MSMQQueueInfo
' Open the queue for receive access
Set objQI = New MSMQ.MSMQQueueInfo
objQI.PathName = ".\aspexe"
Set m_objQueue = objQI.Open(MQ_RECEIVE_ACCESS, MQ_DENY_NONE)
' Create a new event object
Set m_objEvent = New MSMQ.MSMQEvent
' Tell the queue to use our event object to notify us of
' new messages
m_objQueue.EnableNotification m_objEvent
' Tidy up
Set objQI = Nothing
End Sub
Private Sub m_objEvent_Arrived(ByVal Queue As Object, _
ByVal Cursor As Long)
Dim objQ As MSMQ.MSMQQueue
Dim objMSG As MSMQ.MSMQMessage
Dim objRS As ADODB.Recordset
' This event is fired when a new message is in the queue
' This time we are passed a reference to the queue as a
' parameter of the event
Set objQ = Queue
' Get the current message from the queue
' Note that we are specifying a timeout on the
' ReceiveCurrent call (1000 milliseconds which is
' 1 second) as there is no guarantee that the
' message is still there. For example it might
' have already been read by another process
Set objMSG = objQ.ReceiveCurrent(ReceiveTimeout:=1000)
If Not objMSG Is Nothing Then
' There was a message in the queue so let's process it
' Get the recordset from the message
Set objRS = objMSG.Body
' Tidy up
Set objMSG = Nothing
' Process the recordset
While Not objRS.EOF
Select Case UCase(objRS("Action"))
Case "EXE"
On Error Resume Next
Shell objRS("Data")
On Error GoTo 0
End Select
objRS.MoveNext
Wend
objRS.Close
Set objRS = Nothing
End If
' Once we have finished we need to re-create our
' event notification
Set m_objEvent = Nothing
Set m_objEvent = New MSMQ.MSMQEvent
m_objQueue.EnableNotification m_objEvent
End Sub
|
Execute the code and navigate to the ASP page and it will send a message and notepad should execute.
What happens if you use the ASP page to send messages without the EXE running? The message will sit in the queue and when the application starts up and attaches itself to the queue it will receive an Arrived event if there is a message already in the queue. When it is done processing and re-attaches itself if will get another event if there is another message waiting. This happens until the queue is empty at which point the application will sit and wait until a new message is generated.
Next we'll beef up our ASP code. Here is a new version of the ASP code. Save it as AdvancedSend1.asp
<%@ Language=VBScript %>>
<HTML>
<HEAD>
<META NAME="GENERATOR" Content="Microsoft Visual Studio 6.0">
<title>Advanced aspexe send - version 1</title>
</HEAD>
<BODY>
<!-- File: AdvancedSend1.asp -->
<!-- Author: Adrian Forbes -->
<form action="AdvancedSend1.asp" method=POST>
<p>You can specify up to three EXEs</p>
<p>
<table border=0>
<input type=hidden name=intEXECount value="3">
<tr>
<td>1</td><td>EXE:</td><td><input type=text name=txtEXE1 value="">
</tr>
<tr>
<td>2</td><td>EXE:</td><td><input type=text name=txtEXE2 value="">
</tr>
<tr>
<td>3</td><td>EXE:</td><td><input type=text name=txtEXE3 value="">
</tr>
</table>
<p><input type=submit value="Submit">
</form>
<%
' To save on wasted ASP execution time let's first check
' that the form has been submitted. If it has then
' intEXECount will contain a value
if Request.Form("intEXECount") <> "" then
' Define the constants we are going to use
MQ_SEND_ACCESS = 2
MQ_DENY_NONE = 0
MQ_NO_TRANSACTION = 0
adVarChar = 200
' Create an instance of MSMQQueueInfo. This will let us
' manipulate queues
Set objQI = CreateObject("MSMQ.MSMQQueueInfo")
' Specify the name of the queue we want to open
objQI.PathName = ".\aspexe"
' Call the Open method which returns an instance
' of MSMQQueue which represents the actual queue
Set objQ = objQI.Open(MQ_SEND_ACCESS, MQ_DENY_NONE)
' Create a new message
Set objMSG = CreateObject("MSMQ.MSMQMessage")
' Create a recordset to store our data
Set objRS = CreateObject("ADODB.Recordset")
' Add our fields
objRS.Fields.Append "Action", adVarChar, 20
objRS.Fields.Append "Data", adVarChar, 255
' Open the recordset and add our data
objRS.Open
' Loop for each EXE field
for i = 1 to CInt(Request.Form("intEXECount"))
' Get the contents of txtEXE<i>
sEXE = Request.Form("txtEXE" & i)
if sEXE <> "" then
' It contains a value so add it to the
' recordset
objRS.AddNew
objRS("Action") = "EXE"
objRS("Data") = sEXE
Response.Write "<p>" & sEXE
& " has been scheduled for execution</p>"
end if
next
objMSG.Label = "aspexe"
objMSG.Body = objRS
' Send the message to the queue specifying
' no transactions
objMSG.Send objQ, MQ_NO_TRANSACTION
' Tidy up
objRS.Close
Set objRS = Nothing
objQ.Close
Set objMSG = Nothing
Set objQ = Nothing
Set objQI = Nothing
end if
%>
</BODY>
</HTML>
|
Make sure the EXE you've just compiled is running and navigate to the page above. Enter up to three EXEs to execute such as notepad.exe, calc.exe etc and press Submit. Note that if it isn't on a defined PATH then you'll need to include the path to the exe also such as c:\myapps\myapp.exe.
Let's extend the message format to include an extra action to handle SQL queries. We'll call the action "SQL", the data will be the SQL to execute (either direct SQL or a stored procedure) and we'll add an extra field to contain the connection string. Here is the new FORM we will be using so copy it over the old one.
<form action="AdvancedSend1.asp" method=POST>
<p>You can specify up to three EXEs and 1 SQL statement</p>
<p>
<table border=0>
<input type=hidden name=intEXECount value="3">
<input type=hidden name=intSQLCount value="1">
<tr>
<td>1</td><td>EXE:</td><td><input type=text name=txtEXE1 value="">
</tr>
<tr>
<td>2</td><td>EXE:</td><td><input type=text name=txtEXE2 value="">
</tr>
<tr>
<td>3</td><td>EXE:</td><td><input type=text name=txtEXE3 value="">
</tr>
<tr>
<td>1</td><td>SQL:</td><td><input type=text name=txtSQL1 value="">
</tr>
<tr>
<td> </td><td>Connect:</td>
<td><input type=text name=txtConnect1 size=30 value="">
</tr>
</table>
<p><input type=submit value="Submit">
</form>
|
As you can see, we have added an extra field for the SQL statement and one for the connection string. Now add a second loop to the processing code;
' Add our fields
objRS.Fields.Append "Action", adVarChar, 20
objRS.Fields.Append "Data", adVarChar, 255
objRS.Fields.Append "Connect", adVarChar, 255
' Open the recordset and add our data
objRS.Open
' Loop for each EXE field
for i = 1 to CInt(Request.Form("intEXECount"))
' Get the contents of txtEXE<i>
sEXE = Request.Form("txtEXE" & i)
if sEXE <> "" then
' It contains a value so add it to the
' recordset
objRS.AddNew
objRS("Action") = "EXE"
objRS("Data") = sEXE
Response.Write "<p>" & sEXE & " has been scheduled for execution</p>"
end if
next
' Loop for each SQL field
for i = 1 to CInt(Request.Form("intSQLCount"))
' Get the contents of txtSQL<i>
sSQL = Request.Form("txtSQL" & i)
' Get the contents of txtConnect<i>
sConnect = Request.Form("txtConnect" & i)
if sSQL <> "" and sConnect <> "" then
' Both contain a value so add it to the
' recordset
objRS.AddNew
objRS("Action") = "SQL"
objRS("Data") = sSQL
objRS("Connect") = sConnect
Response.Write "<p><i>" & sSQL &
"</i> has been scheduled for execution</p>"
end if
next
|
Note that we are adding a new field to store the connection data, and the second loop is near identical to the first loop only it is looking for data in the txtSQL and txtConnect fields.
Now we have to modify our VB Application to handle this new action. Add a new declaration in the m_objEvent_Arrived sub.
| Dim objCon As ADODB.Connection |
And update the Select Case statement
Select Case UCase(objRS("Action"))
Case "EXE"
On Error Resume Next
Shell objRS("Data")
On Error GoTo 0
Case "SQL"
On Error Resume Next
Set objCon = New ADODB.Connection
objCon.ConnectionString = objRS("Connect")
objCon.Open
objCon.Execute objRS("Data")
objCon.Close
Set objCon = Nothing
On Error GoTo 0
End Select
|
Now we're good to go. Recompile and re-run the EXE and try out the new ASP page. Enter a SQL statement and a connection string into the relevant boxes, and also some EXEs to run if you wish. Press Submit and it should all happen.
Let's start creating some Word documents now. We will provide the option to save the results of the SQL command as a Word document with the filename of our choosing. As this is the final example I'll list all of the code in full.
AdvancedSend1.asp
<%@ Language=VBScript %>
<HTML>
<HEAD>
<META NAME="GENERATOR" Content="Microsoft Visual Studio 6.0">
<title>Advanced aspexe send - version 1</title>
</HEAD>
<BODY>
<!-- File: AdvancedSend1.asp -->
<!-- Author: Adrian Forbes -->
<form action="AdvancedSend1.asp" method=POST>
<p>You can specify up to three EXEs and 1 SQL statement. To save the
contents of the SQL command, enter a filename in the Save As box
to create a Word document of that type.</p>
<p>
<table border=0>
<input type=hidden name=intEXECount value="3">
<input type=hidden name=intSQLCount value="1">
<input type=hidden name=intWordCount value="1">
<tr>
<td>1</td><td>EXE:</td><td><input type=text name=txtEXE1 value="">
</tr>
<tr>
<td>2</td><td>EXE:</td><td><input type=text name=txtEXE2 value="">
</tr>
<tr>
<td>3</td><td>EXE:</td><td><input type=text name=txtEXE3 value="">
</tr>
<tr>
<td>1</td><td>SQL:</td><td><input type=text name=txtSQL1 value="">
</tr>
<tr>
<td> </td><td>Connect:</td>
<td><input type=text name=txtConnect1 size=30 value="">
</tr>
<tr>
<td> </td><td>Save As:</td>
<td><input type=text name=txtSaveAs1 size=30 value="">
</tr>
</table>
<p><input type=submit value="Submit">
</form>
<%
' To save on wasted ASP execution time let's first check
' that the form has been submitted. If it has then
' intEXECount will contain a value
if Request.Form("intEXECount") <> "" then
' Define the constants we are going to use
MQ_SEND_ACCESS = 2
MQ_DENY_NONE = 0
MQ_NO_TRANSACTION = 0
adVarChar = 200
' Create an instance of MSMQQueueInfo. This will let us
' manipulate queues
Set objQI = CreateObject("MSMQ.MSMQQueueInfo")
' Specify the name of the queue we want to open
objQI.PathName = ".\aspexe"
' Call the Open method which returns an instance
' of MSMQQueue which represents the actual queue
Set objQ = objQI.Open(MQ_SEND_ACCESS, MQ_DENY_NONE)
' Create a new message
Set objMSG = CreateObject("MSMQ.MSMQMessage")
' Create a recordset to store our data
Set objRS = CreateObject("ADODB.Recordset")
' Add our fields
objRS.Fields.Append "Action", adVarChar, 20
objRS.Fields.Append "Data", adVarChar, 255
objRS.Fields.Append "Connect", adVarChar, 255
objRS.Fields.Append "SaveAs", adVarChar, 255
' Open the recordset and add our data
objRS.Open
' Loop for each EXE field
for i = 1 to CInt(Request.Form("intEXECount"))
' Get the contents of txtEXE<i>
sEXE = Request.Form("txtEXE" & i)
if sEXE <> "" then
' It contains a value so add it to the
' recordset
objRS.AddNew
objRS("Action") = "EXE"
objRS("Data") = sEXE
Response.Write "<p>" & sEXE & " has been scheduled for execution</p>"
end if
next
' Loop for each SQL field
for i = 1 to CInt(Request.Form("intSQLCount"))
' Get the contents of txtSQL<i>
sSQL = Request.Form("txtSQL" & i)
' Get the contents of txtConnect<i>
sConnect = Request.Form("txtConnect" & i)
' Get the contents of txtSaveAs<i>
sSaveAs = Request.Form("txtSaveAs" & i)
if sSQL <> "" and sConnect <> "" then
' Both contain a value so add it to the
' recordset
objRS.AddNew
objRS("Action") = "SQL"
objRS("Data") = sSQL
objRS("Connect") = sConnect
objRS("SaveAs") = sSaveAs
Response.Write "<p><i>" & sSQL & "</i> has been scheduled for execution"
if sSaveAs <> "" then
Response.Write " and will be saved as <i>" & sSaveAs & "</i>"
end if
Response.Write "</p>"
end if
next
objMSG.Label = "aspexe"
objMSG.Body = objRS
' Send the message to the queue specifying
' no transactions
objMSG.Send objQ, MQ_NO_TRANSACTION
' Tidy up
objRS.Close
Set objRS = Nothing
objQ.Close
Set objMSG = Nothing
Set objQ = Nothing
Set objQI = Nothing
end if
%>
</BODY>
</HTML>
|
The VB Application
We also need to add a reference to Microsoft Word to run this code.
Option Explicit
' This object holds a reference to the queue
Private m_objQueue As MSMQ.MSMQQueue
' This object notifies us when a message has arrived
Private WithEvents m_objEvent As MSMQ.MSMQEvent
Private Sub Form_Load()
Dim objQI As MSMQ.MSMQQueueInfo
' Open the queue for receive access
Set objQI = New MSMQ.MSMQQueueInfo
objQI.PathName = ".\aspexe"
Set m_objQueue = objQI.Open(MQ_RECEIVE_ACCESS, MQ_DENY_NONE)
' Create a new event object
Set m_objEvent = New MSMQ.MSMQEvent
' Tell the queue to use our event object to notify us of
' new messages
m_objQueue.EnableNotification m_objEvent
' Tidy up
Set objQI = Nothing
End Sub
Private Sub m_objEvent_Arrived(ByVal Queue As Object, _
ByVal Cursor As Long)
Dim objQ As MSMQ.MSMQQueue
Dim objMSG As MSMQ.MSMQMessage
Dim objRS As ADODB.Recordset
Dim objCon As ADODB.Connection
Dim objRSResults As ADODB.Recordset
Dim sSaveAs As String
Dim objWord As Word.Application
Dim objDoc As Word.Document
Dim objField As ADODB.Field
' This event is fired when a new message is in the queue
' This time we are passed a reference to the queue as a
' parameter of the event
Set objQ = Queue
' Get the current message from the queue
' Note that we are specifying a timeout on the
' ReceiveCurrent call (1000 milliseconds which is
' 1 second) as there is no guarantee that the
' message is still there. For example it might
' have already been read by another process
Set objMSG = objQ.ReceiveCurrent(ReceiveTimeout:=1000)
If Not objMSG Is Nothing Then
' There was a message in the queue so let's process it
' Get the recordset from the message
Set objRS = objMSG.Body
' Tidy up
Set objMSG = Nothing
' Process the recordset
While Not objRS.EOF
Select Case UCase(objRS("Action"))
Case "EXE"
On Error Resume Next
Shell objRS("Data")
On Error GoTo 0
Case "SQL"
On Error Resume Next
Set objCon = New ADODB.Connection
objCon.ConnectionString = objRS("Connect")
objCon.Open
Set objRSResults = objCon.Execute(objRS("Data"))
sSaveAs = objRS("SaveAs")
If sSaveAs <> "" And objRSResults.Fields.Count > 0 Then
Set objWord = CreateObject("Word.Application")
Set objDoc = objWord.Documents.Add
objDoc.Tables.Add Range:=objDoc.ActiveWindow.Selection.Range, _
NumRows:=1, _
NumColumns:=objRSResults.Fields.Count, _
DefaultTableBehavior:=wdWord9TableBehavior, _
AutoFitBehavior:=wdAutoFitFixed
With objDoc.ActiveWindow.Selection
For Each objField In objRSResults.Fields
.TypeText objField.Name
.MoveRight Unit:=wdCell
Next objField
While Not objRSResults.EOF
For Each objField In objRSResults.Fields
.TypeText objField.Value
.MoveRight Unit:=wdCell
Next objField
objRSResults.MoveNext
Wend
End With
objDoc.SaveAs sSaveAs
Set objDoc = Nothing
objWord.Quit
Set objWord = Nothing
End If
objCon.Close
Set objCon = Nothing
On Error GoTo 0
End Select
objRS.MoveNext
Wend
objRS.Close
Set objRS = Nothing
End If
' Once we have finished we need to re-create our
' event notification
Set m_objEvent = Nothing
Set m_objEvent = New MSMQ.MSMQEvent
m_objQueue.EnableNotification m_objEvent
End Sub
|
Now when we use the ASP page to execute SQL that returns some results we can enter a filename into the Save As field (for example c:\myresults.doc) and the results will be saved to that file as a table in a Microsoft Word document.
Issues
There are a few known issues with the code, mainly to do with error handling. If you enter an invalid connection string then the VB app will simply keep looping and hang. The code is provided as a basic example and you'd have to implement more robust error handling and processing if you were to use this technique in the real world.
Queued Components
If you are not that keen on using the above architecture then your requirements might be met by simply calling your COM objects asynchronously. This has the same general benefits as our above architecture expect there is no serialisation of the requests. If five users all request the same page then the request is executed five times concurrently. There are advantages over our previous architecture though, namely that it is much simpler to set up and you don't need someone logged in to the machine; as we will be using configured components they will run inside a COM+ Application so can be set to impersonate a user. On the other hand it still has the disadvantage of there being no return path for your calls.
We will only implement a very simple SQL command execution object and we will run it asynchronously from an ASP page. As you'll soon see we will need to write very little code to achieve this.
Create a new ActiveX DLL project in VB. Change the name of the project from Project1 to ASPEXE and the class from Class1 to SQL. Add a reference to ADO (Microsoft ActiveX Data Objects) and to Microsoft Message Queue 2.0 Object Library. Inside the SQL class add this sub.
Public Sub ExecuteSQL(ByVal ConnectString As String, ByVal SQL As String)
Dim objCon As ADODB.Connection
Set objCon = New ADODB.Connection
objCon.ConnectionString = ConnectString
objCon.Open
objCon.Execute SQL
objCon.Close
Set objCon = Nothing
End Sub
|
Now compile the DLL as ASPEXE.DLL and start Component Services.
- Expand Component Services
- Expand Computers
- Expand My Computer
- Right click on COM+ Applications and select New->Application from the context menu
- Click Next
- Click Create an empty application
- Enter ASPEXESQL as the name and click Next
- Leave it set to Interactive user (as we're just testing right now) and click Next
- Click Finish
- Expand COM+ Applications
- Expand ASPEXESQL
- Right click on Components and select New->Component from the context menu
- Click Next
- Click Install new component(s)
- Click Add and use the browser to select your ASPEXE.DLL and press Open
- Click Next
- Click Finish
- Expand ASPEXESQL
- Expand Components
- Expand ASPEXE.SQL
- Expand Interfaces
- Expand _SQL
- Expand Methods
At this point your should have something that looks like this;
Our object is now in a COM+ Application but can only be called like a regular configured component at the moment. We need to take a few more steps before we are through.
- Right click on the ASPEXESQL application and select Properties from the context menu
- Select the Queuing tab
- Check the Queued - This application can be reached by MSMQ queues box
- Check the Listen - This application, when activated, will process messages that arrive on its MSMQ queue box.
- Select the Advanced tab
- Select the Leave running when idle radio button
- Click OK
- Inside the Interfaces branch, right click on _SQL and select Properties from the context menu
- Select the Queuing tab
- Check the Queued box
- Click OK
- Right click on the ASPEXESQL application and select Start from the context menu
Next we'll do the ASP code to call this object asynchronously. Once we've seen it up and running we'll look into what is going on under the covers.
The ASP code is very simple. Let's say I want to alter the data in the Pubs database on the local machine, I would do this;
<%@ Language=VBScript %>
<html>
<body>
<%
sConnect = "Provider=SQLOLEDB;Server=localhost;database=pubs;uid=sa;pwd=;"
sSQL = "update authors set au_lname='Me'"
Set objSQL = GetObject("queue:/new:ASPEXE.SQL")
objSQL.ExecuteSQL sConnect, sSQL
Set objSQL = Nothing
%>
</body>
</html>
|
Nothing spectacular or useful but it demonstrates the point. Navigate to this page then look at the authors table and all of the au_lname fields will be set to Me.
So how does this all work? Start the Computer Management application and expand Services and Applications then expand Message Queuing. Look inside the Public Queues folder and you'll see that a new queue has been created for you called aspexesql (ie the name of our COM+ Application). When you use GetObject to get a new instance of your queued component, the method call and arguments you make are marshalled into an MSMQ message and placed in the aspexesql queue. As we have started our COM+ Application and set it to always run and to listen to its queue, it grabs that message, unravels it and calls the appropriate method with the appropriate parameters. Basically it is doing a lot of what our previous application does only with very much less code.
If you want to see this in action then Shutdown the ASPEXESQL COM+ Application and run the ASP code again. Look in the aspexesql queue and you will see 1 message sitting there. Start your COM+ Application again and then refresh the queue and the message has gone.
You can e-mail me at adrian@aspfree.com however I cannot guarantee a response
| DISCLAIMER: The content provided in this article is not warranted or guaranteed by Developer Shed, Inc. The content provided is intended for entertainment and/or educational purposes in order to introduce to the reader key ideas, concepts, and/or product reviews. As such it is incumbent upon the reader to employ real-world tactics for security and implementation of best practices. We are not liable for any negative consequences that may result from implementing any information covered in our articles or tutorials. If this is a hardware review, it is not recommended to open and/or modify your hardware. |
More ASP Code Articles
More By aspfree
 developerWorks - FREE Tools!
|
Hold your calendar on January 30, 2008 for this free webcast on the new i5/OS. Rational's Enterprise Modernization products will be discussed at this webcast as they help to drive the application development environment for this new System i OS. <br />And learn how i5/OS will take you to the next step of efficient, resilient business processing. You will hear about the new i5/OS capabilities as it will be the most significant i5/OS release in years. If you cannot join the webcast on 1/30/08 you can still use this link to listen to the replay.<br />
FREE! Go There Now!
|
|
|
|
Join this webcast, to learn how the Rational Process Library can help with compliance issues, drive process improvement, and assist in service-oriented architecture (SOA) or Agile development. We will take a peek into the Rational Process Library with content around software and systems engineering (including RUP), operations and systems management, program and portfolio management, and asset and SOA governance.
FREE! Go There Now!
|
|
|
|
Join us for this web seminar to learn how you can defend your web applications from attack. Learn about the 3 most common web application attacks, including how they occur and what can be done to prevent them. We’ll also discuss manual versus automated approaches for scanning and identifying web application vulnerabilities and how IBM Rational AppScan, an automated vulnerability scanner, can help you automate more of what you are doing manually today.
FREE! Go There Now!
|
|
|
|
Secure your Web applications with IBM Rational AppScan Standard Edition V7.7, previously known as Watchfire AppScan. This Web application security testing tool automates vulnerability assessments and scans and tests for common Web application vulnerabilities. Visit IBM developerWorks to download a free trial of IBM Rational AppScan Standard Edition V7.7.
FREE! Go There Now!
|
|
|
|
Ken Krugler, co-founder of code search company Krugle, and Laura Merling, vice president of Marketing and Business Development for Krugle, join to talk about the ins and outs of code search and what it means as a new feature for developerWorks users.
FREE! Go There Now!
|
|
|
|
Because access to government information continues to be an area of concern for many U.S. citizens with disabilities, the U.S. government enacted Section 508 of the Rehabilitation Act in 2001 to ensure that government agencies create accessible Web content, enabling all citizens to access the information they need. A fully accessible Web site makes Web content accessible to all individuals, including those with disabilities, who may be accessing Web content via a variety of user agents. Common user agents include standard Web browsers, text-only browsers, assistive devices and mobile devices such as cell phones or personal digital assistants (PDAs).
FREE! Go There Now!
|
|
|
|
Visit IBM developerWorks to try the IBM SOA Sandbox for connectivity. The SOA Sandbox for connectivity provides a trial environment with the tooling and components to help you explore how to effectively connect your infrastructure and integrate all of the people, processes and information in your company. Use the hosted sandbox to explore SOA techniques that streamline connecting existing IT assets together, as well as learn how to connect them to new business logic.
FREE! Go There Now!
|
|
|
|
The Eclipse community is constantly working to extend Eclipse's functionality. In this webcast, learn about some of the most important and feature-rich projects under development. From multi-language support to plug-in development, tune in to see what Eclipse is capable of now.
FREE! Go There Now!
|
|
|
|
The discipline of assembling and delivering software is maturing beyond standard developer-centric compile/test software builds. The end-to-end software development lifecycle is emerging as the new focus moves “Beyond the Build.” Join this on demand webcast to learn about methods for streamlining software delivery and key capabilities of the IBM Rational Build Forge framework for automating build and release management in environments of any size.
FREE! Go There Now!
|
|
|
|
The unprecedented scope of a service-oriented architecture (SOA) initiative brings to the forefront a number of management and governance issues that were sidestepped in the past. The key to a successful SOA implementation is managing and governing activities throughout the entire SOA delivery lifecycle by ensuring that services conform to the needs of all of the business’s stakeholders. Learn how service lifecycle management allows the business to ensure that the process by which services are defined, created, tested, deployed, optimized and retired is manageable, repeatable and auditable.
FREE! Go There Now!
|
|
|
|
All FREE IBM® developerWorks Tools!
|
