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

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:

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:

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:

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:

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.

What Other People Are Searching For

[ local search ] convert struct to query
[ local search ] structoquery()
[ local search ] coldfusion convert a struct to query
[ local search ] how to convert query columns to an array
[ local search ] coldfusion query to struct
[ local search ] coldfusion add query row