ColdFusion MX Components and Recordsets
For a recent project, I have been placing all my SQL queries and stored procedure calls in ColdFusion components. This has been incredibly useful since I have multiple types of the same query for various conditions, and I can just put all the logic in one query. This reduces the number of places in which I have to modify the query in the event of enhancements or bugs. It also is just good programming practice.
This approach does have its drawbacks, however. You are requred to pass the variable out of the component by reference like this:
SELECT name, age, height, weight
WHERE age < 10
When you call this component to perform this query, you have to assign the returned recordset to a handle like this, we'll assume the component has the same file name as the method:
This will return the recordset as personData, so if you wanted to read out all the values from a column you would just use:
This works great, and if you had other methods in that component, you would call those with the same invoke syntax. Here's the rub, what scope is the recordset variable personData? At first I thought that recordsets had their own scope, and weren't subject to the same rules as other variables. I attempted to perform a query of queries on the recordset after a page request, and was told by the application server that the query wasn't in memory. Then I thought that perhaps the recordset was passed by reference only to the cfinvoke return variable, that perhaps it was lost between requests, and that the reference was of the request scope, but the recordset was somehow different. I was somewhat dismayed to find that this wasn't the case. When I used the actual query name in the query of queries it still wasn't in memory.
This was a problem, I couldn't figure out how to get the recordset across requests. That was when I took it out of the component. Once I had done that my problems went away, I could perform my query of queries across requests normally. I figured that what must be happening is that after the query is performed, when the method returns the recordset, it is copied away as a struct to the return variable of the invoke statement. This struct is of the request scope and is lost between pages, unlike the recordset which must be more like the application or server scope. I solved this issue by moving the recordset to the session scope where it was possible to access it across requests.
This had to have been done on purpose, since it would be possible to have the original recordset in memory, while referencing it with the return variable from the cfinvoke. This variable could be of session scope, however this could cause problems if you had invoke variables of the same name in different parts of the application. It could cause still more problems if you wanted to invoke the method in multiple locations with distinct values for each. So why couldn't you write a rule that made recordsets exceptional? Well, they could have, but this would have compromised security and increased the memory requirements of the ColdFusion application server.
So, the resulting issue is not really a bug, but it is annoying since I couldn't find any help on this issue anywhere. Hopefully this will help anyone else who runs across this issue and can't figure out how to get around it.