DSOAP, ADO and XML

Problem/Question/Abstract:

DSOAP, ADO and XML

Answer:

Introduction

Developing distributed applications is a big shift from the client/server world. The traditional client/server model is based on the assumption that a client application can open a database connection and hold it until the task is terminated. While this approach simplifies the task of programmers, it tremendously impacts scalability and maintenance. There are multiple reasons for this: Hardware is a finite resource: for the big a server can be, it's very easy to terminate its resources as the number of connected and concurrent clients increase. Database licenses are expensive and for each client a license is being used. It is not easy if not impossible in some situations to effectively load balance the database tier. Finally, by using a two tier model, business rules are coded in either the client or the database via stored procedures. It is difficult to update these two tiers. Technologies such as COM+ and Corba or frameworks such as Java's J2EE and Microsoft .Net push towards the development of distributed systems and provide the necessary infostructure to build scalable multi-tier systems. Regardless of your needs (LAN or Internet) by developing a multi-tier system you will have a product that is much easier to update and scale (both up or out). Before jumping to the example, I want to reserve a few words to two very important aspects of efficient multi tier development: Client independence: If your middle tier (the one that contains business logic) is accessed by both web and desktop clients, you need to plan ahead and make sure it will work with both. Web clients are very different from desktop clients. The architecture of HTTP makes them implicitly stateless and there's no preservation of state between an HTML page and the other. Internet Information Server has a Session object that can help you storing values of any type (simple data types as well as COM objects) and keeping them until the user keeps his/her browser open. Unfortunately, if you have a cluster of webservers (web farm), this information is not propagated across the servers. Using IIS Session object is not a good idea in a clustered environment. You will need to use other techniques to store client's state. You need to design your middle tier using patterns that do not depend on any specific capability of the clients that will use it. Preferabily you should conform to the lowest denominator which is the web client. I will cover this topic in another article but for now let's continue with the other key requirement. Openness: In a world of web services like the one we are moving towards you don't know who or what will access your system. This is another reason for being client independent but it creates another requirement for successful multi-tier development: when facing the outside world, you need to use open standards that are globally accepted and used. Do not expect your clients will be able to use ADO Recordsets or communicate via Corba. The standards are represented today by SOAP and XML.

The example

The following example illustrates how to retrieve data from the Northwind sample database included in SQL Server. We will create a business object that queries the database using ADO and generates an XML streams that is used by the client for data presentation/manipulation. On the client we will access this XML stream using a common ADO Recordset (I will use the TADODataset included in ADOExpress). Finally, when we are done modifiying the data we will convert the recordset in XML and send it back to the business object. Finally, this will update the database. All this will be done using SOAP. The XML that ADO generates is perfectly usable from any type client. A Java client could use it as well after converting it into a format it's more convenient.

How to install the sample

In my previous article DSOAP Toolkit I explained how you create a COM object and how you expose this to the world using the Microsoft SOAP SDK. In this article I will focus on the implementation of the business object. I will show how you can convert an ADO Recordset in XML and vice versa. I incuded the WSDL and WSML files in the zip so you will not need to recreate them. Download the example, register the ActiveX library DSOAPXMLLib.dll and move the WSDL and WSML files under c:\inetpub\wwwroot. When you are done with this, open the client application and test the webservice by pressing the "Get Customers" button. I assume your computer has SQL Server installed. If SQL Server is installed on a remote machine, modify the constant DBConnStr in the unit uCustomersDataObject_Impl.pas before registering the ActiveX library and launcing the client application. If you need to change the folder where the WSDL file will be contained, make sure you update the URL specified at the end of it top match the new directory.

The business object

The ActiveX library contains one COM object called CustomersDataObject. Its interface contains only two methods (GetCustomers and UpdateCustomers) as shown below:



GetCustomers is declared as:

function GetCustomers(const aFilter: WideString; out Errors: WideString): WideString;

The "aFilter" parameter takes a valid SQL WHERE condition such as "CustomerID line 'A%' " without the WHERE keyword. You will use this to filter the amount of data you want to receive. The method returns the XML generated by an ADO Recordset. UpdateCustomers is declared as:

function UpdateCustomers(const someChanges: WideString; out Errors: WideString):
  WordBool;

The parameter someChanges takes the delta of changes that have been committed on the client and updates the database. It will return TRUE if succesful or FALSE if it failed. Notice the out parameter Errors in both methods. This is not required in order to handle error notification. As I explained in my other article Using SOAP with Delphi, the protocol defines a standard way to report errors. If an exception would be raise on the client, the Microsoft SOAP SDK would trap it and encode it in a SOAP error condition which would then be reraised client side. Sometimes I prefer to trap everything myself. By doing this you could log every error in a database table and have more control over things. Still, leaving the exception unhandled, is a valid approach as well.

Streaming a Recordset in XML

Streaming a Recordset in XML is a matter of calling the Recordset.Save method passing an object that implements IStream as parameter. You can use the ADO Stream or the XMLDOMDocument object for this porpouse. The second one is generally faster because it uses UTF8 encoding (1 byte character) and is optimized for XML processing. The ADO Stream instead works with UTF16 encoding (try saving both XML streams to a file and see the difference in size. I realized this thanks to the comments of Shiv Kumar. Have a look at his site at http://www.matlus.com In the unit uADOCommon.pas you will find the following function that, given a connection string and a SQL command, creates a Recordset and converts it into XML:

function SQLToXML(const aConnection: OleVariant; const aSQLCommand: string): string;
var
  rs: _Recordset;
  xml: IXMLDOMDocument2;
begin
  // Creates a disconnected recordset that will be streamed into XML
  rs := CoRecordset.Create;
  rs.CursorLocation := adUseClient;
  rs.Open(aSQLCommand, aConnection, adOpenForwardOnly, adLockBatchOptimistic, 0);
  rs.Set_ActiveConnection(nil);

  // Streams _Recordset into XML
  xml := CoDOMDocument30.Create;
  rs.Save(xml, adPersistXML);
  rs.Close;

  result := xml.xml;
end;

Pay special attention to the line in which I call the Recordset's Open method. I specified adOpenForwardOnly as cursor type. This has an immediate effect on performances while querying data from a SQL database. Generally speaking, the more flexible the cursor you open, the slower and more resource intensive it will be. Take a look at the article SQL Server Cursor library if you want to know more about the subject. Another important thing I did in the function above is to set the Recordset's connection to NIL and the CursorLocation to adUseClient. When using this combination of values you will generate a disconnected, client side Recordset. The resulting XML will look like this:

<xml xmlns:s="uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882" xmlns:dt="uuid:C2F41010-65B3-11d1-A29F-00AA00C14882" xmlns:rs="urn:schemas-microsoft-com:rowset" xmlns:z="#RowsetSchema">
<s:Schema id="RowsetSchema">
  <s:ElementType name="row" content="eltOnly" rs:updatable="true">
   <s:AttributeType name="CustomerID" rs:number="1" rs:writeunknown="true" rs:basecatalog="Northwind" rs:basetable="CUSTOMERS" rs:basecolumn="CustomerID" rs:keycolumn="true">
    <s:datatype dt:type="string" dt:maxLength="5" rs:fixedlength="true" rs:maybenull="false"/>
   </s:AttributeType>
   <s:AttributeType name="CompanyName" rs:number="2" rs:writeunknown="true" rs:basecatalog="Northwind" rs:basetable="CUSTOMERS" rs:basecolumn="CompanyName">
    <s:datatype dt:type="string" dt:maxLength="40" rs:maybenull="false"/>
   </s:AttributeType>
   [..]

   <s:extends type="rs:rowbase"/>
  </s:ElementType>
</s:Schema>
<rs:data>
  <z:row CustomerID="ALFKI" CompanyName="Alfreds Futterkiste" ContactName="Maria Anders" ContactTitle="Sales Representative" Address="Obere Str. 57" City="Berlin" PostalCode="12209" Country="Germany" Phone="030-0074321" Fax="030-0076545"/>
  <z:row CustomerID="ANATR" CompanyName="Ana Trujillo Emparedados y helados" ContactName="Ana Trujillo" ContactTitle="Owner" Address="Avda. de la Constituci�n 2222" City="M�xico D.F." PostalCode="05021" Country="Mexico" Phone="(5) 555-4729" Fax="(5) 555-3745"/>
[..]

</rs:data>
</xml>

Make it a WebService

What you need to do now is to register your ActiveX library and generate the correct WSDL files using the Microsoft SOAP SDK like I explained in my previous article DSOAP Toolkit. I included the correct WSDL files in the example and the only thing you need to do is to copy them in the root c:\inetpub\wwwroot.

The client

So far so good. We know how to get our data, how to convert it in XML and how to stream it to the client. What we need now is to a way to invoke all this functionality from the client. By using the DSOAP WSDL Import Wizard specifying "http://localhost/DSOAPXMLLib.WSDL" as source, you will be able to generate the unit uCustomersDataObject_Impl.pas. See my other article for details. In order to make the example a little bit more interesting, I decided to show how to feed a TADODataset with this XML stream. By doing this, you will still be able to use all your third party data aware components like it were a regular MIDAS Clientdataset or a BDE TTable. From inside the Delphi IDE the client will look like this:  



Receiving data

In order to receive data you need to invoke the GetCustomers method and ceonvert the XML back into a Recordset. The function XMLToRecordset contained in the common unit uADOCommon.pas takes care of this conversion and returns a Recordset. Once you have that, just assign it to the ADODataset by setting its Recordset property. The following code is execute when you press the "Get Customers" button:

procedure TForm1.bGetCustomersClick(Sender: TObject);
var
  errs: widestring;
  xml: string;
  rs: OleVariant;
begin
  xml := fCustomersDataObject.GetCustomers(eFilter.Text, errs);

  [...]

  ADODataSet1.Recordset := XMLToRecordset(xml) as ADODB._Recordset;
  [...]

end;

While this is the XMLToRecordset funtions' code:

function XMLToRecordset(const someXML: string): _Recordset;
var
  xml: IXMLDOMDocument2;
  rs: OleVariant;
begin
  xml := CoDOMDocument30.Create;
  xml.LoadXML(someXML);
  rs := CoRecordset.Create;
  rs.Open(xml);

  result := IUnknown(rs) as _Recordset;
end;

By reversing what we did on the server side, we can feed the Recordset back with its rows. It almost cannot get simpler than this!

Sending updates back to the middle tier

Here's where it gets a little bit tricky, client side. The ADO Recordset keeps track of all the changes the user made to the original data we received. In order for us to update the database, we need to send these changes back by invoking the server side method UpdateCustomers. The problem we have now is that the XML stream generated client side will contain both the changes and the original data. This is extremely not efficient since the only thing the server needs are the updates. Take a look at the code associated to the button bUpdateCustomers:

procedure TForm1.bUpdateCustomersClick(Sender: TObject);
var
  xml, errs: widestring;
begin
  xml := FilterUpdates(ADODataSet1.Recordset as _Recordset);
  [..]

  if not fCustomersDataObject.UpdateCustomers(xml, errs) then
    MessageDlg(errs, mtError, [mbOK], 0)
  else
  begin
    ShowMessage('Updated!');
    ADODataSet1.Recordset.CancelUpdate;
  end;
end;

I am generating XML using the function FilterUpdates. Here's the code:

function FilterUpdates(const aRecordset: _Recordset): string;
var
  DOMDoc: IXMLDOMDocument2;
  RemNode, DataNode: IXMLDOMNode;
  i, offset: integer;
begin
  DOMDoc := CoDOMDocument30.Create;
  DOMDoc.Async := FALSE;
  aRecordset.Save(DOMDoc, adPersistXML);

  DataNode := DOMDoc.selectSingleNode('xml/rs:data');
  offset := 0;

  for i := 0 to (DataNode.childNodes.length - 1) do
  begin
    if (DataNode.childNodes[i - offset].nodeName = 'z:row') then
    begin
      RemNode := DataNode.removeChild(DataNode.childNodes[i - offset]);
      offset := offset + 1;
    end;
  end;

  result := DOMDoc.xml
end;

This function was inspired by an article I found on the MSDN website. Take a look at it by clicking here. What I do here is nothing but filtering out all the original data while keeping the updates. This is how the XML stream will look after this operation:

<xml xmlns:s="uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882" xmlns:dt="uuid:C2F41010-65B3-11d1-A29F-00AA00C14882" xmlns:rs="urn:schemas-microsoft-com:rowset" xmlns:z="#RowsetSchema">
<s:Schema id="RowsetSchema">
  <s:ElementType name="row" content="eltOnly" rs:updatable="true">
   <s:AttributeType name="CustomerID" rs:number="1" rs:writeunknown="true" rs:basecatalog="Northwind" rs:basetable="CUSTOMERS" rs:basecolumn="CustomerID" rs:keycolumn="true">
    <s:datatype dt:type="string" dt:maxLength="5" rs:fixedlength="true" rs:maybenull="false"/>
   </s:AttributeType>
   [..]   <s:extends type="rs:rowbase"/>
  </s:ElementType>
</s:Schema>
<rs:data>
  <rs:update>
   <rs:original>
    <z:row CustomerID="WOLZA" CompanyName="Wolski  Zajazd" ContactName="Zbyszek Piestrzeniewicz" ContactTitle="Owner" Address="ul. Filtrowa 68" City="Warszawa" PostalCode="01-012" Country="Poland" Phone="(26) 642-7012" Fax="(26) 642-7012"/>
   </rs:original>
   <z:row CompanyName="Wolski  Zajazdxxx"/>
  </rs:update>
  <rs:insert>
   <z:row CustomerID="A    " CompanyName="Test"/>
   <z:row CustomerID="B    " CompanyName="Test #2"/>
  </rs:insert>
</rs:data>
</xml>

The final step: updating the database

We are almost done. Now we sent the batch of updates back to the server using XML and we invoked the method UpdateCustomers. If you take a look at the code, you will find the following:

function TCustomersDataObject.UpdateCustomers(const someChanges: WideString; out
  Errors: WideString): WordBool;
begin
  result := FALSE;
  Errors := '';
  try
    // Updates the database
    UpdateSource(DBConnStr, someChanges);

    result := TRUE;
  except
    on E: Exception do
      Errors := E.Message;
  end;
end;

There's nothing particoular in that code except the call to the method UpdateSource listed below:

procedure UpdateSource(const aConnection: OleVariant; const someChanges: string);
var
  rs, conn: OleVariant;
begin
  rs := XMLToRecordset(someChanges);

  case VarType(aConnection) of

    varUnknown,
      varDispatch: conn := IUnknown(aConnection) as _Connection;

  else
    begin
      conn := CoConnection.Create;
      conn.Open(aConnection, '', '', 0);
    end;
  end;

  rs.ActiveConnection := conn;
  rs.Filter := adFilterPendingRecords;
  rs.UpdateBatch(adAffectAllChapters);

  rs.Close;
end;

We just converted the delta of changes into a Recordset and we finally used its UpdateBatch method to persist the changes to the database.

Conclusion

You can find more information about ADO on the Microsoft website. What you saw above should give you a pretty good idea of how updates work in a distributed, stateless environment and how you can use existing technologies to do this efficiently and in a very simple way.