ColdFusion：查询中存在多个 SQL 语句？
显然，ColdFusion 不喜欢在单个查询中使用多个 SQL 语句...那么这曾经是什么：
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 变成了这样：
<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>
这有效...但我想知道是否有一些我不知道的 ColdFusion 事情。否则，这有效。
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.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
在 ColdFusion Admin 中，转到数据源定义表单并将“allowMultiQueries=true”添加到“连接字符串”框中。完成此操作后，您可以在单个 CFQUERY 标记中传递多个以分号分隔的查询。只需确保您使用 CFQUERYPARAM 来筛选 SQL 注入攻击即可。
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.
如果有人发现这一点并且将
allowMultiQueries=true放入连接框中不起作用，我最后尝试将其附加到实际的 JDBC url，如jdbc:mysql://127.0.0.1。 0.1:3306/mydbname?allowMultiQueries=true。后来第一次尝试就成功了。In case anyone is finding this and putting the
allowMultiQueries=truein the connection box is not working, I finally tried appending it to the actual JDBC url like thisjdbc:mysql://127.0.0.1:3306/mydbname?allowMultiQueries=true. It worked on the first try afterwards.