RE: SQL-generated primary keys
Is MySQL's last_inserted_id thread-safe too? -Original Message- From: Andy [mailto:[EMAIL PROTECTED] Sent: Friday, March 10, 2006 11:52 PM To: CF-Talk Subject: RE: SQL-generated primary keys You chose wisely, my son. -Original Message- From: Chris Mueller [mailto:[EMAIL PROTECTED] Sent: Friday, March 10, 2006 7:11 AM To: CF-Talk Subject: Re: SQL-generated primary keys Thanks everyone for all your help! I am using SQL server, and have reworked my queries to use SCOPE_IDENTITY(). Chris If it is SQL Server, do this cfquery Insert into () Values (); SELECT SCOPE_IDENTITY() AS id /cfquery That will give you the correct identity id Sandra Clark -Original Message- From: Chris Mueller [mailto:[EMAIL PROTECTED] Sent: Thursday, March 09, 2006 4:35 PM To: CF-Talk Subject: SQL-generated primary keys Here's a question I've been wrestling wtih. Suppose that there is an HTML form to create a new user. When the form is submitted, that user's information is added to the 'user' table. The table uses an auto-increment integer for its primary key/user id, so I'd like to grab that value for the newly created user. I've been using queries like this one: SELECT MAX(user_id) FROM users, but I worry that if two users are created at the same time, then my query might give me the user id of the wrong user. Is there a better way to do this? Or maybe I shouldn't even worry about this? Thanks. Chris ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:235128 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: SQL-generated primary keys
Thanks everyone for all your help! I am using SQL server, and have reworked my queries to use SCOPE_IDENTITY(). Chris If it is SQL Server, do this cfquery Insert into () Values (); SELECT SCOPE_IDENTITY() AS id /cfquery That will give you the correct identity id Sandra Clark -Original Message- From: Chris Mueller [mailto:[EMAIL PROTECTED] Sent: Thursday, March 09, 2006 4:35 PM To: CF-Talk Subject: SQL-generated primary keys Here's a question I've been wrestling wtih. Suppose that there is an HTML form to create a new user. When the form is submitted, that user's information is added to the 'user' table. The table uses an auto-increment integer for its primary key/user id, so I'd like to grab that value for the newly created user. I've been using queries like this one: SELECT MAX(user_id) FROM users, but I worry that if two users are created at the same time, then my query might give me the user id of the wrong user. Is there a better way to do this? Or maybe I shouldn't even worry about this? Thanks. Chris ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:234993 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
RE: SQL-generated primary keys
You chose wisely, my son. -Original Message- From: Chris Mueller [mailto:[EMAIL PROTECTED] Sent: Friday, March 10, 2006 7:11 AM To: CF-Talk Subject: Re: SQL-generated primary keys Thanks everyone for all your help! I am using SQL server, and have reworked my queries to use SCOPE_IDENTITY(). Chris If it is SQL Server, do this cfquery Insert into () Values (); SELECT SCOPE_IDENTITY() AS id /cfquery That will give you the correct identity id Sandra Clark -Original Message- From: Chris Mueller [mailto:[EMAIL PROTECTED] Sent: Thursday, March 09, 2006 4:35 PM To: CF-Talk Subject: SQL-generated primary keys Here's a question I've been wrestling wtih. Suppose that there is an HTML form to create a new user. When the form is submitted, that user's information is added to the 'user' table. The table uses an auto-increment integer for its primary key/user id, so I'd like to grab that value for the newly created user. I've been using queries like this one: SELECT MAX(user_id) FROM users, but I worry that if two users are created at the same time, then my query might give me the user id of the wrong user. Is there a better way to do this? Or maybe I shouldn't even worry about this? Thanks. Chris ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:235110 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: SQL-generated primary keys
depending on your database, you can retrieve the identity value when it's created. SQL Server has @@identity mySQL has last_inserted_id http://weblogs.macromedia.com/cantrell/archives/2004/11/safely_selectin.cfm http://www.webtricks.com/sourcecode/code.cfm?CodeID=23 there's a gaggle of info out there on the various techniques that the various RDBMSs offer. Google is your budy :) On 3/9/06, Chris Mueller [EMAIL PROTECTED] wrote: Here's a question I've been wrestling wtih. Suppose that there is an HTML form to create a new user. When the form is submitted, that user's information is added to the 'user' table. The table uses an auto-increment integer for its primary key/user id, so I'd like to grab that value for the newly created user. I've been using queries like this one: SELECT MAX(user_id) FROM users, but I worry that if two users are created at the same time, then my query might give me the user id of the wrong user. Is there a better way to do this? Or maybe I shouldn't even worry about this? Thanks. Chris ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:234923 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: SQL-generated primary keys
There are a ton of different ways to handle this. Starting with using a CFtransaction tag around the two queries down to just not auto-generating it through the database and inserting the value via queries. Some databases offer functions for grabbing the autogenerated ID that was inserted, that might be the best place to start looking. On 3/9/06, Chris Mueller [EMAIL PROTECTED] wrote: Here's a question I've been wrestling wtih. Suppose that there is an HTML form to create a new user. When the form is submitted, that user's information is added to the 'user' table. The table uses an auto-increment integer for its primary key/user id, so I'd like to grab that value for the newly created user. I've been using queries like this one: SELECT MAX(user_id) FROM users, but I worry that if two users are created at the same time, then my query might give me the user id of the wrong user. Is there a better way to do this? Or maybe I shouldn't even worry about this? Thanks. Chris ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:234924 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: SQL-generated primary keys
depends on the DB...which one are you using? Bryan Stevenson B.Comm. VP Director of E-Commerce Development Electric Edge Systems Group Inc. phone: 250.480.0642 fax: 250.480.1264 cell: 250.920.8830 e-mail: [EMAIL PROTECTED] web: www.electricedgesystems.com ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:234925 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: SQL-generated primary keys
Here's a question I've been wrestling wtih. Suppose that there is an HTML form to create a new user. When the form is submitted, that user's information is added to the 'user' table. The table uses an auto-increment integer for its primary key/user id, so I'd like to grab that value for the newly created user. I've been using queries like this one: SELECT MAX(user_id) FROM users, but I worry that if two users are created at the same time, then my query might give me the user id of the wrong user. Is there a better way to do this? Or maybe I shouldn't even worry about this? Thanks. Chris I would definately worry about it. Yes, if you select max(user_id) from users to get the inserted record id after performing an insert without any other preparation, you will create a race condition in which two pages could erroneously receive the same user_id value even though 2 separate values had been inserted into the table. There are several ways to get around this issue. One is to use an nvarchar, varchar or guid column in SQL server and generate your ID prior to the insert. If you need to insert additional records in related tables for the generated user, then you'll have to either generate the ID in ColdFusion or you'll have to find another way to return that GUID to CF from SQL Server (the function NewID() is used in SQL Server to generate these id's) such as a stored procedure. Oracle by comparison will give you SQL Standard Sequences (not widely supported yet) which can be used similarly to identity columns but allow you to fetch the next id prior to insert. Personally I prefer to generate ID's in CF. Another popular method is to use a cftransaction around the insert and the select statements... cftransaction isolation=serializable cfquery ...insert into .../cfquery cfquery ...select max() from .../cfquery /cftransaction This eliminates the race condition at the expense of carving out some scalability. By this I mean that as the size of your table becomes larger, it takes longer to perform the select max() query. Generally speaking the increase in time for the 2nd query isn't noticeable, but it's possible in theory that it could be an issue with very large numbers of records (I would guess in the millions, so it's not likely to be an issue with a users table specifically, but may be with some other tables). Another option is to use database-specific tools to fetch the new id. With SQL Server 2000 or 2005 you can use a single query with 2 statements like so: cfquery ... insert into ... select scope_identity() as newid /cfquery The scope_identity() function was added in SQL Server 2000 to resolve the issue of the previously available @@identity variable not being thread safe (i.e. same race condition issue you have now). Of course, the trade here is that the code then becomes dependant on the choice of SQL Server. Most of the time this is fine, but on the off chance the company hires a new CTO who's in love with Oracle, you'll have to replace any references to this method. Of course, the same is largely true of the first method above if you use newid() to generate GUIDs, although using stored procedures to perform those inserts can insulate the ColdFusion code from those changes (i.e. you would still have to do the work if you needed to convert it to use Oracle, but your CF code would remain unchanged). So -- there are some options, plus and minus. For my money it just seems to be easiest in general to generate ID's in advance of the insert with CF, although in some environments it's necessary to allow records to be inserted by non CF systems as well, at which point a database-specific tool (usually stored procedures) becomes a necessity. s. isaac dealey 434.293.6201 new epoch : isn't it time for a change? add features without fixtures with the onTap open source framework http://www.fusiontap.com http://coldfusion.sys-con.com/author/4806Dealey.htm ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:234926 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
RE: SQL-generated primary keys
If it is SQL Server, do this cfquery Insert into () Values (); SELECT SCOPE_IDENTITY() AS id /cfquery That will give you the correct identity id Sandra Clark -Original Message- From: Chris Mueller [mailto:[EMAIL PROTECTED] Sent: Thursday, March 09, 2006 4:35 PM To: CF-Talk Subject: SQL-generated primary keys Here's a question I've been wrestling wtih. Suppose that there is an HTML form to create a new user. When the form is submitted, that user's information is added to the 'user' table. The table uses an auto-increment integer for its primary key/user id, so I'd like to grab that value for the newly created user. I've been using queries like this one: SELECT MAX(user_id) FROM users, but I worry that if two users are created at the same time, then my query might give me the user id of the wrong user. Is there a better way to do this? Or maybe I shouldn't even worry about this? Thanks. Chris ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:234927 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: SQL-generated primary keys
depending on your database, you can retrieve the identity value when it's created. SQL Server has @@identity mySQL has last_inserted_id Although you really shouldn't use @@identity if you have access to SQL Server 2000 or later... @@identity is not thread safe and will result in the same race condition, that's why scope_identity() was introduced in SQL 2000 s. isaac dealey 434.293.6201 new epoch : isn't it time for a change? add features without fixtures with the onTap open source framework http://www.fusiontap.com http://coldfusion.sys-con.com/author/4806Dealey.htm ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:234928 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
RE: SQL-generated primary keys
After the command for inserting the new user, use the following: select @@identity from MyTable cftransaction cfquery What ever your SQL is to insert the new user /cfquery cfquery select @@identity from MyTable /cfquery /cftransaction -Original Message- From: Chris Mueller [mailto:[EMAIL PROTECTED] Sent: Thursday, March 09, 2006 1:35 PM To: CF-Talk Subject: SQL-generated primary keys Here's a question I've been wrestling wtih. Suppose that there is an HTML form to create a new user. When the form is submitted, that user's information is added to the 'user' table. The table uses an auto-increment integer for its primary key/user id, so I'd like to grab that value for the newly created user. I've been using queries like this one: SELECT MAX(user_id) FROM users, but I worry that if two users are created at the same time, then my query might give me the user id of the wrong user. Is there a better way to do this? Or maybe I shouldn't even worry about this? Thanks. Chris ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:234929 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
RE: SQL-generated primary keys
My mistake... cfquery insert into mytable([columns]) values([values]) select @@identity as user_id /cfquery All in one query, not two like I posted. -Original Message- From: Jacob [mailto:[EMAIL PROTECTED] Sent: Thursday, March 09, 2006 2:13 PM To: CF-Talk Subject: RE: SQL-generated primary keys After the command for inserting the new user, use the following: select @@identity from MyTable cftransaction cfquery What ever your SQL is to insert the new user /cfquery cfquery select @@identity from MyTable /cfquery /cftransaction -Original Message- From: Chris Mueller [mailto:[EMAIL PROTECTED] Sent: Thursday, March 09, 2006 1:35 PM To: CF-Talk Subject: SQL-generated primary keys Here's a question I've been wrestling wtih. Suppose that there is an HTML form to create a new user. When the form is submitted, that user's information is added to the 'user' table. The table uses an auto-increment integer for its primary key/user id, so I'd like to grab that value for the newly created user. I've been using queries like this one: SELECT MAX(user_id) FROM users, but I worry that if two users are created at the same time, then my query might give me the user id of the wrong user. Is there a better way to do this? Or maybe I shouldn't even worry about this? Thanks. Chris ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:234930 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
RE: SQL-generated primary keys
Something I used to do when I was using PostGres years ago was select a new identity from a table and then use it for all subsequent updates. People tell me you can't do this with SQL Server, but I think this is BY FAR the safest way to handle this. Here's some pseudo code for clarity: select new id as @identity from users update users set fname = 'jake', lname = 'munson' where id = @identity [INFO] -- Access Manager: This transmission may contain information that is privileged, confidential and/or exempt from disclosure under applicable law. If you are not the intended recipient, you are hereby notified that any disclosure, copying, distribution, or use of the information contained herein (including any reliance thereon) is STRICTLY PROHIBITED. If you received this transmission in error, please immediately contact the sender and destroy the material in its entirety, whether in electronic or hard copy format. Thank you. A2 ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:234935 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: SQL-generated primary keys
Something I used to do when I was using PostGres years ago was select a new identity from a table and then use it for all subsequent updates. People tell me you can't do this with SQL Server, but I think this is BY FAR the safest way to handle this. Here's some pseudo code for clarity: Just like SELECT nextval FROM dual in Oracle Bryan Stevenson B.Comm. VP Director of E-Commerce Development Electric Edge Systems Group Inc. phone: 250.480.0642 fax: 250.480.1264 cell: 250.920.8830 e-mail: [EMAIL PROTECTED] web: www.electricedgesystems.com ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:234936 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: SQL-generated primary keys
I did some maintenance on a project recently that did this. They also only had one sequence for the entire project. Their theory was it made it harder for someone to try and edit/delete/view records via just changing the ID in the URL/FORM. Since your records could go from 1 to 50 to 190. We just use triggers here and those plug in the nextval of the sequence. We also almost never need the ID of what was just inserted, when we do typically we just rely on a cftransaction and do two queries or if more complex then an SP. On 3/9/06, Bryan Stevenson [EMAIL PROTECTED] wrote: Just like SELECT nextval FROM dual in Oracle ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:234937 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: SQL-generated primary keys
I never said I use 1 sequence...just an example We use a sequence for each table ;-) We also use pre-insert triggers to grab nextval if the PK is not supplied in the insert statement When inserting a parent and then children we always select from the sequence first and use the new PK val in the parent and al children inserts Cheers Bryan Stevenson B.Comm. VP Director of E-Commerce Development Electric Edge Systems Group Inc. phone: 250.480.0642 fax: 250.480.1264 cell: 250.920.8830 e-mail: [EMAIL PROTECTED] web: www.electricedgesystems.com ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:234939 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: SQL-generated primary keys
We too use a sequence per table, just thought it was an interesting approach when I saw someone doing only one and felt like mentioning to see if any others do that. On some of ours we have a pre-insert trigger and others we do not, there is no consistency here on that other than no one ever seems to manual insert the IDs. On 3/9/06, Bryan Stevenson [EMAIL PROTECTED] wrote: I never said I use 1 sequence...just an example We use a sequence for each table ;-) We also use pre-insert triggers to grab nextval if the PK is not supplied in the insert statement When inserting a parent and then children we always select from the sequence first and use the new PK val in the parent and al children inserts Cheers ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:234941 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
RE: SQL-generated primary keys
But use scope_identity() instead of @@identity, otherwise you'll have the same race condition you have now. My mistake... cfquery insert into mytable([columns]) values([values]) select @@identity as user_id /cfquery All in one query, not two like I posted. -Original Message- From: Jacob [mailto:[EMAIL PROTECTED] Sent: Thursday, March 09, 2006 2:13 PM To: CF-Talk Subject: RE: SQL-generated primary keys After the command for inserting the new user, use the following: select @@identity from MyTable cftransaction cfquery What ever your SQL is to insert the new user /cfquery cfquery select @@identity from MyTable /cfquery /cftransaction -Original Message- From: Chris Mueller [mailto:[EMAIL PROTECTED] Sent: Thursday, March 09, 2006 1:35 PM To: CF-Talk Subject: SQL-generated primary keys Here's a question I've been wrestling wtih. Suppose that there is an HTML form to create a new user. When the form is submitted, that user's information is added to the 'user' table. The table uses an auto-increment integer for its primary key/user id, so I'd like to grab that value for the newly created user. I've been using queries like this one: SELECT MAX(user_id) FROM users, but I worry that if two users are created at the same time, then my query might give me the user id of the wrong user. Is there a better way to do this? Or maybe I shouldn't even worry about this? Thanks. Chris ~~ ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:234963 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
