Turning a JDBC Result set into a ColdFusion query

coldfusiondatabases

Recent discussion on the CFCDev mailing list (at cfczone.org) 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);
  }
}




Related Entries

4 people found this page useful, what do you think?

 Download FuseGuard WAF for ColdFusion

Trackbacks

Trackback Address: 73/E5FAA3F2A09F64F3B0F99530DC8CFA44

Comments

On 01/10/2004 at 9:50:43 PM EST dan wrote:
1
You can pass your JDBC result set in to the constructor of the coldfusion.sql.QueryTable class and remain in coldfusion you don't need to make a .class file for that. Just create a object coldfusion.sql.QueryTable and use init(resultset) and you are done. You can also make a resultset and then create a CFQUERY with the returned data from a resultset.

On 03/23/2004 at 2:39:09 PM EST dave jones wrote:
2
Two comments/questions:

1. I've been able to successfully instantiate a QueryTable in Java by passing it a ResultSet. However, in my case this only works for a RS containing no more than 9 records. If the RS is 10 records or larger, the QT throws the following error:

java.lang.ExceptionInInitializerError: coldfusion.server.ServiceFactory$ServiceNotAvailableException: The Runtime service is not available.

Anyone know how to get around this?

2. I can successfully create a QueryTable in CF by passing it a ResultSet. However, what I really want is a Query. How do I get from QueryTable to Query?

On 07/23/2004 at 5:39:44 PM EDT dan wrote:
3
I posted a lot of example code and Q & A here at my DSNLESS JDBC Coldfusion site http://www.geocities.com/empiricallyspeaking/

On 10/30/2004 at 3:50:26 PM EDT Christopher Bradford wrote:
4
You can get from the QueryTable to a true CF Query like this:

Get your QueryTable as above:

<cfset newQuery.init( someObject.getResultSet() )>

Then get the first Table (coldfusion.sql.Table) from the QueryTable. A coldfusion.sql.Table is a CF Query:

<cfset myTrueQuery = newQuery.firstTable() >

You can test this:

<cfoutput>#IsQuery(myTrueQuery)#</cfoutput>

On 06/11/2005 at 6:06:46 AM EDT dan wrote:
5
All you is this <cfset newQuery = CreateObject("java", "coldfusion.sql.QueryTable").init(resultSet)>

IsQuery(newQuery) should return true

On 10/10/2006 at 2:15:14 PM EDT Bill Spratley wrote:
6
Grabbing a QueryTable from a JRUN datasource using parameterized queries (instead of <cfqueryparam>):

<cfset context = createObject("java","javax.naming.Context")> <cfset hashtable = createObject("java","java.util.Hashtable")> <cfset hashtable.put(Context.INITIAL_CONTEXT_FACTORY,"jrun.naming.JRunContextFactory")> <cfset hashtable.put(Context.PROVIDER_URL,"localhost:2902")> <cfset initialContext = createObject("java","javax.naming.InitialContext")> <cfset initialContext.init(hashtable)> <cfset datasource = initialContext.lookup("myDataSource")> <cftry> <cfset connection = datasource.getConnection()> <cfset preparedStatement = connection.prepareStatement("select firstName, lastName from users where userId = ?")> <cfset preparedStatement.setString(1,"XX4567")> <cfset query = createObject("java","coldfusion.sql.QueryTable").init(preparedStatement.executeQuery())> <cfset connection.close()> <cfcatch type="any"> <cftry> <cfset connection.close()> <cfcatch type="any"> </cfcatch> </cftry> <cfrethrow> </cfcatch> </cftry>

Post a Comment




  



Spell Checker by Foundeo

Recent Entries



foundeo


did you hack my cf?