Processing multiple sql statements with ColdFusion, JDBC and MS SQL - Part 1

In a previous entry, I talked about why certain MS SQL queries, containing multiple sql statements, result in an undefined query variable when used under ColdFusion 8.

Is it possible to process the results of multiple sql statements?
Yes. Technically, cfquery could return the results of all sql statements within the cfquery tags. But in its current form it does not. Whether or not it should provide that ability is another question altogether. But to explain how it might do this, let us take a look at running sql statements at a slightly lower level: using jdbc directly. After all that is what your typical ColdFusion query uses behind the scenes anyway. If you are already familiar with jdbc, feel free to skip ahead.

About JDBC terms
Since JDBC operates at a lower level, it is capable of returning detailed information about each sql statement executed. That information is generally referred to as a result. The result may be the number of rows affected or it may be an actual resultset. Resultsets are similar to a ColdFusion query object (ie rows and columns of data).

JDBC 101
With jdbc you can execute a basic sql statement using either a Statement or PreparedStatement object. Both act as a kind of wrapper, passing your sql statements and any parameters to the database. There are differences between the two classes, and also some details that I am glossing over, like the fact that they are interfaces and not concrete classes ;) But you can read more about that on your own.

For now I will focus on three (3) methods of the Statement interface. While all of them can be used to execute a sql statement(s), each is used for a different type of statement.

• Statement.executeQuery(...) is used to execute a sql statement that returns a single resultset. For example, a single SELECT statement.
  


• Statement.executeUpdate(...) is used to run sql statements that return nothing or return a row count only. Examples are INSERT, UPDATE, DELETE or DDL statements.
  


• Statement.execute(...) is used to run statements that may return multiple results. The results may be row counts, resultsets, or a combination of both.
  

Given that you can run almost any type of statement within a cfquery, or multiple statements, it seems likely that cfquery uses execute() internally. So that is what we will be using in our example.

JDBC 201
Though execute() is capable of capturing multiple results, the Statement object can only process one result at a time. So calling execute() runs all of the sql statements in the database, then positions the Statement object to process the first result. To process all of the results we must loop through them. Here are the methods we will use to accomplish this:

• Statement.execute(stringOfSqlStatements) runs the given sql statements against the target database. It returns true if the first result is a resultset. Otherwise, it returns false.
  


• Statement.getResultSet() returns the current resultset. If the current result is a a row count only, or there are no more results, it returns null.
  


• Statement.getUpdateCount() returns the current result as a row count. If the current result is a resultset, or there are no more results, it returns -1.
  


• Statement.getMoreResults() moves to the next result. It returns true if the next result is a resultset. If the next result is a row count only, or there are no more results, it returns false.
  

Phew! That is enough background for now. In Part 2 we will actually apply all of this good information and demonstrate how to use jdbc and ColdFusion to process the results of multiple sql statements.