Using CLOBs with DB/2

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, 2002, 2003, 2004, 2005, 2006, 2007, 2008, 2009, 2010, 2011, 2012, 2013, 2014, 2015 by Phil Selby
All rights reserved internationally.