CFQuery and the underlying Java Objects

On February 1, 2008, in ColdFusion, by Anuj Gakhar

Everything that Coldfusion does, is bascially Java under the hood, this is something almost everyone using Coldfusion would know. I have always been curious to find out more about this (basically to find out what more we can do by making use of this). Recently, I read some blog posts on how we can use Java Reflection to find out more about what Java object, each CF object is mapped to under the hood. So, I decided to do some of this as well and I choose cfquery.

I first created a very simple query like below :-

[xml]<cfquery name="myqry" datasource="myDS">
select top 1 orderid from tblorder
</cfquery>[/xml]

Next, I wanted to find out what Java object is this. This was pretty straight forward :-

[xml]<cfdump var= "#myqry.getClass().getName()#"> [/xml]

That gives me coldfusion.sql.QueryTable , so I know what object this is internally.

So, in order to find out more about this, I did this :-

[xml]
<cfset sql = createObject("java","coldfusion.sql.QueryTable")>
<cfdump var = "#sql#">
[/xml]

That gave me some interesting information. There are a lot of available functions in there and I still havent looked into all of those but the 2 that caught my attention were these.

findColumn() and getShallowCopy()

So, now, remember my original query (myQry) , this is where it comes useful. In order to find out if a column exists in my query or not (really useful when folks sometimes run Select * …… queries) , I can easily do this

[xml]
<cfdump var = "#myqry.findColumn(‘orderid’)#"> – returns 1 (because this column exists in my query)
<cfdump var = "#myqry.findColumn(‘somecolumn’)#"> – returns 0 (because this doesnt)

[/xml]

Now, thats great. I think this can be very useful. There are always situations where you write a big query and when you try to output the resultset, you end up going to the database to look for what the exact column names were.

The next interesting thing I found was this :-

[xml]<cfdump var = "#myqry.getShallowCopy()#">[/xml]

That gives some useful information as well.

Here is a dump of what it looks like. It gives you the executiontime of the query and tells you if the query is cached or not apart from giving you the actual SQL statement and the resultset.

Dump of getShallowCopy

I am sure there must be lot more to find out here, but as soon as I find more, I can post here.

Lastly, because a lot many people have been playing with CF underlying Java objects, its very much possible that this post is completely out-dated today and everyone knows this already and its just that I am too late to find this out. If thats the case, I will just keep it for myself to read in future 🙂

This code was tested on Coldfusion 8, I assume it would work on CF7 and MX as well, but havent tested it myself.

Tagged with:  

8 Responses to CFQuery and the underlying Java Objects

  1. todd sharp says:

    That Query dump is the standard query dump in CF 8.

    Also, why not just do listContainsNoCase(myqry.columnList, ‘somecolumn’)??

  2. Radek says:

    Just use java.sql.ResultSet to get more info 🙂

  3. Anuj Gakhar says:

    @Todd, Agreed. We could use that listContainsNoCase but I just didnt know that there was another way of doing it which is why I get a bit over-joyous.

    @Radek, yes there is probably a lot more that can be done.

  4. Ah great post!

    I’ve been using myqry.isFirst() and myqry.isLast() for ages, I find it much cleaner than if myqry.currentRow EQ 1 etc

    @Todd the same would hold for findColumn(), just seems a bit cleaner to type and to read.

    Of course listContainsNoCase et al is great for everyone to use, but if you dig a little further you find these jems!

    My only concern is how that would go if you switched to Railo or Blue Dragon etc. Any official ‘cfml’ should be fine, but what about these Java methods?

  5. Anuj Gakhar says:

    @Michael, That would probably be an issue if someone using CF moves to the likes of Blue Dragon, but I havent seem anyone do that till date. However, Since Adobe doesnt officially support the use of these little gems, the only sensible thing is to use it at your own risk.

  6. […] area. Anuj Gakhar shows just how easily the Java underbelly of ColdFusion can be inspected with CFQuery and the underlying Java Objects, and Rick Osborne has launched a series of tutorials (currently numbering four) on generating […]

  7. Gonxo says:

    no working on coldfusion 6 and 7

  8. Anuj Gakhar says:

    @Gonxo, it should work . whats the error you getting ?

Leave a Reply to Radek Cancel reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

© 2011 Anuj Gakhar
%d bloggers like this: