cffile and append

[tanny]

Hi all,

I'm having trouble using the CFFILE action="APPEND" tag. I run a query that outputs a whole heap of records which are destined to be placed into a csv file.

I create the file with the appropriate headings. This is not a problem. The problem arises when I get into the cfoutput query loop and start writing the individual lines out the the file. I even use cflock around the cffile action="APPEND" tag!

Usually, the first record writes out once. In the example below, the first record is the one saying '00185. Also in the example below, the next record (which should have been '00185A) also only gets written once. After that, I get at least two, and sometimes three (and I can't consistently reproduce this error) lines written to the file

The other odd thing is that although the query orders the records for me, they are written out of order into the file. For example I get this output even though the query has ordered it!:

'00185
'00185A11A1
'00185A11A2
'00185A
'00185A1
'00185A11A3
'00185A1AA2
'00185A11A4
'00185A1AA3
'00185A11A5
'00185A11
'00185A11A1
'00185A11A6
'00185A11A2
'00185A11A7
'00185A11A3
'00185A11A8
'00185A1AA4
'00185A1AA5
'00185A11A9
'00185A1AA6
'00185A11B1
'00185A1AA7
'00185A11B2
'00185A11A4
'00185A11A5
(The "'" is to make sure Excel realises it's looking at text, not a number)

To reiterate, these numbers should be in order, and only a single instance of them should appear. That's what the query does!

Any ideas anybody?

 

[JHoarty]

Can you post your code? You mention a query loop, but I can't tell if you are actually using cfloop or simply cfquery. I do these all the time to produce text outputs of tables. John Hoarty
jhoarty@quickestore.com

http://www.quickestore.com

 

[tanny]

Thanks for your interest John,

I use pretty modular code so I'll do my best to show you how I've done it. But to give you a short answer, this gets fired off during a cfoutput.

Anyway here goes:

<cfparam name=&quot;attributes.filename&quot; default=&quot;#DateFormat(now(),&quot;ddmm&quot##TimeFormat(now(),&quot;HHmmss&quot#.csv&quot;>


<cfinclude template=&quot;qry_getsubassets.cfm&quot;> <!--- gets all subassets for this assetnum --->

<cfif qry_getsubassets.recordcount gt 0>
<cfinclude template=&quot;act_createfile.cfm&quot;> <!--- This creates the file and the header row. It works ok --->
<cfoutput query=&quot;qry_getsubassets&quot;>
<cfloop list=&quot;#qry_getsubassets.columnlist#&quot; index=&quot;x&quot;>
<cfset &quot;variables.#x#&quot;=Evaluate(&quot;qry_getsubassets.#x#&quot>
</cfloop>
<cfinclude template=&quot;act_writedataline.cfm&quot;>
</cfoutput>
</cfif>


<!--- This code sets the html header and content for a download.--->

<CFHEADER NAME=&quot;content-disposition&quot; VALUE=&quot;download; filename=#attributes.filename#&quot;>
<cfcontent TYPE=&quot;application/msexcel&quot; FILE=&quot;#request.site.outputdir#/#attributes.filename#&quot; deletefile=&quot;no&quot;>

<!--- The act_writedataline.cfm template processes all of the columns in the query to make them excel friendly, and then does the cffile action=&quot;append&quot;. This is the code. --->
<cfparam name=&quot;attributes.dataline&quot; default = &quot;&quot;>
<cfset attributes.dataline = &quot;&quot;>
<cfset attributes.dataline = &quot;'&quot; & #variables.assetnum# & ',&quot;'
& #variables.realassdes1# & '&quot;,&quot;'
& #variables.assdes2# & '&quot;,&quot;'
& #variables.assdes3# & '&quot;,&quot;'
& #variables.assdes4# & '&quot;'>
<!--- This dataline is actually a lot longer, but it would bore you to death and it's working ok anyway --->


<!--- This cffile command appends the appropriate asset information to the file created above --->
<cflock timeout=&quot;3&quot; throwontimeout=&quot;No&quot; type=&quot;EXCLUSIVE&quot;>
<cffile action=&quot;APPEND&quot; file=&quot;#request.site.outputdir#/#attributes.filename#&quot; output=&quot;#attributes.dataline#&quot; mode=&quot;644&quot; addnewline=&quot;Yes&quot;>
</cflock>

The code for the query called in qry_getsubassets.cfm is like this:

<cfquery name=&quot;qry_getsubassets&quot; datasource=&quot;#request.site.mainDSN#&quot; dbtype=&quot;#request.site.mainDSNdbtype#&quot;>
select rtrim(assetnum) assetnum,
assdes1 realassdes1,
assdes2,
assdes3,
assdes4
from assets
START WITH ASSETNUM = upper(<cfqueryparam value=&quot;#attributes.assetnum#&quot;>)
CONNECT BY PRIOR assetnum = parentasset
ORDER BY assetnum, levelnum
</cfquery>

Hope that all makes sense

 

[tanny]

Ooops,

That query says ORDER BY assetnum, levelnum but levelnum is not showing in the SELECT list. That's because I've chopped out a whole heap of boring stuff from the query as well as the dataline processing. As I've said, if I run that query inside Oracle using the assetnum I've passed in, the records come out just fine.

Cheers,

Tania