How does one convert from a Java resultset to ColdFusion query in Railo?

Question

The following works fine in CFMX 7 and CF8, and I'd assume CF9 as well:

<!--- 'conn' is a JDBC connection --->
<cfset stat = conn.createStatement() />
<cfset rs = stat.executeQuery(trim(arguments.sql)) />

<!--- convert this Java resultset to a CF query recordset --->
<cfset queryTable = CreateObject("java", "coldfusion.sql.QueryTable")>
<cfset queryTable.init(rs) >
<cfset query = queryTable.FirstTable() />

This creates a statement using a JDBC driver, executes a query against it, putting it into a java resultset, and then coldfusion.sql.QueryTable is instantiated, passed the Java resulset object, and then queryTable.FirstTable() is called, which returns an actual coldfusion resultset (for cfloop and the like).

The problem comes with a difference in Railo's implementation. Running this code in Railo returns the following error: No matching Constructor for coldfusion.sql.QueryTable(org.sqlite.RS) found.

I've dumped the Railo java object, and don't see init() among the methods. Am I missing something simple? I'd love to get this working in Railo as well.

Please note: I am doing a DSN-less connection to a SQLite db. I understand how to set up a CF datasource. My only hiccup at this point is doing the translation from a Java result set to a Railo query.

Littlejohn

This may be a dumb question - but why don't you use a regular ColdFusion data source and <cfquery> to create a query result? – Silvester

See: coldfusionjedi.com/index.cfm/2009/9/24/… – Silvester

That is probably because QueryTable is an interface in Railo. So you cannot instantiate it. (It is a concrete class in Adobe CF). I am not sure what concrete class Railo uses for queries, or if it is similar to QueryTable in Adobe CF. But, as mentioned, is there is a reason you cannot use a regular query? – Bohemianism

I already know how to set up a ColdFusion datasource. I know how to load the sqlitejdbc.jar in the classes folder and make it an available 'Other' datasource in ColdFusion, and I know how to set up the connection string to connect to it. I am the developer of the SqliteCFC project sqlitecfc.riaforge.org and I'm doing DSN-less creation of SQLite dbs. I find that everything works in CF or Railo, except the actual running of queries. So that explains why I'm not using a CF datasource. What I need to know is how Railo constructs a query from a Java resultset. – Littlejohn

The railo devs are pretty active over here groups.google.com/group/railo - it may be a better question for them – Essa

@Shawn - Makes sense. (Not everyone has a good reason for using them though ;) But glad you found the "Railo" version of the concrete class. Good to know. – Bohemianism

Answer 1

Looking at the Railo source code, I see that railo.runtime.type.QueryImpl might fit your needs. It implements railo.runtime.type.Query and accepts a ResultSet in its constructor, but it does not seem to implement QueryTable.

If that's the right class, you'll want to pass in a ResultSet and a string for the query name, as it doesn't have a constructor that only takes a ResultSet.

Skaw

That did it. The resulting CFML for Railo is: <cfset queryTable = CreateObject("java", "railo.runtime.type.QueryImpl") /> <cfset query = queryTable.init(rs,"queryTable") /> – Littlejohn

Thats the beauty of open source. I have looked at the Railo source code to also see how they generate PDFs using the cfdocument tag (it turns out Railo uses pdf4ml). – Untouched