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:

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.

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.

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.