Skip to main content
Ben Nadel at cf.Objective() 2012 (Minneapolis, MN) with: Daniel Heighton
Ben Nadel at cf.Objective() 2012 (Minneapolis, MN) with: Daniel Heighton

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

By
Published in — Comments (51)

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:

<!--- Loop over query. --->
<cfloop query="qTest">

	<!--- Check for first row. --->
	<cfif (qTest.CurrentRow EQ 1)>
		<!--- This is the first row. --->
	</cfif>

</cfloop>

Using the Java method, it would look like this:

<!--- Loop over query. --->
<cfloop query="qTest">

	<!--- Check for first row. --->
	<cfif qTest.IsFirst()>
		<!--- This is the first row. --->
	</cfif>

</cfloop>

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:

<!--- Loop over query. --->
<cfloop query="qTest">

	<!--- Check for last row. --->
	<cfif (qTest.CurrentRow EQ qTest.RecordCount)>
		<!--- This is the last row. --->
	</cfif>

</cfloop>

Using the Java method, it would look like this:

<!--- Loop over query. --->
<cfloop query="qTest">

	<!--- Check for last row. --->
	<cfif qTest.IsLast()>
		<!--- This is the last row. --->
	</cfif>

</cfloop>

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:

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

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:

<!--- Create the new query with proper columns. --->
<cfset qNew = QueryNew( qTest.ColumnList ) />

<!--- Loop over query. --->
<cfloop query="qTest">

	<!--- Check condition for deleting. --->
	<cfif (qTest.id NEQ 4)>

		<!---
			This is a good row, copy it over. Do this
			by loop over columns and adding to new query.
		--->
		<cfset QueryAddRow( qNew )>

		<!--- Loop over columns. --->
		<cfloop index="strColumn" list="#qTest.ColumnList#">

			<!--- Copy over cell value. --->
			<cfset qNew[ strColumn ][ qNew.RecordCount ] =
				qTest[ strColumn ][ qTest.CurrentRow ] />

		</cfloop>

	</cfif>

</cfloop>

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:

<!--- 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) ),
			JavaCast( "int", 1 )
			) />

		<!--- We only want to delete one record. --->
		<cfbreak />

	</cfif>

</cfloop>

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.

Want to use code from this post? Check out the license.

Short link: https://bennadel.com/204

Reader Comments

4 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>

15,902 Comments

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.

1 Comments

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>

15,902 Comments

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!

6 Comments

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.

1 Comments

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>

2 Comments

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!

11 Comments

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 ;)

11 Comments

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>

11 Comments

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>

14 Comments

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.

1 Comments

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

2 Comments

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>

15,902 Comments

@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?

15,902 Comments

@Craig,

I am not sure. I am not sure you would get any benefit from trying to do things at the Java level, especially when ColdFusion makes it so easy.

1 Comments

Can I assume that if I am looping over a query and during a loop iteration, remove the current row being processed the loop will skip a row like any normal for loop?

Example: I have a recordset of 4. On the 3rd iteration of the loop (record #3), I remove that row. Now my recordset is 3. So the loop finishes without processing that last record.

22 Comments

Quite possibly, easy to find out ;) If not, the looping backwards trick seems to be the slimmest way to do it.

15,902 Comments

@Sean,

Yeah, from what I remember (cause I think I tried that), if you remove the row mid-iteration, it will break the query loop (out of bounds). As @Dominic eludes, the easiest way to get around this typically is to loop backwards.

... of course, I am have not double-checked this, this is just what I think I remember happening.

9 Comments

I am trying to use QueryDeleteRows. SO i create a separate file and then on the calling lage calling the function. INstead I get the error message saying that function can not be declared more than once what do i do wrong

9 Comments

ot was actually ony defined once and it is outside of any loop . So i do not knopw what it is. And i ended up creating a CFC and re initiating the object

4 Comments

For the first time, i actually had a need to loop through a query (checks its validity against other data), and remove records from the query. One quick google search, and your blog answered my question! Awesome find!

7 Comments

Oh this is awesome... and I don't use that word casually.

I just used this logic to remove those pesky empty rows that show up on the last page of a CFGRID when the number of rows returned doesn't match up to your page size.

Very Cool!! Thanks.

15,902 Comments

@Mickey,

Awesome my man - glad to help. I haven't played around with these methods too much lately; you comment reminds me to look into these again, see if I can come up with some cool ways to leverage them.

12 Comments

I've got a unique issue. I've got a multiple select box that displays output using nested cfoutput groups. By the time I get to the innermost level, the groups work but I can't sort the data that is displayed as that is not one of the columns being sorted in the original source query (remember the order by clause has to match up with the cfoutput groups).

Has anyone used the underlying java methods to tackle something like this? That'd be a huge help. thanks,

roger

15,902 Comments

@Roger,

I've actually never used the Group attribute on the CFOutput tag (I think because it didn't also exist in the CFLoop tag and I always had nested CFOutput errors). But, I would think it would still follow the general ordering of the entire query?

What happens if you ORDER BY several columns to begin with?

1 Comments

Thanks for this Ben.

I'm using this in conjunction with your POI utility as I was getting back some empty rows.

IT's all good now.

12 Comments

Ben,

You are correct, it didn't need any extra java or cf code. I've got multiple nested output grouping and I just changed the order of the grouping and things worked out.

For those who're kinda green on the nested output grouping (like I was), make sure that the order by in the query is also changed when the order of grouping is changed.

As always, muchas gracias Ben, for all the cool stuff you put out here!

15,902 Comments

@Gary,

Awesome.

@Roger,

Ok cool - glad you got that sorted. The nested CFOutput thing never really jelled in my head. I believe there is an "enhancement request" for future versions of ColdFusion to add the Group attribute to the CFLoop tag so we can get away from nested CFOutput errors. I hope that happens.

@Ben,

I am not sure how Railo has implemented their query objects. It is still likely that they are underlying Java objects. If you want to take a look at the Railo's query objects make available, try outputting the hidden Java methods using something like this approach:

www.bennadel.com/blog/206-Finding-Available-Java-Constructors-Methods-For-ColdFusion-Objects.htm

That approach can be buggy sometimes; but, it can be interesting to see what's going on under the covers.

3 Comments

Thanks for that. I tested and found out that Railo Express (at least) has removeRow(int) not removeRows. And it isn't zero indexed. removeRow(3) actually removes row 3. This was my simple test

[code]
<cfset tQuery=querynew("col1")>

<cfloop from="1" to="10" index="x">
<cfset queryaddrow(tQuery,1)>
<cfset querysetcell(tQuery,'col1',x)>
</cfloop>

<cfdump var="#tQuery#">

<cfset tQuery.removeRow(javacast('int',3))>

<cfdump var="#tQuery#">
[/code]

I see a cross server component in my near future.

15,902 Comments

@Ben,

Nice detective work. Just goes to show you that each CFML engine is going to have its own caveats. Of course, the underlying Java implementation is not a "documented" thing; so, it is understandable that they have different implementations.

1 Comments

Works fine with cfquery and Query objects. But i tried to use it with a Query object saved with memcached2 and when i remove a row, always delete the last row.

I think it's a problem with memcached and its way to save the objects.

¿Do you have worked with memcached objects?

Thanks.

1 Comments

Thanks for the reverse-loop tip - got me over exactly the same issue I was having with only the first row being deleted.

1 Comments

Hi Ben

I tried using deleteRows() and removeRows() in CF9 like such

< cfset qTest.removeRows(qTest,0,8) >

and I'm getting a "method was not found" error. Any ideas why?

Oh my chickens, this post is old!
I believe in love. I believe in compassion. I believe in human rights. I believe that we can afford to give more of these gifts to the world around us because it costs us nothing to be decent and kind and understanding. And, I want you to know that when you land on this site, you are accepted for who you are, no matter how you identify, what truths you live, or whatever kind of goofy shit makes you feel alive! Rock on with your bad self!
— Ben Nadel