6 Replies Latest reply on Nov 11, 2011 6:37 AM by insuractive

    How to run cfstoredproc in the background

    biene22 Level 1

      Hi,

       

      I'm looking for a possibility to run an oracle procedure via cfstoredproc

      and continue my CF-page immediately afterI starting the procedure.

       

      I need to do that because the procedure sometimes needs more than 10 mintues  so that I get

      a timeout.

       

      Does anybody has an idea?

       

      regards Claudia

        • 1. Re: How to run cfstoredproc in the background
          Adam Cameron. Level 5

          Google "cfthread", or look it up in the docs.  That will do what you need.

           

          --
          Adam

          • 2. Re: How to run cfstoredproc in the background
            biene22 Level 1

            Okay, thank you.

            I'll test it.

            • 3. Re: How to run cfstoredproc in the background
              biene22 Level 1

              I tested cfthread, but still I have the same problem like before(page timeout).

               

              Here is what I do:

               

              Start Database procedure to create a data export file. It will be saved on the server.

               

              <cfthread  name="t1" action="run" priority="normal">

              <cfstoredproc procedure="exp" datasource="test" returncode="no" >

                  <cfprocparam type="in" dbvarname="id"  cfsqltype="cf_sql_integer" value="#url.id#">   

              </cfstoredproc>

              </cfthread>

               

              When the procedure finished, (this can take some minutes), the export file is opened.

              If it is a big export I get a page timeout. If the export is fast enough a popup window opens to download or

              open the file.

               

              <cfthread  name="t1" action="join"  />

               

                <cfheader

                  name="content-disposition" charset="utf-8"

                  value="attachment; filename=export.txt"

                  />

               

              <cfcontent type = "text/plain" 

                  file = "export" 

                  deleteFile = "no">

               

               

              What's wrong with my code? How to tell the page to wait until the procedure is finished and not to get a timeout?

              • 4. Re: How to run cfstoredproc in the background
                Adam Cameron. Level 5

                If you have a request timeout set in CFAdmin, then that will be enforced irrespective of how the code is called.  If you need more time than that, you can override it with CFSETTING.

                 

                --

                Adam

                • 5. Re: How to run cfstoredproc in the background
                  Owain North Level 4

                  I think you misunderstand what it is you're doing - it looks to me as if you're trying to get the file downloaded to the browser once it's finished?

                   

                  Yes, the CFTHREAD tag is running it in the background, but you're then doing a <cfthread action="join">, which makes the main thread wait for the other one to finish, rendering the other thread essentially pointless. All you're doing is making on thread wait whilst another does the work, which is only useful if you're doing more than one thing at once. It looks there like you're only doing one task, so having more than one thread is pointless.

                   

                  You have two choices - run the proc in your main thread and get output to the screen afterwards (as you are now), or run it in a different thread but accept you have severed all ties between the page and the running thread - you cannot get a running background thread to suddenly come back and connect to someone's browser once their page has loaded.

                  • 6. Re: How to run cfstoredproc in the background
                    insuractive Level 3

                    Also be aware that depending on what CF server you are using (standard, enterprise, etc) you may be limited to the number of threads that your CF server can have active at any given time.  Its usually not a problem, but can cause unexpected problems if you are planning a high traffic application.