Ben Nadel
On User Experience (UX) Design, JavaScript, ColdFusion, Node.js, Life, and Love.
I am the chief technical officer at InVision App, Inc - a prototyping and collaboration platform for designers, built by designers. I also rock out in JavaScript and ColdFusion 24x7.
Meanwhile on Twitter
Loading latest tweet...
Ben Nadel at Scotch On The Rock (SOTR) 2010 (Brussels) with:

Passing Arrays By Reference In ColdFusion - SWEEET!

By Ben Nadel on
Tags: ColdFusion

I happened upon this discovery last night and I am very excited about it. Yes, learning IS fun and this goes to show you have important messing around with code is. For those of you who have been programming in ColdFusion for a while, you know that complex structures such as structures, components, and Java objects are passed around by reference and simple values like strings, dates, numbers, and even arrays are passed by value. For values that are passed by value, you know that if you pass them to a function for alteration, you MUST past the new value back otherwise the changes will be lost.

Let's explore this idea. First, I am going to create an array of girls:

  • <!--- Define the array. --->
  • <cfset arrGirls = ArrayNew( 1 ) />
  •  
  • <!--- Set some values. --->
  • <cfset arrGirls[ 1 ] = "Sarah" />
  • <cfset arrGirls[ 2 ] = "Libby" />
  • <cfset arrGirls[ 3 ] = "Mary-Kate" />
  • <cfset arrGirls[ 4 ] = "Ashley" />

Now that we have that set up, I am going to define a ColdFusion function that will take that array and alter it by adding some girls to it:

  • <cffunction
  • name="AddGirls"
  • returntype="void"
  • output="false"
  • hint="Adds some girls to the passed in array.">
  •  
  • <!--- Define arguments. --->
  • <cfargument name="Array" type="array" required="true" />
  •  
  • <!--- Add some girls to the array. --->
  • <cfset ArrayAppend( ARGUMENTS.Array, "Heather" ) />
  • <cfset ArrayAppend( ARGUMENTS.Array, "Azure" ) />
  • <cfset ArrayAppend( ARGUMENTS.Array, "Myriam" ) />
  • <cfset ArrayAppend( ARGUMENTS.Array, "Lori" ) />
  •  
  • <!--- Return out. --->
  • <cfreturn />
  • </cffunction>

Now, if I CFDump out the initial array, you will see that it has the first 4 girls:


 
 
 

 
CFDump 1 - Arrays Passed By Refrence in ColdFusion  
 
 
 

Now, I am going to pass the array to the altering function:

  • <!--- Add some girls! --->
  • <cfset AddGirls( arrGirls ) />

One might hope that when this array gets passed to the function, AddGirls(), that it is passed by reference. If that were the case, you would see the four additional girls in the next CFDump:


 
 
 

 
CFDump 2 - Arrays Passed By Refrence in ColdFusion  
 
 
 

As you can see, however, the only girls that show up in this last CFDump were the original four girls. That is because the array is passed by value. In the function, a complete copy of the array was altered and the original was never changed.

But, it's time to rock ... your ... world, baby-oh-baby!

First, let's take a look at the function, AddGirls(). Look at how it actually adds girls to the given array. It uses the ArrayAppend() ColdFusion function. Look closely. That function is NOT returning any value. That means that for that function call, the array IS getting passed by value. This might seem insignificant, but it is the clue needed to solve the "pass by reference" mystery.

If you look at what the ArrayAppend() method takes, it takes two arguments. At first glance, you might think it takes an array as the first argument and the value as the second argument. This is incorrect! If you look at the Java implementation of the method, the first argument is NOT an array. It is, in fact, an instance of java.util.List. If you look at what a List is in Java 2, you will see that it is an interface definition for a family of collection objects. This is "base class" of object, if you will. What that means is that ArrayAppend() is expecting ANY object that implements the interface java.util.List.

Ok, so how did I make that jump? Well, for one, that is part of what interfaces are used for (polymorphism??). But, if you just look at what a ColdFusion array is underneath the surface, you will see that it is in fact a Java object of type coldfusion.runtime.Array. Now, how does an object of type coldfusion.runtime.Array pass for an object of type java.util.List? Because coldfusion.runtime.Array implements the java.util.List interface.

Ladies and gentlemen, this is HUGE. It basically means that we can treat anything that implements java.util.List AS AN ARRAY! What? Get out of here with that! No - it's true.

Let's explore...

If you look at the classes the implement the java.util.List interface, the first one that pops out (at least it did for me) is the class java.util.ArrayList. It just feels right, doesn't it? Let's recreate our first example, except instead of declaring a standard ColdFusion array, let's create a Java ArrayList:

  • <!--- Define the array AS AN ARRAY LIST. --->
  • <cfset arrJavaGirls = CreateObject(
  • "java",
  • "java.util.ArrayList"
  • ).Init() />
  •  
  • <!--- Set some values. --->
  • <cfset arrJavaGirls[ 1 ] = "Sarah" />
  • <cfset arrJavaGirls[ 2 ] = "Libby" />
  • <cfset arrJavaGirls[ 3 ] = "Mary-Kate" />
  • <cfset arrJavaGirls[ 4 ] = "Ashley" />

I am renaming the array "arrJavaGirls" so you don't think I am trying to fool you somehow. When we dump out this object, you will see that in fact, it IS acting just like any ColdFusion array would:


 
 
 

 
CFDump 3 - Arrays Passed By Refrence in ColdFusion  
 
 
 

Notice that I can use the java.util.ArrayList just as if it were a regular indexed array. I set values using array notation. In fact, all ColdFusion array manipulation functions will work on it. Here are a few to demonstrate:

  • <!--- Append a value. --->
  • <cfset ArrayAppend( arrJavaGirls, "Kimmie" ) />
  •  
  • <!--- Prepend a value. --->
  • <cfset ArrayPrepend( arrJavaGirls, "Christina" ) />
  •  
  • <!--- Delete a value. --->
  • <cfset ArrayDeleteAt( arrJavaGirls, 4 ) />
  •  
  • <!--- Insert a value. --->
  • <cfset ArrayInsertAt( arrJavaGirls, 4, "Jo" ) />

CFDumping out the array you will see that it acts exactly like a standard ColdFusion array.


 
 
 

 
CFDump 4 - Arrays Passed By Refrence in ColdFusion  
 
 
 

Do you believe me? Implementing the java.util.List interface is all we needed. While I do not show it here, I have also testing things like ArrayAvg(), ArraySum(), ArrayMin(), and ArrayMax() and they ALL work. Now, how does this work with our function above, AddGirls()? Let's give it a go.

  • <!--- Add some girls! --->
  • <cfset AddGirls( arrJavaGirls ) />

Notice that I am NOT expecting any return value. I am passing the array as the only action and hoping that it is used by reference. And, CFDumping the array value now:


 
 
 

 
CFDump 5 - Arrays Passed By Refrence in ColdFusion  
 
 
 

... YOU WILL SEE THAT THE ORIGINAL ARRAY WAS UPDATED! Yes, that means the array WAS passed by reference and NOT by value. Not only that, look at the argument in the AddGirls() method:

  • <!--- Define arguments. --->
  • <cfargument name="Array" type="array" required="true" />

It is expecting a object of type "array." This just goes to show that our java.util.ArrayList is a VALID ColdFusion array. If it looks like an array, smells like an array, and walks like an array - it's an array. Just to drive the point home, let's perform perhaps the most popular action on an array: Looping over it and outputting values:

  • <!--- Loop over array. --->
  • <cfloop
  • index="intI"
  • from="1"
  • to="#ArrayLen( arrJavaGirls )#"
  • step="1">
  •  
  • <!--- Output indexed value. --->
  • #intI# : #arrJavaGirls[ intI ]#<br />
  •  
  • </cfloop>

You will see that this gives us the following list, just as we would expect an array to do:

1 : Christina
2 : Sarah
3 : Libby
4 : Jo
5 : Ashley
6 : Kimmie
7 : Heather
8 : Azure
9 : Myriam
10 : Lori

The only reason that you know that this is not a standard ColdFusion array is because of the array declaration. Everything else acts exactly the same.... well, not exactly the same. This object IS PASSED BY REFERENCE.

Now, just as a test, I wonder how this affects performance? My gut feeling is that passing an array by reference is going to be faster than constantly duplicating array values. But, on the other hand, there is an overhead to instantiating and passing around a component. Let's see it in action.

For this test, we have to define another function that returns an array. Since ColdFusion arrays are inherently passed by value, we need a function that will update the passed-in array and then return the updated array:

  • <cffunction
  • name="AlterGirls"
  • returntype="array"
  • output="false"
  • hint="Adds some girls to the passed in array and pass it back.">
  •  
  • <!--- Define arguments. --->
  • <cfargument name="Array" type="array" required="true" />
  •  
  • <!--- Add some girls to the array. --->
  • <cfset ArrayAppend( ARGUMENTS.Array, "Heather" ) />
  • <cfset ArrayAppend( ARGUMENTS.Array, "Azure" ) />
  • <cfset ArrayAppend( ARGUMENTS.Array, "Myriam" ) />
  • <cfset ArrayAppend( ARGUMENTS.Array, "Lori" ) />
  •  
  • <!--- Return altered array. --->
  • <cfreturn ARGUMENTS.Array/>
  • </cffunction>

As you can see, this one is basically the same as our first function. The only differences are that this one has a return type of "array" and actually returns the altered array. Now, for ColdFusion arrays, those are duplicates being passed around. For the java.util.ArrayList, those are references being passed around. Now, onto the testing:

  • <!--- Set up the iterations. --->
  • <cfset intIterations = 10000 />
  •  
  • <!--- Test the standard array calls. --->
  • <cftimer label="Test ColdFusion Array" type="outline">
  •  
  • <!--- Loop for speed testing. --->
  • <cfloop index="intI" from="1" to="#intIterations#" step="1">
  •  
  • <!--- Alter the array. --->
  • <cfset arrGirls = AlterGirls( arrGirls ) />
  •  
  • </cfloop>
  •  
  • <!--- Output final size. --->
  • Final Size: #ArrayLen( arrGirls )#
  •  
  • </cftimer>
  •  
  •  
  • <!--- Test the java.util.ArrayList calls. --->
  • <cftimer label="Test java.util.ArrayList" type="outline">
  •  
  • <!--- Loop for speed testing. --->
  • <cfloop index="intI" from="1" to="#intIterations#" step="1">
  •  
  • <!--- Alter the array. --->
  • <cfset AlterGirls( arrJavaGirls ) />
  •  
  • </cfloop>
  •  
  • <!--- Output final size. --->
  • Final Size: #ArrayLen( arrJavaGirls )#
  •  
  • </cftimer>

Holy crap! The test was insane! For 10,000 iterations, the ColdFusion array test took a draw-dropping 292,087 ms. The java.util.ArrayList test took a sleek, sexy 281 ms. Grabbing the calculator, I see that this means that the java.util.ArrayList performs in .00096 percent of the time that the ColdFusion array took. I don't think I have ever seen such a HUGE performance difference in all my testing in ColdFusion.

So, to sum up:

Test ColdFusion Array

Time: 292,087 ms
Final Size: 40,004 items

Test java.util.ArrayList

Time: 281 ms
Final Size: 40,010 items

I hope this has rocked your world the way it has rocked mine.




Reader Comments

Just to let you know, Ben, you could try mucking around with Struct as well - anything that implements the java.util.Map interface can be treated as a Struct.

Reply to this Comment

It makes sense that a list (which is a String) would be considerable slower than Java array. Concatenating strings in Java is very slow when the string gets large in size.

I wonder if Adobe will eventually change the way the Array works. I wonder if this has anything to do w/Arrays in Java starting at zero (0) and Arrays in CF starting at one (1.)

Reply to this Comment

Ashwin,

That is good to now. Structs are pretty darn cool already... but a little bit of experimentation never hurts :)

Dan,

I am not sure if the indexing is going to matter. When you attempt to use the java object as a ColdFusion array, it seems to translate the indexes for you. Likewise with a string. In Java, you can get the first character using string.CharAt( 0 )... however, when you treat the same object like a ColdFusion string, the first letter is at index 1: Mid( string, 1, 1 ).

Reply to this Comment

That IS pretty sweet.

How come one of the arrays has 6 more items than the other at the end of the test? Did they not start off empty?

I'm curious how this test scales. In other words, if you did 10,000 manipulations of a small array, if the difference would be as big. I assume that the normal array method really bogs down once your array grows in size like your test.

Reply to this Comment

Brad,

Yeah, for the testing, I used the arrays that were created earlier in the example. So, one had the original 4 items I think and the other had a few more. But, once the time-testing is underway, they are being manipulated similarly.

Excellent question about the manipulation of small arrays. I will try that at lunch :)

Reply to this Comment

Try and run this code (hope the tags will not get killed):

<cfset javaArray = CreateObject("java", "java.util.ArrayList").Init()/>
<cfset regArray = arrayNew(1)>
<cfset arrayResize(regArray,100000)>
<!--- <cfset arrayResize(javaArray,100000)> --->

<cftimer label="Test Java Array" type="outline">
<cfloop index="m" from="1" to="100000" step="1">
<cfset javaArray[m] = m>
</cfloop>
</cftimer>

<cftimer label="Test ColdFusion Array" type="outline">
<cfloop index="k" from="1" to="100000" step="1">
<cfset regArray[k] = k>
</cfloop>
</cftimer>

Reply to this Comment

Tom,

Good comment. It demonstrates that for straight up setting of cell values, the ColdFusion array trends towards slightly better performance. Sometimes the Java array was faster, but mostly the ColdFusion one was faster by like 30-50 ms over 10,000 iterations.

At lunch, though, I want to test how a similar test performs when the array is passed as an argument to a method.

Reply to this Comment

For those of you not on CF-Talk, Tom just posted an excellent catch. The java array does NOT support ArrayResize(). That means that you can't just pass it around like it was any old array.

I got so excited about this feature that perhaps I assumed too much. I will go back to the drawing board and see what I can figure out as a fix to this.

Dangy, and I thought this was so freakin' cool!

Reply to this Comment

One thing you MUST be aware of when using ArrayLists and various other collections.

They are NOT thread safe by design (and may account for some of the times you are getting on performance).

If you are going to use them in CFCs that are singletons (i.e. placed in a shared scope), you will have to create syncronised versions of the Collections.

This can be done through java.util.Collections class -
http://java.sun.com/j2se/1.4.2/docs/api/java/util/Collections.html

Reply to this Comment

Mark,

When you say it's not thread safe (cause I am not that used to the non-thread safe world), do you mean, if I call:

ArrayList::Add()

followed by

ArrayList::Size()

.. the size() method might not return the appropriate size of array. Meaning, does the processing of the page continue before Add() is completed processing.

Also, is this something would throw an error? Or just something that might present "corrupt" data?

Reply to this Comment

To copy and paste from the javadocs on ArrayList:

----8<----
Note that this implementation is not synchronized. If multiple threads access an ArrayList instance concurrently, and at least one of the threads modifies the list structurally, it must be synchronized externally.
----8<----

So if two people hit a bean that has an Arraylist inside it at the same time, and try to change that data at the same time, you are going to encounter some strange error.

Reply to this Comment

Ugg... I wish I knew more about this stuff. There are errors that I am willing to take on and those that I am not. For intance, I am ok with one person getting a wrong SIZE() value... however, if you people add at the same time, I am NOT willing to have one item lost.

Reply to this Comment

Well, that is why you have the ability to create synchronised collections.

It's all there in the Javadocs -

Again, to copy and paste:

-----8<-----------
This is typically accomplished by synchronizing on some object that naturally encapsulates the list. If no such object exists, the list should be "wrapped" using the Collections.synchronizedList method. This is best done at creation time, to prevent accidental unsynchronized access to the list:

List list = Collections.synchronizedList(new ArrayList(...));
-----8<-----------

There you go... a syncrhonised list.

Reply to this Comment

Mark,

Thanks a lot for the heads up on this kind of situation and the good solution. I am a ColdFusion programmer venturing into Java, so I am a bit slow to wrap my head around it.

They are doing work on the server at the moment, so I can't test it, but I will definately try it.

Reply to this Comment

I was thinking about this on the way home -

A Coldfusion array extends java.util.Vector (which implements List, and is syncronised), so I wonder if you create a java.util.Vector, will the reference trick work or not...

Figured I'd leave it to you to work out for us ;o)

Reply to this Comment

Mark,

I think I tried with the Vector class and no luck. But, I will try again, because I am possibly confused. I think it had the same type casting issues that the ArrayList had.

Thanks for taking the time to think about it though. And thanks again for the thread-safe tips.

Reply to this Comment

Great article, but a little long. In summary, "To pass arrays by reference in Coldfusion, don't use Coldfusion arrays" ;)

Reply to this Comment

Hmm... in ColdFusion 8.01, I get this error when I try to do an ArrayLen() on it:

Error Messages: Object of type class coldfusion.runtime.java.JavaProxy cannot be used as an array.

Any ideas?

Reply to this Comment

I did end up resolving my issue with that error post above -- Error Messages: Object of type class coldfusion.runtime.java.JavaProxy cannot be used as an array. I don't remember what I ended up doing to fix it but I do have some recent insight. I started playing around with making my own JARs for performance reasons and noticed ColdFusion generates JavaProxy subclasses to communicate with the JAR's classes. I believe you have another post somewhere out there (or maybe it was someone else, too lazy to Google) -- talking about how the constructor on your object is called -- the default constructor gets called and returns your object rather than the JavaProxy the first time you go to use it via a method (besides a constructor). The JavaProxy is in place so you can access static members/methods without instantiating a new instance. My guess is I was doing something like:

var o = CreateObject('java', 'java.util.ArrayList');
o[1] = SomeStructOrSomething;

Either the JavaProxy doesn't account for [] or [] isn't treated as a method like it is in C# (don't care to Google that, either... all I know is it won't work).

So, the obvious solution is:

var o = CreateObject('java', 'java.util.ArrayList').init();
o[1] = SomeStructOrSomething;

or

var o = CreateObject('java', 'java.util.ArrayList').init(someIntDefaultSize);
o[1] = SomeStructOrSomething;

Reply to this Comment

Dear Ben,
first sorry for my miserable english…
second: thank you for this tutorial, this is exactly what I have searched for.

I think Tom's test above is not really fair, because the regArray know its minimal initial size, by setting
<cfset arrayResize(regArray,100000)> .

The javaArray has an initial capacity of ten by using the default constructor!
<cfset javaArray = CreateObject("java", "java.util.ArrayList").Init()/>

So I think something like the following happens:
If you add the eleventh element, the javaArray must resize itself. This means, that it takes its new size ( i.e. 11 at the first resize time), multiply it with 1, 5 to get a value for an upper bound for the new size and reserve new memory cell for a java.util.ArrayList with capacity maximum 11 * 1,5 = 17. Then every value of the old memory is copied in the new memory.
The eighteenth element will start this procedure again and so on…

Perhaps using
<cfset javaArray = CreateObject("java", "java.util.ArrayList").Init(100000)/>
will help to get better performance?

Reply to this Comment

@Josh,

Yeah, I think I know which post you are referring too (I remember creating the graphic for it - I love creating graphics). I would recommend always calling the init() method on Java objects, unless of course the constructor is protected.

@Isabel,

If you were to init() the ArrayList with a default capacity, my only concern might be that it would result in undefined array indices. Of course, it might not; I am not sure what an "initial capacity" quite means. I'll will have to do some testing.

Reply to this Comment

I'm sorry, maybe I've used the wrong function...
but I find the following:

For Java ArrayList there is a function named

ensureCapacity(int minCapacity),

which increases the capacity of the ArrayList instance, if necessary, to ensure that it can hold at least the number of elements specified by the minimum capacity argument (without resize itself).

So this is the same, we want to do with the call of arrayResize(javaArray,100000), isn't it?

If this is true, we have to use this function instead of arrayResize, i.e. in the examples above

<cfset javaArray = CreateObject("java", "java.util.ArrayList").Init()/>
<cfset javaArray.ensureCapacity(100000)/>

I've tried to test this, but every time I reload my testpage, I've got different values in the timer for the same array... this confused me, because I do not understand why same operations need different time...

Reply to this Comment

@Isabel,

The biggest reason that the same operation would take a different amount of time each time you try it, is most likely due to your operating system and multi-processing.

When a request is made for a ColdFusion page, the ColdFusion engine doesn't automatically get all the processor time it needs to fulfill the request. What's really happening, is that your operating system is constantly switching between the running processes (programs) on your system, and allotting them a small amount of processor time to run before the next process is scheduled and run. This constant switching between programs is called "context switching," and can happen over 10,000 times per second. This gives the illusion that all open programs on your system are running "at the same time;" but in reality, each program is just executing a relatively few number of instructions before it's cut off, and the next program gets to run some instructions.

The thing with this is, processes are never allotted the same exact amount of time to run in this never ending cycle. So with some requests, the ColdFusion engine may have been allotted more actual processor time to complete requests than others. It's all dependent on the operating system's scheduling engine, and the running processes on the system.

So basically what you have to do is just run the page a few times, and take an average of the cftimer results. You will really never be able to get consistent times, but you can at least get a ballpark figure which would allow you to determine if one method of processing is faster than another.

@Ben,

You're the man, keep up the great work. I've learned a lot from your blog posts over the years. And in my 9 years of ColdFusion development, I didn't realize that arrays are passed by value until today... lol.

Reply to this Comment

@Isabel,

Between me and you, I don't think I've ever actually used ArrayResize() in a production web site :) The issue is not so much that it can't be resized - it's that it can't be resized using the same method as one might expect.

Of course, if we just call a "dog" a "dog" and say, hey this is actually an ArrayList and NOT an array, well, then that issue goes away. After all, the ArrayList will act like an ArrayList is expected.

So, maybe what we have to do is simply stop trying to get *arrays* to work like ArrayLists and simply start working with ArrayLists (where necessary).

@Greg,

Thanks my man - glad you're getting good stuff out of my site.

Reply to this Comment

So I have a similar, yet hopefully not too unrelated, question. This post was amazing and I pass arrays around by reference all the time now.

I have a query that, in order to load it quickly on the page, I need to convert it to a struct, add some more information, then convert it back into a query. When I go to use the QueryNew() function and put the column names back into the new version of the query it gets angry at me and the coldfusion error informs me that I cannot have spaces in column names.

This got me wondering if the cfquery tag uses a different java library to build its queries than the QueryNew() function and the QueryAddColumn() function.

I thought of this post and wondered, how did you find out which java library the coldfusion functions were using? Is there a way to look at the source code for cfquery?

Reply to this Comment

@Shannon,

Hmm, interesting question. You might be able to decompile the CFQuery Java code, but I wouldn't know where to find it. You might want to look to see how Railo or OpenBD does it - I think their code is open-source and easier to access (not to say they implement it the same way).

As a side note, you might be able to use queryAddColumn() rather than going to struct and then back to query. If you are just augmenting a query, queryAddColumn() should be perfect for such a thing.

Reply to this Comment

"This got me wondering if the cfquery tag uses a different java library to build its queries than the QueryNew() function and the QueryAddColumn() function."

If I were a betting man, I would guess you might find your answer in :
<ColdFusionHome>\lib\cfusion.jar\coldfusion\tagext\sql\QueryTag.class
and
<ColdFusionHome>\lib\cfusion.jar\coldfusion\runtime\QueryFunction.class

Of course, there is that little matter of source decompilation being against the ELUA. :)

This code appears to indicate that they are the same class (coldfusion.sql.QueryTable):
<cfset queryNew = queryNew("test")>
<cfdump var="#queryNew.getClass().getName()#"><br>

<cfquery name="cfquery" datasource="bradwood">
SELECT '' AS test
</cfquery>
<cfdump var="#cfquery.getClass().getName()#">

I have a feeling that the column name restriction is not inherent to the QueryTable class, but is instead something that the query functions enforce.

Reply to this Comment

@Ben

So the reason why I am going from a query to a struct is because I have 4 or 5 queries (depending on arguments passed into my function) that all contain an orderID and need to be blended together. All the queries except for the main one need to have their row data become column names. This becomes a major nightmare in looping and query hell. Because there could be x number of registrants, the amount of queries/looping that will need to happen could potentially skyrocket.

I convert my main query to a structure with an index of orderID so that when I populate the new columns based on the other queries, I don't have to loop at all.

The problem is when I convert my structure back into a query, all my previous column names (that are user-created) cannot go back into being columns because of the spaces.

The new query gets passed into another function that I wrote that creates a csv file of the data.

Obviously I could just make a new function that uses a structure instead of a query and pass that in but I thought it was so strange that both QueryNew() and QueryAddColumn() do not accept spaces and I couldn't think of a good reason why they would do that when the cfquery tag was fine with it. If there was another way to add a column without using the query functions, that would also work for me.

@Brad
I also think it is probable that the spaces issue is something the function enforces.

Reply to this Comment

@Shannon: if you need a ColdFusion query object with crazy names and don't mind being really sneaky (and a little dirty)-- just ask your database to return that query for you. The following code will return an empty result set (This works with CF8 and SQL Server 2000)

<cfset mycolumnList = []>
<cfset mycolumnList[1] = "Hello There.">
<cfset mycolumnList[2] = "!I like spam?">
<cfset mycolumnList[3] = "33A65710-75C4-483D-B9C3-7DBFD7DCE783">
<cfset mycolumnList[4] = "~!@##$%^&*()_+-=<>?./:"";'{}[]\|">
<cfset mycolumnList[5] = "no_r£eaLlY_y0U_©ant_be.serious]">

<cfquery datasource="datasourceName" name="myQuery">
SELECT
<cfloop array="#mycolumnList#" index="i">
'' AS [#replace(i,"]","]]","all")#],
</cfloop>
'' AS __Im_lazy
FROM (SELECT '' AS test) r
WHERE 1 = 0
</cfquery>

<cfdump var="#myQuery#">

Reply to this Comment

@Brad
That is a great idea! I already have the column list prebuilt anyways so it would actually look cleaner than that even. Hopefully this works with CF 7 but I can't see why it wouldn't. Why doesn't that query realize the table doesn't exist in the FROM and choke?

Reply to this Comment

@Shannon,

If you can find a way to create the query with valid column names, you should be able to change the query column names using the underlying Java. I just tested this and it *does* support column names with spaces:

http://www.bennadel.com/blog/357-Ask-Ben-Changing-ColdFusion-Query-Column-Names.htm

... it uses the underlying query.setColumnNames() method which takes an array of column names - just be careful of the ordering.

Reply to this Comment

"Why doesn't that query realize the table doesn't exist in the FROM and choke?"

It does exist. It's a derived table (SELECT '' AS test) which I aliased as "r". Technically the FROM clause isn't required since SQL Server will give you a result set if you just do "SELECT '' AS foo" but I wanted an empty result set so I selected from a derived table and used the WHERE clause to filter out all records since the column names were all I needed.

Reply to this Comment

@Ben
Sweet thanks!
I wonder if that 'setColumnNames' function will let me add new columns at the same time as I am altering them. I could grab the correct order with the underlying java, tack on the new columns, and go... potentially. Even if it doesn't let me add new columns, it will still do exactly what I need.

Reply to this Comment

I thought I would post the final outcome just in case someone else would like to do this in the future. Hopefully this isn't too much code for a comment...

<!--- Convert query to struct --->
<cfloop from="1" to="#origQuery.recordcount#" index="i">
<cfset j = origQuery["index"][i] />
<cfset csvStruct[j] = StructNew() />
<cfloop list="#origQuery.columnList#" index="k">
<cfset csvStruct[j][k] = origQuery[k][i] />
</cfloop>
<!--- Set a default value for new column rows --->
<cfloop list="#newColumnsList#" index="k">
<cfset csvStruct[j][k] = "No" />
</cfloop>
</cfloop>

<!-- Set new columns with their value. Use index so that searching is unnecessary. --->
<cfloop query="populateColumnsQuery">
<cfset csvStruct[populateColumnsQuery.index][populateColumnsQuery.Name] = populateColumnsQuery.Quantity />
</cfloop>

<!--- Make a new query with a temporary column, this column will be overwritten in the next step --->
<cfset csvQuery = QueryNew("temp") />
<cfset columnNameList = personalInfo.columnList & "," & nameList />
<!--- Create an array of column names. --->
<cfset columnNameArray = ListToArray(columnNameList) />

<!--- Set the column names of the new query. This does not work with extra columns if the query already has data in it. --->
<cfset csvQuery.setColumnNames(columnNameArray) />

<!--- Set new query rows with struct data --->
<cfloop query="OrigQuery">
<cfset row = QueryAddRow(csvQuery) />
<cfloop list="#columnNameList#" index="k">
<cfset csvQuery[k][row] = csvStruct[origQuery.index][k] />
</cfloop>
</cfloop>

I edited some of the variable names to be more generic so hopefully I was consistent throughout. I found that using the original query for the final data did not work properly and it was easier to create a new query and stuff it with columns, then stuff it with the data.

Reply to this Comment

@Shannon,

Thanks for sharing. Starting off with an empty query definitely does make things easier. If you start off with data then calling the setColumnNames() definitely gets much more complicated since you need to find the order of the underlying columns.

Reply to this Comment

@Ben
I found that not only do you have to figure out the order of the columns but if there are rows and the new columns don't have any data in them, coldfusion seems to have trouble referencing the new row locations and errors if you try to dump the query. Creating a column doesn't seem to guarantee an initialized memory space for the row data that goes with it.

Reply to this Comment

This is huge, thanks so much for posting this. I only skimmed the comments, but I was able to push Structures into ArrayList (Array of Structs). That was my only concern after reading your post.

Reply to this Comment

I glanced of this page and I didn't see you mention how to create a multi dimensional array using Arraylist. Is that possible?

Reply to this Comment

The question you should ask yourself is, why do I need a multidimensional array to begin with. ;-)

Reply to this Comment

@Roe,

Well the MD array is already there in code (prior me working on it), I'd like to write a function to do some work on it.

Reply to this Comment

@Steve,

Try this - I didn't test it.

  • <cfscript>
  • var Bounds1 = 10;
  • var Bounds2 = 20;
  • var i = 1;
  • var j = 1;
  • var ArrayListProxy = CreateObject('java', 'java.util.ArrayList');
  • var MyMDArrayList= ArrayListProxy.Init(Bounds1); // Setting the initial size
  • for (i = 1; i <= Bounds1; i++) {
  • MyMDArrayList[i] = ArrayListProxy.Init(Bounds2); // Setting the initial size
  • for (j = 1; j <= Bounds2; j++) {
  • MyMDArrayList[i][j] = i & ', ' & j;
  • }
  • }
  • </cfscript>
  • <cfdump var="#MyMDArrayList#"/>

Reply to this Comment

@Steve,

To be honest, I don't have much experience with multi-dimensional arrays. Even with the core data types (which go up to three dimensions), I found MD arrays to be something mentally hard to visualize. Now, I simply use an Array-of-arrays (which is what I think @Josh is talking about in his MD proxy).

Also, one thing you might be careful of - I am not sure that this ArrayList implements *all* of the array functions. If my memory serves me correctly, everything that I tried to do failed one array method or another (typically the arrayResize() method):

http://www.bennadel.com/blog/277-Arrays-By-Reference-In-ColdFusion-Sort-Of.htm

So, just be careful which path you commit to.

Can I ask why your current multi-dimensional array approach is not working?

Reply to this Comment

@Ben, yea that's what I was wondering too so I decided not to mess with the array or pass it to a function.

It's working fine I just couldn't pass it to a function by reference. I needed to do some data validation and editing on it. The md array could potentially be pretty big, I guess i just didn't want to copy all that data around. Seemed inefficient.

Thanks for the help everyone!

Reply to this Comment

@Steve,

Every time you pass a ColdFusion Array into a function, it effectively Duplicate()s it -- so, if you pass the data more than once, it's already less efficient to use a ColdFusion Array vs. a Java ArrayList.

Depending on what you're doing with the Array(s), optimizing up front can be more trouble than it's worth -- just depends on if the final performance is "good enough" for you. However, the code to copy from your old ColdFusion Arrays to ArrayLists is pretty minimal... see http://download.oracle.com/javase/1.5.0/docs/api/java/util/ArrayList.html#addAll(java.util.Collection)

Reply to this Comment

@Steve,

As @Josh says, it all comes down to context. For example, all of the array-functions that break the pass-by-reference approaches are *not* even functions that I typically use :) Meaning, they might break, but it will never cause me any concern.

Reply to this Comment

@Ben,

What functions do you use? ...actually I'm guessing you've mentioned it on your site and I can just go look :-)

Thanks @Josh / @ben

Reply to this Comment

@Steve,

When it comes to arrays, I mostly use arrayAppend(). Probably like 99% of the time. Maybe some arrayDeleteAt(). Although, it's been a while since I've experimented with a by-reference array. It would be interesting to know if this kind of approach (ArrayList) will work with features like Array-looping (CFLoop/Array) and the newer CF9 arrayFind() and arrayContains() functions.

But, mostly, I just use the standard ColdFusion arrays these days. I've just become used to their nature and work around it when possible.

Reply to this Comment

Hi Ben,

I have just tried your code on Railo 3.2.3.000 and it took 11ms for the ColdFusion array and 10ms for the java.util.ArrayList. The guys at Railo are obviously doing something similar behind the scenes and the guys at Adobe obviously are not...

Andrew.

Reply to this Comment

@Andrew That implies Railo doesn't copy the default array by default, which is presumably where the overhead is coming from. This would mean it is not functionally equivalent. Can you confirm it actually makes a copy (as ColdFusion does)?

Unrelated to that: copying arrays by default is plain-old-wrong, so this is a very welcome blog post!

Reply to this Comment

I've been trying to force a pass-by-reference from the other side of things. If you look at a lot of the built in ColdFusion Array functions, they'll work as if the array is passed by reference (e.g. ArrayAppend(ar, "value")). Doing a dump of the coldfusion.runtime.Array class you see that they'll accept a java.lang.List as the first argument.

Unfortunately, CF doesn't let you set native Java types, as a function argument type, when you're defining your own functions. Sucks as I wanted to backport some of the Array* functions to earlier versions of ColdFusion, but can't make them behave the same.

Reply to this Comment

Post A Comment

You — Get Out Of My Dreams, Get Into My Comments
Live in the Now
Oops!
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.