CODEWITHSUNDEEP
mysqlcoldfusiontemp-tables
frequent
frequent

Reputation: 28483

How to create a temporary MySQL table with a UUID name in Coldfusion?

I'm using Coldfusion8 and MySQL 5.0.91 and am trying to create a temporary table, import some data from CSV, validate, store in database and drop the temp table.

I can do all steps in MySQL directly, where it works without a problem. However when I try to create the temp table in Coldfusion I keep getting a MySQL error.

This is what I have:

<cfset variables.tt = CreateUUID()>
<cfquery datasource="#session.datasource#">
    DROP TEMPORARY TABLE IF EXISTS ##variables.tt;
    CREATE TEMPORARY TABLE ##variables.tt (
      tmp_kto VARCHAR(7) DEFAULT ''
    , tmp_kz VARCHAR(3) DEFAULT ''
    , tmp_preisliste VARCHAR(1) DEFAULT ''
    , tmp_eg VARCHAR(3) DEFAULT ''
    ) ENGINE=MEMORY;
 </cfquery>

which does not work. I also tried #variables.tt# and plain variables.tt but I keep getting the same error, while it works fine when I run the code in MySQL directly (with table name "test")

Question:
I have no clue what I'm doing wrong. Is there a max length for table names perhaps?

Thanks for some insights.

EDIT:
I get the usual error:

 you have an error in your SQL syntax;
 check the manual that corresponds to your MySQL server version for the right syntax to use near 
 'CREATE TEMPORARY TABLE #variables.tt (tmp_sa VARCHAR(3) DEFAULT ''

Upvotes: 0

Views: 1764

Answers (2)

ale
ale

Reputation: 6430

It looks to me you are missing the hash marks around your variable to output.

CREATE TEMPORARY TABLE ##variables.tt (

should be

CREATE TEMPORARY TABLE #variables.tt# (

The way you have it, MySQL is attempting to use the string #variables.tt (the doubled hash escapes the character in ColdFusion) when what you want is to create a temp table with the name of the value of variables.tt.

Upvotes: 2

Dan Bracuk
Dan Bracuk

Reputation: 20794

The most likely source of your problem is that the uuid is not suitable as a tablename. For one thing, it contains hyphens. I don't know if MySQL allows this, but I can't remember ever seeing hyphens in a table name.

We have a similar situation, but with redbrick. We want to create unique names for temporary tables. Our approach, written in a custom tag, is to use the string "temp" followed by a random number. We also have some try/catch stuff going on in case we get really unlucky with the random number.

I wrote that tag several years ago. If I were to do it again, I would probably replace the random number with the cftoken, cfid, and tickcount.

Also, you can't drop a table before you create it.

Upvotes: 3

Related Questions