How to find the number of rows affecting an INSERT INTO statement in ColdFusion and MySql database?

Question

I have the 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 the number of rows affected. Is there any way to find this in ColdFusion?

Answer 1

 <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 to the settings to allow multiple sql statements with one cfquery. To make this happen, make changes as follows:

In ColdFusion Admin, go to the data source definition form and add allowMultiQueries = true in the "Connection string" field. Once you do this, you can pass multiple semicolon-delimited queries in a single CFQUERY tag. Just make sure you use CFQUERYPARAM for escaping for SQL Injection Attacks.

Answer 2

Try it.

 <cfquery datasource="test" result="myResult"> INSERT INTO test (id,name) VALUES (1,"santy"),(2,"raj"),(3,"nil") </cfquery> <cfset getNumberOfRecords = listLen(#myResult.generated_key#)> <cfdump var="#getNumberOfRecords#"> 

myResult.generated_key contains a list of generated identifiers, so we can find how many rows were inserted when we use the listLen() function.

Answer 3

Use the result attribute, and then access the recordCount key of the result.

 <cfquery result="myResult" datasource="test"> INSERT INTO test (id,name) VALUES (1,"santy"),(2,"raj"),(3,"nil") </cfquery> <cfdump var="#myResult.recordCount#">