Sample Host and Remote Agents

Visual Basic Host Agent

The actual implementation of a simple host agent in Visual Basic is shown next. The host agent application looks like the simple form below.

Each of the five steps requires only a few lines of code.

The host periodically checks for new request.
We can set the responsiveness of the system by changing the timer interval. We permit only one query to be active at a time by using a static variable.

Sub Timer1_Timer ()
Static Active%
If Active% Then Exit Sub
Active% = True
ProcessQueue
Active% = False
End Sub

The host searches for any pending files.
We check for any files in the incoming directory.

Sub ProcessQueue ()
'Only the RAS check is done
'MAPI could drop the files in the same location
'to serve as a signal
If cbRASAgent.Value <> 0 Then 'Check RAS
A$ = Dir$(Incoming$ + "\*.QRY")
While Len(A$) > 0
ProcessQuery Incoming$ + "\" + A$
A$ = Dir$
Wend
End If
End Sub

The host uses the data in the request to execute query.
If a file is found, we start to process it. The file must be in the appropriate format; in this case, the first character on a line must be correct and the keywords in the SQL query must be in capitals.

Sub ProcessQuery (ByVal Fname$)
Const DB_LANG_GENERAL = ";LANGID=0x0809;CP=1252;COUNTRY=0"
'On Error Resume Next
FilePath$ = Incoming$ + "\" + Fname$
If Dir$(FilePath$) = "" Or Fname = "" Then Exit Sub
fno = FreeFile
Open FilePath$ For Input As #fno
While Not EOF(fno)
Line Input #fno, A$
If Left$(A$, 1) = "<" Then Outfile$ = Mid$(A$, 2)
If Left$(A$, 7) = ">SELECT" Then Query = Mid$(A$, 2)
If Left$(A$, 1) = "=" Then TableName$ = Mid$(A$, 2)
If Left$(A$, 1) = "@" Then ZipName$ = Mid$(A$, 2)
If Left$(A$, 1) = "!" Then GoSub ExecuteQuery
Wend
Close fno
We need three parameters: the file to put it in, the table name, and the query. We can add other parameters. In this example, we allow the files to be compressed. We add a new command or option to execute the existing values whenever a '!' is found. The use of an MDB allows us to store many result sets in one MDB.

The host saves result sets.
If result sets are returned, they must be placed into the specified table. This means modifying the query somewhere. It may be done at any agent or in the application, but it must be at one place only.

ExecuteQuery:
'Validate parameters
FromAt% = InStr(Query, " FROM ")
OutPath$ = Outgoing$ + "\" + Outfile$
If FromAt% = 0 Then
Query = "" 'Invalid Format
Else
Query = Left$(Query, FromAt%) + " Into [" + TableName$ + "] In '" + OutPath$ + "' " + Mid$(Query, FromAt%)
End If
If Len(Outfile$) And Len(Query) And Len(TableName$) Then
If Dir$(OutPath$) = "" Then
Set OutDB = CreateDatabase(OutPath$, DB_LANG_GENERAL)
OutDB.Close
End If
On Error Resume Next
DB.Execute Query
On Error GoTo 0
End If
Return
This example executes queries against the HOST.MDB only. Any ODBC source could be accessed by adding more commands or options in the query request.

The host compresses the file if requested.
The following section shows how we can compress an MDB with a common utility. A 65,536-byte database may be reduced to only 5,888 bytes, resulting in significantly shorter transmission times.

If Len(ZipName$) > 0 Then
fbatch = FreeFile
Batchfile$ = Outgoing$ + "\ZIPMDB.BAT"
ZipName$ = Outgoing$ + "\" + ZipName$
Open Batchfile$ For Output As #fbatch
Print #fbatch, "PKZIP -m " + ZipName$ + " " + OutPath$
Print #fbatch, "DEL " + Batchfile$
Close fbatch
If Shell(Batchfile$, 3) Then
While Dir$(Batchfile$) <> ""
DoEvents
Wend
End If
End If

The host deletes request file.
The signal that the result set is ready to be picked up is the removal of the query file.

Kill FName$ 'Remove file to signal completion

Visual Basic Remote Agent

The actual implementation of a simple remote agent in Visual Basic is shown next.

Each step requires only a few lines of code.

The remote agent sends the request.

The application must use a scheme to prevent collision. A counter is an effective one.

QryNo% = QryNo% + 1
fno = FreeFile
RQST$ = RASQuery$ + "\KEN" + Format(QryNo%, "0") + ".QRY"
Open RQST$ For Output As #fno
MDBName$ = "KEN" + Format(QryNo%, "0") + ".MDB"
TableName$ = "Table " + Format(QryNo%, "0") + ".QRY"
fno = FreeFile
RQST$ = RASQuery$ + "\KEN" + Format(QryNo%, "0") + ".QRY"
Open RQST$ For Output As #fno
Print #fno, "<" + MDBName.Text
Print #fno, "=" + TableName.Text
Print #fno, ">" + Query.Text
If ZipBMD <> 0 Then
Print #fno, "@" + "ZIP" + Format$(QryNo%, "0")
End If
Close #fno
After the query is sent, we must start a receiver. In our sample, we create a form for each set of data returned.

ReDim Returns(QryNo%) As New frmReturns
SetInformation Returns(QryNo%), RQST$, MDBName.Text, TableName
Returns(QryNo%).Visible = True

The remote agent waits until the request file is deleted.

The timer permits periodic checks.

Sub Timer1_Timer ()
If Dir(Tag) = "" Then
CopyFile ResultsFile$, MDBFILE$
SetResults MDBFILE$
Timer1.Enabled = False
caption = "Results Back"
End If
End Sub

The remote agents copies the database (or zipped file) and deletes it from the host.

Sub CopyFile ()
BatchFile$ = App.Path + "\" + Left$(MDBName, InStr(MDBName, ".")) + "Bat"
fno = FreeFile
Open BatchFile$ For Output As #fno
If Len(ZipName) > 0 Then
Print #fno, "COPY " + RASResults$ + ZipName + " " + App.Path + "\" + ZipName
Else
Print #fno, "COPY " + RASResults$ + MDBName + " " + App.Path + "\" + MDBName
End If
If Len(ZipName) > 0 Then
Print #fno, "CHDIR " + App.Path
Print #fno, "PKUNZIP " + App.Path + "\" + ZipName
Print #fno, "DEL " + RASResults$ + ZipName
Else
Print #fno, "DEL " + RASResults$ + MDBName
End If
Print #fno, "DEL " + BatchFile$
Close #fno
If Shell(BatchFile$, 6) Then
While Dir(BatchFile$) <> ""
DoEvents
Wend
End If
End Sub

The remote agent assigns results.

The assignment of results is always application-specific. A good schema is to have one MDB match each form. In our sample application, we implemented a generic form.