How do I discard a row from a ColdFusion query?

Question

Given a query (pseudo-code):

<cfquery name="myquery">SELECT * FROM stuff</cfquery>

How do I get rid of the first record? In this case, altering the SQL is not an option. I have tried: myquery.RemoveRows(0,1); but received an error:

No matching Method/Function for Query.REMOVEROWS(numeric, numeric) found

I'm on Railo 3 BTW

Answer 1

Lo and behold:

myquery.RemoveRow(1);

Does exactly what I wanted. Leave it to Railo to do things a little differently!

Answer 2

Can't think of a way offhand to remove a row from the original object. Two things I can think of are:

1. do a query of queries. That assumes you'd be able to identify the records you don't want and specify them in the WHERE.

2. construct a new query with queryNew(). Loop over the original query doing a querySetCell() into the new query for the records that you want. This functionality could be incorporated into a UDF pretty easily. In fact, stating that made me think to check cflib.org. See #3

3. Check cflib.org :) See http://www.cflib.org/udf/QueryDeleteRows

Answer 3

"RemoveRows" is actually a call to the underlying Java method, so you have to cast those numbers. like so:

myquery.RemoveRows(
    JavaCast( "int", 0 ) // starting row, zero-based
    ,JavaCast( "int", 1 ) // number to delete, returns error if too many
)

So probably the method is "int,int", and if you don't cast it, it looks like "numeric,numeric". One might argue that this is undocumented, but it's so succinct that you could (as I did) wrap it in a function, so that if it changes you just need to replace the contents of the function with an alternative workaround.

Answer 4

Railo has added removeRows, see here. My ACF code that uses this method now runs on Railo too, no changes.

With this, the Railo implementation now matches ACF. (Note also that the original Railo implementation was 0 based, while the new one and the ACF version are both 1 based.)