Calling A Stored Procedure In Java With A Blob Field

Here is the situation. You are writing a program in Java. You need to access data in a Microsoft SQL Server via stored procedure and the data has an XML field. A what field? SQL Server 2005 introduces a new datatype for XML. It is essentially a blob field that you can perform XML operations on. For the Java program though, it doesn’t really care about that functionality and can just access it to write it out to a file (or process it inline).

JDBC Support

First off you are going to need to check your JDBC driver documentation thoroughly for which operations it supports. There also seems to be some variation in the actual implementation. For example, I tried used Microsoft’s own JDBC driver in my project but it caused a strange SQL Server error to be thrown when writing back to an XML field. I could not get around it (at least not in a timely fashion) so I switched to using the JTDS JDBC driver (go open-source!).

The Stored Procedure

Let us say that the stored procedure in SQL Server looks something like this:

CREATE procedure [GetSomeData] (@NumberParameter int, @NumberParameter2 int=null)
AS
BEGIN

SELECT IDField, SomeText, LargeXMLField, DateTimeField
FROM dbo.SomeTable
WHERE @NumberParamter=NumberFieldID

END

Nothing major going on here, it just executes a SELECT statement against a table and returns four fields. The “LargeXMLField” column is the one we are looking at as a Blob (actually a Clob).

Writing The Code

The actual code to access the stored procedure should be nothing new and is not complicated at all. First we need to connect to the database in the standard fashion.

// Initialize the JDBC Driver
Driver driver = (Driver)Class.forName("net.sourceforge.jtds.jdbc.Driver").newInstance();
DriverManager.registerDriver(driver);

// Create The DB Connection
String dbUrl = "jdbc:jtds:sqlserver://[host]:[port]/[database]";
Connection conn = DriverManager.getConnection(dbUrl, "username", "password");

You will want to substitute real values in where the [host], [port] and [database] placeholders are in the database url.

Next we will use the CallableStatement object to execute the stored procedure.

// Prepare and call the stored procedure
CallableStatement proc = conn.prepareCall("{ ? = call GetSomeData(?, ?) }");

// Register the ResultSet
proc.registerOutParameter(1, java.sql.Types.OTHER);

// Register Input Parameters
proc.setInt("@NumberParameter", this.messageVersion);
// If you need to set a NULL parameter
proc.setNull("@NumberParameter2", java.sql.Types.INTEGER);

// Execute the stored procedure
proc.execute();

// If we have a ResultSet
if(proc.getMoreResults())
{
  ResultSet rs = proc.getResultSet();
  if(rs.next())
  {
    // Dump the XML message from the ResultSet BLOB field to the file
    InputStream in = null;
    OutputStream out = null;
    byte[] buf = new byte[1024];
    int len;

    // Open the XML Message (BLOB) field in the ResultSet
    Clob clob = rs.getClob("LargeXMLField");
    in = clob.getAsciiStream();

    // Write contents of the BLOB to the output file
    while((len = in.read(buf)) > 0) {
      out.write(buf, 0, len);
    }
  }
}

So you see we use the proc.getMoreResults() to iterate through multiple ResultSet objects that may be returned from a stored procedure. We then call proc.getResultSet() to retrieve the ResultSet that has our data. We fetch our data field as a Clob object and use it’s getAsciiStream() method to get an input stream to read from. After that reading from the stream and writing out to another stream is a straightforward task.