@@Rowcount in CFQuery in CF8

Dev Shed Forums Sponsor:

Stop making mediocre tutorials.The best tutorials are video! Camtasia Studio makes it easy to create engaging, buzz-building screen videos at any size, in any popular format. Download the free trial!

May 6th, 2008, 05:56 PM

jaeSun

got Rice?

Join Date: Nov 2004

Posts: 437

Time spent in forums: 3 Days 10 h 27 m 42 sec
Reputation Power: 4

@@Rowcount in CFQuery in CF8

Code:

<cfquery name="qryTest" datasource="DBTest">
	INSERT INTO T_0097878_W (ID,feed_client_id,Account_ID,Contact_ID, Client_Identifier,Data_Partner_ID,Marker)
	SELECT 
		id, feed_client_id, Account_ID, Contact_ID, Client_Identifier, Data_Partner_ID, Marker
	FROM T_0097878_R
	;
	SELECT ISNULL(@@ROWCOUNT,0) as rCnt;
</cfquery>

<cfdump var="#qryTest#">

rCnt is not defined within qryTest.

Doing a basic update statement and getting the @@ROWCOUNT makes qryTest undefined.

i dont see anything in CF8 docs that would allow me to get the rowcount, nor see anything that would have changed from 6.1 to 8

http://livedocs.adobe.com/coldfusion/8/htmldocs/help.html?content=Tags_p-q_17.html

May 6th, 2008, 08:44 PM

kiteless

Moderator

Join Date: Jun 2002

Location: Raleigh, NC

Posts: 3,475

Time spent in forums: 1 Week 3 Days 17 h 7 m 51 sec
Reputation Power: 42

In CF8 you can get the inserted key from the result structure:

result_name.IDENTITYCOL

SQL Server only. The ID of an inserted row.
result_name.ROWID

Oracle only. The ID of an inserted row. This is not the primary key of the row, although you can retrieve rows based on this ID.
result_name.SYB_IDENTITY

Sybase only. The ID of an inserted row.
result_name.SERIAL_COL

Informix only. The ID of an inserted row.
result_name.GENERATED_KEY

MySQL only. The ID of an inserted row. MySQL 3 does not support this feature.

__________________
Ask if you have a question, but also help answer questions that you have knowledge of! Thanks, Brian.
How to Post a Question in the Forums

May 6th, 2008, 11:00 PM

jaeSun

got Rice?

Join Date: Nov 2004

Posts: 437

Time spent in forums: 3 Days 10 h 27 m 42 sec
Reputation Power: 4

i know that, but i am not trying to get the id of the inserted row.

i want to know how many was inserted (@@ROWCOUNT)

this also has affected getting the # of rows affected if i do a basic UPDATE statement

the result structure does not seem to give me this information

May 6th, 2008, 11:53 PM

kiteless

Moderator

Join Date: Jun 2002

Location: Raleigh, NC

Posts: 3,475

Time spent in forums: 1 Week 3 Days 17 h 7 m 51 sec
Reputation Power: 42

Ah sorry, I had misread. I don't believe the JDBC drivers allow you to do multiple statements like that without changing the connection strings, which essentially means setting up your own driver and connection.

May 7th, 2008, 12:06 AM

jaeSun

got Rice?

Join Date: Nov 2004

Posts: 437

Time spent in forums: 3 Days 10 h 27 m 42 sec
Reputation Power: 4

Quote:

Originally Posted by kiteless

gay....worked in 6.1

guess ill have to do a workaround

May 7th, 2008, 07:28 AM

kiteless

Moderator

Join Date: Jun 2002

Location: Raleigh, NC

Posts: 3,475

Time spent in forums: 1 Week 3 Days 17 h 7 m 51 sec
Reputation Power: 42

I believe it was a general change to the way all the JDBC drivers were used, due to the rise of SQL injection attacks. It's actaully pretty dangerous to allow this since it becomes so much easier for hackers to sneak extra statements in via URL or FORM variables that are used in the SQL.

May 7th, 2008, 07:45 AM

kiteless

Moderator

Join Date: Jun 2002

Location: Raleigh, NC

Posts: 3,475

Time spent in forums: 1 Week 3 Days 17 h 7 m 51 sec
Reputation Power: 42

This will probably help:

http://cfsearching.blogspot.com/200...esults-are.html

May 7th, 2008, 10:20 AM

jaeSun

got Rice?

Join Date: Nov 2004

Posts: 437

Time spent in forums: 3 Days 10 h 27 m 42 sec
Reputation Power: 4

Quote:

Originally Posted by kiteless

This will probably help:

http://cfsearching.blogspot.com/200...esults-are.html

i am not sure how he states:

Quote:

However, prior to ColdFusion 8, cfquery did not provide a direct method for accessing some of these values. With the introduction of ColdFusion 8, you can now access "rows affected" and IDENTITY values using cfquery's result attribute. It is a great feature, but it was bound to change some of cfquery's behavior.

http://livedocs.adobe.com/coldfusion/8/htmldocs/help.html?content=Tags_p-q_17.html

doesn't mention anything of a rows affected attribute in that result struct. there is the recordcount value, which i believe he was tying to use to mimic what I need.

i've tried that, with no luck. recordcount=0 even though it may have selected 316 rows.

Code:

struct 
CACHED false  
EXECUTIONTIME 78  
RECORDCOUNT 0  
SQL SET NOCOUNT ON INSERT INTO T_0097878_W (ID) SELECT id FROM T_0097878_R SET NOCOUNT OFF

though i will say, i ran into this:

Code:

<cfquery name="dbQry" datasource="someDS" result="test">
	INSERT INTO someTable (ID)
	VALUES (35)
</cfquery>


<cfdump var="#test#">

gives me this error:

Quote:

Element GENERATED_KEYS is undefined in a CFML structure referenced as part of an expression.

ColdFusion cannot determine the line of the template that caused this error. This is often caused by an error in the exception handling subsystem.

May 7th, 2008, 10:29 AM

jaeSun

got Rice?

Join Date: Nov 2004

Posts: 437

Time spent in forums: 3 Days 10 h 27 m 42 sec
Reputation Power: 4

i believe he had his examples backwards.

his example

Code:

<cfquery name="create" datasource="#dsn#">
SET NOCOUNT ON

INSERT INTO OtherTable ( Title, DateModified )
SELECT   Title, DateModified
FROM     MyTable
WHERE    MyID = <cfqueryparam value="2" cfsqltype="cf_sql_integer">
SELECT SCOPE_IDENTITY() AS OtherID

SET NOCOUNT OFF
</cfquery>

NOCOUNT: Stops the message indicating the number of rows affected by a Transact-SQL statement from being returned as part of the results.

i switched it around and i got the result i was looking for

#10

May 7th, 2008, 02:49 PM

kiteless

Moderator

Join Date: Jun 2002

Location: Raleigh, NC

Posts: 3,475

Time spent in forums: 1 Week 3 Days 17 h 7 m 51 sec
Reputation Power: 42

Great!

#11

May 7th, 2008, 03:18 PM

jaeSun

got Rice?

Join Date: Nov 2004

Posts: 437

Time spent in forums: 3 Days 10 h 27 m 42 sec
Reputation Power: 4

not sure if this will help anyone, but a co-worker said doing this:

Quote:

<cfquery name="qryTest" datasource="DBTest" result=”rows_result”>
INSERT INTO T_0097878_W (ID,feed_client_id,Account_ID,Contact_ID, Client_Identifier,Data_Partner_ID,Marker)
SELECT
id, feed_client_id, Account_ID, Contact_ID, Client_Identifier, Data_Partner_ID, Marker
FROM T_0097878_R
;
SELECT ISNULL(@@ROWCOUNT,0) as rCnt; --
</cfquery>
<cfoutput>
#rows_result.rCnt#
</cfoutput>

also works.

stated that coldfusion also sends a sql statement at the end of your query to the sql server (select SCOPE_IDENTITY() AS GENERATED_KEYS), so by putting the SQL comments at the end, it comments that last statement out, and also works.

thought i would post it here also just in case it helps anyone.

#12

May 8th, 2008, 09:44 PM

cfSearching

Registered User

Join Date: May 2008

Posts: 1

Time spent in forums: 54 m 40 sec
Reputation Power: 0

Quote:

Originally Posted by jaeSun

i believe he had his examples backwards.

No. Your goal is slightly different than the one described in my entry. However, I suspect the main problem is that you are using a different driver version. If you look at the results the entry, it shows the driver used was the built-in CF8 driver for MS SQL Server. From what you have described, I would guess you are using the MS SQL JDBC 1.2 driver or possibly the JTDS driver.

Driver version makes a big difference. The results of certain statements can vary widely depending on which driver you are using. I wrote several entries about it. If you are interested in exactly how different, read my entry titled "CF8 + MS JDBC 1.2 Driver - .. And for my next trick, I will make this query disappear!". (Due to my new user status, I cannot post urls yet) Because of those differences (bugs IMO), the solution in the original entry only applies to the built-in CF8 driver. It will not work with the MS JDBC 1.2 driver.

Quote:

..stated that coldfusion also sends a sql statement at the end of your query to the sql server (select SCOPE_IDENTITY() AS GENERATED_KEYS), so by putting the SQL comments at the end, it comments that last statement out, and also works.

Yes. That is why the solution does not work for you. When using the JDBC 1.2 driver (and possibly JTDS), CF is returning the "recordCount" and results from the extra statement and ignoring the rest. If you had dumped the query and "result" structure, you could clearly see the "rCnt" column is nowhere to be seen. Instead it is replaced by GENERATED_KEYS.

Dan G. Switzer, II was kind enough to point out this issue a few months ago. I wrote about in "CF8 + MS JDBC 1.2 Driver - Generated key issues. Curiouser and Curiouser". Again, I think this is a bug and have submitted a bug report on this/related issues.

In any case, I believe the problem only applies to INSERT statements. It should already work correctly for UPDATE and DELETE statements. So assuming you are using the JDBC 1.2 driver, you can still use cfquery's "result" attribute as kiteless suggested.

For INSERT's, eliminate the SELECT @@ROWCOUNT and just add an MS SQL comment "--" to the end of the sql. Once you do that, the "recordCount" from cfquery's "result" attribute will work correctly.

Code:

<cfquery name="qry" datasource="#dsn#" result="results">
   INSERT INTO T_0097878_W (ID, feed_client_id, ..)
   SELECT ID, feed_client_id, ...
   FROM   T_0097878_R
   -- this fixes a bug between CF8 and MS SQL JDBC 1.2 driver
</cfquery>
<cfoutput>recordcount = #results.recordCount#</cfoutput>

Thread Tools	Search this Thread
Email this Page	Search this Thread: Advanced Search
Display Modes	Rate This Thread
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode	Rate This Thread: