tracker issue : CF-3998005

select a category, or use search below
(searches all categories and all time range)
Title:

CFQuery's recordCount is not reliable for SQL Server datasources

| View in Tracker

Status/Resolution/Reason: Closed/Fixed/

Reporter/Name(from Bugbase): Dennis Ting / Dennis Ting (Dennis Ting)

Created: 05/29/2015

Components: Database, CFQuery

Versions: 11.0

Failure Type:

Found In Build/Fixed In Build: CF11_Final /

Priority/Frequency: Major / Some users will encounter

Locale/System: ALL / Windows 7

Vote Count: 0

Listed in the version 2016.0.03.300466 Issues Fixed doc
Problem Description:

When using cfquery to insert rows, one of the things we can obtain is the recordCount, which indicates how many rows were inserted.  However, sometimes the recordCount is not accurately obtained.  (See attached example.)


Steps to Reproduce:

A sample cfm file is attached that demonstrates the issue.  The file requires a SQL Server datasource, so such a datasource must be first set up in CF Administrator.

Once the datasource is set up, update line 7 in the attached cfm file to match the name of the datasource.

The attached cfm file will run a few queries on the datasource; notably, it creates 4 temp tables and then inserts a single row into each of the temp tables.

We then examine the recordCount property to see how many rows were inserted.

Actual Result:

In ColdFusion 11, the output of the attached code is:

    Number of rows inserted: ( 1, 1, 1, 0 )

Expected Result:

In ColdFusion 8, the output of the attached code is: 
 
    Number of rows inserted: ( 1, 1, 1, 1 )

We believe the ColdFusion 8 output is correct (and the ColdFusion 11 output is incorrect), because there is indeed one row inserted into each of the tables.

We did not test ColdFusion 9 or ColdFusion 10.

----------------------------- Additional Watson Details -----------------------------

Watson Bug ID:	3998005

External Customer Info:
External Company:  
External Customer Name: Dennis
External Customer Email:  
External Test Config: My Hardware and Environment details:



Windows 7

ColdFusion 11 Update 3



The datasource is a SQL Server database (Microsoft SQL Server 2012 - 11.0.5058.0 (X64))

Attachments:

  1. May 30, 2015 00:00:00: 1_cfqueryRecordCount.cfm

Comments:

I have received a fix from DataDirect. Marking this bug as tofix after having discussion with Hima. Hi, We have further investigated the reported behavior and have concluded that the driver behaves properly. It is not incorrect just because it behaves differently than the Microsoft SQL Server driver. On the surface, it does seem concerning that our driver returns an update count of 0 from the execute of the INSERT statement, while the Microsoft driver returns 1. However, our driver is coded to identify the various update counts in the reply and return them each to the application. In order to see all update counts, the application requires a slight modification. Specifically, it should call “getMoreResults” and “getUpdateCount” until the former returns false and the latter returns -1. If the app is modified to do this, then each of the update counts returned by the server will be returned by our driver. The Microsoft driver is skipping over the first returned update count (which is 0), while our driver is returning it. We modified the test app to include a showAllResults method. Logic for this method is as follows: private static void showAllResults (boolean isResultSet, Statement stmt) throws SQLException { boolean hasMoreResults = false; if (isResultSet) { hasMoreResults = true; } else { hasMoreResults = (stmt.getUpdateCount() != -1); } while (hasMoreResults) { println("\n\nRESULT:"); if (isResultSet) { dispResultSet(stmt.getResultSet(), true, false); } else { println("Rows affected count = " + stmt.getUpdateCount()); } isResultSet = stmt.getMoreResults(); if (isResultSet) { hasMoreResults = true; } else { hasMoreResults = (stmt.getUpdateCount() != -1); } } } We modified the test app to use this method as follows: sql = "INSERT INTO " + tableName + " (x) " + " SELECT 200 as x" + " UNION ALL" + " SELECT dummyTableA.x FROM (SELECT -1 as x) dummyTableA" + " inner JOIN (SELECT -2 as x) dummyTableB ON 0=1"; ps = con.prepareStatement(sql); System.out.println("executeUpdate: "+ ps.executeUpdate()); //An int that indicates the number of rows affected, or 0 if using a DDL statement. System.out.println("getUpdateCount: " + ps.getUpdateCount()); //An int that contains the update count. showAllResults(false, ps); We encourage you to do the same, and you will see that our driver will return 3 update counts for the executed INSERT statement: 0,1,1. The additional update counts are on account of the trigger. Please utilize this type of mechanism, since it is more in keeping with the JDBC specification. Please let me know if you have any questions or concerns. Thanks. Pim
Comment by Nimit S.
7301 | August 19, 2015 03:49:31 AM GMT
This issue is fixed now. The fix for this issue will be available as part of ColdFusion 2016 Update 3.
Comment by Nimit S.
7302 | July 20, 2016 05:15:57 AM GMT