Temporary Tables

[Guest]

I am hoping someone is able to help me regarding the syntax/info regarding temp tables. I have googled this all day and have little success. I have created my Temp table (MYSQL) and created my query to extract data both work fine in MySQL. I Put this in a componet as well as in a .cfm, but I get the error can't find the table I created when it goes to run the query. I know it created the temp table because I remove the syntax...drop temp... and error forthcoming would be table already exists. Attached is the snippet. I think it is something simple. But I just don't see it.any help/direction would be appreciated.

Steve

[Dan_Bracuk]

The approach we take when creating temporary tables is to not use static table names. The reason is that there may be conflicts during periods when more than one person is running the app.

What we do instead is:
1. create a random table name.
2. use cftry/cfcatch to attempt to create it.
3. in cfcatch, look at the reason it didn't work. If it's because the table already exists, try again, otherwise treat it as a normal error to be dealt with.
4. drop the table once we are finished using it.

We put steps 1-3 into a custom tag. The attribute going in are the sql and the variable that contains the name of the table created by the custom tag.

[Guest]

Thanks, for the quick response, but it is still a little unclear.
I believe I understand about the cftry/cfcatch to trap the error, but why is it when I attempt to execute the 2nd query [knowing that the temp table has been created] ...What keeps it from executing? or rather how do I access the table that was created in memory of the server? I am guessing the reason why is that the query doesn't know how to reference a MYSQL temp table that was created because MYSQL recognizes the query as a different connection because it is wasn't executed within the <CFQUERY> tag? Thanks for your patience.

Steve

[Dan_Bracuk]

Since the temporary table you are creating actually has data in it (the ones I create don't), why do you need it anyway. You could either run the sql as a cfquery and then the data is available for Q of Q if necessary.

Or, if mysql support subqueries in the from clause, you can join that subquery to other tables as you see fit.

[BKBK]

What about the following strategy

cfm page
========
<cfquery datasource="AF" name="ITEMS">DROP TEMPORARY TABLE IF EXISTS UNION_TABLE;</cfquery>
<cfinvoke component="AF.CFC.INVENTORY" method="get" PONUM="#URL.PO#" returnvariable="PO">
<cfinvoke component="AF.CFC.queryTest" method="do_AF"/>
<cfquery datasource="AF" name="ITEMS">SELECT * FROM UNION_TABLE WHERE UNION_TABLE.PO =#URL.PO# AND UNION_TABLE.TYPE_ID ='3'</cfquery>

queryTest.cfc
============
<cfquery datasource="AF" name="ITEMS">
CREATE TEMPORARY TABLE UNION_TABLE (INDEX IDX_ITEM_ID(ITEM_ID))
...etc. etc.
</cfquery>

[Dan_Bracuk]

If you simply want to see if cf and mysql can do temporary tables with each other, try something simple like.

<cfquery name = "q1">
create temporary table mytable (id integer))
</cfquery>
<cfquery name = "q2">
insert into mytable values (1)
</cfquery>
<cfquery name = "q3">
select id from mytable
</cfquery>
<cfquery name="q4">
drop mytable
</cfquery>
<cfdump var="#q3#>

[Kronin555]

Temporary tables are tables that exist within a transaction. Since you're not starting a transaction, the temporary table disappears right after you create it (because each cfquery is run in its own transaction unless you wrap a group of them in a <cftransaction> tag).

[Dan_Bracuk]

quote:

---

Originally posted by: Kronin555
Temporary tables are tables that exist within a transaction. Since you're not starting a transaction, the temporary table disappears right after you create it (because each cfquery is run in its own transaction unless you wrap a group of them in a <cftransaction> tag).

---

Mine work without cftransaction tags.

Temporary tables created by cold fusion exist as long as cf maintains it's connection to the db.

[Guest]

Thanks for all the suggestions/directions & advice, but I am still in the lurch.

Kronin555 - I had thought of the CFTRANSACTION previously, and it made sense(safety net) to have the cftransaction there, but it didn't seem to make a difference in the out put.

Dan - I took your suggestion and ran with it into a wall...and almost knocked it down... a few more brick and I hope to see the light of day and not prison guard with a flashlight.
Created the query,
Put it into a component.
Was able to get some of the data to display, but not all that was necessary.
When I attempt to do a QoQ on it I get the QoQ syntax error. After googling it a while it seems that CF doesn't support the more complex MYSQL statements. I am fairly new (Obviously)....Any suggestions?

I felt sure that if I could only access the temp table I could get the below listed query to work, but now I am not sure it would of to begin with. The query works fine in MYSQL.
Below is the problem query.

<CFQUERY name="TEST" dbtype="query">
SELECT Description,ITEM_ID,
SUM(IF(TYPE_ID=3 ,TOTAL_ORDERED*-1,0)) AS TOTAL_ORDERED,
SUM(IF(TYPE_ID=1 ,TOTAL_ORDERED,0)) AS RECEIVED,
SUM(IF(TYPE_ID=9 ,TOTAL_ORDERED,0)) AS MODIFIED,
IF(SIGN(SUM(TOTAL_ORDERED ))=-1,SUM(TOTAL_ORDERED ),0) AS SHORTAGE,
IF(SIGN(SUM(TOTAL_ORDERED ))=-1,'NOT COMPLETED','COMPLETE') AS STATUS
FROM MAIN_QU
GROUP BY Description
</CFQUERY>

[Dan_Bracuk]

quote:

---

Originally posted by: ssmacwilliams
Thanks for all the suggestions/directions & advice, but I am still in the lurch.

Kronin555 - I had thought of the CFTRANSACTION previously, and it made sense(safety net) to have the cftransaction there, but it didn't seem to make a difference in the out put.

Dan - I took your suggestion and ran with it into a wall...and almost knocked it down... a few more brick and I hope to see the light of day and not prison guard with a flashlight.
Created the query,
Put it into a component.
Was able to get some of the data to display, but not all that was necessary.
When I attempt to do a QoQ on it I get the QoQ syntax error. After googling it a while it seems that CF doesn't support the more complex MYSQL statements. I am fairly new (Obviously)....Any suggestions?

I felt sure that if I could only access the temp table I could get the below listed query to work, but now I am not sure it would of to begin with. The query works fine in MYSQL.
Below is the problem query.

<CFQUERY name="TEST" dbtype="query">
SELECT Description,ITEM_ID,
SUM(IF(TYPE_ID=3 ,TOTAL_ORDERED*-1,0)) AS TOTAL_ORDERED,
SUM(IF(TYPE_ID=1 ,TOTAL_ORDERED,0)) AS RECEIVED,
SUM(IF(TYPE_ID=9 ,TOTAL_ORDERED,0)) AS MODIFIED,
IF(SIGN(SUM(TOTAL_ORDERED ))=-1,SUM(TOTAL_ORDERED ),0) AS SHORTAGE,
IF(SIGN(SUM(TOTAL_ORDERED ))=-1,'NOT COMPLETED','COMPLETE') AS STATUS
FROM MAIN_QU
GROUP BY Description
</CFQUERY>

---

I don't work with mysql and won't pretend to know the syntax, but, that asterisk just looks out of place on this line:
SUM(IF(TYPE_ID=3 ,TOTAL_ORDERED*-1,0)) AS TOTAL_ORDERED,

[Guest]

Asterisk works fine in MYSQL, I tried the varaitions line to the following:
SUM(IF(TYPE_ID=3 ,TOTAL_ORDERED*(-1),0)) AS TOTAL_ORDERED
(IF(TYPE_ID=3 ,TOTAL_ORDERED*(-1),0) AS TOTAL_ORDERED
IF(TYPE_ID=3 ,TOTAL_ORDERED*(-1),0 AS TOTAL_ORDERED
and got QoQ syntax errors the whole way.

I believe that the MYSQL IF() function is causing the syntax error.
Do you think this is somewhere in the ball park?
If the CF QoQ has genereated the recordsset that we want the results from; haven't we have taken MYSQL out of the equation and are now in the realm of "CF-SQL" with a limited amount of SQL syntax in CF's vocabulary? IF this is the case, Any sugguestions where to go to attempt to add it? I know it's a stretch, but ...

[Guest]

I followed my logic and came to:Backus-Naur Form
http://livedocs.macromedia.com/coldfusion/6.1/htmldocs/using_39.htm#wp1175636
Does this confirm my theory?