Getting number of inserted records

[notbug]

Hi,

I'm moving from 6.1 to 8.0 and have found a query which works in 6.1 but not in 8.0, and I'm just wondering if there is a workaround for it.

What I'm doing is inserting multiple records into a table and using @@rowcount to find out the number of records inserted, this code worked in 6.1 but in 8.0 the "qry" variable is not defined. Any help would be appreciated, thanks.

[cf_dev2 · Accepted solution]

You must use SET NOCOUNT to supress the record count returned by the insert

SET NOCOUNT ON
INSERT INTO emp
SELECT f_name, l_name
FROM applicants
WHERE hired = 1

SELECT @@rowcount AS NumberOfRecords
SET NOCOUNT OFF

[notbug]

This query works in 6.1 without having to set NOCOUNT. In any case adding the line SET NOCOUNT OFF does not help, the "qry" variable is still not defined if using 8.0.

[cf_dev2]

I know it works in 6.1, but it shouldn't. You just got lucky.

Is the first line SET NOCOUNT ON? I don't have access to CF8 right now so I can't test it.

[notbug]

Yes sorry I missed the first line. Setting NOCOUNT ON worked. Thanks very much for your help.

[Newsgroup_User]

bubbabouy wrote:
> This query works in 6.1 without having to set NOCOUNT. In any case adding
> the line SET NOCOUNT OFF does not help, the "qry" variable is still not
> defined if using 8.0.

no, w/out the NOCOUNT OFF that wouldn't work in any version of cf using the
builtin JDBC driver.

[cf_dev2]

It works just fine for me with 6.1 using the builtin sql server driver (not MS). I don't know which version though.

[Newsgroup_User]

cf_dev2 wrote:
> It works just fine for me with 6.1 using the builtin sql server driver (not MS). I don't know which version though.

w/out quoting not sure what you're replying to in my news reader, but are you
saying you *don't* need a NOCOUNT ON/OFF block w/the 6.1 DD driver? it's
certainly been a while but i recall that you *always* needed a NOCOUNT when
using multiple SQL statements, even as far as back as cf3/4/5 & their ODBC
drivers. otherwise cf would interpret the rowcount returned by the 1st SQL
statement as a returned result set & stop looking for any more data.

[cf_dev2]

PaulH wrote:
> are you saying you *don't* need a NOCOUNT ON/OFF block w/the 6.1 DD driver?

Yes. I even tried setting NOCOUNT OFF before the insert. Perhaps it has to do with the DD driver version? I don't know which version is installed (3.3 maybe?) just that they're not the ones that shipped with 6.1. Any other settings you can think to check?

> certainly been a while but i recall that you *always* needed a NOCOUNT when
> using multiple SQL statements, even as far as back as cf3/4/5 & their ODBC

Maybe my memory is shot, but I thought that it worked without NOCOUNT in *some* versions, but did not work in others. I understand that it shouldn't work .. but it does on this machine.

[Newsgroup_User]

cf_dev2 wrote:
> do with the DD driver version? I don't know which version is installed (3.3
> maybe?) just that they're not the ones that shipped with 6.1. Any other
> settings you can think to check?

if they're not the DD drivers shipped w/cf then i have no idea. i guess those
drivers might be prepending that SET statement on it's own. i suppose as long as
it still works when the cf apps adds then it doesn't matter much (that & that it
*is* cf EOLed 6.1 so who cares anyway).

> Maybe my memory is shot, but I thought that it worked without NOCOUNT in
> *some* versions, but did not work in others. I understand that it shouldn't
> work .. but it does on this machine.

well i recall it always being needed to get @@IDENTITY, etc. back.

um....i just tested the jTDS driver w/cf8 & SET NOCOUNT isn't needed for two
statements. curious, i also just tried w/the DD drivers that ship w/cf8 and it
doesn't need it either! this is against sql server 2000.

cf8 added the ability to return identity values (when you specify the result
argument it would be in result_name.IDENTITYCOL for sql server), i wonder if
this is a side-effect? which makes what bubbabouy's seeing strange.

bubbabouy, what version of sql server are you using? are you using the JDBC
driver (it would be labeled sql server in cfadmin)?

[Newsgroup_User]

bubbabouy wrote:
> <cfquery name="qry">
> INSERT INTO emp
> SELECT f_name, l_name
> FROM applicants
> WHERE hired = 1
> </cfquery>

ah i'm so dumb. if you're on cf8, you can simply add the result argument to your
cfquery

<cfquery name="qry" result="myResult">

then you can access a bunch of interesting stuff like:

CACHED this query cached or not
EXECUTIONTIME how long
IDENTITYCOL identity value (or maybe a list) for this query
RECORDCOUNT how many rows effected by this statement, seems to work for
UDPATE,INSERT, etc. very sweet.
SQL sql for this query

[cf_dev2]

PaulH wrote:
> RECORDCOUNT how many rows effected by this statement, seems to work
> for UDPATE,INSERT, etc. very sweet.

I knew about @@IDENTITY but not recordcount. Nice!