Forta.com - Blog

Comments

This is a sweet little tidbit for sure!!

# Posted By Dan | 7/6/07 1:32 PM

This is sweet. It would have been just a tad bit sweeter if CF returned the same key name, regardless of DB used.

# Posted By Scott Stroz | 7/6/07 2:21 PM

Scott, I agree, and had I have discovered this feature much earlier I'd have pushed for that. But too late to change that now. The goos news is that we can always add an abstraction name in the future.

--- Ben

# Posted By Ben Forta | 7/6/07 2:33 PM

Just curious if for MSSQL if they are using SCOPE_IDENTITY() or @@IDENTITY to get the generated key value, any ideas? In the past I've done the following in MSSQL to not have to use triggers or a second query call:

<cfquery name="qCreate" datasource="#getDatasource().getName()#">
INSERT INTO
Person(firstName, lastName, dob)
VALUES(
<cfqueryparam cfsqltype="cf_sql_varchar" value="#firstName#" />,
<cfqueryparam cfsqltype="cf_sql_varchar" value="#lastName#" />,
<cfqueryparam cfsqltype="cf_sql_datetime" value="#dob#" />
)

SELECT
SCOPE_IDENTITY() AS personId
</cfquery>

<cfscript>
personId = qCreate.personId;
</cfscript>

# Posted By phill.nacelli | 7/6/07 3:21 PM

Phill, neither. I am sure that under the hood that is exactly what the drivers are doing, but, it is the drivers that are exposing this information and we're capturing it.

--- Ben

# Posted By Ben Forta | 7/6/07 3:43 PM

Suppose my database server supports multiple row inserts. Is an array returned in this case?

INSERT INTO test
(val1, val2, val3, val4)
(SELECT 'a1', 'a2', 'a3', 'a4')
UNION
(SELECT 'b1', 'b2', 'b3', 'b4')
UNION
(SELECT 'c1', 'c2', 'c3', 'c4')

# Posted By Daren Munroe | 7/6/07 4:56 PM

Ben-
This is cool, but.....
Has anyone mentioned the possibility of returning a newID() (mssql).
I am just saving it now before inserting it, but this would be way easier.

DECLARE newUUID CHAR(36) OUT
SET newUUID=newID()

INSERT INTO table(uuid, name,...)
VALUES(newUUID, @name,...)

# Posted By Phillip Gagnon | 7/6/07 11:23 PM

Phillip,

You insert guid manually to tables?

# Posted By radek | 7/7/07 4:08 PM

I wonder if it handles triggers. Ive been screwed in the past when an insert fired triggers ; select @@identity returned the last generated ID, which may not be the ID for the record I inserted.

# Posted By Jim Collins | 7/7/07 5:23 PM

@radek

It's easy to do:

insert("-", CreateUUID(), 23)

will create a compatable GUID for MSSQL

@jim

If you're use MSSQL: NEVER depend on @@Identity. Use SCOPE_IDENTITY().

# Posted By Tony Petruzzi | 7/8/07 1:09 AM

@tony thanks for the tip will do

# Posted By Jim Collins | 7/8/07 1:31 AM

hey Jim,

That's why I was wondering if it used @@IDENTITY versus SCOPE_IDENTITY(). @@IDENTITY does not guarantee that the value returned is the last id generated from your transaction, where SCOPE_IDENTITY() does.

Cheers..

# Posted By phill.nacelli | 7/8/07 12:38 PM

i know its a little lower level [beginner] then the other data bases.
but what about an access *.mdb odbc connection?
will it return the newly created primary key?

thanks

# Posted By -paul | 7/8/07 1:38 PM

@paul,

Tell me if I'm wrong here, but as far as I know, MSAccess doesn't have support for this. In the pass the only way I knew how to get an identity from a table was to use two queries wrapped inside a cftransaction tag. One would do the insert while the other would do a MAX(PK) afterwards. Now being the DB savvy guy that I am (stop laughing), I know this isn't a guaranteed method. There are some that will claim that setting the isolation level on the driver to serializable would do the trick, but it never sat right with me.

Anywho. My suggestion is always this: If you're using MSAccess as a database, I see no reason why you couldn't just use the express version of MSSQL 2005 or MSDE.

# Posted By Tony Petruzzi | 7/8/07 11:12 PM

I was just wondering, does this conflict with the code I am using for a long time?
I think that CF just adds the @@identity line to the code, what if it's allreadt there?

<cfquery name="qryIns" dsn="some_dsn">
INSERT INTO tbl_test (name)
VALUES ('my name');
SELECT @@identity as pkey
</cfquery>

<cfoutput>The primary Key is: #qryIns.pkey#</cfoutput>

# Posted By Jorrit | 7/9/07 4:41 AM

Tony, I don't believe so.

Jorrit, no, no conflict. But that code is unneeded in CF8.

--- Ben

# Posted By Ben Forta | 7/9/07 8:30 AM

Correct me if I'm wrong but I believe that using @@identity like in Jorrit's code can potentially return the wrong id. That's why SCOPE_IDENTITY() is used, it only deals with identity value within the same scope, where @@IDENTITY does not. So if user A runs the insert and then user B runs the insert before user A gets his SELECT @@IDENTITY as pkey user A will actually get user B's insert key.

# Posted By phill.nacelli | 7/9/07 8:48 AM

No that's not correct. @@identity is bound to your own session, which means you will never get someone else's PK. The real difference between the 2 is that scope_identity() will only return PKs that are explicitly inserted. For example, if a trigger for insert on table_source inserts a row in table_triggered, @@identity will return the PK for table_triggered whereas scope_identity() will return the PK for table_source.

It's not that one is better than the other, they just do different things. Admittedly, yes, if you have an plain insert statement in a cfquery, you probably want to retrieve the identity without worrying of whatever trigger is out there, so scope_identity() is a better fit.

Tof

# Posted By tof | 7/9/07 8:23 PM

Hey Ben, you say that Derby doesn't seem to support this, but I find it's working, as (using your nomenclature) result.identifycol, just like SQL Server.

FWIW, I'm using the embedded version, and the column definition for the table in which I'm inserting a record defines the primary key as "generated always as identity".

I'm just offering this in case others (like me) find the entry and wonder about Derby.

# Posted By charlie arehart | 7/25/07 11:48 PM

sorry, I meant to say identitycol (not identifycol).

# Posted By charlie arehart | 7/25/07 11:52 PM

This is a great feature but has anyone else been having problems with using existing (MS-SQL) triggers with CF8?

# Posted By John Scott | 8/6/07 11:54 AM

is there have a bug in coldfusion <cfquery/>

when u passs the cfquery a sql var,the var has the ' char,the cfquery will convert to the ' to '' auto,why?

for example:
<code>
<cfset sql="select * from some_table where char_col='1'"/>
<cfquery name="testQuery" datasource="someDs">
#sql#
</cfquery>
</code>

the sql pass to db's will be: select * from some_table where char_col=''1'',so throws a db exception's

but if u code the sql inner cfquery the every work ok:

<cfquery name="testQuery" datasource="someDs">
select * from some_table where char_col='1'
</cfquery>

is a bug of coldfusion 8 cfquery?!

please help me!thanks!

# Posted By feiy | 8/13/07 11:23 PM

john -

I am also having an issue with CF8 and exisiting triggers..

my triggers are usualy select scope_identity() or identity@@ as 'identity' but in CF8 it is throwing an error saying undefined. confirmed the same query works in cfmx 7

# Posted By jason | 8/22/07 10:25 PM

I am new to CF8 and i was wondering what is the best book to start with to learn how to create a application with CF8 and Dreamweaver.

Thank you

# Posted By Bobby S | 10/12/07 2:25 PM

Bobby, I think you'll find universal agreement that the best book is the CF Web Application Construction Kit Series, which has existed as 2 volumes for several years and as of CF8 is now 3 volumes. The first came out last month. You can find more here on Ben's site:

http://www.forta.com/books/032151548X/" target="_blank">http://www.forta.com/books/032151548X/

BTW, Ben, curiously it's not listed in the main page content of your books page (http://www.forta.com/books/), though it is in the left nav bar. Perhaps an oversight, just not updated since the book shipped?

# Posted By Charlie Arehart | 10/12/07 3:16 PM

For those of you who do not use auto-increment fields, you can achieve the same functionality by using the syntax below.
result_name.SQLPARAMETERS[index of pk]

# Posted By Jeremy Rottman | 10/30/07 3:40 PM

I guess I should have been more specific on my previous post. You can use that when, you are properly using your cfqueryparams.

# Posted By Jeremy Rottman | 10/31/07 1:14 PM

I think Jorrit was on to something with their inquery earlier. My CF8 is having problems with my old insert statements within the cfquery. Mainly the @@ portion. They just do not work unless I remove the Select @@ option. Anyone else witnessing this?

Jorritt wrote:
I was just wondering, does this conflict with the code I am using for a long time?
I think that CF just adds the @@identity line to the code, what if it's allreadt there?

<cfquery name="qryIns" dsn="some_dsn">
INSERT INTO tbl_test (name)
VALUES ('my name');
SELECT @@identity as pkey
</cfquery>

<cfoutput>The primary Key is: #qryIns.pkey#</cfoutput>

# Posted By Chill | 1/27/08 1:29 AM

Sorry, typo. *inquiry

# Posted By Chill | 1/27/08 1:31 AM

As an update. I was using Access and did not see the earlier post about Access's limitatons. I've dropped access and moved on to SQL Server. My old sets of code now work fine on CF8. Thanks for the helpful posts.

# Posted By Chill | 1/27/08 8:54 PM

I submitted the following bug to Adobe:
CF8 with SQl Server 2005 and JDBC 1.2. Doing an insert query as follows fails to work as advertised with [result].IDENTITYCOL. I have to use [result].GENERATED_KEYS instead. I use the code below as a workaround until it is fixed.


<cfquery name="qAppenddiadocument" datasource="DocumentImaging" result="diaresult">
insert into diadocument
(diadocument.diadocno, diadocument.diaentrydatetime, diadocument.dianoofpages, diadocument.diasuperdoctypekey)
values
(#lndiadocno#, #ldnow#, #lndianoofpages#, #lndiasuperdoctypekey#)
</cfquery>


<cfif IsDefined("diaresult.IDENTITYCOL") >
<CFSET lndiadocumentkey = #diaresult.IDENTITYCOL# />
<cfelse>
<CFSET lndiadocumentkey = #diaresult.GENERATED_KEYS# />
</cfif>

# Posted By Brian Oeding | 4/11/08 9:56 AM

Hi!
I tried unsuccessfully to get the Return Identity Values, the way you show us! All I get is:”KEY_VALUE” is not defined …”
Would you provide a valid example for Coldfusion 8 and MySQL? Thank you!

<CFQUERY NAME="qInsert" DATASOURCE="verb">

       INSERT INTO club
   (firstname, lastname)

      VALUES
       ('#formData.firstname#', '#formData.lastname #')

   </CFQUERY>
Silviu

# Posted By Silviu | 6/19/08 1:40 PM

This is a great feature but has anyone else been having problems with using existing (MS-SQL) triggers with CF8?

http://www.simpy.com/user/winbill
http://www.diigo.com/user/lullabot

# Posted By nesthone | 8/14/08 2:57 AM

Here is a working example. REMEMBER you must be using the v4 o5 v5 ODBC driver for mySQL... version 3 does not work!

<cfquery datasource="#APPLICATION.dsn#" name="update_my_db" result="my_result">
INSERT INTO tbl_foo
(some_field_name)
VALUES (some_field_value)
</cfquery>
<cfoutput>My New Primary KeyID = #my_result.GENERATED_KEY#</cfoutput>

# Posted By Steve | 9/2/08 4:03 PM

[Add Comment]

ColdFusion 8 Can Return Identity Values