Rollback with CFTRANSACTION

You may be surprised to find out that software documentation is not always clear or complete. Shocking, I know. Even when it is, sometimes I just have to see things in action for myself. This is one of those cases.

A question came up today at work about the <cftransaction> tag. (Yes, we still use ColdFusion. There are dozens of us. Dozens!) The question was if the tag would automatically rollback changes or if you needed to explicitly type <cftransaction action="rollback"> to rollback the changes. I have always assumed the latter.

So here is my test case. First I created 2 test tables in the database for testing each with a test record.

CREATE TABLE test_table_1 (
  tt1_record_id NUMBER(3) NOT NULL,
  tt1_text VARCHAR2(100),
  CONSTRAINT pk_test_table_1 PRIMARY KEY (tt1_record_id)
);

CREATE TABLE test_table_2 (
  tt2_record_id NUMBER(3) NOT NULL,
  tt2_text VARCHAR2(100),
  CONSTRAINT pk_test_table_2 PRIMARY KEY (tt2_record_id)
);

INSERT INTO test_table_1 (
  tt1_record_id,
  tt1_text
)
VALUES (
  1,
  'Blue'
);

INSERT INTO test_table_2 (
  tt2_record_id,
  tt2_text
)
VALUES (
  1,
  'Triangle'
);

And now the ColdFusion code. This block of code first outputs the text from the database. Then it updates the first table while intentionally failing to update the second table. Lastly, it displays the text from the database.

<cfquery name="variables.qryBefore" datasource="#application.dsn#">
SELECT
  tt1_text, tt2_text
FROM
  test_table_1,
  test_table_2
WHERE
  tt1_record_id = tt2_record_id
</cfquery>

<p>
    <cfoutput>
        Before: 
        #variables.qryBefore.tt1_text#
        #variables.qryBefore.tt2_text#
    </cfoutput>
</p>

<cftry>
    <cftransaction>
        <cfquery name="variables.qryUpdate1" datasource="#application.dsn#">
            UPDATE
                test_table_1
            SET
                tt1_text = 'Red'
            WHERE
                tt1_record_id = 1
        </cfquery>

        <cfquery name="variables.qryUpdate2" datasource="#application.dsn#">
            UPDATE
                test_table_2
            SET
                tt2_text = 'Square'
            WHERE
                foo = bar
        </cfquery>
    </cftransaction>
    <cfcatch type="any">
        <p>
            Database update failed.  Changes should have been rolled back.
        </p>
    </cfcatch>
</cftry>

<cfquery name="variables.qryAfter" datasource="#application.dsn#">
SELECT
  tt1_text, tt2_text
FROM
  test_table_1,
  test_table_2
WHERE
  tt1_record_id = tt2_record_id
</cfquery>

<p>
    <cfoutput>
        After: 
        #variables.qryAfter.tt1_text#
        #variables.qryAfter.tt2_text#
    </cfoutput>
</p>

The database values remained the same. The first table was updated but when the second table failed to update, the changes were rolled back. I could have added <cftransaction action="rollback"> to the <cfcatch> block and gotten the same result.

So why use the rollback command then? You may only want to rollback to a certain savepoint. Another use case I have found for it is unit testing. In my test, I want to execute the entire method even the database code. However, before I leave the <cftransaction> block, I rollback my changes:

<cfif this.testing>
 <cftransaction action="rollback" />
</cfif>

Clipboard Managers

I do a lot of copying and pasting. A lot. One of the things that often happens is that I copy a piece of information, paste it, copy something else, paste it, then need to go back to that previous information I copied. Or maybe I need to copy two (or more) pieces of information from one source before switching to the other source. The best way to handle this is with a clipboard manager. These tools allow you to keep a history of everything you have copied (up to a limit) and the refer back to them. Here are a couple that I have used in my job:

ClipMenu for MacOX (Updated 12/06/18 with new link)

Ditto for Windows

ColdFusion 2016 Broke My API (And How I Fixed It)

While on ColdFusion 9, I built a REST API for our an application at work. Everything was working fine until we upgraded to ColdFusion 2016. When I went to test the API, I got an HTTP 500 error. The Apache log gave me no clues to what was happening, so my next stop was the ColdFusion logs. In the exception log, I found this:

Error","ajp-nio-8015-exec-9","03/09/17","07:22:12",,"Application  could not be found. The specific sequence of files included or processed is: '''' "
javax.servlet.ServletException: Application  could not be found.
        at coldfusion.rest.servlet.CFRestServlet.invoke(CFRestServlet.java:512)
        at coldfusion.rest.servlet.RestFilter.invoke(RestFilter.java:60)
        at coldfusion.filter.ExceptionFilter.invoke(ExceptionFilter.java:94)
        ...

A quick Google search did not reveal much. However, the fact that it was calling the CFResetServlet gave me an idea. What if the /api directory now had special meaning? So I renamed the /api directory to /API-TEST and sure enough my code was working again.
I found the solution in the web.xml file:

    <servlet-mapping id="coldfusion_mapping_16">
        <servlet-name>CFRestServlet</servlet-name>
        <url-pattern>/api/*</url-pattern>
    </servlet-mapping>

This mapping is forcing everything in the /api directory to be processed by the ColdFusion REST service. To fix this, I simply commented out this section of the configuration and restarted the ColdFusion service.

    <--
    <servlet-mapping id="coldfusion_mapping_16">
        <servlet-name>CFRestServlet</servlet-name>
        <url-pattern>/api/*</url-pattern>
    </servlet-mapping>
    -->