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

In Part 1 we reviewed how jdbc objects can be used to execute and process the results of sql statements. In Part 2 we will apply this information in a ColdFusion example. Though the example uses an MS SQL database, it could also be used with other databases, with a few minor modifications.

First let us create a few test tables and populate them with sample data. Since this example is really about jdbc, I chose the wildly imaginative and descriptive table names: SourceTable and DestinationTable.

--   create test tables
CREATE TABLE DestinationTable (
  DestinationTableID int identity(1,1),
  Title varchar(100) NULL,
  LastModified datetime NOT NULL
)

CREATE TABLE SourceTable (
  SourceTableID int identity(1,1),
  Title varchar(100) NULL,
  DateModified datetime NOT NULL
)

--   populate table with sample data
INSERT INTO SourceTable (Title, DateModified)
SELECT     'Transact-SQL',   getDate() UNION
SELECT     'ColdFusion 8 (CFWACK)',   getDate() UNION
SELECT     'iText in Action',   getDate()

Before we can run any sql statements, we must first open a connection to the database. MS SQL runs on port 1433 by default, but your database settings may differ.

<cfscript>
  Class = createObject( "java", "java.lang.Class" );
  Class.forName( "macromedia.jdbc.sqlserver.SQLServerDriver" );
  manager = createObject( "java", "java.sql.DriverManager" );
  connection = manager.getConnection(
                  "jdbc:macromedia:sqlserver://localhost:1433;databaseName=YourDatabaseName;",
                  "YourDatabaseUserName",
                  "YourDatabasePassword" );
</cfscript>

Then we construct a string containing a few sql statements. The statements I selected were only chosen to demonstrate the concept. So they are rather meaningless ;) Next we obtain a Statement object from our connection. Then use the Statement to execute our sql string and get information about the first set of results.

<cfscript>
 sqlToExecute = " SET NOCOUNT OFF; "
   & " INSERT INTO DestinationTable ( Title, LastModified ) "
   & " SELECT  TOP 1 Title, DateModified FROM SourceTable WHERE SourceTableID = 2; "
   
   & " SELECT SCOPE_IDENTITY() AS DestinationTableID; "

   & " SELECT SourceTableID, Title, DateModified FROM SourceTable; ";

  statement = connection.createStatement();
  isResultSet = statement.execute(sqlToExecute);
  rowCount = statement.getUpdateCount();
  results = arrayNew(1);
</cfscript>

Now you may have noticed the sql string above starts with SET NOCOUNT OFF. In my tests, the CF8 macromedia.jdbc.sqlserver.SQLServerDriver did not behave correctly unless I used set nocount. I suspect this may be a bug, but am not positive. I posted a test case in a previous entry. So if you happen to be bored and feel like running it with CF8, let me know if you get the same results. Anyway, back to the example.

We are now ready to process the results using a do / while loop. Inside the loop, we check if the current result is a row count or a resultset and save it accordingly. Then move the next set of results. The loop continues until there are no more results to process. According to the java API, there are no more results when: getMoreResults() returns false AND getUpdateCount() == -1.

<cfscript>
  do {

      if (isResultSet) {

          // process and store the resultset ..
        
      }  
      // this is a row count only
      else if (rowCount  >= 0) {

          // store the row count ..

      }
    
      // move to the next result      
      isResultSet = statement.getMoreResults();
      rowCount = statement.getUpdateCount();
  }
  // continue while there are still results to process
  while (isResultSet OR rowCount GTE 0);

  // ALWAYS close connections when finished!
  statement.close();
  connection.close();
</cfscript>

To save the resulsets, we simply loop through them and copy the values into a ColdFusion query object. It is not that different from looping through a CF query dynamically. The main difference is that jdbc does not provide a columnList variable to loop through. So you must construct a list, or array, of column names from the resultset metadata.

<cfscript>
  // get the current resultset and metadata
  resultset = statement.getResultSet();
  metadata = resultset.getMetaData();
  columnCount = metadata.getColumnCount();
  columnNames = arrayNew(1);

  // construct an array of query column names
  for (col = 1; col LTE columnCount; col = col + 1) {
   arrayAppend(columnNames, metadata.getColumnName( col ));
  }

  // convert the resultset to a CF query
  row = 0;
  rsQuery = queryNew( arrayToList(columnNames) );
  while (resultset.next()) {
   row = queryAddRow( rsQuery, 1 );
   for (col = 1; col LTE columnCount; col = col + 1) {
    rsQuery[ columnNames[col] ] [ row ] = resultset.getObject( javacast("int", col) );
   }
  }
 
 resultset.close();

  // store the current results
  data = structNew();
  data.recordCount = row;
  data.isQuery = true;
  data.query = rsQuery;
  arrayAppend( results, data );
</cfscript>

The final result is an array of structures that should look like the image below. Each item in the array is a structure containing three elements:

• isQuery (true/false)


• recordCount (query record count or number of rows affected)


• query (a query object or an empty string)

So there you have it. A basic example of using jdbc. Now, I am not suggesting you should start using this method instead of cfquery. Cfquery has a lot of advantages such as its simplicity, caching, connection pooling, etcetera. But if you ever do need to use jdbc directly, this simple example should give you a starting point.


Complete Code

<cfscript>
   // database settings
   dbIPAddress = "127.0.0.1";
   dbPortNumber = "1433";
   dbUsername = "username";
   dbPassword = "password";
   dbName = "YourDatabaseName";
  
   //     open a connection to the database
   Class = createObject( "java", "java.lang.Class" );
   Class.forName( "macromedia.jdbc.sqlserver.SQLServerDriver" );
   manager = createObject( "java", "java.sql.DriverManager" );
   connectionURL = "jdbc:macromedia:sqlserver://"& dbIPAddress &":"& dbPortNumber &";databaseName="& dbName &";";
   connection = manager.getConnection( connectionURL, dbUsername, dbPassword );
                  
   //     construct a string with a few meaningless sql statements
   sqlToExecute = " SET NOCOUNT OFF; "
           & " INSERT  INTO DestinationTable ( Title, LastModified ) "
           & " SELECT  TOP 1 Title, DateModified FROM SourceTable WHERE SourceTableID = 2; "
           & " SELECT  SCOPE_IDENTITY() AS DestinationTableID; "
           & " SELECT  SourceTableID, Title, DateModified FROM SourceTable; ";

   //     run the sql statments and get the first results
   statement = connection.createStatement();
   isResultSet = statement.execute(sqlToExecute);
   rowCount = statement.getUpdateCount();

   // construct an array for storing the results
   results = arrayNew(1);
  
   do {
       // the current result is a resultset   
       if (isResultSet) {

           // get the resultset and metadata
           resultset = statement.getResultSet();
           metadata = resultset.getMetaData();
           columnCount = metadata.getColumnCount();
           columnNames = arrayNew(1);
          
           // construct an array of query column names
           for (col = 1; col LTE columnCount; col = col + 1) {
               arrayAppend(columnNames, metadata.getColumnName( col ));
           }
          
           // convert the resultset to a CF query
           row = 0;
           rsQuery = queryNew( arrayToList(columnNames) );

           // for each row of data ..
           while (resultset.next()) {
               row = queryAddRow( rsQuery, 1 );
               // copy the values for each column into the CF query object
               for (col = 1; col LTE columnCount; col = col + 1) {
                   rsQuery[ columnNames[col] ] [ row ] = resultset.getObject( javacast("int", col) );
               }
           }
          
           // close the resultset to release the resources
           resultset.close();
          
           // store the results
           data = structNew();
           data.recordCount = row;
           data.isQuery = true;
           data.query = rsQuery;
           arrayAppend( results, data );

       }   
       // this is a row count only
       else if (rowCount  >= 0) {

           // store the current row count
           data = structNew();
           data.recordCount = statement.getUpdateCount();
           data.isQuery = false;
           data.query = "";
           arrayAppend( results, data );
       }
      
       // get the next set of results       
       isResultSet = statement.getMoreResults();
       rowCount = statement.getUpdateCount();
   }
   // while there are still results to process
   while (isResultSet OR rowCount GTE 0);

   // ALWAYS close connection objects!
   statement.close();
   connection.close();
</cfscript>

<!--- display the results --->
<cfdump var="#results#">