Multiple UPDATE or INSERT in a Single cfquery block

dev support
1

I would normally expect that any cfquery that errors in a transaction will force all other previous cfquery blocks in the same transaction to rollback. But I noticed that if one cfquery block has multiple DML statements, it behaves differently between Lucee and Coldfusion.

On ColdFusion, if any one of the statements in the cfquery block fails, then it will error.
On Lucee, if the first statement succeeds, then any failure in the subsequent statements will be silent.

<cftransaction>
    <cftry>
        <cfquery name="insertIntoMultipleTables">
            INSERT INTO [A] VALUES('a', 'b')

            INSERT INTO [B] VALUES('a', 'b')

            INSERT INTO [C] VALUES('a', 'b')
        </cfquery>
        <cftransaction action="commit" />
        <cfcatch>
                <cftransaction action="rollback" />
        </cfcatch>
    </cftry
</cftransaction>

If we posit that object [B] doesn’t exist in the database, then on Lucee, the server does not raise an exception and the insertion into [A] will not rollback whereas on ColdFusion, the server raises an exception and everything in the transaction is rolled back.

Are there any settings on Lucee that changes this behavior or is it just how Lucee’s cfquery works and I just need to avoid having multiple statements in a single cfquery?

OS: Windows 10
Tomcat Version: Tomcat 9
Lucee Version: 5.3.8.199
Database: MS SQL Server 2019

Thank you.

2

@mpark This is already a known issue ([LDEV-3127] - Lucee). Also the issue is fixed in Lucee version 5.3.8.169-SNAPSHOT. But due to some issues with other drivers the changes were reverted to 5.3.8.186-SNAPSHOT.

You can use the changes made for LDEV-3127 using any of the options below. Can you please check with this and report here back?

System property: lucee.datasource.mssql.modern=true (OR)
Environment variable: LUCEE_DATASOURCE_MSSQL_MODERN=true
3

See this related ticket which explains a bit better IMO, not only the loss of exceptions, but Lucee’s failure to preserve ALL exceptions when there are multiple statements in the query. This is the source of these downstream issues where errors get ignored.

https://luceeserver.atlassian.net/browse/LDEV-4140

4

Hello @cfmitrah. I am reporting back.
I tried the system property and env variable and it seems to be working. When a statement other than the first statement in a cfquery block fails, Lucee now raises an exception, which can be caught and rollback the transaction.

1 Like