While working on importing video data into the Oracle database I came upon an continued error message from ColdFusion that went like (among others):
“The request has exceeded the allowable time limit Tag: CFSTOREDPROC”
This was strange because I have used the code in question for a long time and uploaded even large video files (50MB or more) to the database without problems. After some further investigation trough the code I could not find anything that went wrong. So I decided to read up upon <cfstoredproc> in the Adobe manual (when are they going to change this to make it really work? As of today it is still very slow and a pain to read). Anyhow, I could not find anything to pass or to fix it. I then tried to put a <cfsetting Requesttimeout = “xxx”> on top of my page to let ColdFusion know that this page takes longer then usual. Unfortunately this did not solve the problem, also. The reason is written up in this Adobe Technote where it states that:
“There are certain tags that will not “obey” a timeout, whether it’s set in the ColdFusion Administrator or in code on the CFM page. These tags are cfstoredproc,cfcontent, cfftp, cfexecute and cfobject. Also, CFX tags fall into the category of not obeying the ColdFusion timeout setting. While these tags are executing, ColdFusion must wait until the action completes before it can check for a Request Timeout. Therefore, if ColdFusion never receives a response from either the database or the objectcfobject is calling, a hung thread occurs and only restarting the ColdFusion service will clear this out.”
So this left me with…. nothing…..not quite right.
Walking trough my code let me think that what I actually do and need is some sort of “serialization” of my whole code statement (it was not only the stored procedure that executed but many more commands like file i/o operations, db selects, etc.). Funny thing is that the solution is quite easy and I have used it many times before to make sure that no other request could interfere with my current process and that is a simple wrapping of my whole code with <cftransaction>.
Reading up on the LiveDocs about cftransaction will help us out some more what is going on but I think the important part is this sentence:
“CFTRANSACTION can be used to group multiple queries that use CFQUERY into one business event. Changes to data that is requested by the queries are not committed to the datasource until all actions within the transaction block have executed successfully.”
Looks like <cftransaction> can also help out with cfstoredproc and other processes within it. So far, since I used cftransaction around my code all is working out fine and all commands work as expected.
