CFTransaction/CFCatch/CFTry

[weezerboy]

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>

[Dave Watts · Accepted solution]

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

[weezerboy]

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

[Dave Watts]

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

[Owainnorth]

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.

[Dave Watts]

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

[Owainnorth]

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>