Image of Navigational Map linked to Home / Contents / Search Passing Recordsets via HTTP

by Jim Karabatsos - GUI Computing
Image of Line Break

It started as a fairly normal sort of a day at GUI Central. The usual slow crawl through the traffic was followed by a quick stop at the coffee shop for a latte to drink while scanning the hundred or so messages that accumulate in the Inbox overnight. While reading the latest antics of one of our clients (you really would be surprised at what some people will get up to when they have entirely too much free time on their hands), Mark stuck his head through the door and asked if it was a good time to go over the system architecture for a new system.

The system was of a type that is becoming ever more common for us, essentially a web-based front end to an existing legacy system. For this particular system, security was an important issue, as was the need to ensure that data was not lost due to communication or system problems. None of these requirements are particularly unusual and we went over for the umpteenth time the standard set of precautions we would take.

We had previously decided on e-mail as a secondary transportation mechanism, allowing the front-end of the application to send a log of changes through e-mail. This e-mail would be read by a software agent inside the corporate firewall and compared to the data that was sent in batches via a physical file copy. The software agent could report any discrepancies and this provided a great deal of confidence that a transaction that was reported as complete to the web user would not be lost.

Gary Wisniewski had suggested that we ditch e-mail and instead write our own server process running inside the firewall that just listened on a socket. This would allow us to confirm the success of the actual logging process before reporting success to the user and opened up some additional facilities that we could certainly make good use of. We were exploring (indeed, we had already started to code the generic infrastructure of) this server. Obviously, we would need to handle multiple concurrent requests for action and data. We therefore needed to develop a flexible, multi-threaded, secure socket port server for our NT Server machine. As we went over the requirements and the initial design, I caught myself saying, "you know, this is just like IIS". By about the fifth time I said that, I said out load to Mark, "Why don't we just scrap the server and use another copy of IIS inside the firewall?"

Mark started to say, "that'll never work" but caught himself mid-sentence and looked at me kind of blankly. I could see the gears turning as a smile came to his face. "You know, that just might work…" he said.

And work it does indeed. It is quite straightforward to talk to an HTTP server from a VB application, but we decided to go one step further. One of the cool new features of DCOM is the ability to access a DCOM server over HTTP. I know it sounds bizarre, but you can basically mime-encode anything. After all, if you can send a ZIP file over HTTP, why not a DCOM object? Add to this the ability to pass ADO recordsets across HTTP and their use in a disconnected state, and you have a very powerful tool-set for use in creating truly distributed web applications.

Let's look at what you need to do to create an object over HTTP. Firstly, DON'T PANIC! Internet users can NOT arbitrarily instantiate a COM object on your IIS web server and use it. That would be rather scary, so it is not possible. If you want to make a COM object creatable via HTTP, then you need to tell IIS that it is OK to do so. You do this by inserting a key in the registry of your IIS machine.

In the registry hive:


You will see listed a few keys (note - I don't mean values, I mean KEYS, the folder thingies you see in RegEdit). These keys are the server names that IIS is allowed to create when asked to by an HTTP client request. These are just the strings you would use if you were to call CreateObject in your code, as in "Excel.Application". What you do is write your server as an in-process ActiveX server - a DLL. You then register that component on your web server and create the key in the ADCLaunch hive shown above and that is just about all there is to it.

To demonstrate, I wrote a server called jkTestBiblio that simply returns a recordset from the Authors table in the Biblio MDB. As part of something else I was testing, I actually used DAO to access the MDB, then copied the data to an ADO recordset and returned that to the caller. Don't ask - just trust me, there was a good reason. I decided to leave it in because it demonstrates another cool technique in ADO2, namely the creatable recordset. Here is the code for the server - pretty simple, I am sure you agree:

Option Explicit

Const gsDBPATH = "D:\Program Files\DevStudio\VB\Biblio.mdb"

Public Function GetRS(ByVal Where As String) As Object

   Dim daodb As DAO.Database
   Dim daors As DAO.Recordset
   Dim adors As ADODB.Recordset
   Dim SQL As String
   SQL = "SELECT Au_ID, Author, [Year Born] FROM Authors"
   If Len(Trim$(Where)) > 0 Then SQL = SQL & " WHERE " & Where
   SQL = SQL & ";"
   Set daodb = DAO.OpenDatabase(gsDBPATH)
   Set daors = daodb.OpenRecordset(SQL, dbOpenSnapshot)
   Set adors = New ADODB.Recordset  ''' REF1
   adors.Fields.Append "Au_ID", adBigInt
   adors.Fields.Append "Author", adBSTR
   adors.Fields.Append "Year Born", adInteger
   Dim vColNames(2)
   Dim vColValues(2)
   vColNames(0) = "Au_ID"
   vColNames(1) = "Author"
   vColNames(2) = "Year Born"
   'vColNames = Array("Au_ID", "Author", "Year Born")
   'vColValues = Array(0, "", 0)
   Do While Not daors.EOF
      vColValues(0) = daors.Fields(0)
      vColValues(1) = daors.Fields(1)
      If IsNull(daors.Fields(2)) Then
         vColValues(2) = 0
         vColValues(2) = daors.Fields(2)
      End If
      adors.AddNew vColNames, vColValues
   If adors.RecordCount > 0 Then
   End If
   Set GetRS = adors
   Set adors = Nothing
   Set daors = Nothing
   Set daodb = Nothing

End Function

The function GetRS takes a single string parameter, an SQL WHERE clause minus the keyword "WHERE" which may be blank. Following through, it simply opens a DAO connection to the database and opens the required DAO recordset to get at the data.

When that is done, it creates an ADO recordset that will become the return value of the function. If you look at the line commented REF1, you will see something that might amaze you if you have not seen creatable recordsets before. Notice how the adors recordset is being created out of thin air using the "New" keyword rather than through a hierarchy as is necessary in DAO. We then proceed to add the three appropriate Fields to the recordset before opening it.

Adding records to the recordset is done using another new facility in ADO, the extended AddNew method where you pass two arrays, an array of field names and an array of field values. This is quicker than assigning each field's value separately. As you can see, we just loop through the DAO recordset, adding each row to the ADO recordset.

When we come to the end of the DAO recordset, we basically reposition the ADO recordset to the first row (if there is one) and assign a reference to it as the return value of the method. Note that the ADO recordset is NOT closed before being returned. After we assign a reference to it as the return value of the function, we have two pointers referencing the SAME INSTANCE of the recordset. If we close one, the other one is also closed. Instead, we simply destroy our original reference to the recordset (by setting adors = Nothing) and return. Of course, the DAO recordset is closed and disposed of in the normal way.

Just one more thing to note. I found that I had difficulties if I wrote the above as a property instead of a function or if I tried to return a specific object type. Using a function of type As Object seems to work just fine.

The class shown above was called Authors, so I created a key

on my system and restarted IIS.

That's all there was to the server side. Let's look at the client. All I did was place a text box, a list box and a button on the form. I added a reference to the ADODB and RDS type libraries to my project and wrote this simple code:

Private Sub Command1_Click()

  Dim jktest As Object
  Dim adors  As ADODB.Recordset
  Dim ADS    As New RDS.DataSpace

  ''' Set jktest = New jkTestBiblio.Authors 'used in local testing
  Set jktest = ADS.CreateObject("jkTestBiblio.Authors", _
  Set adors = jktest.GetRS(CStr(Text1.Text))
  Do While Not adors.EOF
     List1.AddItem adors.Fields("Au_ID") & " - " _
                 & adors.Fields("Author") & " - " _
                 & adors.Fields("Year Born")

   Set adors = Nothing

End Sub

The first Set statement (which is commented out above) was used to run the client application accessing the server object by straight COM. Get the app working first before you try to tackle the tricky bits. When I had the app working, I commented out the line as you see above and uncommented the second Set statement.

Notice how you create an object over HTTP. You do not use the standard COM CreateObject. Instead, you create an instance of an RDS Dataspace object, and call it's CreateObject method. The first parameter is the program ID of the object you want to create. The second, in this case, is a URL that points to the IIS server that is going to create the object for us. You can specify an IP address if the server does not have a DNS entry or for increased security and I have also explicitly specified port 80 - again, you might choose a different port for this as long as IIS is listening there.

When I had it working on my machine, I placed the client EXE on a network share and asked Mark to run it from his machine (he has ADO and VB6 installed, so I didn't need anything else). It worked first time. This turned out to be much easier than I expected.

Admittedly, the documentation just plain sucks (have I mentioned that before?) but this whole concept of access to objects via an IIS proxy is just too cool for words so I am prepared to go easy on Microsoft for the documentation for just this one article.

The complete source to both the server and the client are available for download. You will, of course, need to change machine names and paths as appropriate.

Written by: Jim Karabatsos
October '98

Image of Arrow linked to Next Article
Image of Line Break