Friday, February 28, 2003

Turning a JDBC Result set into a ColdFusion query

Recent discussion on the CFCDev mailing list (at shows how to return a ColdFusion query object from a Java class using a JDBC result set (java.sql.ResultSet). The solution posed by both Brandon Purcell, and I was to pass your JDBC result set in to the constructor of the coldfusion.sql.QueryTable class. Joe Eugene tested the hypothisis, and asserts that it does work, but is slower then returning the result set, and navigating through using the methods of the ResultSet class.

The coldfusion.sql.QueryTable class comes with ColdFusion MX (this technique will only work on CFMX), and is located in the cfusion.jar file. You will need to add the cfusion.jar file to your class path before you can compile any java code that uses the QueryTable class (you don't need to worry about the classpath if you are accessing QueryTable via CFOBJECT, or CreateObject within CFML). More information can be found about the coldfusion.sql.QueryTable class here.

Some ColdFusion code to convert a result set to a Query object:

<cfset newQuery = CreateObject("java", "coldfusion.sql.QueryTable")>
<cfset newQuery.init( someObject.getResultSet() ) >

Some Java code to return a query from a java class (requres cfusion.jar in classpath)

import coldfusion.sql.QueryTable;
public class QueryUtil
  public static coldfusion.sql.QueryTable getColdFusionQuery(java.sql.ResultSet rs)
    return new coldfusion.sql.QueryTable(rs);