Re: CF and SQL Server temporary tables
That's my understanding too - CF gets a connection from the pool on the first query in a request and keeps that connection for the duration of the request, using it for all subsequent queries. On Fri, Apr 18, 2008 at 1:09 PM, Dave Watts [EMAIL PROTECTED] wrote: It's entirely possible for a CF page to change connections in the middle of processing a request, and therefore lose sight of the temp table. Actually, I don't think that's possible. My understanding is that a connection used by a page is retained by the page for the duration of its execution. -- mxAjax / CFAjax docs and other useful articles: http://www.bifrost.com.au/blog/ ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;192386516;25150098;k Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:303721 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: CF and SQL Server temporary tables
I'm with Dave... The connection persists for the length of the request. I wonder however about scoping and components. For example, what if I did the following: 1) createobject(component,com.dbObj).createTempTable() 2) createobject(component,com.dbObj).getDataFromTempTable(firstname='Bob'); Could the second instantiation of the object result in the use of a different connection? Since the scope is sort of agnostic of the request I wonder if that would be the case. It might be interesting to find out. -Mark Mark A. Kruger, CFG, MCSE (402) 408-3733 ext 105 www.cfwebtools.com www.coldfusionmuse.com www.necfug.com -Original Message- From: Dave Watts [mailto:[EMAIL PROTECTED] Sent: Friday, April 18, 2008 12:09 AM To: CF-Talk Subject: RE: CF and SQL Server temporary tables It's entirely possible for a CF page to change connections in the middle of processing a request, and therefore lose sight of the temp table. Actually, I don't think that's possible. My understanding is that a connection used by a page is retained by the page for the duration of its execution. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ Fig Leaf Training: Adobe/Google/Paperthin Certified Partners http://training.figleaf.com/ WebManiacs 2008: the ultimate conference for CF/Flex/AIR developers! http://www.webmaniacsconference.com/ ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;192386516;25150098;k Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:303725 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: CF and SQL Server temporary tables
This topic came up back in December. Read this post here: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:265101 Jochem clarified that Adobe ColdFusion does maintain the same connection for the duration of a page, but one should not rely on that behavior since it is un-documented. Furthermore, alternative CFML engines like BlueDragon don't follow the same behavior. If you want to be sure your code will be 1) portable and 2) forward compatible, use a cftransaction to force it to reuse the same connection. ~Brad -Original Message- From: James Holmes [mailto:[EMAIL PROTECTED] Sent: Friday, April 18, 2008 2:02 AM To: CF-Talk Subject: Re: CF and SQL Server temporary tables That's my understanding too - CF gets a connection from the pool on the first query in a request and keeps that connection for the duration of the request, using it for all subsequent queries. On Fri, Apr 18, 2008 at 1:09 PM, Dave Watts [EMAIL PROTECTED] wrote: It's entirely possible for a CF page to change connections in the middle of processing a request, and therefore lose sight of the temp table. Actually, I don't think that's possible. My understanding is that a connection used by a page is retained by the page for the duration of its execution. ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;192386516;25150098;k Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:303758 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: CF and SQL Server temporary tables
If I remember correctly if you have CF maintaining connections your temp table will hang around almost indefinitely, best practice would be to DROP it once you are done with it. Also worth noting that if the page that creates the table can be called multiple times simultaneously (by different users of the system for example) then the second request will fail and error on you because the table has already been created. If you need a truly temporary table you could lock the database (to prevent other requests getting in the way) create the table, do what you need it for, then drop the table and unlock the database. If the table will hold data needed by multiple requests I would look at some way of creating a permanent table and managing the data across requests. -- Jay On Thu, Apr 17, 2008 at 1:13 PM, Rick Root [EMAIL PROTECTED] wrote: so I have a question about temporary tables in SQL Server 2005. From the SQL Server documentation for CREATE TABLE: You can create local and global temporary tables. Local temporary tables are visible only in the current session, and global temporary tables are visible to all sessions. Temporary tables cannot be partitioned. Prefix local temporary table names with single number sign (#*table_name*), and prefix global temporary table names with a double number sign (##* table_name*). What exactly does current session mean as it applies to a Coldfusion request? The life of the request? the life of the transaction, if any? The life of the executing query? Or, since CF maintains the database connection between requests, and pools connections, how does that affect things? I'm hoping that each cf request is a separate session as far as SQL Server is concerned. Thanks! Rick -- Rick Root New Brian Vander Ark Album, songs in the music player and cool behind the scenes video at www.myspace.com/brianvanderark ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;192386516;25150098;k Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:303663 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: CF and SQL Server temporary tables
On Thu, Apr 17, 2008 at 8:38 AM, James Smith [EMAIL PROTECTED] wrote: If I remember correctly if you have CF maintaining connections your temp table will hang around almost indefinitely, best practice would be to DROP it once you are done with it. Well I did some tests... and I determined that the table *DOES* get deleted at the end of the request... that's good. Basically I ran a query that created a temp table, then I ran another request that selected from that temp table and it wasn't there .. so either it got deleted at the end of the request, *OR* the second request was a separate session and thus CF couldn't see the original temp table. Either way, that's good. rick ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;192386516;25150098;k Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:303664 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: CF and SQL Server temporary tables
On Thu, Apr 17, 2008 at 8:38 AM, James Smith [EMAIL PROTECTED] wrote: If I remember correctly if you have CF maintaining connections your temp table will hang around almost indefinitely, best practice would be to DROP it once you are done with it. Well I did some tests... and I determined that the table *DOES* get deleted at the end of the request... that's good. Basically I ran a query that created a temp table, then I ran another request that selected from that temp table and it wasn't there .. so either it got deleted at the end of the request, *OR* the second request was a separate session and thus CF couldn't see the original temp table. Either way, that's good. rick For temporary data manipulation, in general, variable table, is more efficient than creating temporary tables, however, there are two exceptions where one can't use this technique. You may consider BOL for more detail. ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;192386516;25150098;k Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:303673 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: CF and SQL Server temporary tables
It's entirely possible for a CF page to change connections in the middle of processing a request, and therefore lose sight of the temp table. It's also possible for the new connection to be one that was previously used by another invocation of the same page, producing the problem that James mentions. You can avoid both of these problems by wrapping the relevant code in a cftransaction - the transaction binds the connection to the CF thread - but, like all locking, you have to consider the impact on throughput. Jaime Metcher -Original Message- From: James Smith [mailto:[EMAIL PROTECTED] Sent: Thursday, 17 April 2008 10:38 PM To: CF-Talk Subject: Re: CF and SQL Server temporary tables If I remember correctly if you have CF maintaining connections your temp table will hang around almost indefinitely, best practice would be to DROP it once you are done with it. Also worth noting that if the page that creates the table can be called multiple times simultaneously (by different users of the system for example) then the second request will fail and error on you because the table has already been created. If you need a truly temporary table you could lock the database (to prevent other requests getting in the way) create the table, do what you need it for, then drop the table and unlock the database. If the table will hold data needed by multiple requests I would look at some way of creating a permanent table and managing the data across requests. -- Jay On Thu, Apr 17, 2008 at 1:13 PM, Rick Root [EMAIL PROTECTED] wrote: so I have a question about temporary tables in SQL Server 2005. From the SQL Server documentation for CREATE TABLE: You can create local and global temporary tables. Local temporary tables are visible only in the current session, and global temporary tables are visible to all sessions. Temporary tables cannot be partitioned. Prefix local temporary table names with single number sign (#*table_name*), and prefix global temporary table names with a double number sign (##* table_name*). What exactly does current session mean as it applies to a Coldfusion request? The life of the request? the life of the transaction, if any? The life of the executing query? Or, since CF maintains the database connection between requests, and pools connections, how does that affect things? I'm hoping that each cf request is a separate session as far as SQL Server is concerned. Thanks! Rick -- Rick Root New Brian Vander Ark Album, songs in the music player and cool behind the scenes video at www.myspace.com/brianvanderark ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;192386516;25150098;k Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:303698 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: CF and SQL Server temporary tables
It's entirely possible for a CF page to change connections in the middle of processing a request, and therefore lose sight of the temp table. Actually, I don't think that's possible. My understanding is that a connection used by a page is retained by the page for the duration of its execution. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ Fig Leaf Training: Adobe/Google/Paperthin Certified Partners http://training.figleaf.com/ WebManiacs 2008: the ultimate conference for CF/Flex/AIR developers! http://www.webmaniacsconference.com/ ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;192386516;25150098;k Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:303715 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
