Re: MySQL Auto Increment Field

The article did mention trouble coming from the database itself if ones insert caused a trigger to be fired.  Then the @@identity could return the generated key from what ever the trigger created. Yes, and it does. Having had that unpleasant experience once, with an older application,

Re: MySQL Auto Increment Field

On Tue, Sep 29, 2009 at 12:22 PM, Azadi Saryev az...@sabai-dee.com wrote: if you are on CF8, use RESULT attribute of cfquery tag and then get GENERATED_KEY from that result: cfquery name=myquery datasource=... result=qResult INSERT ... /cfquery cfset newID = qResult.GENERATED_KEY As an

Re: MySQL Auto Increment Field

Just out of curiosity Sean, do you know if the value is lazy loaded, or do you have to pay the cost of retrieving the value on every query (or at least introspecting to determine whether a value is available) regardless? I know you're closest to Railo, but I'm interested in both (all three?)

Re: MySQL Auto Increment Field

if you are on CF8, use RESULT attribute of cfquery tag and then get GENERATED_KEY from that result: cfquery name=myquery datasource=... result=qResult INSERT ... /cfquery cfset newID = qResult.GENERATED_KEY Azadi Saryev Sabai-dee.com http://www.sabai-dee.com/ On 30/09/2009 03:16, Agha Mehdi

Re: MySQL Auto Increment Field

select last_insert_id() as id http://dev.mysql.com/doc/refman/5.0/en/information-functions.html#function_last-insert-id cheers, barneyb On Tue, Sep 29, 2009 at 12:16 PM, Agha Mehdi aghaime...@gmail.com wrote: All, How do I get the ID value back in CF from MySQL after doing an insert with

RE: MySQL Auto Increment Field

Nice Azadi, I didn't know you could do that. Will -Original Message- From: Azadi Saryev [mailto:az...@sabai-dee.com] Sent: 29 September 2009 20:23 To: cf-talk Subject: Re: MySQL Auto Increment Field if you are on CF8, use RESULT attribute of cfquery tag and then get GENERATED_KEY

Re: MySQL Auto Increment Field

Thanks Azadi and Barney. Which approach is better in performance? I'm assuming result.generated_key? Thanks again for help Agha On Sep 29, 2009, at 12:20 PM, Barney Boisvert bboisv...@gmail.com wrote: select last_insert_id() as id

RE: MySQL Auto Increment Field

You can also do this using SQL. Sure this works with MS SQL, not 100% sure about mySQL but it's standard SQL, so it should work. cfquery name=putData datasource=#dsn# SET NOCOUNT ON INSERT INTO my_table (my_field1, my_field2) VALUES ('#trim(value_field1)#',

Re: MySQL Auto Increment Field

You can also do this using SQL. Sure this works with MS SQL, not 100% sure about mySQL but it's standard SQL, so it should work. cfquery name=putData datasource=#dsn#      SET NOCOUNT ON         INSERT INTO my_table (my_field1, my_field2)         VALUES ('#trim(value_field1)#',

Re: MySQL Auto Increment Field

Thanks Azadi and Barney. Which approach is better in performance? I'm assuming result.generated_key? I would assume that there wouldn't be a significant performance difference either way. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ Fig Leaf Software provides the highest

RE: MySQL Auto Increment Field

Auto Increment Field You can also do this using SQL. Sure this works with MS SQL, not 100% sure about mySQL but it's standard SQL, so it should work. cfquery name=putData datasource=#dsn#      SET NOCOUNT ON         INSERT INTO my_table (my_field1, my_field2)         VALUES ('#trim

Re: MySQL Auto Increment Field

And for those folks using MSSQL, please note that @@IDENTITY is probably not what you want either. @@IDENTITY returns the last identity produced by a connection to the database, regardless of the table. So in some circumstances you can end up with an identity returned that is not from the table

RE: MySQL Auto Increment Field

Man. Now I'm glad I posted that. Just went through and changed all: SELECT @@Identity AS newId To SELECT SCOPE_IDENTITY() AS newId Looks like it worked. I've been using that statement for six or seven years. Never failed, but I could see where if it did I'd have had no

Re: MySQL Auto Increment Field

I had to go and make that very same change a couple years ago, so I can totally understand missing that. Glad to be of help. Cheers, Judah On Tue, Sep 29, 2009 at 1:29 PM, Robert Harrison rob...@austin-williams.com wrote: Man. Now I'm glad I posted that. Just went through and changed all:

Re: MySQL Auto Increment Field

While I agree that SCOPE_IDENTITY is the preferred syntax, it's unlikely that you'll encounter a problem with @@IDENTITY. CF will use a single connection for all queries within a page that use the same datasource, username and password, and the queries will be executed sequentially unless

Re: MySQL Auto Increment Field

Thank you all for the great inputs. On Tue, Sep 29, 2009 at 1:48 PM, Judah McAuley ju...@wiredotter.com wrote: I had to go and make that very same change a couple years ago, so I can totally understand missing that. Glad to be of help. Cheers, Judah On Tue, Sep 29, 2009 at 1:29 PM,

Re: MySQL Auto Increment Field

Dave Watts wrote: In summary, I would write new T-SQL with SCOPE_IDENTITY, but I wouldn't bother rewriting existing code. The article did mention trouble coming from the database itself if ones insert caused a trigger to be fired. Then the @@identity could return the generated key from what