I'm now working under the assumption that you cannot use <cfqueryparam> on temp tables. Can anyone confirm whether or not this is documented anywhere and whether it is a feature, bug or both? Also, is it database independent? I'm using Informix....what about MS SQL & Oracle?
> Hi, > > I'm on CFMX 7 with updater installed and my db is Informix 10 > > Okay, here's the deal...when I create a temp table, do a select insert > into that table, and then later, if I do a select on that temp table, > join a permanent table, and use a <cfqueryparam> for one of my values, > the first time it runs, it works fine. But with any subsequent runs, > I get: > > Table (dphillips.temp_csa_table_1) has been dropped, altered or > renamed. > > My cfqueryparam is of type CF_SQL_VARCHAR. If I eliminate the > cfqueryparam and put the value straight in (like column = 'blahblah' > instead of column = <cfqueryparam value="blahblah" > cfsqltype="cf_sql_varchar">), it works every time without an error. > > My *assumption* is that somehow cfqueryparam is altering the temp > table...Although I DROP the temp table before I create it, and when I > run it again, it is dropping, re-creating, and re-querying, it still > fails on the subsequent runs. I have to re-cycle ColdFusion or change > the table name to get it to work again (and then it only works once > and fails after that). I prefer not to get rid of the <cfqueryparam> > but at this point, it seems my only option. > > Any thoughts or comments on this? Here's some pseudo-code: > > drop table tmp_tbl_1 > > create temp table tmp_tbl_1(columns...) with no log > > insert into tmp_tbl_1(columns) SELECT columns... FROM ..... > > select columns.... FROM tmp_tbl_1, outer permtable WHERE ... column1 = > <cfqueryparam value="blahblah" cfsqltype="cf_sql_varchar"> > > Again, this works the first time, but not subsequent times.... If I > change the cfqueryparam to just be the value (i.e. 'blahblah'), then > it works EVERY time. > > TIA! > Dave ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:225598 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
