6 Replies Latest reply on Dec 27, 2010 3:01 AM by Owain North

    CFTransaction/CFCatch/CFTry

    weezerboy Level 1

      I have an insert query that parses a large text file into a query object and then loops through it and inserts it into a data table.

       

      However sometimes there are bad values are in the text file...like a text value  where a date should be etc.  or a nonnumeric value where a numeric should be.....  This causes the loo/ insert query to STOP.

       

      If I get all good values I want the data to be inserted and have processing continue.

       

      However If I get a bad value in the file I want all processing to stop and all of the prior inserted rows to be deleted and I want to get an error message emailed to me

       

      How do I do this?

       

      Here is the Cfloop and the insert query below..again the problem is that if I get a bad value I want processing to stop and the data table to rollback to the initial state prior to any processing.....and I want an email alerting me there was an issue ( I know how to use CFMAIL)

       

      <cftransaction>

      <cfloop query="stocks">
          <CFQUERY name="load_the_data" datasource="abc" >
          INSERT INTO portfolios_new (portfolio , portfolio_ID , load_date , ticker , cusip,BC1, BC1_ID , BC2 , BC2_ID , BC3 , BC3_ID, Load_ID)

      VALUES ('#Portfolio#'
          ,'#PORTFOLIOID#','#loadDate#','#Ticker#','#Cusip#','#BC1#','#BC1Id#','#BC2#','#BC2Id#','# BC3#','#BC3Id#', '#next_load_id#' );
          </CFQUERY>
        </cfloop>

       

       

      </cftransaction>

        • 1. Re: CFTransaction/CFCatch/CFTry
          Dave Watts Adobe Community Professional

          You can do this by using CFIF within your CFTRANSACTION to catch the bad values, then have a nested CFTRANSACTION with ACTION="ROLLBACK" within the CFIF.

           

          <cfif [bad value]>

               <cftransaction action="rollback"/>

               <cfmail ...></cfmail>

               <cfbreak>

          </cfif>

           

          That said, why not check the values as you're reading them in the first place? That way, you can avoid writing the rows to the database if there's a bad one.

           

          Dave Watts, CTO, Fig Leaf Software

          http://www.figleaf.com/

          http://training.figleaf.com/

           

          Read this before you post:

          http://forums.adobe.com/thread/607238

          • 2. Re: CFTransaction/CFCatch/CFTry
            weezerboy Level 1

            Dave,

             

            I am going to look into this : That said, why not check the values as you're reading them in the first place? That way, you can avoid writing the rows to the database if there's a bad one.

             

            But on this below...I guess instead of , I'd need to know if there was any kind of error.

            Isn't there a way to do a cfif to see if there is any type of database error...or any kind of error for that matter

             

            What would that CFIF look like?

             

            <cfif >

                  

             

             

             

            Patrick Hutchinson

             

            www.1design.com

             

            Office:   804-360-0695

            Cell:       804-370-6873

            Email:     pat@1design.com

            • 3. Re: CFTransaction/CFCatch/CFTry
              Dave Watts Adobe Community Professional

              In the case you had, with a CFTRANSACTION around a CFLOOP, if any of the queries within the loop failed the transaction would automatically roll back.

               

              Otherwise, you can capture database errors using CFTRY or the onError event handler of Application.cfc.

               

              <cftry>

               

                   <cfquery ...>...</cfquery>

               

                   <cfcatch type="database">

                   ... do something here ...

                   </cfcatch>

               

              </cftry>

               

              Dave Watts, CTO, Fig Leaf Software

              http://www.figleaf.com/

              http://training.figleaf.com/

               

              Read this before you post:

              http://forums.adobe.com/thread/607238

              • 4. Re: CFTransaction/CFCatch/CFTry
                Owain North Level 4

                How about this, pretty sure this'd work:


                <cftransaction>
                    <cfloop>
                        <cftry>
                            <cfquery>
                                INSERT...
                            </cfquery>
                            <cftransaction action="commit" />
                        <cfcatch>
                            <!--- Log, whatever --->
                        </cfcatch>
                        </cftry>
                    </cfloop>
                </cftransaction>

                 

                So rather than avoiding rollbacks, you're explicitly committing after a successful insert.

                 

                As has been suggested it's normally best to validate the line so you know it'll work rather than relying on exceptions, but sometimes the simplest methods will do just fine.

                 

                O.

                • 5. Re: CFTransaction/CFCatch/CFTry
                  Dave Watts Adobe Community Professional

                  Based on the OP's initial requirements, that won't work - he wants to rollback if any rows are bad, not commit every row but the bad ones. With your code sample, the CFTRANSACTION tags don't actually do anything, and you'd get exactly the same result if you removed them.

                   

                  Dave Watts, CTO, Fig Leaf Software

                  http://www.figleaf.com/

                  http://training.figleaf.com/

                   

                  Read this before you post:

                  http://forums.adobe.com/thread/607238

                  • 6. Re: CFTransaction/CFCatch/CFTry
                    Owain North Level 4

                    Ah yes good spot, sorry I misread that.

                     

                    In which case it's easy to change:

                     

                    <cftransaction>
                        <cfloop>
                            <cftry>
                                <cfquery>
                                    INSERT...
                                </cfquery>
                            <cfcatch>

                                <cftransaction action="rollback" />

                                <cfmail .../>
                                <!--- Log, whatever --->
                            </cfcatch>
                            </cftry>
                        </cfloop>

                        <cftransaction action="commit" />
                    </cftransaction>