15 Replies Latest reply on Feb 26, 2008 12:32 PM by -==cfSearching==-

    SQL Query to Text file

    reya276 Level 1
      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.
        • 1. Re: SQL Query to Text file
          TiGGi Level 1
          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?
          • 2. Re: SQL Query to Text file
            Dan Bracuk Level 5
            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.

            • 3. Re: SQL Query to Text file
              reya276 Level 1
              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.
              • 4. Re: SQL Query to Text file
                reya276 Level 1
                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.
                • 5. Re: SQL Query to Text file
                  reya276 Level 1
                  no ASP/.NET is not an option
                  • 6. Re: SQL Query to Text file
                    Dan Bracuk Level 5
                    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.
                    • 7. Re: SQL Query to Text file
                      Level 7
                      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.

                      • 8. Re: SQL Query to Text file
                        edgriffiths
                        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.
                        • 9. Re: SQL Query to Text file
                          reya276 Level 1
                          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_"&#Dat eFormat(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_EX P_D|MEMBER_COUNT|MEMBER_PREMIUM|NET_AB|NET_PART_D|PART_D_INCLUDE_FLAG|PART_D_PREMIUM|PCP_C AP|PHARMACY|PLAN_DISBURSEMENTS|PREMIUM|PROF_CLAIM|REINSURANCE|RISK_CORRIDOR|RX_CLAIM_DOLLA RS|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_N O|MEDICARE_NO|SUBSCRIBER_ID|LAST_NAME|FIRST_NAME|ADDRESS_LINE_1|ADDRESS_LINE_2|CITY|WDW_ST ATE|ZIP|RISK_SCORE_AB|RISK_SCORE_D|HOSPICE|ESRD|INSTITUTIONAL|NURSING_HOME_CERTIFIABLE|MED ICAID|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_M EMBER_PREMIUM|GROSS_DC_ABOVE_ATTACH_PNT_AMT|IBNR|INST_CLAIM|IPA_CLAIM|IPA_DISBURSEMENTS|LO W_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_CLA IM|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_ELI G_CATEGORY|MASTER_DOB|MASTER_SEX|MEDICAID_NO|MEDICARE_NO|SUBSCRIBER_ID|LAST_NAME|FIRST_NAM E|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|RE GION" 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,P REMIUM,PROF_CLAIM,
                          REINSURANCE,RISK_CORRIDOR,RX_CLAIM_DOLLARS,RX_STOP_LOSS,SPEC_CAP,SSC_AMT,STOP_LOSS,WDW_FUN DING_COUNTY,PLAN_CODE,
                          PCP_LAST_NAME,PCP_FIRST_NAME,WDW_INPUT_CHANNEL,WDW_COHORT,WDW_ELIG_CATEGORY,MASTER_DOB,MAS TER_SEX,MEDICAID_NO,
                          MEDICARE_NO,SUBSCRIBER_ID,LAST_NAME,FIRST_NAME,ADDRESS_LINE_1,ADDRESS_LINE_2,CITY,WDW_STAT E,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,ACTI VITY_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,P REMIUM,PROF_CLAIM,
                          REINSURANCE,RISK_CORRIDOR,RX_CLAIM_DOLLARS,RX_STOP_LOSS,SPEC_CAP,SSC_AMT,STOP_LOSS,WDW_FUN DING_COUNTY,PLAN_CODE,
                          PCP_LAST_NAME,PCP_FIRST_NAME,WDW_INPUT_CHANNEL,WDW_COHORT,WDW_ELIG_CATEGORY,MASTER_DOB,MAS TER_SEX,MEDICAID_NO,
                          MEDICARE_NO,SUBSCRIBER_ID,LAST_NAME,FIRST_NAME,ADDRESS_LINE_1,ADDRESS_LINE_2,CITY,WDW_STAT E,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_C OST_SHARING_AMT#|#MED_EXP_AB#|#MED_EXP_D#|#MEMBER_COUNT#|#MEMBER_PREMIUM#|#NET_AB#|#NET_PA RT_D#|#PART_D_INCLUDE_FLAG#|#PART_D_PREMIUM#|#PCP_CAP#|#PHARMACY#|#PLAN_DISBURSEMENTS#|#PR EMIUM#|#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_NAM E#|#WDW_INPUT_CHANNEL#|#WDW_COHORT#|#WDW_ELIG_CATEGORY#|#MASTER_DOB#|#MASTER_SEX#|#MEDICAI D_NO#|#MEDICARE_NO#|#SUBSCRIBER_ID#|#LAST_NAME#|#FIRST_NAME#|#ADDRESS_LINE_1#|#ADDRESS_LIN E_2#|#CITY#|#WDW_STATE#|#ZIP#|#RISK_SCORE_AB#|#RISK_SCORE_D#|#HOSPICE#|#ESRD#|#INSTITUTION AL#|#NURSING_HOME_CERTIFIABLE#|#MEDICAID#|#MEDICAID_ADD_ON#|#PREVIOUS_DISABLE#|#PHONE#|#RE GION#|#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#|#PREMI UM#|#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_N O#|#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#|#REGIO N#" addnewline="yes" />--->
                          </cfloop>
                          </cfoutput>
                          </cfcase>
                          </cfswitch>
                          <!------Revised Code------>
                          • 10. Re: SQL Query to Text file
                            edgriffiths Level 1
                            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?
                            • 11. Re: SQL Query to Text file
                              reya276 Level 1
                              no not at all this was just some testing I was doing, the TOP stuff will be removed. Thanks for the help
                              • 12. Re: SQL Query to Text file
                                reya276 Level 1
                                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.
                                • 13. Re: SQL Query to Text file
                                  reya276 Level 1
                                  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.
                                  • 14. Re: SQL Query to Text file
                                    edgriffiths Level 1
                                    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-D isk
                                    http://www.bennadel.com/blog/305-ColdFusion-CFFile-vs-Java-java-io-BufferedOutputStream.ht m
                                    http://www.dgrigg.com/post.cfm/07/21/2006/CFFILE-vs-JavaIOBufferedWriter

                                    hope this helps
                                    • 15. SQL Query to Text file
                                      -==cfSearching==- Level 4
                                      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?