XML SQL gateway (socket based)

Posted by

What is it
Design
Overview
Source tree
Usage examples
Using the XML SQL processing engine (XMLProcessorIF)
XML document description
Using the SocketServer (socket gateway to the engine)
Using the SocketClient (client to the gateway)
Source archive

What is it

The XML SQL database gateway is an engine that accepts an SQL command and returns the ResultSet as an XML document. The gateway uses JDBC in order to connect to the actual database. The gateway also comes with a socket connector, but not an HTTP (Servlet) connector. This allows the gateway’s services to be accessed over a simple socket. Only the socket implementation is provided in this tutorial, the Servlet connector is not provided. You can use the pattern and code described here to provide an HTTP connector.

Design

Here is a diagram that shows the major pieces of the system:

Overview

The SQL Processor (or engine) that actually uses JDBC to connect to a database, and convert a ResultSet to XML, is contained in the xmlsq.core and xmlsql.jdbc packages. This functionality of this processor (or engine) is defined by the XMLSQLProcessorIF. The ProcessorFactory is responsible for creating instances of classes that implement this interface. A JDBC implementation is provided in the source code, but you are free to implement this interface using OCI8 or any mechanism you desire.

A socket gateway server is provided to allow clients from a variety of different platforms to use the processor via sockets. These classes are in the xmlsql.socketgateway package. In the implementation provided in the sources, ASCII encoding is used in the data that is passed over the sockets. Feel free to extend this to the encoding requirements that you have, as the gateway is designed in a very extensible manner, and byte arrays are used at the lowest levels (which makes it very easy to use different kinds of encoding, by allowing reuse of the engine). The rawdata package is useful for doing byte manipulations and encoding.

A socket client is also provided so you can test out the socket gateway, and these classes are available in the xmlsql.socketclient package.

In order to do socket based communications with the gateway, I’ve provided a simple protocol for doing request response type operations. It is a simple protocol that requires that a client has to create a packet, send it to the server, and wait for another packet back from the server. There are no callbacks from the server to the client, and all communications are half duplex. This is a simple protocol, and was designed to be easy to understand and quick to implement. A packet is defined to have a command, and data, like: command:data_length:[data]. These packets can be given to the socket gateway, which then extracts the command and data and executes it on the processor; it then takes the results and sends it back to the client in a packet as well. This simple request response protocol can very easily be layered over HTTP if necessary, and is extremely “web-friendly”. You can use the pattern described here to accomplish this.

Source tree

xmlsql/
  TestProcessor.java       --> tests processor (has main() )
  TestSQLServlet           --> tests HTTP gateway
  core/
    XMLSQLProcessorIF.java --> main interface
    ProcessorFactory.java  --> factory class
  jdbc/
    JDBCProcessorImpl.java --> default impl of main interface
  socketgateway/
    Console                --> CLI for gateway server
XMLSQLSocketServer.java--> socket gateway to processor

SocketProcessor.java   --> helper class for SocketServer
ConstantsIF.java       --> Valid command names, etc.
Protocol.java          --> Protocol static methods
GatewayProtocolException.java
--> Protocol violation exception
socketclient/
Console                --> CLI for client
SocketClient           --> socket client for gateway

xmlsqlservlet/
  XMLSQLServlet.java       --> HTTP gateway to main interface
rawdata/
ByteBuffer.java          --> Dynamic byte array
ByteTokenizer.java       --> Tokenizer for byte arrays
ConstantsIF.java         --> holds default encoding consts
util/
Util.java                --> static utility methods

All the source code is included in the src.jar file. Usage examples

Using the XML SQL Processing engine (XMLSQLProcessorIF)

Use the xmlsql.core.ProcessorFactory class to create() an XMLSQLProcessorIF implementation object. Then init(..) this object with the database connection parameters, and use execute(..) to have the db server execute your SQL commands and return an XML document with the results. You can use execute() many times. Once you are done, simply call disconnect() on this object and you’re done.

Each SQL command is auto-committed, so there is no rollback. This is done for simplicity, and can be changed easily in the source code (details on how to do this are given below).

A database connection is held for the duration of your requests, in order to be efficient, as getting a db connection is an expensive operation.

Source code example of using the XMSQLProcessorIF

The first step to using the SQL to XML engine is getting an instance of the XMLSQLProcessorIF interface. Use the xmlsql.core.ProcessorFactory class to get an object that implements this interface. Different implementations might be available that handle the Connection and Statement objects differently (which affect exactly how transactions are done). The user does not need to care about these implmentation details, they just want to get XML from an SQL statement.

The next step is connecting to the database, using init( dbUrl , jdbcDriverClassname, userId , password ). The params are all Strings. Once init() is complete, a connection to the database is established, and the engine is ready to accept SQL statements, and turn them into XML responses.

The execute( String sqlCommand ) is used to send the database SQL statements. You simply have to take your SQL statements, put them in a String and pass it to execute(). This method takes the SQL commands and passes them to the database engine (via JDBC), gets the result (could be a ResultSet or int), turns this into XML and returns it to you. You can take the XML document, returned in a String, and do what you want with it; you can parse it into a DOM object, or pass it to some other object, etc. Now, if no ResultSet is returned, for example if the SQL statement was an UPDATE, INSERT or DELETE, the number of rows that were affected by this statement is returned in the following format <updateCount=[number of rows]>.

If at anytime a SQLException is generated, it is simply passed on to you. The engine is not in a position to know what you want to do with these exceptions. If you want to override this behaviour, you can simply create a different implementation of the interface (XMLSQLProcessorIF) and make it available using the factory class (ProcessorFactory).

Once you are done executing your SQL statements, you have to call disconnect() to shutdown the engine, and close the database connections (closes Statement, Connection).

Test.java is a simple program that takes you through these steps:

 1:
 2: package xmlsql;
 3:
 4:
 5: import java.lang.reflect.*;         //Array, etc.
 6: import java.text.*;                 //DateFormat class
 7: import java.util.*;                 //Vectors, etc
 8: import java.io.*;                   //Serializable, etc
 9: import java.net.*;                  //Network classes 
 10:
 11: import java.sql.*;
 12:
 13:
 14: import xmlsql.core.*;
 15:
 16: /**
 17:  * Test simply tests all the operations available on the
 18:  * XMLSQL Processor.
 19:  * 
 20:  * @author Nazmul Idris
 21:  * @version 1.0
 22:  * @since 11/16/2000,3:43pm
 23:  */
 24: public class Test
 25: {
 26: public static void main( String args[] ){
 27:   try{
 28:     System.out.println( ":: testing xml sql processor ::" );
 29:
 30:     XMLSQLProcessorIF xsp = ProcessorFactory.getInstance();
 31:
 32:     //init it
 33:     xsp.init(
 34:       "jdbc:cloudscape:rmi:SampleDB;create=true",
 35:       "COM.cloudscape.core.RmiJdbcDriver",
 36:       "" , "" );
 37:
 38:
 39:     //execute SQL commands and display the output
 40:     System.out.println(
 41:       xsp.execute( "select * from inventory" )
 42:     );
 43:
 44:     System.out.println(
 45:       xsp.execute( "DELETE FROM inventory WHERE id = 4" )
 46:     );
 47:
 48:     //disconnect it
 49:     xsp.disconnect();
 50:   }
 51:   catch(Exception e){
 52:     System.out.println( e );
 53:   }
 54:
 55: }
 56:
 57: }//end of Test class
 58:
 59:
 60:

If you want to make changes to any of the behaviors of the engine (like how it deals with transactions, exceptions, connection pooling, etc.) you are free to create your own implementation of the XMLSQLProcessorIF, and plug it in to this framework, by using the ProcessorFactory to instantiate the class.

XML document description

This is what the XML document looks like (in response to a SQL Query):

QUERY:

select * from inventory;

XML RESPONSE:

 1: <?xml version="1.0" encoding="us-ascii"?>
 2: <table>
 3:
 4:  <row>
 5:   <cell name="ID" type="INTEGER">1</cell>
 6:   <cell name="NAME" type="LONGVARCHAR">Palm VII</cell>
 7:   <cell name="DESCRIPTION" type="LONGVARCHAR">Palm
 8:    device</cell>
 9:  </row>
 10:
 11:  <row>
 12:   <cell name="ID" type="INTEGER">2</cell>
 13:   <cell name="NAME" type="LONGVARCHAR">Handspring Visor</cell>
 14:   <cell name="DESCRIPTION" type="LONGVARCHAR">Palm device
 15:    competitor</cell>
 16:  </row>
 17:
 18:  <row>
 19:   <cell name="ID" type="INTEGER">3</cell>
 20:   <cell name="NAME" type="LONGVARCHAR">RIM Blackberry</cell>
 21:   <cell name="DESCRIPTION" type="LONGVARCHAR">Pager device
 22:    that supports Java</cell>
 23:  </row>
 24:
 25:  <row>
 26:   <cell name="ID" type="INTEGER">4</cell>
 27:   <cell name="NAME" type="LONGVARCHAR">garbage</cell>
 28:   <cell name="DESCRIPTION" type="LONGVARCHAR">bla bla
 29:    bla</cell>
 30:  </row>
 31:
 32: </table>

If a SQL statement does not return a result set, then an XML document is not returned, but a String containing the number of rows affected by the statement is returned in this format: <updateCount=[number of rows]>.

Using the SocketServer (socket gateway to the engine)

In order to make the services of the XMLSQL processing engine available to software running on other platforms, and strange devices, a socket server gateway is provided that allows any socket client to connect to the server (using TCP), and use simple “US-ASCII” encoded commands. The protocol is a simple text based request-response protocol, where the client sends a command to the server, which processes it and sends the results back. All communications between the client and server have to be encoded in a simple way:

<command>:<number of bytes in data>:[data]

There can be no “:” character in the command string. The <number of bytes in data> field can’t be left blank, if there is no data, this should be 0. This is a simple packet header scheme, that makes it easy to write the client and server. Here is a list of commands that are available: init, processsql, disconnect, xmlresult, ok, err. Everything is case insensitive. All the valid command names can be found in xmlsql.socketgateway.CommandNamesIF.java. Here is a simple conversation between the client and the server:

Client -> Server Client <- Server
init:85:jdbc:cloudscape:rmi:SampleDB;
create=true,
COM.cloudscape.core.RmiJdbcDriver,
uid,pswd
ok:0:
OR

err:10:err msg...
processsql:23:select * from inventory
xmlresult:100:
<?xml version="1.0" ...
OR
err:12:err msg...
processsql:32:delete from inventory where id=4
xmlresult:0:
OR
err:15:err msg...
disconnect:0:
<none>

Here is a detailed description of each command:

Command name Parameter description Return value Requested by Responded by
init JDBC url connect String,JDBC driver classname,

userId,

password

ok or err Client Server

For example, a valid init command string for Cloudbase database is:

init:76:jdbc:cloudscape:rmi:SampleDB;create=true,
COM.cloudscape.core.RmiJdbcDriver,,

The response from the gateway to this command could be:

ok:0:
OR
err:??:some_error_message

Command name Parameter description Return value Requested by Responded by
processsql SQL statement(s) xmlresult Client Server

For example, a valid processsql command string would be:

processsql:24:select * from inventory

If you have more than one SQL statement, be sure to separate them with “;”. The response from the gateway to this command could be:

xmlresult:635:<?xml version=”1.0″ encoding=”us-ascii”?>…
OR
xmlresult:0:

Command name Parameter description Return value Requested by Responded by
disconnect <none> <none> Client Server

For example, a valid disconnect command string would be:

disconnect:0:

To start up the server, you can use the xmlsql.socketgateway.Console class, which provides a command line interface (CLI) to the server. You can check on the status, and shutdown the server from the CLI, and see what requests have been made by clients.

You are free to implement you own SocketServer that uses your own commands, and protocol that interfaces with the underlying XMLSQL processing layer.

Using the SocketClient (client to the gateway)

A default Socket client is provided to allow you to test the gateway and send commands to XMLSQL processing engine. The Socket client has a CLI as well, and is contained in the xmlsql.socketgateway.Console class. You can invoke all the supported commands on the gateway from this console. This includes the init, processsql and disconnect commands.

Source archive

Please look at the source code and review all the files that are included to see how all this is implemented exactly. There are a lot of things that happen in the background with byte encoding and such, that are taken care of by many classes in the rawdata package. Also, study the protocol translation between the socket client and the server, and the xmlsql.socketgateway.Protocol class. Be sure to study the CLI framework in the xmlsql.socketclient.SocketClient class, and how it cleanly maps into xmlsql.socketgateway.SocketProcessor class. The source code is extensively and clearly documented, so please take advantage of that.

Click here to download the entire archive as one src.jar file. There are three batch files to make it easier to invoke these classes. build.bat is provided so you can rebuild the entire project if you make any changes to any class. runServer.bat is provided so you can invoke the socket gateway Console (and the server). runClient.bat is provided so you can invoke the socket client Console. apidocs.bat is provided, so you can generate Javadoc documentation.