<CFX_QueryColumns>

This tag does two things:

1) Allows you to retrieve a list of column names--as a comma-separated list--for any query. Similar to the ColumnList property of queries in CF3.0 and up, except that the list of columns is in the same order that the columns are returned by the database system, rather than the seemingly random order that they appear in the ColumnList variable.

2) Allows you to create a new query with the same data but with new column names, effectively allowing you to rename a query's column names. Useful if your query's column names can't be used in your CF code as-is. Note that there is also a CF_RenameQueryColumns tag available--that acts as a "wrapper" for this CFX tag--which allows you to create "safe" names for your query columns automatically. If your problem is that your query column names contain spaces or hyphens, etc., you may want to use that tag rather than calling this tag directly.

Parameters

QUERY Required. The name of the query whose columns you want to get or manipulate. Could be the results of a CFQUERY, CFLDAP, CFPOP, QueryNew(), etc.

ACTION Required. Valid ACTIONs are GET or RENAME.

Use ACTION="GET" to retrieve the list of columns for QUERY, which will be placed into the variable you specify in VARIABLE (see below).
Use ACTION="RENAME" to create a copy of QUERY that has the same data but with the column names you provide for NEWQUERYCOLUMNS (see below).

VARIABLE Required if ACTION="GET" (ignored otherwise). The name of a variable that should be populated with the comma-separated list of column names from QUERY. So if VARIABLE="MyColumnList", then you can refer to #MyColumnList# in your CF code after this tag is executed. Use it in the same way you'd use CF's built-in #QueryName.ColumnList# variable.

NEWQUERY Required if ACTION="RENAME" (ignored otherwise). A name for the new, "corrected" version of the query. So if NEWQUERY="MyNewQuery", then you would be able to refer to MyNewQuery as a query name in CFOUTPUT tags, etc. Note that you can supply the same value for QUERY and NEWQUERY, which would cause the original query to be "overwritten" (replaced) with the new version of the query result set.

NEWQUERYCOLUMNS Required if ACTION="RENAME" (ignored otherwise). A comma-separated list to be used as the column names for the new version of the query. So if a query originally had columns named "One,Two,Three" and you provided NEWQUERYCOLUMNS="MyOne,MyTwo,MyThree" here, then you could refer to #MyNewQuery.MyOne# in your CF code, which will have the same data as the corresponding column in the original query.
Of course, the number of comma-separated items in NEWQUERYCOLUMNS should be the same as the number of columns in QUERY, or your results will be strange (or you may get a "unexpected error occurred in CFX tag" type of error message).
Obviously, it is important that the column names you provide here be in the correct order. Therefore, if the nature of your code is such that you do not know the names/order of the columns in the original query, it is essential that you do not use CF's built-in ColumnList variable to obtain the column names, because that variable does not always return the column names in the "correct" order. In such an instance, it is recommended that you use this tag with ACTION="GET" separately to obtain the column names from the original query. Then manipulate the column names in whatever way you need to, using any of CF's string and list functions (but while preserving the order of the list, of course). Then use the tag again with ACTION="RENAME".

Example:
The following demonstrates getting a list of column names from a query, manipulating the column names a bit, and creating a new version of the query with the new column names. In reality, you normally would be able to get the same effect by using the AS keyword in the SQL statement itself rather than going through these steps. But it serves as a good example.

<CFQUERY NAME="MyQuery" DATASOURCE="Coffee Valley"> SELECT * FROM Blends </CFQUERY>  <CFX_QueryColumns QUERY="MyQuery" ACTION="GET" VARIABLE="MyColumnList">  The original columns were:<BR> <CFOUTPUT>#MyColumnList#</CFOUTPUT><P>  <CFSET NewColumnList = ReplaceNoCase(MyColumnList, "Blend_ID", "BlendNumber")>  <CFX_QueryColumns QUERY="MyQuery" ACTION="RENAME" NEWQUERY="MyNewQuery" NEWQUERYCOLUMNS="#NewColumnList#">  <CFOUTPUT QUERY="MyNewQuery"> #BlendNumber#: #Blend# <BR> </CFOUTPUT>

Installation
Same as any CFX tag--put the .dll file into your \CFUSION\CFX directory or wherever you choose, then "register" the tag using the CF Administrator. (Note that this is not the same procedure as with CF_ tags. See your CF documentation about installing "C++ Custom Tags" for details.)

Disclaimer:
I am not a skilled C++ programmer. This was my first C++ project. I don't know much about memory allocation. I wrote this tag mainly as a learning exercise, and because a number of people seemed to need its functionality. It is entirely possible that this tag may have a "memory leak" or something in it (I doubt it would ever amount to much, but I really don't know). If you would like to take a look at or "tweak" my source code for this tag, write me a note and let me know. - Nate Weiss, 4/98.

QUERY	Required. The name of the query whose columns you want to get or manipulate. Could be the results of a CFQUERY, CFLDAP, CFPOP, QueryNew(), etc.
ACTION	Required. Valid ACTIONs are GET or RENAME. Use ACTION="GET" to retrieve the list of columns for QUERY, which will be placed into the variable you specify in VARIABLE (see below). Use ACTION="RENAME" to create a copy of QUERY that has the same data but with the column names you provide for NEWQUERYCOLUMNS (see below).
VARIABLE	Required if ACTION="GET" (ignored otherwise). The name of a variable that should be populated with the comma-separated list of column names from QUERY. So if VARIABLE="MyColumnList", then you can refer to #MyColumnList# in your CF code after this tag is executed. Use it in the same way you'd use CF's built-in #QueryName.ColumnList# variable.
NEWQUERY	Required if ACTION="RENAME" (ignored otherwise). A name for the new, "corrected" version of the query. So if NEWQUERY="MyNewQuery", then you would be able to refer to MyNewQuery as a query name in CFOUTPUT tags, etc. Note that you can supply the same value for QUERY and NEWQUERY, which would cause the original query to be "overwritten" (replaced) with the new version of the query result set.
NEWQUERYCOLUMNS	Required if ACTION="RENAME" (ignored otherwise). A comma-separated list to be used as the column names for the new version of the query. So if a query originally had columns named "One,Two,Three" and you provided NEWQUERYCOLUMNS="MyOne,MyTwo,MyThree" here, then you could refer to #MyNewQuery.MyOne# in your CF code, which will have the same data as the corresponding column in the original query. Of course, the number of comma-separated items in NEWQUERYCOLUMNS should be the same as the number of columns in QUERY, or your results will be strange (or you may get a "unexpected error occurred in CFX tag" type of error message). Obviously, it is important that the column names you provide here be in the correct order. Therefore, if the nature of your code is such that you do not know the names/order of the columns in the original query, it is essential that you do not use CF's built-in ColumnList variable to obtain the column names, because that variable does not always return the column names in the "correct" order. In such an instance, it is recommended that you use this tag with ACTION="GET" separately to obtain the column names from the original query. Then manipulate the column names in whatever way you need to, using any of CF's string and list functions (but while preserving the order of the list, of course). Then use the tag again with ACTION="RENAME".