?
Solved

Using variable column names

Posted on 1999-12-16
4
Medium Priority
?
182 Views
Last Modified: 2013-12-24
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.




0
Comment
Question by:nelchr
  • 2
4 Comments
 
LVL 1

Expert Comment

by:deepchanda
ID: 2288365
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
0
 
LVL 1

Accepted Solution

by:
rod_nolan earned 300 total points
ID: 2293268
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 ---


0
 
LVL 1

Expert Comment

by:rod_nolan
ID: 2293588
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


0
 

Author Comment

by:nelchr
ID: 2295595
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>

0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you don't have the right permissions set for your WordPress location in IIS, you won't be able to perform automatic updates. Here's how to fix the problem.
What You Need to Know when Searching for a Webhost Provider
The video provides a quick and easy steps to migrate MBOX file to well known Outlook PST and Office 365. Besides this, it also supports and migrates more than 20 email clients of MBOX which include AppleMail, Opera, Thunderbird and SeaMonkey effortl…
The video will let you know the exact process to import OST/PST files to the cloud based Office 365 mailboxes. Using Kernel Import PST to Office 365 tool, one can quickly import numerous OST/PST files to Office 365. Besides this, the tool also comes…
Suggested Courses

589 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question