Using ColdFusion Query's Underlying Java Methods For Query Manipulation And Logic

Posted August 16, 2006 at 6:38 PM

Tags: ColdFusion

A while back, I was trying to help someone use a ColdFusion query within Java code. I didn't quite solve that problem, but as a result, I discovered (from other people's examples) that the ColdFusion query is built on top of the Java object: coldfusion.sql.QueryTable. As a result, there are some cool Java methods that are available as part of the standard ColdFusion query object.

I looked at the list of methods, but didn't really think much about it... until now. Turns out, there are some really useful methods. The one's that I can see uses for are:

QueryTable::IsFirst()
QueryTable::IsLast()
QueryTable::RemoveRows( intRowIndex, intRowCount )

QueryTable::IsFirst()

This one is fairly self explanatory; when you are looping through a query via CFLoop, you can call this method to determine if the current row is the first record being looped over. If you were to do this the old way, you might have:

 Launch code in new window » Download code as text file »

Using the Java method, it would look like this:

 Launch code in new window » Download code as text file »

The difference here is purely aesthetic. They do the same thing, but in my opinion, IsFirst() makes the code slightly more readable. In speed tests, I have found them both to run at the same speed. IsFirst() is every so slightly slower, but over thousands of iterations, I am talking like several milliseconds slower.

QueryTable::IsLast()

This is just like IsFirst(), only it checks the current row for being the last row to be looped over. If you were doing this the old way, you might have:

 Launch code in new window » Download code as text file »

Using the Java method, it would look like this:

 Launch code in new window » Download code as text file »

Again, the difference here is purely aesthetic. IsLast() is simply more meaningful and readable.

QueryTable::RemoveRows( intRowIndex, intRowCount )

This one is a real gem! It allows you delete a number of records from a given query. A seemingly simple task, think about how you would do this without this method? This simplest way to do this I think would be to do a ColdFusion query of queries and select every row that does not satisfy a particular condition:

 Launch code in new window » Download code as text file »

This would, in all practicality, delete any row where the ID equals 4. This however, is a fairly slow technique as it has to parse the SQL, run it, and create an entirely new query set. The other option would be to loop over a query and create a mirrored query, copying over only rows that do not satisfy a particular condition:

 Launch code in new window » Download code as text file »

This is a perfectly valid solution, but again, it can be very slow as it has to process, read, and set every value of the query except for the given records.

The other solution would be to use the RemoveRows() method. This takes two arguments: the index of a given row and the number of rows to delete. There are some caveats here: First, this is a Java record set, NOT a ColdFusion query object. Therefore, it is ZERO-BASED. The first record is at index ZERO, NOT one. Secondly, if you delete more rows than are available, you will throw an error.

To use this method to delete a record, you could do this:

 Launch code in new window » Download code as text file »

Assuming we only want to delete one record here, since ID is the primary key on this query, we loop through the query until we hit the proper record, then we delete it and break out of the loop. The down side here is that we only delete a single record (as opposed to all records that match a condition). The upside here is that in our best case scenario we loop through only one record and in our worst case scenario we loop through N records with minimal logic.

This is not the best solution, but it is pretty cool and fairly powerful. Where I really see this option shining is when you need to delete a given row such as the first or last row and therefore know the index. Basically, anytime you know the index of the row you need to delete, this is gonna be a sweet-ass solution.

So, this post is not about what is good or bad, but merely about your options. To learn more about the Java methods of the query, check out this site.

Download Code Snippet ZIP File

Post Comment  |  Ask Ben  |  Permalink  |  Other Searches  |  Print Page

• Add To Del.icio.us
• Add To Digg
• Add to Spurl.net
• Add to Wists
• Add to Simpy
• Add to Newsvine
• Add to BlinkList
• Add to Furl
• Add to reddit
• Add to FARK
• Add to BlogMarks
• Add to Yahoo! My Web
• Add to smarking
• Add to Ma.gnolia
• Add to Segnalo

Reader Comments

Have you done any comparisons to see how much faster a query of queries would be, when using it to delete rows? For example

<cfquery dbtype="qTest" name="qTest2">
SELECT *
FROM qTest
WHERE id <> 4
</cfquery>

Josh, I have not done the time comparison yet (as my lunch break was coming to an end... working for the man!) However, in past posts, I have found that doing a query of queries is MUCH MUCH slower when it comes to duplicating a query when compared with the Duplicate() method. I am carrying that logic over to RemoveRows() vs. Query of queries. But, like I said, I have not done any time trials yet.

This process doesn't work to delete more than one record by simply removing the break. It seems that the CurrentRow count shifts in the loop when you remove a record. A method that does work is:

<cfset LoopCount = 0>
<!--- Loop over query. --->
<cfloop query="qTest">
<!--- Check condition for deleting. --->
<cfif (qTest.id EQ 4)>
<!--- Delete this record. --->
<cfset qTest.DeleteRows(
JavaCast( "int", (qTest.CurrentRow - 1 - LoopCount) ),
JavaCast( "int", 1 )
) />
</cfif>
<cfset LoopCount = LoopCount + 1>
</cfloop>

Dan,

You are completely correct. If you do a traditional loop, the index may go out of bounds. However, if you loop through the query backwards, then this should never be an issue.

Good comment though!

I looked for DeleteRows() inside 6 & 7 and I think it was replaced by removeRows() !

CFQUERY Java Docs
http://www.geocities.com/empiricallyspeaking/JavaDocs_CFQUERY.html

Strangly DeleteRows() does work but only when WddxSerializer.id EQ 500

If I change that 500 which something else is breaks.

removeRows() is much more flexable

I will post a few example over at Off Road Coldfusion Java Hacks

I found a one line way to not only remove a row from a cfquery object but also the right row just like a SQL DELETE statement with the where clause. I think that was the missing link. I was just about to give up when
I found your post but it took me all day to find out the method does not
exist.

the following sample code works fine for deleting multiple records in the original query
looping over the array backwards works a treat and
using array notation in the second query keeps things simple

hope this helps someone,
cheers!

<cfloop index = "currentRow" from = "#yourQuery.recordCount#" to = "1" step = "-1">

<cfquery name="anotherQuery" datasource="#request.dsn#">
SELECT userID
FROM users
WHERE users.email = #yourQuery.email[currentRow]#
</cfquery>

<cfif (anotherQuery.recordCount NEQ 0)>
<!--- Delete this record. --->
<cfset yourQuery.removeRows(
JavaCast( "int", (currentRow - 1) ),
JavaCast( "int", 1 )
) />
</cfif>
</cfloop>

Thanks for using my removeRows() and not deleteRows()

You rock Ben! The backwards looping and .removeRows method just allowed me to remove a whole lot of bulky (and embarrassing) logic which took forever to execute. Thanks for another great post!

My pleasure dude.

Ummmm, what?!??!

Great info and code dude, thanks. I've always found looping queries in cfscript a bit clunky, with this info however, you can do something like this:

<cfscript>
qMyQry.afterLast();
do
{
qMyQry.previous();
// condition met...
qMyQry.RemoveRows(javacast('int',qMyQry.currentrow), javacast('int',1));
}while(not qMyQry.isFirst())
</cfscript>

Well, I prefer that anyway ;)

I'm an eejit, this is far better:

<cfscript>
while(qMyQry.Next())
{
if(qMyQry.GetString(sColName) EQ sCondition)
qMyQry.RemoveRows(javacast('int',qMyQry.getRow() -1), javacast('int',1));
}
</cfscript>

I'm an even bigger eejit, that won't work - must loop backwards:

<cfscript>
qMyQuery.AfterLast();
while(qMyQry.Previous())
{
if(qMyQry.GetString(sColName) EQ sCondition)
qMyQry.RemoveRows(javacast('int',qMyQry.getRow() -1), javacast('int',1));
}
</cfscript>

@Domonic,

Good samples. I will have to test that out. Thanks.

Thanks for this. It lead me to doing this:

http://murrayhopkins.wordpress.com/2008/10/23/looping-over-a-query-with-cfscript/

@Murray,

Glad to have inspired :)

Haha, I completely wrote it the long and ugly way, THEN thought to do a Google search and found your recommended faster way. Oh well, in the end, the code is better. :-) Thanks as always dude.

@Josh,

No problem my man :)

btw, removeRows helped me out today... :) thanks! and also, in the code above you have it as DeleteRows() not RemoveRows. the snippet file has it correct, its just not correct up above...

tw

The removeRows() function does not work for me.

When i go and output my query it still has the rows that i wanted deleted.

I dont see any typos in my code... not sure what is going on.

<cfloop query="getProductsForSearch">
<!--- Check condition for deleting. --->
<cfif getProductsForSearch.PageType EQ 'Product' AND getProductsForSearch.ProductLive NEQ "1">
<!--- Delete this record. --->
<cfset getProductsForSearch.RemoveRows(JavaCast( "int", (getProductsForSearch.CurrentRow - 1) ),JavaCast( "int", 1 )) />
</cfif>
</cfloop>

<cfoutput query="getProductsForSearch">
#getProductsForSearch.CurrentRow# -- #getProductsForSearch.PageType# - #getProductsForSearch.ProductLive#<br />
</cfoutput>

Does the query object have to have a primary key in it for removeRows() to work?

@Chad,

It shouldn't need a primary key because once the result set is created and wrapped in a ColdFusion query object, it is separated from the primary key concept (as far as I understand).

It's possible that since you are looping AND deleting the same query and same row, there is a threading issue? Perhaps you need to loop over the query backwards?

Post Comment  |  Ask Ben

Post A Comment

Please review the following issues:

Author Name:
Author Email:
Author Website:
Comment:	Formatting: <strong>bold</strong> <em>italic<em>
	Remember my information
	Subscribe to entry *
	Send me a copy of this comment (as part of the comment thread)

 

* If you subscribe, any new posts to this thread will be sent to your email address.