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

Posted August 16, 2006 at 6:38 PM by Ben Nadel

Tags: ColdFusion

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?

Thanks very much for this, exactly what I needed and had been struggling with all afternoon.

Can we write queries in java, or be more aware of data caching/freshness?

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

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.

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

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

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

@Erik,

This happens when you have a ColdFusion function defined more than one times within the same context.

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

Very Very Very Great. Thanks.

Brazillll.

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!

@Craig,

Awesome :)

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.

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

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

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

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.

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!

Hi Ben, Im using Railo and the removeRows() call doesnt seem to work. Am I right in assuming this is a CF8/9 only solution?

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

http://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.

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.

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

Ben!
Got time to implement in my app.

My good luck is reverse loop working fine where forward loop is failing to delete multiple records due to index compatible issue(Java and ColdFusion).

Thanks Ben! Updated working code of mine at
http://raghuramcf.blogspot.com/

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.

Try with reverse looping as i did in http://raghuramcf.blogspot.com/. It may work for you.

Thanks,
Raghuram Reddy Gottimukkula
Adobe Certified ColdFusion Developer
Hyderabad India

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

Post A Comment

Comment Etiquette: Please do not post spam. Please keep the comments on-topic. Please do not post unrelated questions or large chunks of code. And, above all, please be nice to each other - we're trying to have a good conversation here.

Please review the following issues:

Author Name:
Author Email:
Author Website:
Comment:	Supported HTML tags for formatting: <strong>bold</strong>   <em>italic</em>   <code>code</code>
	Remember my information
	Subscribe to comments
	Send me a copy of this comment