SQL and XML

Tanzarine Technology is currently working on client/server solutions to data transfer issues on the world wide web.

This work can be dated back to a development problem on a previous project in 1998. As part of preliminary work on a database-driven content management system, a Java applet was developed to run an SQL query on a remote database. The applet was chosen as the client technology because of the need to ease the so-called "burden of deployment" to the desktop. As the client software components were developed, they would be released to the web server, and downloaded on demand by client machines.

The client/server mechanism chosen for the applet to server communication was Java's Remote Method Invocation (RMI). Once the necessary objects had been created for the distributed application, testing of the applet led to two main conclusions.

Firstly, that even on a LAN, the speed of communication between the client and server (in the test case both 32-bit Windows machines) was rather slow. This is to be expected for cases using RMI, because this is the price paid for having distributed object management services pre-supplied.

Secondly, and more importantly, for the majority of clients tested, the applet did not work correctly. This was due to the differing JVMs supported by various versions of various browsers. Whilst this can be solved by the use of Sun's Java Plug-In, each desktop client must have this plug-in installed before the application can run, a restriction which is counter to the requirement of easing the burden of deployment.

In the light of these conclusions, the architecture used for the content management system was changed to a very thin client model, with all SQL statements generated and processed server side by (in the first instance) perl/CGI based on form requests submitted by the client. This solved both the problems encountered with the applet/RMI approach, but didn't initially use Java technology, although subsequent developments utilised Java servlets instead of perl/CGI.

A spin-off development from this work was a simple Java application to send SQL statements to a database via JDBC, and present the results as formatted text columns. Whilst the initial versions of this worked well enough, and in the case of databases held on remote (for example, Unix) servers saved the user the trouble of logging in and using a native utility to run their query, they required information about datasources to be held locally, which again does not help with the "burden of deployment" argument.

Once again, there seemed to be a requirement to develop a client/server solution which would allow the client to know less about the remote data sources and get on with the real work (i.e. getting the SQL statement from the user, submitting it, and displaying the results).

In the second half of 1999, Tanzarine Technology spent some time working on Internet based data capture applications. These used a straightforward HTML form to submit information to a server application, which then processed it. However, in the bigger picture, it would be quite likely that users might be running local applications which already had this information stored, and that this traditional approach led to some duplication. The users would have to print a report from their application, and then access the Internet to fill in the form with the correct data.

In this case there seems to be a requirement to allow the local application to transfer the data to the server over the Internet using some standard protocol, a situation further complicated by the fact that different users will have different local applications producing that data.

One of the possible solutions is to have a dedicated server talking a dedicated protocol on a particular TCP port. However, the biggest problem here is that many corporate firewalls would have to have that port "opened up" to allow the transfer to work. In many cases, this proved to be difficult, so a solution which uses a well-defined service such as HTTP on port 80 is more desirable, but the solution must still allow the definition of a specific protocol.

It is at this point that XML becomes a major factor - it can be transmitted between the client and server without any firewall issues, and the layout of the data can be declared in advance to implement a specific protocol.

To demonstrate how this might work, the SQL tool previously developed has been extended to implement a separated client/server interface, with the SQL request and the subsequent response both sent between the two as XML documents.

As both the client and the server are implemented in Java, it is necessary to both build and deconstruct XML documents from Java. Fortunately, this is an area which has received a great deal of attention recently, and for this work Objectspace DXML was used (which in turn uses IBM's XML4J parser).

To give a flavour of the communication which takes place between the client and the server, the client might send an XML document which looks like this:

<Sql>
  <Datasource>
    corp
    </Datasource>
  <Request>
    select fullname,join_date from employee where join_date>'10-01-1999' 
    </Request>
  </Sql>

This contains two pieces of information, the query to be run, and the database where it should be run. The server might then return:

<Sql>

  <ColumnInfo>

    <ColumnDefinition>
      <Name>
        fullname
        </Name>
      <Type>
        String
        </Type>
      <Length>
        30
        </Length>
      </ColumnDefinition>

    <ColumnDefinition>
      <Name>
        join_date
        </Name>
      <Type>
        String
        </Type>
      <Length>
        10
        </Length>
      </ColumnDefinition>

    </ColumnInfo>

  <ResultSet>

    <Row>
      <Column>
        Joe Bloggs
        </Column>
      <Column>
        11-16-1999
        </Column>
      </Row>

    <Row>
      <Column>
        Janet Bloggs
        </Column>
      <Column>
        10-29-1999
        </Column>
      </Row>

    </ResultSet>

  </Sql>

This contains two sections, one which defines the columns used, in case the client wishes to use them, and the actual result set itself. Whilst this may seem long-winded, the structure of the documents exchanged is governed by the Document Type Definition, or DTD, providing control of the interchange format, and all of the conversion is performed using library routines.

The client doesn't have to be the SQL query utility, it can be any other program which can generate and interpret the XML documents and do something with the data, for example, a corporate database search tool written in Java. So, whilst this example deals with the interactive query tool, the XML server produced can be used for anything, such as the content management tool described in part 1 above.

The client doesn't have to be written in Java at all, again, the requirement is that the client can generate the XML request document and send it via HTTP to the server, and then process the results when they come back. Therefore, for the case discussed in part 2 above, where a variety of off-the-shelf or bespoke packages might be required to submit their data to the server, this would be an ideal solution.