Ask Ben: Converting A Struct To A Query (And More Java)
Posted September 12, 2006 at 12:30 PM
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
Newer Post
ColdFusion Email Validation, IsValid(), And CFMail Errors
Older Post
Give Me Email Feed Back
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.
