Ask Ben: Converting A Struct To A Query (And More Java)
Posted September 12, 2006 at 12:30 PM by Ben Nadel
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:
- <!--- 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:
- <!--- 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:
- <!--- 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:
- <!--- 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.
Reader Comments
Or you can go to cflib.org and find a function to do that. :p
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
@Chrysler,
Usually that means that you messed up a variable assignment somewhere or are misspelling a variable name.
Hi Ben,
I have scenario to convert transfer data(methods which will have data kind of stuff). how can i convert this to a generic format to expose this data to many languages(other than cf). suggest me the better way to do this.
I thought this is the right place after watching this blog..
Thanks,
Raghuram Reddy Gottimukkula
Adobe Certified Coldfusion Professional
Bangalore India
@Raghuram,
Sorry for not commenting - did you figure this out? I don't know very much about Transfer objects.
