Generating a QueryTable succeeds in ColdFusion but fails in Java

Question

I have the Java code snippet:

import coldfusion.runtime.Struct;
import java.sql.ResultSet;
import java.sql.SQLException;
import oracle.jdbc.OracleCallableStatement;

public class CursorTest {
  public static Struct getCursor() throws SQLException {
    OracleCallableStatement statement = null;
    Struct variables = new Struct();
    // prepare statement with a cursor out parameter
    ResultSet results = statement.getCursor( 1 );
    variables.put ( "cursor", results );
    return variables;
  }
}

Including the cfusion.jar and the hotfix jars from the ColdFusion lib directoy and Oracle's ojdbc6.jar in the build path.

I can run it in ColdFusion:

<cfscript>
  vars   = createObject( 'java', 'CursorTest' ).getCursor();
  cursor = createObject( 'java', 'coldfusion.sql.QueryTable' )
             .init( vars.cursor )
             .firstTable();
  WriteDump( cursor );
  // close the statement and connection.
</cfscript>

This works and the QueryTable is created.

However, trying to move the generation of the QueryTable into the Java code:

import coldfusion.runtime.Struct;
import coldfusion.sql.QueryTable;
import coldfusion.sql.Table;
import java.sql.ResultSet;
import java.sql.SQLException;
import oracle.jdbc.OracleCallableStatement;

public class CursorTest {
  public static Struct getCursor() throws SQLException {
    OracleCallableStatement statement = null;
    Struct variables = new Struct();
    // prepare statement with a cursor out parameter
    ResultSet results = statement.getCursor( 1 );
    Table table = new QueryTable( results ).firstTable();
    variables.put ( "cursor", table );
    return variables;
  }
}

Fails to build with:

error: cannot access QueryTableWrapper
      Table table = new QueryTable( results ).firstTable();
                    ^
  class file for coldfusion.runtime.QueryTableWrapper not found

Now, the error is self explanatory and unpacking the Jar files shows that that file is not there ... however, I cannot find it in any of the other Jar files that are in the ColdFusion lib directory, ColdFusion's JRE directory or other Jars that might be on ColdFusion's class path.

Does anyone know where this file is located so I can include it on the build path or, alternatively, how ColdFusion manages to generate the QueryTable using createObject without that wrapper?

Answer 1

coldFusion.sql.QueryTable has multiple constructors:

• QueryTable()
• QueryTable( Query cfx )
• QueryTable( QueryTable rs )
• QueryTable( QueryTableWrapper qtWrapper )
• QueryTable( ResultSet rs )
• QueryTable( int rows )
• QueryTable( QueryTable rs, int maxrows )
• QueryTable( int rows, String collist )
• QueryTable( int rows, String[] cols )
• QueryTable( int rows, String[] cols, String[] sqlColTypeNames )
• QueryTable( int rows, String[] cols, String[] sqlColTypeNames, int[] sqlColTypes )

The first 7 constructors listed there all use, somewhere under the surface, QueryTableWrapper and fail in Java as the class cannot be found.

However, QueryTable( int rows, String[] cols, String[] sqlColTypeNames, int[] sqlColTypes ) does not use this wrapper.

So, while you cannot pass in a ResultSet, Query or an existing QueryTable and have it automatically parsed by the constructor, you can generate an empty QueryTable and then parse the ResultSet and add it row-by-row to the QueryTable like this:

final OracleResultSet results    = statement.getCursor( 1 );
final ResultSetMetaData metadata = results.getMetadata();
final int columns                = metadata.getColumnCount();
final String[] colNames          = new String[ columns ];
final String[] sqlColTypeNames   = new String[ columns ];
final int[] sqlColTypes          = new int[ columns ];
final boolean[] caseSensitive    = new boolean[ columns ];

for ( int i = 1; i <= columns; i++ )
{
  colNames[i-1]        = metadata.getColumnName( i );
  sqlColTypeNames[i-1] = metadata.getColumnTypeName( i );
  sqlColTypes[i-1]     = metadata.getColumnType( i );
  caseSensitive[i-1]   = metadata.isCaseSensitive( i );
}
final QueryTable table = new QueryTable(
  0,
  colNames,
  sqlColTypeNames,
  sqlColTypes
);
table.getMeta().setColumnCase( caseSensitive );

int rows = 0;
while ( results.next() )
{
  table.addRows( 1 );
  ++rows;
  for ( int c = 1; c <= columns; ++c )
  {
    Object value = null;
    switch( sqlColTypes[c-1] )
    {
      case OracleTypes.ARRAY:   value = results.getARRAY( c ).getArray();
                                break;
      case OracleTypes.VARCHAR: value = results.getString( c );
                                break;
      case OracleTypes.DATE:    value = results.getDate( c );
                                break;
      case OracleTypes.NUMERIC: value = results.getBigDecimal( c );
                                break;
      // Add other types
      default:                throw new UnsupportedOperationException( "Unable to handle: " + sqlColTypes[c-1] );
    }
    if ( results.wasNull() )
    {
      value = null;
    }
    table.setField( rows, c, value );
  }
}

The QueryTable.firstTable() can then be returned back to ColdFusion and used as if it was a query result generated by any of the built-in ColdFusion functions.