CODEWITHSUNDEEP
coldfusion-8
Mathieu Bertin
Mathieu Bertin

Reputation: 465

Creating temp tables in ColdFusion (copying records without duplicate id)

I know, I know, this is older than your grandma but I can't figure out for the life of me why I can execute the following query on a phpmyadmin but not in a cfquery tag.

  DROP TABLE IF EXISTS temp;
  CREATE TEMPORARY TABLE temp ENGINE=MEMORY

Upvotes: 0

Views: 2311

Answers (2)

Leigh
Leigh

Reputation: 28873

When using the JDBC driver, the execution of multiple statements is disabled by default, for security reasons. It can be enabled in your DSN by adding allowMultiQueries=true to the DSN "Connection Settings". Just be aware that enabling this setting will expose the database to certain forms of sql injection. So be sure to use cfqueryparam on ALL query parameters.

allowMultiQueries

Allow the use of ';' to delimit multiple queries during one statement (true/false), defaults to 'false'

Having said that, not sure why you need a temp table. A simple INSERT / SELECT should do the trick. There is no reason you cannot insert and select from the same table. Not unless the end result would violate a constraint.

 INSERT INTO mytable ( Name, Description, SomeDateCol )
 SELECT name, description, NOW() 
 FROM   mytable 
 WHERE  id = <cfqueryparam value="#id_im_looking_for#" cfsqltype="cf_sql_YourTypeHere">

Upvotes: 0

Mathieu Bertin
Mathieu Bertin

Reputation: 465

I realized I can't make more than one query per cfquery tag. Essentially this is for copying records in MySQL with ColdFusion. If someone can simplify this further I'd appreciate it.

  <cfquery datasource="#request.datasource#">
      DROP TABLE IF EXISTS temp
  </cfquery>
  <cfquery result="copied" datasource="#request.datasource#">
      CREATE TABLE temp ENGINE=MEMORY
      SELECT * FROM mytable WHERE id= <cfqueryparam value="#id_im_looking_for#">
  </cfquery>

  <cfquery result="copied" datasource="#request.datasource#">
    INSERT INTO mytable SELECT NULL, name, description, NOW() FROM temp
  </cfquery>

  <cfquery datasource="#request.datasource#">
    DROP TABLE temp
  </cfquery>

A little verbose to me.

Upvotes: 0

Related Questions