Ask Ben: Converting A Struct To A Query (And More Java)

Posted September 12, 2006 at 12:30 PM

Tags: ColdFusion, Ask Ben

How can I convert a structure to a query?

You can easily convert a structure to a query, but I am going to use your question to also re-touch upon a topic that I have covered before. That is converting a query to a structure. First though, let's talk about what kind of structure you are dealing with. Since you didn't give me much detail to go on, I am assuming that you have a structure that has as its values, arrays of data. I am assuming you want to create a query that has, as column names, the keys of the structure, and as values, the values from the structure.

That being said, let me first say that I don't really ever see a need to convert a query to anything else. A ColdFusion query is a very amazing object that can be looped over, duplicated, and even referenced like it WAS a structure. Most people, in my experience, want to convert a query to a structure because they don't realize that you can directly access parts of a query as such:

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

  • <!--- Get data at the given column and row. --->
  • <cfset strValue = qData[ COLUMN_NAME ][ ROW_NUMBER ] />

If you want to go the conversion route for some reason (perhaps for use within another black-boxed module), you can do so in a few ways. There is a longer, more standard way which I touched upon before. However, as I have gotten more into Java, I have realize that there is an even faster way.

Let's create a test query to work with:

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

  • <!--- Create a query of body parts. --->
  • <cfset qParts = QueryNew(
  • "id, name, turn_on",
  • "INTEGER, VARCHAR, VARCHAR"
  • ) />
  •  
  • <!--- Add rows to the query. --->
  • <cfset QueryAddRow( qParts, 7 ) />
  •  
  • <!--- Set row data. --->
  • <cfset qParts[ "id" ][ 1 ] = 1 />
  • <cfset qParts[ "name" ][ 1 ] = "Feet" />
  • <cfset qParts[ "turn_on" ][ 1 ] = "No" />
  •  
  • <cfset qParts[ "id" ][ 2 ] = 2 />
  • <cfset qParts[ "name" ][ 2 ] = "Calves" />
  • <cfset qParts[ "turn_on" ][ 2 ] = "Yes" />
  •  
  • <cfset qParts[ "id" ][ 3 ] = 3 />
  • <cfset qParts[ "name" ][ 3 ] = "Thighs" />
  • <cfset qParts[ "turn_on" ][ 3 ] = "Yes" />
  •  
  • <cfset qParts[ "id" ][ 4 ] = 4 />
  • <cfset qParts[ "name" ][ 4 ] = "Butt" />
  • <cfset qParts[ "turn_on" ][ 4 ] = "Very Much" />
  •  
  • <cfset qParts[ "id" ][ 5 ] = 5 />
  • <cfset qParts[ "name" ][ 5 ] = "Hands" />
  • <cfset qParts[ "turn_on" ][ 5 ] = "Yes" />
  •  
  • <cfset qParts[ "id" ][ 6 ] = 6 />
  • <cfset qParts[ "name" ][ 6 ] = "Smile" />
  • <cfset qParts[ "turn_on" ][ 6 ] = "Yes" />
  •  
  • <cfset qParts[ "id" ][ 7 ] = 7 />
  • <cfset qParts[ "name" ][ 7 ] = "Hair" />
  • <cfset qParts[ "turn_on" ][ 7 ] = "No" />

Now that we have a query, let's use the query's Java methods to convert it to a structure. We are going to create a structure and map the query columns to the structure keys and copy over arrays of data:

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

  • <!--- Create an structure for the query. --->
  • <cfset objParts = StructNew() />
  •  
  • <!---
  • Copy the data to the structure, mapping the columns
  • to the keys.
  • --->
  • <cfloop index="strColumn" list="#qParts.ColumnList#" delimiters=",">
  •  
  • <!--- Add column as array to column key. --->
  • <cfset objParts[ strColumn ] = qParts[ strColumn ].ToArray() />
  •  
  • </cfloop>

Here, we are using the Java method ToArray() that is accessible from the ColdFusion query-column object. This returns the column data in an array with each row representing an index in the returned array. How easy was that? This is also a really cool method where things like ValueList() will not be fast or useful.

Now, that brings us to your question: converting a structure to a query. We will work with the structure that was just created as this should set up the most common scenario for converting a structure to a query. To do this, we want to map the structure keys to the columns of a query and then transfer the array data from the structure to the query column data:

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

  • <!--- Create a new query. --->
  • <cfset qPartsTwo = QueryNew( "" ) />
  •  
  • <!--- Loop over keys in the struct. --->
  • <cfloop index="strKey" list="#StructKeyList( objParts )#" delimiters=",">
  •  
  • <!--- Add column to new query with default values. --->
  • <cfset QueryAddColumn(
  • qPartsTwo,
  • strKey,
  • "VARCHAR",
  • objParts[ strKey ]
  • ) />
  •  
  • </cfloop>

As you can see, we are creating an empty query without passing any of the data. Then, we loop over the structure and add the array data as we create the columns. The one problem you will notice is that we are creating ALL columns as type VARCHAR, where as in reality, the original query contained an INTEGER field. This is just a limitation the demo. If you know the column types, you can certainly put them in.

Download Code Snippet ZIP File

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




Reader Comments

Ben
Nov 10, 2006 at 5:38 PM // reply »
2 Comments

Or you can go to cflib.org and find a function to do that. :p


Apr 1, 2009 at 9:50 PM // reply »
1 Comments

Hi there, I tried your code but I always get the error message:

Object of type class java.lang.String cannot be used as an array

on the line that uses the QueryAddColumn. I am using Coldfusion 8.

Thanks


Apr 2, 2009 at 8:30 AM // reply »
6,371 Comments

@Chrysler,

Usually that means that you messed up a variable assignment somewhere or are misspelling a variable name.


Post Comment  |  Ask Ben

Recent Blog Comments
Nov 7, 2009 at 5:53 PM
Ask Ben: Javascript String Replace Method
You can find here an advanced function that prepared with javascript replace function. This can make the first letters of words, sentences, lines and whatever you define automatically: http://www.m ... read »
Andrew Neely
Nov 7, 2009 at 4:56 PM
A Moment That Touched Me - The Fountainhead
Ben, Glad you enjoyed the podcast. Yeah, the Tank Riot guys can get really chatty during the episodes, but that's part of the charm of it for me. They've covered everything from Nichola Tesla to Cha ... read »
Nov 7, 2009 at 4:43 PM
Building A Fixed-Position Bottom Menu Bar (ala FaceBook)
Is it possible to make some more MenĂ¼`s ? ... read »
Jill
Nov 7, 2009 at 11:40 AM
How To Unformat Your Code (Like A Pro)
Derek, I think you might be right - sweet! Thanks for the link :) ... read »
Nov 7, 2009 at 11:25 AM
How To Unformat Your Code (Like A Pro)
I think it would be way easier to just use this http://www.logichammer.com/html-formatter/ He just released v3 and it rocks. ... read »
Jill
Nov 7, 2009 at 7:58 AM
How To Unformat Your Code (Like A Pro)
LMAO - this was pretty funny! I have to admit - I also love to reformat code so I can read it. My boss used to tell me to leave my OCD at home. Now I don't feel so bad after reading everyone else' ... read »
Nov 6, 2009 at 10:10 PM
How To Unformat Your Code (Like A Pro)
The timing of this post is just uncanny. I spent the last 15-20 minutes manually un-formatting my "Ben Nadel" style code within a CFC of mine. I was really digging the readability a few weeks ago, bu ... read »
Roe
Nov 6, 2009 at 5:11 PM
Passing Arrays By Reference In ColdFusion - SWEEET!
ArraySort also reorders the results of these java obj's ... read »