Is there any limitation with number queries/statements we can write inside cftransaction?

Question

Today while fixing bugs in some existing code I found a strange error.

Branch target offset too large for short

After searching I found that it is something to do with Java byte code conversion. Here are the links I found:

• Branch target offset too large for short
• Branch Target Offset Error
• Why does a long cfc file work in CF8, but not CF9? Getting "Branch target offset too large for short" error

In my case cftransaction contains around 870 statements and it's working fine. But I need to add 2 more queries to this transaction. Now I am getting this error when I am adding even one line of code inside cftransaction. Currently I can not move any of the existing cfquery out of the cftransaction.

Here is the overall structure of the code:

<cftransaction action="begin">

   <cfif URL.action eq 'add'>
         Around 200 lines of queries/statements
   <cfelseif URL.action eq 'edit'>
        Around 200 lines of queries/statements
   </cfif>

    <cfif URL.action eq 'add' or URL.action 'edit'>
          Around 450 lines of queries/statements
    </cfif>

</cftransaction>

Is there any workaround to fix this problem?

Guildsman

You could always move all that sql to database stored procedures. – Chism

Are you certain the transaction is the cause? One of the threads mentioned transactions were not involved and splitting things into smaller functions/components resolved the issue. Some things to test A) Does it work if you temporarily remove the transaction? b) Does it work if you rearranged the code into smaller functions (or cfc's if neeed), then call those from within a transaction? c) Having said all that, it does sounds like a lot of SQL for a query. (That said, IMO complex sql logic is better packaged in a stored procedure rather than a query.) – Quoin

@Leigh: A) Yes it works fine when I removed the transaction.B) Yes I tried its working. C) I am also planning to package it inside a stored procedure but the thing is this requires a lot of time/effort/testing and I am not sure whether the client will be ready for that or not , that's why searching for small workarounds. – Guildsman

@DeepakKumarPadhy - Interesting. Sounds like option B is a good option for now. Though, long term - stored procs is usually the best option for complex sql IMO. – Quoin

Answer 1

Branch offset has to do with the size of the module/function. It can also be caused due to a large conditional code block of cfif/cfelse or cfswitch.

Technically, I am not sure if there is any cap on the no. of queries you can put inside the cftransaciton block. It has nothing to do with the code migration from CF8 to CF9 but the length of your code inside conditional blocks.

I would want to split the function and try to put the each of the big sized conditional blocks as a separate function inside the cfc:

 <cffunction name="myFunc1">
    <cftransaction action="begin">
      <cfif URL.action eq 'add'>
        <!--- function call with your xxx lines of queries/statements --->
        <cfinvoke component="MyCfc" method="firstQueryBlock" result="result1">
      <cfelseif URL.action eq 'edit'>
        <!--- second function call with your yyy lines of queries/statements --->
        <cfinvoke component="MyCfc" method="secondQueryBlock" result="result2">
      </cfif>

       <cfif URL.action eq 'add' or URL.action 'edit'>
            <!--- third function call with your zzz lines of queries/statements --->
            <cfinvoke component="MyCfc" method="thirdQueryBlock" result="result3">
       </cfif>
    </cftransaction>
 </cffunction>

Unconsidered