SQL Query to Text file

[reya276]

I need to dump a query out to files, the issue is that this query has to break by an specific code and then create the text file based on this code. But when I try to execute it I get a Jrun Server error "Jrun 500 Null".

please take a look at the code and tell me what I'm doing wrong.

[TiGGi]

I don't really see anything wrong with your code at first look, check the table/field names. Also on file write do you need nameconflict?

[Dan_Bracuk]

It might be your cfinvoke tag. Hard to tell, I can't see all of it.

What I did notice is,

inside your function you have a group by clause that is unnecessary.

you have a cfif tag where you are looking at something from a query but you are not specifying the row number.

[reya276]

yes the group by is necessary as the Master_IPA can be repeated several times, and since I only want to bring back one instance of each then the GROUP BY is necessary.

[Dan_Bracuk]

quote:

---

Originally posted by: reya276
yes the group by is necessary as the Master_IPA can be repeated several times, and since I only want to bring back one instance of each then the GROUP BY is necessary.

---

Using group by will work, but using select distinct makes your query more intuitive.

[reya276]

also there is nothing wrong with the cfinvoke tage, for some reason it seems as if the process is timing out, getting a Jrun 500 Null error should not happen as the code works and there are no errors. This seems like ColdFusion can't handle large queries or process that take a while to execute. Because VB does it just fine, but in this case we need this to be done on the web.

[reya276]

no ASP/.NET is not an option

[Newsgroup_User]

reya276 wrote:
> also there is nothing wrong with the cfinvoke tage, for some reason it seems as
> if the process is timing out, getting a Jrun 500 Null error should not happen
> as the code works and there are no errors. This seems like ColdFusion can't
> handle large queries or process that take a while to execute. Because VB does
> it just fine, but in this case we need this to be done on the web.
>

Two notes:

One if you are getting the JRun 500 null error, IIRC 90% of the time
this is a generic error message because the ColdFusion Administrator has
not be configured to send a specific error.

Secondly ColdFusion can be told to process a request for a month of
Sundays if one wants it to, the default is 60 seconds. This is a
balancing act, since allowing long running threads to build up can cause
a server to preform poorly. But if it is required, it is required. One
can adjust this either globally from the CF Administrator with the
"Timeout Request after (seconds)" setting or for a specific template
with a <cfsetting requesttimeout="{seconds}"> tag at the beginning of
the file.

[Guest]

A few quick suggestions:

How many rows does this return?

SELECT *
FROM dbo.CapPremSpecInload
WHERE MASTER_IPA = '#curLine#'
ORDER BY ACTIVITY_DATE ASC

If this query returns multiple rows, use SELECT TOP 1 (if on SQLServer, on MYSQL use LIMIT) to limit returned rows to one since you're only outputting one row in your cffile append statement. Should the output file contain all returned rows? Should the code loop through this entire resultset and performing a cffile append for every row instead?

Specify column names in preference to using SELECT * FROM.

Get your DBA to optimise the table indexes in dbo.CapPremSpecInload. Candidates for indexing would be MASTER_IPA and ACTIVITY_DATE.

[reya276]

ok some of these suggestions worked, I no longer get the Jrun Server 500 NUll error but the query still does not execute except for the last Master_IPA code.
<!------Revised Code------>
<cfsetting requesttimeout="500000">
<cfparam name="form.data_var" default="0">
<cfswitch expression="#form.data_var#">
<cfcase value="1">
<cfinvoke component="dataCOM" method="getCapdata" returnvariable="ipaList">
<cfset List = #ValueList(ipaList.MASTER_IPA,",")#>
<CFSET CRLF = chr(13) & chr(10)>
<cfoutput>
<cfloop index="curLine" list="#List#" delimiters = ",">
<cfset fields = #TRIM(curLine)#>
<cfset filePath = #GetDirectoryFromPath(ExpandPath("."))#&"downloads\"&#TRIM(curLine)#&"_capPremSpec_"&#DateFormat(NOW(),'mmddyyyy')#&".txt">
<cffile action="write" file="#filePath#" output="WDW_PRODUCT|MASTER_IPA|WDW_IPA_ID|WDW_PCP_ID|EXTERNAL_PCP_ID|SEQ_MEMB_ID|ACTIVITY_DATE|ADJUSTMENT|FUNDING_AB|FUNDING_D|FUNDING_MEMBER_PREMIUM|GROSS_DC_ABOVE_ATTACH_PNT_AMT|IBNR|INST_CLAIM|IPA_CLAIM|IPA_DISBURSEMENTS|LOW_INCOME_COST_SHARING_AMT|MED_EXP_AB|MED_EXP_D|MEMBER_COUNT|MEMBER_PREMIUM|NET_AB|NET_PART_D|PART_D_INCLUDE_FLAG|PART_D_PREMIUM|PCP_CAP|PHARMACY|PLAN_DISBURSEMENTS|PREMIUM|PROF_CLAIM|REINSURANCE|RISK_CORRIDOR|RX_CLAIM_DOLLARS|RX_STOP_LOSS|SPEC_CAP|SSC_AMT|STOP_LOSS|WDW_FUNDING_COUNTY|PLAN_CODE|PCP_LAST_NAME|PCP_FIRST_NAME|WDW_INPUT_CHANNEL|WDW_COHORT|WDW_ELIG_CATEGORY|MASTER_DOB|MASTER_SEX|MEDICAID_NO|MEDICARE_NO|SUBSCRIBER_ID|LAST_NAME|FIRST_NAME|ADDRESS_LINE_1|ADDRESS_LINE_2|CITY|WDW_STATE|ZIP|RISK_SCORE_AB|RISK_SCORE_D|HOSPICE|ESRD|INSTITUTIONAL|NURSING_HOME_CERTIFIABLE|MEDICAID|MEDICAID_ADD_ON|PREVIOUS_DISABLE|PHONE|REGION|WDW_PRODUCT|MASTER_IPA|WDW_IPA_ID|WDW_PCP_ID|EXTERNAL_PCP_ID|SEQ_MEMB_ID|ACTIVITY_DATE|ADJUSTMENT|FUNDING_AB|FUNDING_D|FUNDING_MEMBER_PREMIUM|GROSS_DC_ABOVE_ATTACH_PNT_AMT|IBNR|INST_CLAIM|IPA_CLAIM|IPA_DISBURSEMENTS|LOW_INCOME_COST_SHARING_AMT|MED_EXP_AB|MED_EXP_D|MEMBER_COUNT|MEMBER_PREMIUM|NET_AB|NET_PART_D|PART_D_INCLUDE_FLAG|PART_D_PREMIUM|PCP_CAP|PHARMACY|PLAN_DISBURSEMENTS|PREMIUM|PROF_CLAIM|REINSURANCE|RISK_CORRIDOR|RX_CLAIM_DOLLARS|RX_STOP_LOSS|SPEC_CAP|SSC_AMT|STOP_LOSS|WDW_FUNDING_COUNTY|PLAN_CODE|PCP_LAST_NAME|PCP_FIRST_NAME|WDW_INPUT_CHANNEL|WDW_COHORT|WDW_ELIG_CATEGORY|MASTER_DOB|MASTER_SEX|MEDICAID_NO|MEDICARE_NO|SUBSCRIBER_ID|LAST_NAME|FIRST_NAME|ADDRESS_LINE_1|ADDRESS_LINE_2|CITY|WDW_STATE|ZIP|RISK_SCORE_AB|RISK_SCORE_D|HOSPICE|ESRD|INSTITUTIONAL|NURSING_HOME_CERTIFIABLE|MEDICAID|MEDICAID_ADD_ON|PREVIOUS_DISABLE|PHONE|REGION" addnewline="yes" />
<cfquery name="textFiles" datasource="#request.dsn#">
SELECT TOP 100 WDW_PRODUCT,MASTER_IPA,WDW_IPA_ID,WDW_PCP_ID,EXTERNAL_PCP_ID,SEQ_MEMB_ID,ACTIVITY_DATE,
ADJUSTMENT,FUNDING_AB,FUNDING_D,FUNDING_MEMBER_PREMIUM,GROSS_DC_ABOVE_ATTACH_PNT_AMT,IBNR,INST_CLAIM,
IPA_CLAIM,IPA_DISBURSEMENTS,LOW_INCOME_COST_SHARING_AMT,MED_EXP_AB,MED_EXP_D,MEMBER_COUNT,MEMBER_PREMIUM,
NET_AB,NET_PART_D,PART_D_INCLUDE_FLAG,PART_D_PREMIUM,PCP_CAP,PHARMACY,PLAN_DISBURSEMENTS,PREMIUM,PROF_CLAIM,
REINSURANCE,RISK_CORRIDOR,RX_CLAIM_DOLLARS,RX_STOP_LOSS,SPEC_CAP,SSC_AMT,STOP_LOSS,WDW_FUNDING_COUNTY,PLAN_CODE,
PCP_LAST_NAME,PCP_FIRST_NAME,WDW_INPUT_CHANNEL,WDW_COHORT,WDW_ELIG_CATEGORY,MASTER_DOB,MASTER_SEX,MEDICAID_NO,
MEDICARE_NO,SUBSCRIBER_ID,LAST_NAME,FIRST_NAME,ADDRESS_LINE_1,ADDRESS_LINE_2,CITY,WDW_STATE,ZIP,RISK_SCORE_AB,
RISK_SCORE_D,HOSPICE,ESRD,INSTITUTIONAL,NURSING_HOME_CERTIFIABLE,MEDICAID,MEDICAID_ADD_ON,PREVIOUS_DISABLE,
PHONE,REGION,WDW_PRODUCT,MASTER_IPA,WDW_IPA_ID,WDW_PCP_ID,EXTERNAL_PCP_ID,SEQ_MEMB_ID,ACTIVITY_DATE,
ADJUSTMENT,FUNDING_AB,FUNDING_D,FUNDING_MEMBER_PREMIUM,GROSS_DC_ABOVE_ATTACH_PNT_AMT,IBNR,INST_CLAIM,
IPA_CLAIM,IPA_DISBURSEMENTS,LOW_INCOME_COST_SHARING_AMT,MED_EXP_AB,MED_EXP_D,MEMBER_COUNT,MEMBER_PREMIUM,
NET_AB,NET_PART_D,PART_D_INCLUDE_FLAG,PART_D_PREMIUM,PCP_CAP,PHARMACY,PLAN_DISBURSEMENTS,PREMIUM,PROF_CLAIM,
REINSURANCE,RISK_CORRIDOR,RX_CLAIM_DOLLARS,RX_STOP_LOSS,SPEC_CAP,SSC_AMT,STOP_LOSS,WDW_FUNDING_COUNTY,PLAN_CODE,
PCP_LAST_NAME,PCP_FIRST_NAME,WDW_INPUT_CHANNEL,WDW_COHORT,WDW_ELIG_CATEGORY,MASTER_DOB,MASTER_SEX,MEDICAID_NO,
MEDICARE_NO,SUBSCRIBER_ID,LAST_NAME,FIRST_NAME,ADDRESS_LINE_1,ADDRESS_LINE_2,CITY,WDW_STATE,ZIP,RISK_SCORE_AB,
RISK_SCORE_D,HOSPICE,ESRD,INSTITUTIONAL,NURSING_HOME_CERTIFIABLE,MEDICAID,MEDICAID_ADD_ON,PREVIOUS_DISABLE,PHONE,REGION
FROM dbo.CapPremSpecInload
WHERE MASTER_IPA ='#TRIM(curLine)#'
</cfquery>
<cfset msg = "file"&#TRIM(curLine)#&" created!">
#msg#<br />

</cfloop>
</cfoutput>
<cfoutput>

<cfloop query="textFiles">
<cffile action="append" file="#filePath#" output="#WDW_PRODUCT#|#MASTER_IPA#|#WDW_IPA_ID#|#WDW_PCP_ID#|#EXTERNAL_PCP_ID#|#SEQ_MEMB_ID#|#ACTIVITY_DATE#|#ADJUSTMENT#|#FUNDING_AB#|#FUNDING_D#|#FUNDING_MEMBER_PREMIUM#|#GROSS_DC_ABOVE_ATTACH_PNT_AMT#|#IBNR#|#INST_CLAIM#|#IPA_CLAIM#|#IPA_DISBURSEMENTS#|#LOW_INCOME_COST_SHARING_AMT#|#MED_EXP_AB#|#MED_EXP_D#|#MEMBER_COUNT#|#MEMBER_PREMIUM#|#NET_AB#|#NET_PART_D#|#PART_D_INCLUDE_FLAG#|#PART_D_PREMIUM#|#PCP_CAP#|#PHARMACY#|#PLAN_DISBURSEMENTS#|#PREMIUM#|#PROF_CLAIM#|#REINSURANCE#|#RISK_CORRIDOR#|#RX_CLAIM_DOLLARS#|#RX_STOP_LOSS#|#SPEC_CAP#|#SSC_AMT#|#STOP_LOSS#|#WDW_FUNDING_COUNTY#|#PLAN_CODE#|#PCP_LAST_NAME#|#PCP_FIRST_NAME#|#WDW_INPUT_CHANNEL#|#WDW_COHORT#|#WDW_ELIG_CATEGORY#|#MASTER_DOB#|#MASTER_SEX#|#MEDICAID_NO#|#MEDICARE_NO#|#SUBSCRIBER_ID#|#LAST_NAME#|#FIRST_NAME#|#ADDRESS_LINE_1#|#ADDRESS_LINE_2#|#CITY#|#WDW_STATE#|#ZIP#|#RISK_SCORE_AB#|#RISK_SCORE_D#|#HOSPICE#|#ESRD#|#INSTITUTIONAL#|#NURSING_HOME_CERTIFIABLE#|#MEDICAID#|#MEDICAID_ADD_ON#|#PREVIOUS_DISABLE#|#PHONE#|#REGION#|#WDW_PRODUCT#|#MASTER_IPA#|#WDW_IPA_ID#|#WDW_PCP_ID#|#EXTERNAL_PCP_ID#|#SEQ_MEMB_ID#|#ACTIVITY_DATE#|#ADJUSTMENT#|#FUNDING_AB#|#FUNDING_D#|#FUNDING_MEMBER_PREMIUM#|#GROSS_DC_ABOVE_ATTACH_PNT_AMT#|#IBNR#|#INST_CLAIM#|#IPA_CLAIM#|#IPA_DISBURSEMENTS#|#LOW_INCOME_COST_SHARING_AMT#|#MED_EXP_AB#|#MED_EXP_D#|#MEMBER_COUNT#|#MEMBER_PREMIUM#|#NET_AB#|#NET_PART_D#|#PART_D_INCLUDE_FLAG#|#PART_D_PREMIUM#|#PCP_CAP#|#PHARMACY#|#PLAN_DISBURSEMENTS#|#PREMIUM#|#PROF_CLAIM#|#REINSURANCE#|#RISK_CORRIDOR#|#RX_CLAIM_DOLLARS#|#RX_STOP_LOSS#|#SPEC_CAP#|#SSC_AMT#|#STOP_LOSS#|#WDW_FUNDING_COUNTY#|#PLAN_CODE#|#PCP_LAST_NAME#|#PCP_FIRST_NAME#|#WDW_INPUT_CHANNEL#|#WDW_COHORT#|#WDW_ELIG_CATEGORY#|#MASTER_DOB#|#MASTER_SEX#|#MEDICAID_NO#|#MEDICARE_NO#|#SUBSCRIBER_ID#|#LAST_NAME#|#FIRST_NAME#|#ADDRESS_LINE_1#|#ADDRESS_LINE_2#|#CITY#|#WDW_STATE#|#ZIP#|#RISK_SCORE_AB#|#RISK_SCORE_D#|#HOSPICE#|#ESRD#|#INSTITUTIONAL#|#NURSING_HOME_CERTIFIABLE#|#MEDICAID#|#MEDICAID_ADD_ON#|#PREVIOUS_DISABLE#|#PHONE#|#REGION#" addnewline="yes" />--->
</cfloop>
</cfoutput>
</cfcase>
</cfswitch>
<!------Revised Code------>

[Guest]

good good... OK, looks like the query itself does execute for every MASTER_IPA, but your 'cffile append' loop needs to be nested inside the curLine list loop in order to be executed once for each query resultset. (as the code is written above, the cffile append will only work on the final cfquery resultset).

try something like this:

<cfloop list>
<cfquery for current list item>
</cfquery>
<cffile write the file and its column headers>
<cfloop over results of query>
<cffile append query result row to file>
</cfloop>
</cfloop>

- is there a reason why there's no ORDER BY clause in the SELECT TOP... query?

- is there a reason to SELECT exactly TOP 100 rows for each MASTER_IPA given that you're not ordering the resultset and not adding any conditionals to your WHERE clause to apply boundaries to the results?

[reya276]

no not at all this was just some testing I was doing, the TOP stuff will be removed. Thanks for the help

[reya276]

I'm still getting the same issue, I think the problem is that the loop is trying to do everything at once instead of waiting until one query for a particualer IPA code is process and dumped to the file and then start the next. Which brings me to my next question does coldfusion have something like a For Loop. I realized this when I did a TOP 100 and all the files where written with the correct data dump, but once I removed the TOP 100 or made it TOP 100 Percent the Jrun Server 500 NULL error came back.

[reya276]

Ok so I know now that the code works correctly and it does what is suppose to do. But is it too much data for coldfusion to handle? What does the server time one if I bring back more that 800 lines for each IPA. This just does not seem correct. I anyone can give me an answer on this I would appreciate it. Thanks.

[Guest]

the request might be timing out because CFFILE is pretty slow.

a couple of things you could try:

1. create the content of the text file in a variable first a line at a time, then when you're done, write the file out to disk. some simple pseudocode...

<cfset textFileContent = "">
<cfloop over query results>
<cfset lineContent = query.col1 & "|" & query.col2 etc...>
<cfset textFileContent = textFileContent & lineContent & CRLF>
</cfloop>
<cffile write out textFileContent to disk>

string handling in CF is traditionally pretty slow, so you could try writing to an array instead of a list.

2. use the underlying Java file IO classes instead of CFFILE to write the data to disk

http://www.burnette.us/blog/index.cfm/2006/1/30/Using-Java-Instead-of-cffile-to-Write-to-Disk
http://www.bennadel.com/blog/305-ColdFusion-CFFile-vs-Java-java-io-BufferedOutputStream.htm
http://www.dgrigg.com/post.cfm/07/21/2006/CFFILE-vs-JavaIOBufferedWriter

hope this helps

[-__cfSearching__-]

Have you actually examined the code to see how much time is spent on each step? A few things jump out at me. You are performing a database query for every iteration of the outer loop and an io operation (ie file append) for every iteration of the inner loop. Depending on the number of loops, that may add up to a lot of time.

Exactly how many database queries are we talking about, and how many records/files total?