cftransaction will only run first cfquery, skips over second

Question

I'm trying to write to two different tables, both in the same database. In one CFTRANSACTION with two CFQUERY, the first CFQUERY will INSERT properly but the second (also INSERT) is just skipped over. No errors are thrown, I can see the data in the first table, and commenting out the first INSERT will allow the second to go through as desired.

A simplified version of my code is:

<cffunction name="insertReport">
<cfset var strReturn="">
<cftransaction>
<cftry>
<cfquery name="updateTable1" datasource="DB1">
...
</cfquery>
<cfquery name="UpdateTable2" datasource="DB1">
...
</cfquery>

<cfcatch type="any">
<cfset errMsg = "#cfcatch.Message#">
</cfcatch>
</cftry>

<cfif trim(errMsg) eq ''>
<cftransaction action="commit">
<cfelse>
<cftransaction action="rollback">
<cfset strReturn = "Error: #errMsg#.">
</cfif>
</cftransaction>

<cfreturn strReturn>
</cffunction>

This is probably something really simple, but I'm stuck. Any help is appreciated.

I updated my answer. Seems to be working fine after defining errMsg. What version of CF are you running? — luxdvie, Feb 07 '18 at 15:44
ColdFusion 2016, and I've confirmed that we've allowed multiple transactions for this data source. I've also defined errMsg (and a secondary errMsg2 to capture any detail as well), but still no luck. — MLynch, Feb 07 '18 at 15:53
How are you verifying the `insert` didn't occur? I don't see how that could happen unless maybe real code contains conditional logic, like an if/else statement that skips the query under certain conditions. When I can't figure out an issue, I start troubleshooting with a VERY vanilla test case (no functions, try/catch, ....). Just the cftransaction and the two queries. If that works, start adding back the rest of the logic until things break. — SOS, Feb 07 '18 at 15:56
I'm using Sybase Central and "SQLANY12 - Interactive SQL" to view the table layouts and view the data within them. — MLynch, Feb 07 '18 at 16:00
You realize of course that try/catch prevents errors from being thrown, right? — Dan Bracuk, Feb 07 '18 at 16:12
What happens when you try the vanilla test case I mentioned above? No functions or error handling. — SOS, Feb 07 '18 at 16:13
We've been using similar try/catch statements assigning 'cfset errMsg = "#cfcatch.Message#"' in a lot of previous code, and it's always shown us when there is an error. We're not getting anything here. — MLynch, Feb 07 '18 at 16:18
That could be, but if things aren't working as expected something must be different. Time to throw expectations out the window and start with a very simple test case and go from there. Also, verify the obvious things like caching, correct datasource, tables, etc.. — SOS, Feb 07 '18 at 16:28

luxdvie · Answer 1 · 2018-02-07T15:50:45.303

Update

I just tried the below code on CF11 and it worked fine. The only error I received was that errMsg was undefined, which it isn't in your code until the <cfcatch> occurs. I defined errMsg and re-ran -- it was successful.

<cffunction name="insertReport">
    <cfset var strReturn="">
    <cfset errMsg = "">
    <cftransaction>

    <cftry>
        <cfquery name="updateTable1" datasource="DS1">
            INSERT INTO ...
        </cfquery>

        <cfquery name="UpdateTable2" datasource="DS1">
            INSERT INTO ...
        </cfquery>

    <cfcatch type="any">
        <cfset errMsg = "#cfcatch.Message#">
    </cfcatch>
    </cftry>

    <cfif trim(errMsg) eq ''>
        <cftransaction action="commit">
    <cfelse>
        <cftransaction action="rollback">
        <cfset strReturn = "Error: #errMsg#.">
    </cfif>

    </cftransaction>

    <cfreturn strReturn>
</cffunction>

<cfoutput>#insertReport()#</cfoutput>

Regarding multiple datasources

According to this Adobe forum and this SO post, you must commit changes to each datasource before continuing to the next.

According to the SO post, this will work:

<cftransaction action="begin">

    <cfquery datasource="foo">
    select * from foo_test
    </cfquery>

<cftransaction action="commit"><!-- Commit before switching DSNs --->

   <cfquery datasource="bar">
    select * from bar_test
    </cfquery>

</cftransaction>

Note that if you can access your data via one datasource using three part names, (e.g. fooDB.dbo.table ) you can write to different databases in one <cftransaction>

Of course I made a typo in my first post, because that's just my luck today. I am NOT changing datasources - it's the same database, two different tables. Really sorry about that, but thank you for being to quick! — MLynch, Feb 07 '18 at 15:24

score 0 · Answer 2 · answered Feb 08 '18 at 15:14

0

Thanks to everyone who helped. It turns out the problem was the fact that we're using CFQUERYPARAMs in the CFQUERY instead of hard-coding the values, and they were throwing Null Pointer exceptions.

It seems like those CFQUERYPARAM elements would write to the database properly when we only had one CFQUERY, but still threw that Null Pointer exception that would skip over the second CFQUERY. Because the Null Pointer only threw a "#cfcatch.type#" value, not "#cfcatch.Message#" or "#cfcatch.detail#" that our error-checking was looking for, we didn't flag the problem.

We're looking unto using jTDS now to see if that will solve the issue.

answered Feb 08 '18 at 15:14

MLynch

425
1
4
12

(Update) Normally, you *should* be using cfqueryparam, because it has lots of benefits. What makes you think it's a driver issue? Null Pointer is extremely common. Likely, it's indicating another logic error in this case. Before changing drivers, I'd figure out the actual cause first. Use `cfdump var="#cfcatch#"` to display the complete error message. – SOS Feb 08 '18 at 15:28
This thread is what led us to believe we needed another driver: https://forums.adobe.com/thread/692745 – MLynch Feb 08 '18 at 15:38
Well, it's an older thread and hard to tell without more info. A) Are you using the Sybase driver? Which driver and CF versions? B) What happened when you tried using to the Adobe driver? The OP suggested worked for them C) Can you post a small test case that reproduces the error? Here's an example of what I mean (for SQL Server, but you get the idea). https://gist.github.com/anonymous/73d3220fde94f2ef48e79ac52b139c82 – SOS Feb 08 '18 at 15:52
A) We have been using the "com.sybase.jdbc4jdbc.SybDriver" drivers for the past several years, without problems. We recently updated to ColdFusion 2016 and haven't experienced any issues related to it. B) It was suggested to me by the coworker who originally designed our site that we use the jTDS, not the Adobe. I'm just going by what he said to use. C) Your GitHub examples is pretty much what we're doing. The code writes to the database just fine, but throws that seemingly-non-fatal exception that skips over any additional CFQUERY we try to run. – MLynch Feb 08 '18 at 15:53
Well nothing wrong with using jTDS. Trying to rule out other causes that might have simpler fixes. *seemingly-non-fatal exception* Well, it *is* a fatal exception, but ... the try/catch logic changes that and allows the code to proceed anyway. What I meant by C) was, can you take a few minutes to put together a test case which triggers a NP .. that we can test independently? – SOS Feb 08 '18 at 16:01
Apologies, what I meant by "seemingly-non-fatal" is that it writes all of the desired data to the database. There's no field that's left empty, all of the data we submit is saved properly. It seems really odd that we get an error that doesn't actually show any symptoms. Here's the example code from my coworker who is also working on this: https://gist.github.com/anonymous/3e3adcac2d2e6135374eed754e73cf39 – MLynch Feb 08 '18 at 16:15
(Update) Hm... if #testNum# is "99999999999" then that 1st query should throw a totally different error, even before the query runs, because "99999999999" isn't valid for cf_sql_integer. It's too big. What's the actual data type of that column in the db? – SOS Feb 08 '18 at 17:34
Yeah, the "99999999999" was just an example. That integer will always be < 1000. We have a few data types, including integer, decimal, varchar, and dates. Forgive my ignorance (and thank you for all of this help), but would a mismatch in "cf_sql_[type]" cause the Null Pointer error even if the data saves properly? – MLynch Feb 08 '18 at 19:50
(Edit) Well, drivers are weird beasts so I won't say it's impossible, but ... I haven't had that issue. Usually it's something else in the code causing problems. Wrong "cf_sql_[type]'s" usually just throw an error OR the db tries to convert everything to the right type implicitly. That's why I was hoping to see a real example of what produces the error, and the actual error dump itself. Could be some other cause.. At the very least someone else running Sybase could confirm or disprove the theory. – SOS Feb 08 '18 at 20:40
I'm afraid my boss is paranoid about us giving away "proprietary code," so I can't offer all that much. Not very helpful, I know. But thanks again for your help, you've given me some good things to look into. – MLynch Feb 08 '18 at 20:52
Totally understandable. Though for simple cases, you can usually rig up a small, purely fictional example that demos the problem. Use silly fictional values like "widgets", "fruits" or "band names". As long it represents the actual problem, such as using "99999999999" to represent a number that's 11 digits long, the actual column names and input don't matter much. That's what I, and a lot of folks here, do. Case in point, my earlier ["John Smith"](https://gist.github.com/anonymous/73d3220fde94f2ef48e79ac52b139c82) example ;-) – SOS Feb 08 '18 at 21:06

cftransaction will only run first cfquery, skips over second

2 Answers2