A JDBC-based relay for SQL commands embedded in XML
This program listens on a port for connections from independent clients
running as standalone applications, Applets or JavaScript scripts.
Once a connection has been made, this program expects to
receive a (read-only) SQL command embedded in XML, which it then
unpacks, and relays to the DB2 server hosting some database via JDBC.
The request format actually allows for a series of requests that will
be executed using the same connection. Here is the format for a list
of SQL requests:
<![CDATA[
<request-list account_name="some_db_account" password="some_password">
<request>
some_specific_SQL_command_for_the_database
</request>
.
.
.
<request>
a_final_specific_SQL_command_for_the_database
</request>
</request-list>
]]>
The database server response is returned to the client as a sequence of
PRINTABLE STRINGS representing the contents of each field of each
row being returned. The responses may be formatted as lines of raw text
or as an HTML table whose first row is a list of fieldnames being
returned.
Returned fieldnames can be used to direct further processing of the data.
(Note that SQL will choose arbitrary field names for any "computed
fields, and that binary or pointer data will not be meaningful, when
returned within printed strings.)
This program provides a buffer between clients and JDBC. Clients
need not download or run JDBC drivers, and can probably make their
requests over SOAP (though this has yet to be tested).
In fact, it would seem that this program can be reduced to 50 lines or so
if it is implemented as a Web Service, so that all XML processing will
occur "under the covers."
Run this program with
java XMLRelaySQL
or with a colon-separated classpath on Unix
java -cp db2jcc.jar:db2jcc_license_cu.jar:. XMLRelaySQL
or with a semi-colon separated classpath (!) on Windows
java -classpath db2jcc.jar;db2jcc_license_cu.jar;. XMLRelaySQL
Change MYPORT as necessary, but if you do change it, you must also
change the port used by any clients you expect to work with this
server.
Caveat: this is an experimental hack, not a production-quality program.
In fact, its first version was taken from a web site of tutorial
examples.
<![CDATA[
import java.io.*;
import java.net.*;
import java.util.*;
import java.sql.*;
import javax.xml.parsers.*;
import org.xml.sax.*;
import org.xml.sax.helpers.*;
// Copyright Indiana University at Indianapolis, 2006.
public class XMLRelaySQL extends Thread
{
public static final int MYPORT = 6701;
ServerSocket listen_socket;
public XMLRelaySQL()
{
try
{
listen_socket = new ServerSocket ( MYPORT );
}
catch( IOException e )
{
System.err.println( e );
}
System.out.println( "Listening" + " on " + MYPORT + "....\n" );
this.start();
}
public void run()
{
try{
while( true )
{
Socket client_socket = listen_socket.accept();
SQLConnection c = new SQLConnection ( client_socket );
}
}
catch( IOException e )
{
System.err.println( e );
}
}
public static void main( String[] argv )
{
new XMLRelaySQL();
System.out.println( "Start new Web Services SQL relay server..." );
}
}
class SQLConnection extends Thread
{
protected Socket client;
protected DataInputStream in;
protected PrintStream outToClient;
Connection JDBC_Connection; // database connection object
String dsn = "jdbc:db2://some_host.some_domain:some_port/some_database";
Statement statement; // SQL statement object
public SQLConnection ( Socket client_socket )
{
System.out.println( "Starting a connection.\n" );
client = client_socket;
try
{
in = new DataInputStream( client.getInputStream() );
// define with auto-flush.
outToClient = new PrintStream ( client.getOutputStream(), true );
}
catch( IOException e )
{
System.err.println( e );
try
{
client.close();
}
catch ( IOException e2 )
{
};
return;
}
this.start();
} // end SQLConnection constructor
public void run()
{
String query;
try
{
BufferedInputStream programsIn = new
BufferedInputStream( in );
// Get a parser factory, and instruct that factory to
// generate parsers that validate the incoming XML file with
// respect to its DTD as the file is read.
SAXParserFactory factory = SAXParserFactory.newInstance();
factory.setValidating( true );
// Get a parser, and set it up to read from the input stream
// and call methods within GetSQLRequestsListHandler2 to
// handle generated events.
SAXParser sax = factory.newSAXParser();
sax.parse( programsIn, new GetSQLRequestListHandler2() );
}
catch ( ParserConfigurationException pce )
{
System.out.println( "Could not create that parser:" );
System.out.println( pce.getMessage() );
}
catch ( SAXException se )
{
System.out.println( "Problem with the SAX parser:" ) ;
System.out.println( se.getMessage() );
try
{
client.close();
}
catch( IOException e2 )
{
}
}
catch ( IOException ioe )
{
System.out.println(
"SAX encountered an error reading input stream.\n" );
System.out.println( "Here is the message: " + ioe.getMessage() );
}
} // end run
public void do_single_request_sql( String query, String return_format )
{
// This method uses the already-open JDBC connection and JDBC
// statement (statement) to send an SQL command. It offers two
// return formats: raw text and HTML_table.
// As of 5-23-2006 it does not support returning a limited range
// of rows.
ResultSet resultSet; // SQL query results
ResultSetMetaData resultSetMD;
boolean moreRows; // "more rows found" switch
String record;
int colcount, i;
int rowCount = 0;
try
{
resultSet = statement.executeQuery( query );
if( return_format.equals( "HTML_table" ) ||
return_format.equals( "HTML_TABLE" ) )
{
resultSetMD = resultSet.getMetaData();
moreRows = resultSet.next();
colcount = resultSetMD.getColumnCount();
System.out.println( "Found " + colcount +
"columns in result set." );
outToClient.println( "<table border=1>" );
// get and print column names as table headers....
outToClient.println( "<tr>" );
for( int aCounter = 1; aCounter <= colcount; aCounter++ )
{
String result = resultSetMD.getColumnLabel( aCounter );
outToClient.println( "<th>" + result + "</th>" );
}
outToClient.println( "</tr>\n" );
// Return dataset values to the client.
System.out.println(
"Returning dataset records to the client." );
rowCount = 0;
while ( moreRows )
{
//build result string
outToClient.println( "<tr>" );
record = "";
for ( i=1; i <= colcount; i++)
{
record = record.concat( "<td>" +
resultSet.getString(i) +
"</td>" );
}
rowCount++;
//outToClient.println( rowCount + " " +record );
outToClient.println( record );
outToClient.println( "</tr>\n" );
//System.out.println( rowCount + " " + record );
moreRows = resultSet.next();
}
outToClient.println( "</table>" );
}
else // right now only 2 formats supported.
{
moreRows = resultSet.next();
if ( !moreRows )
{
outToClient.println( "No rows found." );
return;
}
resultSetMD = resultSet.getMetaData();
colcount = resultSetMD.getColumnCount();
System.out.println( "Found " +colcount +
" columns in result set." );
// Return dataset values to the client.
System.out.println(
"Returning dataset records to the client." );
rowCount = 0;
while ( moreRows )
{
//build result string
record = "";
for ( i=1; i <= colcount; i++ )
{
record = record.concat( resultSet.getString( i ) +
" " );
}
rowCount++;
//outToClient.println( rowCount + " " +record );
outToClient.println( record );
//System.out.println( count + " " + record );
moreRows = resultSet.next();
}
}
System.out.println( "Sent " + rowCount + " records." );
sleep( 2000 ); // was connection closing too soon?
resultSet.close();
}
catch ( Exception e )
{
outToClient.println( "Sorry...can't do that." );
System.out.println( "Exception occurred " + e.toString() );
}
} // end do_sql
class GetSQLRequestListHandler2 extends DefaultHandler
{
// Here are the 3 methods that will be called by the SAX parser.
// They handle starting tags, characters between two tags, and
// ending tags. Each method may have to determine which tag type
// is being operated on when the method is called.
Request request = new Request();
Stack tagStack = new Stack();
public void startElement( String uri, String localName,
String qName, Attributes attributes )
{
// Process a start tag..
tagStack.push( qName ); // record the start tag on the stack.
// If a tag has attributes they must be picked out of the
// attribute map while processing the start tag.
String currentTag = ( String )tagStack.peek();
if( currentTag.equals( "request" ) )
{
request.return_format = attributes.getValue( "return_format" );
if( request.return_format == null )
{
request.return_format = "HTML_table";
}
}
else if ( currentTag.equals( "request-list" ) )
{ // These should be protected by if statements checking for null.
request.account = attributes.getValue( "account_name" );
request.password = attributes.getValue( "password" );
System.out.println( "Account_name=" + request.account );
//System.out.println( "Password=" + request.password );
// Make the JDBC Connection to the server, using tag
// attributes for account_name and password.
try
{
Class.forName ( "com.ibm.db2.jcc.DB2Driver" );
JDBC_Connection = DriverManager.getConnection( dsn,
request.account, request.password );
}
catch ( Exception e )
{
outToClient.println( "Sorry...can't open JDB connection." );
System.out.println( "Exception occurred " + e.toString() );
}
try
{
statement = JDBC_Connection.createStatement();
}
catch ( Exception e )
{
outToClient.println( "Sorry...can't create new statment." );
System.out.println(
"An exception occurred " + e.toString() );
}
}
} // end startElement tag.
public void characters( char[] characterArray, int start, int length )
{
// get the stuff between start and end tags. Note that it
// may involve multiple lines.
if( length <= 0 ) // it's a null section.
{
return;
}
String value = new String( characterArray, start, length );
String blanklessString = value.trim();
if( blanklessString.length() <= 0 ) // nothing but whitespace
{
return;
}
// ...but if the char string actually holds something besides
// whitespace, find out which tag is being processed...
String currentTag = ( String )tagStack.peek();
// ...and store the char string in the proper variable.
// tags of no interest to this request can be ignored,
// so they will not be placed into the request object.
if ( currentTag.equals( "request" ) )
{
request.sql = request.sql.concat( value.trim() );
}
// ...but what if request.sql is null? or otherwise unusable?
System.out.println( "SQL=" + request.sql );
}
public void endElement( String uri, String localName, String qName )
throws SAXException
{
// Process an ending tag...
// First check to see if it matches its start tag.
String poppedTag = ( String ) tagStack.pop();
if( ! qName.equals( poppedTag ) )
{
System.out.println( "Popped tag (" + poppedTag +
") does not match end tag(" + qName + ")\n" );
System.exit( 0 );
}
// Then check to see if it ends a set of request sub-tags, and
// if so, process the request.
if( qName.equals( "request" ) )
{
System.out.println(
"Performing embedded do_single_request_sql()" );
do_single_request_sql( request.sql, request.return_format );
// ...and create a new object to start a new data exchange.
request = new Request();
System.out.println( "Created a new request object..." );
}
if( qName.equals( "request-list" ) )
{
// close the JDBC connection.
try
{
statement.close();
JDBC_Connection.commit();
JDBC_Connection.close();
}
catch ( Exception e )
{
outToClient.println(
"Sorry...couldn't close JDBC connection." );
System.out.println( "Exception occurred " + e.toString() );
}
// Stop SAX parser processing so the connection to the client
// can be closed.
throw new SAXException(
"Stopping SAX parser due to </request-list> tag.\n" );
}
}
} // end GetSQLRequestListHandler2
class Request // holds the data collected for a single request.
{
String sql = "";
String account = "";
String password = "";
String return_format = "HTML_table";
} // end Request
} // end SQLConnection
]]>
Exception processing needs to be cleaned up, and revised to return better
error messages to the clients. Perhaps a new tag needs to be added to the
response XML with which to return error status.
For more information contact Michael
Grobe.
First version of this page: 2006-5-25.