Using variable column names

I want to pass table name to a CF query/SQL statement and return the contents of that table in comma separated format.  

This means the number of fields and their corresponding names are variable.  

Currently, I am retrieving individual field names from the SQL syscolumns table and using those names to construct another query to retrieve the actual data.   However, when I try to get the actual data, I cannot output data using variables (forget the CSV part).  Just show me the data.




nelchrAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

deepchandaCommented:
You can use the AS keyword in your SELECT query something like "SELECT colname AS col from table". This would make the col accessible later on as a query var to use like #queryname.col#

DC
rod_nolanCommented:
Hi nelchr,

My example allows you to dynamically specify the datasource and the table name that you want to query against. It will present query recordset in an HTML table.

You ugly part is that the columns are presented in a somewhat random order because I'm using SELECT * in my SQL statement which tends to select column names in no particular order. But you mentioned that you can retrieve the columns to be selected using the syscolumns query, so that shouldn't be an issue for you.

It's not exactly creating a csv file but I think you'll be able to modify this example to fit your specific needs. Let us all know how it goes.

Good Luck,
Rod


--- snip ---

<cfset tableName = "tablename">
<cfset DSN = "datasource">

<h3>Query the <cfoutput>#Variables.Tablename#</cfoutput> table.</h3>

<CFQUERY NAME="GetData" DATASOURCE="#Variables.DSN#">
SELECT * FROM #Variables.Tablename#
</CFQUERY>

<h3>Here are the columns that are being selected. </h3>
<!--- Generate the column list --->
            <CFLOOP FROM="1" TO="#ListLen(getData.ColumnList)#" INDEX="Counter">
                  <CFOUTPUT>#ListGetAt(getData.ColumnList, Counter)#</CFOUTPUT>
            </CFLOOP><p>

<h3>Now convert that column list to a variable name list.</h3>

<cfset varList = "##GetData." & #ListGetAt(getData.ColumnList, 1)# & "##">

<cfloop from="2" to="#ListLen(getData.ColumnList)#" index="i">
      <cfset varList = varList & ", " & "##GetData." & #ListGetAt(getData.ColumnList, i)# & "##">
</cfloop>
 
<cfoutput>#varList#</cfoutput><p>


<h3>The next step is to output the query results in a table</h3>

<table border="1" width="100%">
      <tr>
            <CFLOOP FROM="1" TO="#ListLen(getData.ColumnList)#" INDEX="Counter">
                  <CFOUTPUT><th>#ListGetAt(getData.ColumnList, Counter)#</th></CFOUTPUT>
            </CFLOOP>
      </tr>
      <CFOUTPUT query="GetData">
      <tr>
            <CFLOOP FROM="1" TO="#ListLen(varList)#" INDEX="i">
                        <td>#evaluate(ListGetAt(varList, i))#</td>
            </CFLOOP>
       </tr>
      </cfoutput>
</table>


--- snip ---


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
rod_nolanCommented:
OK, I've had a chance to look over that last bit of code and i took a chainsaw to it to arrive at the following, shorter vresion:


<!--- set the datasource and table names --->
<cfset DSN = "datasource">
<cfset tableName = "table">

<!--- then go get the data --->
<CFQUERY NAME="GetData" DATASOURCE="#Variables.DSN#">
SELECT * FROM #Variables.Tablename#
</CFQUERY>

<!--- put that data in a table --->
<table>
      <tr bgcolor="lightblue" valign="top">
            <CFLOOP FROM="1" TO="#ListLen(getData.ColumnList)#" INDEX="i">
                  <CFOUTPUT><th>#ListGetAt(getData.ColumnList, i)#</th></CFOUTPUT>
            </CFLOOP>
      </tr>
      <CFOUTPUT query="GetData">
<cfif getData.CurrentRow MOD 2>
      <tr bgcolor="##cccccc" valign="top">
<cfelse>
      <tr bgcolor="##aaaaaa" valign="top">
</cfif>
            <CFLOOP FROM="1" TO="#ListLen(getData.ColumnList)#" INDEX="i">
                        <td>#evaluate(ListGetAt(getData.ColumnList, i))#</td>
            </CFLOOP>
       </tr>
      </cfoutput>
</table>



Then I started thinking about the CSV file and I came up with the following:

<!--- set the datasource and table names --->
<cfset DSN = "datasource">
<cfset tableName = "table">

<!--- then go get the data --->
<CFQUERY NAME="GetData" DATASOURCE="#Variables.DSN#">
SELECT * FROM #Variables.Tablename#
</CFQUERY>

<!--- replace the commas in the columnList with tabs --->
<cfset colHeads = #Replace(getData.ColumnList, ",",  chr(9), "ALL")#>

<!--- and then write them to a file --->
<cffile action="WRITE"
        file="C:\data.txt"
        output="#Variables.colHeads#">

<!--- process each row in the result set and add it to the file --->
<CFOUTPUT query="GetData">

<!--- initialize the variable to hold the text of each row --->
<CFSET RowToAppend = "">

      <!--- go through each column and add it to the variable, seperating each value with a tab character --->
      <CFLOOP FROM="1" TO="#ListLen(getData.ColumnList)#" INDEX="i">
                  <CFSET RowToAppend = RowToAppend & #evaluate(ListGetAt(getData.ColumnList, i))# & Chr(9)>
       </CFLOOP>


      <!--- now append the row to the file --->
      <cffile action="Append"
        file="C:\data.txt"
        output="#Variables.RowToAppend#">

</cfoutput>



There! That's that. Try it on and let me know how it fits.

Rod


nelchrAuthor Commented:
rod,

Here is my chopped down version of what you wrote.  

I chose not to write column names to a file and I send output to another form.  

What was stumping me was how to use the evaluate() with the listgetat() with #

Thanks for your help.

nelchr

<cfquery name="nelsfirst" datasource="hotline">
      select * from #tableselect#
</cfquery>

<cfset colList = #nelsfirst.columnlist#>
<cfloop query="nelsfirst">
      <cfset outline = "">
      <cfloop index="i" from="1" to="#ListLen(colList)#">
            <cfset outline = #outline# & chr(34) & #evaluate(ListGetAt(colList,i))# & chr(34) & ",">  
      </cfloop><br>
      <cfset outline = left(outline, len(outline)-1)>        
      <cfoutput>#outline#</cfoutput>      
</cfloop>

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Web Servers

From novice to tech pro — start learning today.