I would like to add the ability for a user to download the data from a
query. For example, if the user fills out a query form and the results
are returned, how do I get that result into a file for them to download?
What I can do use Cffile to create a file based on query results -
that's no problem because the data is output and written in the same
template. But I was wondering - what is the best way to pass a SQL
Statement from one page to another? It's not like I can pass the entire
dataset via the URL, but surely there's a way. Below, I'm running a
query and creating a file from it - all in one template. In reality, I
need the query or the results passed in from one template to another via
some variable. I guess I can go through all of my templates and create a
string variable that contains my statement and pass that around, but
that seems tedious, given how complex the statement can become given the
options the user has access to in the control panel I created. Is there
a better way?
<CFQUERY NAME="ExportData" DATASOURCE="#form.DSN#"
cachedwithin="#CreateTimeSpan(1,0,0,0)#">
SELECT lname,fname from employees where id=3 and a=a and b=b and c=c
and d=d, etc.
</CFQUERY>
<cfset randomNum = RandRange(1000,9999)>
<CFSET exportfile =
"C:\inetpub\wwwroot\downloads\export#randomNum#.tsv">
<CFSET output = "" & "#ExportData.ColumnList#">
<CFFILE ACTION="" FILE="#exportfile#" OUTPUT="#output#"
NAMECONFLICT="OVERWRITE">
<CFLOOP QUERY="ExportData">
<CFSET output = "">
<CFLOOP INDEX="X" LIST="#ExportData.ColumnList#" DELIMITERS=",">
<cfset formatted1 = ListQualify(Evaluate(#X#), "", ",", "All")>
<CFSET formatted = Replace(formatted1, ",", "&comma&", "ALL")>
<CFSET output = output & formatted &",">
</CFLOOP>
<CFFILE ACTION="" FILE="#exportfile#" OUTPUT="#output#">
</CFLOOP>
<CFOUTPUT><a href="" the
file</a></CFOUTPUT>
Is there any wan to capture the SQL Statement that is found in the
Debugging? This output is what I'm talking about:
SQL Queries
getResults (Datasource=mysource, Time=188ms, Records=1) in
C:\Inetpub\wwwroot\EETotal.cfm @ 14:24:03.003
SELECT Sum(EXPENDED_YTD) AS ExpTotal
FROM WH.SUMM_EXP_APPR_05
WHERE 0=0 AND (Not(APPR_TYPE='9'))
If so, let me know - I'd greatly appreciate the help. It'd be cool to be
able to do a <cfset myQuery="#getResults.SQLStatement#">
Thanks in advance
Sincerely,
Andrew
Anticipating an answer that begins with "Why don't you just do a ..."
(the best kind)
[Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
