Search code examples
coldfusioncfquery

How to Execute 2 or more insert statements using CFQuery in coldfusion?

Is it possible to Execute 2 insert or Update Statements using cfquery?

If yes how?

if no, what is the best way to execute multiple queries in Coldfusion, by opening only one Connection to DB.

I think every time we call cfquery we are opening new connection DB

Solution

  • Within the data source settings you can tell it whether to keep connections open or not with the Maintain Connections setting.

    Starting with, I believe, ColdFusion 8 datasources are set up to run only one query at a time due to concerns with SQL injection. To change this you would need to modify with the connection string.

    Your best bet is to turn on Maintain Connections and if needed use cftransaction:

    <cftransaction>
<cfquery name="ins" datasource="dsn">
insert into table1 values(<cfqueryparam value="#url.x#">)
</cfquery>
<cfquery name="ins" datasource="dsn">
insert into table2 values(<cfqueryparam value="#url.x#">)
</cfquery>
</cftransaction>

    And always, always use cfqueryparam for values submitted by users.