Of course, Oracle isn't the only RDBMS which supports CLOBs. Unfortunately,
the mechanisms used to access these fields in the database do differ.
In some ways, the DB/2 approach is a bit cleaner than the Oracle one. Here's
the SQL code used to create the table:
create table philstest (
fourdigit varchar(10) not null,
transactionid integer not null,
request CLOB ( 1G ),
response CLOB ( 1G ),
unique( fourdigit, transactionid )
);
Note that we are required to specify a maximum size for the CLOBs here. The interpreter, as with the Oracle example, is case sensitive, so syntax has to be followed precisely.
With DB/2, we don't have to create the record first in order to obtain
a handle to the LOB. We can specify the CLOB as a String when inserting
the record. We can also simply perform an UPDATE of the record when we
want to add the response to the table. Here's the complete code:
import oracle.xml.parser.schema.*;
import oracle.xml.parser.v2.*;
import org.w3c.dom.*;
import org.xml.sax.*;
import COM.ibm.db2.jdbc.app.*;
import java.io.*;
import java.sql.*;
import javax.servlet.*;
import javax.servlet.http.*;
public class DB2XMLProvServlet extends HttpServlet {
private static final int ERR500 =
HttpServletResponse.SC_INTERNAL_SERVER_ERROR;
private ServletContext context;
private String driverName;
private String connectString;
private String username;
private String password;
private String tempDir;
public void init( ServletConfig config ) {
context = config.getServletContext();
driverName = config.getInitParameter( "DRIVER_NAME" );
connectString = config.getInitParameter( "CONNECT_STRING" );
username = config.getInitParameter( "USERNAME" );
password = config.getInitParameter( "PASSWORD" );
tempDir = config.getInitParameter( "TEMP_DIR" );
try {
Class.forName( driverName );
}
catch( ClassNotFoundException e ) {
e.printStackTrace();
context.log( "Could not load driver " + driverName );
}
}
public void doPost( HttpServletRequest req, HttpServletResponse resp ) {
CharArrayWriter buff = null;
BufferedReader br = null;
PrintWriter pw = null;
Connection conn = null;
PreparedStatement stmt = null;
ResultSet rs = null;
String fourDigit = null;
int transactionID = -1;
String lineIn = null;
String rr = null;
File inputFile = null;
DOMParser parser = null;
Document doc = null;
Element top = null;
Element elem = null;
NodeList list = null;
Node node = null;
String docType = null;
StringBuffer sb = new StringBuffer();
int i;
/*
* trap all exceptions so that we can send an
* error return to the caller
*/
try {
/*
* save incoming document to a temp file
*/
inputFile = File.createTempFile( "Prov", ".xml",
new File( tempDir ) );
pw = new PrintWriter( new FileWriter( inputFile ) );
br = new BufferedReader( new InputStreamReader( req.getInputStream() ) );
while( ( lineIn = br.readLine() ) != null )
pw.println( lineIn );
pw.flush();
pw.close();
br.close();
/*
* perform an initial parse of the document
*/
parser = new DOMParser();
parser.parse( new InputSource(
new FileInputStream( inputFile ) ) );
doc = parser.getDocument();
top = doc.getDocumentElement();
docType = top.getNodeName();
context.log( "document type = '" + docType + "'" );
/*
* extract required fields
*/
list = top.getElementsByTagName( "TransactionID" );
if( list == null )
throw new Exception( "Missing TransactionID" );
node = list.item( 0 );
node = node.getChildNodes().item( 0 );
transactionID = Integer.parseInt( node.getNodeValue() );
/*
* get the fourdigit from the URL
*/
String query;
if( ( query = req.getQueryString() ) == null )
throw new Exception( "DB2XMLProvServlet: missing query string" );
if( ! query.startsWith( "fourdigit=" ) )
throw new Exception( "DB2XMLProvServlet: '" +
query + "': invalid query string" );
fourDigit = query.substring( query.indexOf( '=' ) + 1 );
context.log( "four digit = '" + fourDigit + "'" );
/*
* get the connection and create the statement
*/
conn = DriverManager.getConnection(
connectString, username, password );
stmt =
conn.prepareStatement( "SELECT RESPONSE " +
"FROM PHILSTEST WHERE FOURDIGIT = ? AND " +
"TRANSACTIONID = ?" );
/*
* populate and execute the query
*/
stmt.setString( 1, fourDigit );
stmt.setInt( 2, transactionID );
rs = stmt.executeQuery();
/*
* check to see whether we have a response to send
*/
if( rs.next() ) {
rr = (String) rs.getObject( 1 );
if( rr.length() > 0 ) {
sendResponse( resp, rr );
stmt.close();
conn.close();
return;
}
}
stmt.close();
/*
* read the input file to a string
*/
br = new BufferedReader( new FileReader( inputFile ) );
while( ( lineIn = br.readLine() ) != null )
sb.append( lineIn + "\n" );
br.close();
/*
* if response is still null then INSERT a
* new row in the table
*/
if( rr == null ) {
stmt =
conn.prepareStatement( "INSERT INTO " +
"PHILSTEST ( FOURDIGIT, TRANSACTIONID, " +
"REQUEST ) VALUES ( ?, ?, ? )" );
stmt.setString( 1, fourDigit );
stmt.setInt( 2, transactionID );
stmt.setString( 3, sb.toString() );
stmt.executeUpdate();
stmt.close();
conn.close();
return;
}
/*
* UPDATE the row with the response
*/
stmt =
conn.prepareStatement( "UPDATE PHILSTEST " +
"SET RESPONSE = ? WHERE FOURDIGIT = ? AND " +
"TRANSACTIONID = ?" );
stmt.setString( 1, sb.toString() );
stmt.setString( 2, fourDigit );
stmt.setInt( 3, transactionID );
stmt.executeUpdate();
}
catch( Exception e ) {
context.log( "DB2XMLProvServlet: " + e.toString() );
try {
resp.setStatus( ERR500, e.toString() );
}
catch( Exception f ) {
}
}
try {
if( inputFile != null )
inputFile.delete();
if( stmt != null )
stmt.close();
if( conn != null )
conn.close();
}
catch( Exception e ) {
}
}
private void sendResponse( HttpServletResponse resp, String response )
throws IOException {
PrintWriter pw = resp.getWriter();
resp.setContentType( "text/xml" );
pw.print( response );
pw.flush();
pw.close();
}
}
Copyright © 2001 by Phil Selby
All rights reserved internationally.