coldfusion 8 and SQL global temp tables

[setter1234]

Hi,

We have recently upgrade to coldfusion 8.01 from cfmx 7 and have noticed something different in the behaviour of temporary tables. We sometimes create a global temporary table which is unique to a user and then access the same temporary table from a different cfm page within the same session.

ie. create Table ###variables.tempname#
and then in a different page within the same session ( and within seconds ) access it with

select * from ###variables.tempname#

This worked fine in CFMX 7 but in coldfusion 8 we are getting the error :

Invalid object name '#shill116015'

Meaning that the temporary table no longer exists.
We can access the temp table in the same page in which it is created so we know it is being created correctly.

I read that a solution is to wrap everything in a cftransaction but seeing as the select is on a different page this
isn't possible.

Any ideas ?

Thanks,
Simon,

[Dan_Bracuk]

Make it a normal table and then drop it when you are finished using it. Make sure you take steps to make the tablename unique.

[setter1234]

This is more of a workaround than the solution.
I'm more interested in what has changed between cfmx 7 and 8 to make temporary tables unusable across
separate pages.

The datasource is set to maintain the connection and we are only talking seconds between the different pages
accessing the temp table. Its global so should be accessible by everyone even though this is the same user in the same session accessing the table.

[setter1234]

Just noticed in my previous example I was using local temporary tables and not global temporary tables.
The synax should be :
create Table #####variables.tempname#

However, even using global temporary tables I can't access the table from another page.

This definitely worked in CFMX 7 even using local temporary tables.
The connection is maintained in the datasource so surely this table should be visible until it is dropped ?

[Guest]

Did you find a solution to this problem?  I'm researching and debating about trying to do something similar.

[JR__Bob__Dobbs]

I would advise against using global temporary table across multiple CF pages.   CF data sources don't give you the fine control you would need to guarantee that connections across separate page requests will use the same database connection session. Global temporary tables will be dropped (in MS SQL) as soon as the session which created them is closed, assuming they are not currently in use by another session.  In CF the underlying server controls the closing of connections, not the developer.

An alternate approach, as previously suggested, would be to use standard tables.

[Guest]

@ Bob, agreed.

In the end I decided to store the search string in a session.sql.var so it could be called as requested and cleared. It does the most important thing making a real table doesn't - delete when the session closes.

I tinkered with SP's for a while, but these suffer from the same problem: once the session connection is broken, the temptable is dumped.

If speed actually becomes an issue, then I'll look at building and removing tables with the OnSessionStart, populating as queries are run, then dropping  OnSessionEnd events. This seems messy to me, but I'll think about it if I have to.

I also wondered about CreateTimeSpan() to keep the query data alive for a while... this may work in certain circumstances, but not I don't think its best form my needs.

Thanks again for responding... hopefully this brainstorming will prove useful to others later.