ColdFusion: Multiple SQL statements in a query?

Question

Apparently ColdFusion doesn't like multiple SQL statements within a single query... so what once was this:

SET @sender_user_id = 3, @recipient_user_id = 5;

INSERT INTO messages (message_type, sender_id, message_title, message_content) 
  VALUES(3, @sender_user_id, 'One more thing...', 'I am testing this message');

SET @saved_message_id = LAST_INSERT_ID();

INSERT INTO message_recipient (message_id, user_id) 
  VALUES(@saved_message_id, @recipient_user_id);

INSERT INTO message_status (message_id, user_id, is_read, read_datetime, is_deleted, deleted_datetime)
  VALUES (@saved_message_id, @recipient_user_id, 0, NULL, 0, NULL);

Get's turned into this:

<cftransaction>

    <cfquery name="insertMessage" dataSource="mySource">

        SET @sender_user_id = 3, @recipient_user_id = 5;

    </cfquery>
    <cfquery name="insertMessage2" dataSource="mySource">

        INSERT INTO messages (message_type, sender_id, message_title, message_content) 
        VALUES(3, @sender_user_id, '#params.message_title#', '#params.message_content#');

    </cfquery>
    <cfquery name="insertMessage3" dataSource="mySource">

        SET @saved_message_id = LAST_INSERT_ID();

    </cfquery>
    <cfquery name="insertMessage4" dataSource="mySource">   

        INSERT INTO message_recipient (message_id, user_id) 
        VALUES(@saved_message_id, @recipient_user_id);

    </cfquery>
    <cfquery name="insertMessage5" dataSource="mySource">   

        INSERT INTO message_status (message_id, user_id, is_read, read_datetime, is_deleted, deleted_datetime)
        VALUES (@saved_message_id, @recipient_user_id, 0, NULL, 0, NULL);

    </cfquery>

</cftransaction>

This works... but I'm wondering if there is some ColdFusion thing that I don't know about. Otherwise, this works.

Surveyor

What do you mean by "gets turned into"? For a process such as shown in the example, it really should be in a transaction since you are dealing with multiple tables/record that are in essence, one atomic record. I'm assuming you have this spread over multiple tables to allow the messages to have multiple users receive a message with an individual status on each? – Faythe

Yes, you're correct... but even when declaring MySQL variables, those variables have to be in their own query for it to work. I just wasn't sure if there was some type on syntax to at least combine the variables into the queries. – Surveyor

Answer 1

In ColdFusion Admin, go to your Data Source definition form and add "allowMultiQueries=true" to the Connection String box. Once you do that, you can pass multiple semi-colon separated queries in a single CFQUERY tag. Just make sure you're using CFQUERYPARAM to screen for SQL Injection Attacks.

Dicho

"Just make sure you're using CFQUERYPARAM to screen for SQL Injection Attacks." - Should this be applied even in my broken out query above? – Surveyor

Dang, you type faster than me.. :) Also, you can use result_name.GENERATED_KEY to get the last key (from the last cfquery insert) and don't have to do that query for LAST_INSERT_ID() unless you are doing it within the same cfquery block – Faythe

Answer 2

In case anyone is finding this and putting the allowMultiQueries=true in the connection box is not working, I finally tried appending it to the actual JDBC url like this jdbc:mysql://127.0.0.1:3306/mydbname?allowMultiQueries=true. It worked on the first try afterwards.

Globetrotter