Search code examples
mysqlcoldfusioncoldfusion-9

How to find number of rows affected by INSERT INTO statement in ColdFusion And MySql Database?

I have following query in ColdFusion:

<cfquery name="test" datasource="test">
    INSERT INTO test (id,name) VALUES (1,"santy"),(2,"raj"),(3,"nil")
</cfquery>

I want to find number of affected rows count. Is there any way to find this in ColdFusion?

Solution 
  • <cftransaction>
    <cfquery name="test" datasource="test">
        INSERT INTO test (id,name) VALUES (1,"santy"),(2,"raj"),(3,"nil")
    </cfquery>
    <cfquery name="test1" datasource="test">
        SELECT ROW_COUNT() AS numberOfRowsAffected
    </cfquery>
</cftransaction>
<cfdump var="#test1.numberOfRowsAffected#">

    Or

    You can make changes in the settings to allow multiple sql statements withing one cfquery. To make that happen, make changes as mentioned below:

    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.