Re: Dynamic ID's
Dominic Watson wrote: Is that definately true with the select insert as I exampled? I imagine (but that is all I do, quite willing to be wrong) that the single SQL statement must complete before another is allowed to run..? I.e. I imagine that this SQL will not run into the problem you describe: INSERT INTO myTable (id, title) SELECT Max(id) + 1, 'Some title' FROM myTable I haven't tested your statements, but extrapolating from the basics of the concurrency control algorithms used by different databases I think that is not correct. With databases that use a locking approach to concurrency (DB2, MS SQL Server, Sybase) you will not have the problem because multiple occurences of this query will be serialized. With databases that use multiversioning (Oracle, PostgreSQL and MS SQL Server with snapshot isolation) this query can run concurrently and you can have conflicts. I think with MySQL/InnoDB your query will be serialized if you have a primary key (due to next key locking), and with MySQL/MyISAM you should be safe due to the overall restriction that only one data changing statement can write to a table. Jochem ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:295233 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Dynamic ID's
I haven't tested your statements, but extrapolating from the basics of the concurrency control algorithms used by different databases I think that is not correct. Thanks Jochem, good to know :) Good job to isaac pointing out the need for IsNull doing it this way too ;) Dominic ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:295235 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Dynamic ID's
Thanks Jochem, good to know :) Good job to isaac pointing out the need for IsNull doing it this way too ;) tnx p.s. I never know how to handle complements. :P -- s. isaac dealey ^ new epoch isn't it time for a change? ph: 503.236.3691 http://onTap.riaforge.org ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:295282 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Dynamic ID's
With databases that use a locking approach to concurrency (DB2, MS SQL Server, Sybase) you will not have the problem because multiple occurences of this query will be serialized. With databases that use multiversioning (Oracle, PostgreSQL and MS SQL Server with snapshot isolation) this query can run concurrently and you can have conflicts. I think with MySQL/InnoDB your query will be serialized if you have a primary key (due to next key locking), and with MySQL/MyISAM you should be safe due to the overall restriction that only one data changing statement can write to a table. Well that's definitely a much more thorough understanding of it than I had... interesting (and good to know) that the answer varies with SQL Server depending on version and/or configuration. So I guess the short answer here would be -- unless you know specifically how your database handles it, use the serializeable transaction... and maybe even if you do know. :P -- s. isaac dealey ^ new epoch isn't it time for a change? ph: 503.236.3691 http://onTap.riaforge.org ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:295283 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Dynamic ID's
If someone has a better idea for a unique ID, I'm open to suggestions. Well, if the id is for records in a table, how about using an auto counter field? -- ___ REUSE CODE! Use custom tags; See http://www.contentbox.com/claude/customtags/tagstore.cfm (Please send any spam to this address: [EMAIL PROTECTED]) Thanks. ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:295173 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Dynamic ID's
If you need an absolute unique id that isn't tied to a database, feel free to use the createUUID() function. Otherwise, what you're asking for can be done via SQL... so... not sure why this is overcomplicated. Do you have a unique situation? care to explain? On Dec 20, 2007 9:23 AM, Rick Sanders [EMAIL PROTECTED] wrote: Hello list, I've done dynamic ID's before in .net and xml. I can't seem to figure out how to do it in CF. Here's what I want to do: - Query the database with the ID's. - Sort the ID's in proper numerical order - Grab the last ID (largest number which will be the last row of the query) - Then, add 1 to the number If someone has a better idea for a unique ID, I'm open to suggestions. Happy Holidays! Rick Sanders Webenergy Canada: 902-401-7689 USA: 919-799-9076 Canada: www.webenergy.ca USA: www.webenergyusa.com ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:295174 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: Dynamic ID's
What exactly are you trying to do? Robert B. Harrison Director of Interactive services Austin Williams 125 Kennedy Drive, Suite 100 Hauppauge NY 11788 T : 631.231.6600 Ext. 119 F : 631.434.7022 www.austin-williams.com Great advertising can't be either/or... It must be . -Original Message- From: Rick Sanders [mailto:[EMAIL PROTECTED] Sent: Thursday, December 20, 2007 9:23 AM To: CF-Talk Subject: Dynamic ID's Hello list, I've done dynamic ID's before in .net and xml. I can't seem to figure out how to do it in CF. Here's what I want to do: - Query the database with the ID's. - Sort the ID's in proper numerical order - Grab the last ID (largest number which will be the last row of the query) - Then, add 1 to the number If someone has a better idea for a unique ID, I'm open to suggestions. Happy Holidays! Rick Sanders Webenergy Canada: 902-401-7689 USA: 919-799-9076 Canada: www.webenergy.ca USA: www.webenergyusa.com ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:295176 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Dynamic ID's
Your db platform doesn't support autonumber? Steve Cutter Blades Adobe Certified Professional Advanced Macromedia ColdFusion MX 7 Developer _ http://blog.cutterscrossing.com Rick Sanders wrote: Hello list, I've done dynamic ID's before in .net and xml. I can't seem to figure out how to do it in CF. Here's what I want to do: - Query the database with the ID's. - Sort the ID's in proper numerical order - Grab the last ID (largest number which will be the last row of the query) - Then, add 1 to the number If someone has a better idea for a unique ID, I'm open to suggestions. Happy Holidays! Rick Sanders Webenergy Canada: 902-401-7689 USA: 919-799-9076 Canada: www.webenergy.ca USA: www.webenergyusa.com ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:295175 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Dynamic ID's
cfquery declare @newid int select @newid = max(id) from Members if @newid is null begin set @newid=1 end else begin set @[EMAIL PROTECTED] end INSERT INTO Members ... /CFQUERY On 12/20/07 9:23 AM, Rick Sanders [EMAIL PROTECTED] wrote: Hello list, I've done dynamic ID's before in .net and xml. I can't seem to figure out how to do it in CF. Here's what I want to do: - Query the database with the ID's. - Sort the ID's in proper numerical order - Grab the last ID (largest number which will be the last row of the query) - Then, add 1 to the number If someone has a better idea for a unique ID, I'm open to suggestions. Happy Holidays! Rick Sanders Webenergy Canada: 902-401-7689 USA: 919-799-9076 Canada: www.webenergy.ca USA: www.webenergyusa.com ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:295179 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: Dynamic ID's
I know of someone who uses a Sequence table for the ID functions: Sequence table: table_name (varchar) last_sequence (int) next_sequence (int) Query the table based on table name get the next_sequence and then update The sequence table after the insert -Original Message- From: Rick Sanders [mailto:[EMAIL PROTECTED] Sent: Thursday, December 20, 2007 9:23 AM To: CF-Talk Subject: Dynamic ID's Hello list, I've done dynamic ID's before in .net and xml. I can't seem to figure out how to do it in CF. Here's what I want to do: - Query the database with the ID's. - Sort the ID's in proper numerical order - Grab the last ID (largest number which will be the last row of the query) - Then, add 1 to the number If someone has a better idea for a unique ID, I'm open to suggestions. Happy Holidays! Rick Sanders Webenergy Canada: 902-401-7689 USA: 919-799-9076 Canada: www.webenergy.ca USA: www.webenergyusa.com ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:295178 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: Dynamic ID's
The database does support autonumber, but this is a legacy app and database. Also, there are many times where autonumber isn't useful because when you delete a record, the database re-numbers everything changing the ID which we didn't want. Rick Sanders Webenergy Canada: 902-401-7689 USA: 919-799-9076 Canada: www.webenergy.ca USA: www.webenergyusa.com -Original Message- From: Cutter (CFRelated) [mailto:[EMAIL PROTECTED] Sent: December-20-07 10:31 AM To: CF-Talk Subject: Re: Dynamic ID's Your db platform doesn't support autonumber? Steve Cutter Blades Adobe Certified Professional Advanced Macromedia ColdFusion MX 7 Developer _ http://blog.cutterscrossing.com Rick Sanders wrote: Hello list, I've done dynamic ID's before in .net and xml. I can't seem to figure out how to do it in CF. Here's what I want to do: - Query the database with the ID's. - Sort the ID's in proper numerical order - Grab the last ID (largest number which will be the last row of the query) - Then, add 1 to the number If someone has a better idea for a unique ID, I'm open to suggestions. Happy Holidays! Rick Sanders Webenergy Canada: 902-401-7689 USA: 919-799-9076 Canada: www.webenergy.ca USA: www.webenergyusa.com ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:295181 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Dynamic ID's
I'd let the database create the number. But anyway, you can do it like this: cfquery name=getnumbers datasource=#variables.dsn# SELECT max(tableID) as Lastnumber from tablename /cfquery cfset newID = getNumbers.Lastnumber + 1 / I have functions where i do this, but generally you'd be best to let the databaes do it for you. Cheers Mike Kear Windsor, NSW, Australia Adobe Certified Advanced ColdFusion Developer AFP Webworks http://afpwebworks.com ColdFusion, PHP, ASP, ASP.NET hosting from AUD$15/month On 12/21/07, Cutter (CFRelated) [EMAIL PROTECTED] wrote: Your db platform doesn't support autonumber? Steve Cutter Blades Adobe Certified Professional Advanced Macromedia ColdFusion MX 7 Developer _ http://blog.cutterscrossing.com Rick Sanders wrote: Hello list, I've done dynamic ID's before in .net and xml. I can't seem to figure out how to do it in CF. Here's what I want to do: - Query the database with the ID's. - Sort the ID's in proper numerical order - Grab the last ID (largest number which will be the last row of the query) - Then, add 1 to the number If someone has a better idea for a unique ID, I'm open to suggestions. Happy Holidays! Rick Sanders Webenergy Canada: 902-401-7689 USA: 919-799-9076 Canada: www.webenergy.ca USA: www.webenergyusa.com -- ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:295182 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Dynamic ID's
How about an Identity field (in MS SQL) Or alternatively CreateUUID() ? On 20/12/2007, Rick Sanders [EMAIL PROTECTED] wrote: Hello list, I've done dynamic ID's before in .net and xml. I can't seem to figure out how to do it in CF. Here's what I want to do: - Query the database with the ID's. - Sort the ID's in proper numerical order - Grab the last ID (largest number which will be the last row of the query) - Then, add 1 to the number If someone has a better idea for a unique ID, I'm open to suggestions. Happy Holidays! Rick Sanders Webenergy Canada: 902-401-7689 USA: 919-799-9076 Canada: www.webenergy.ca USA: www.webenergyusa.com ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:295177 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: Dynamic ID's
Hey guys, Yup, this is a unique situation, it's a new application on top of an old database built in Pervasive SQL. The Primary key holds ID numbers that aren't auto generated. The legacy app that sat on top of it was Tango 2000, and the way the ID was done was by querying the database, sorting the ID's in the resultset in numeric order, grabbing the last ID number and adding 1 to it. This actually worked very well. Kind regards, Rick Sanders Webenergy Canada: 902-401-7689 USA: 919-799-9076 Canada: www.webenergy.ca USA: www.webenergyusa.com -Original Message- From: Todd [mailto:[EMAIL PROTECTED] Sent: December-20-07 10:31 AM To: CF-Talk Subject: Re: Dynamic ID's If you need an absolute unique id that isn't tied to a database, feel free to use the createUUID() function. Otherwise, what you're asking for can be done via SQL... so... not sure why this is overcomplicated. Do you have a unique situation? care to explain? On Dec 20, 2007 9:23 AM, Rick Sanders [EMAIL PROTECTED] wrote: Hello list, I've done dynamic ID's before in .net and xml. I can't seem to figure out how to do it in CF. Here's what I want to do: - Query the database with the ID's. - Sort the ID's in proper numerical order - Grab the last ID (largest number which will be the last row of the query) - Then, add 1 to the number If someone has a better idea for a unique ID, I'm open to suggestions. Happy Holidays! Rick Sanders Webenergy Canada: 902-401-7689 USA: 919-799-9076 Canada: www.webenergy.ca USA: www.webenergyusa.com ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:295180 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Dynamic ID's
Or, if you really need to do it the way you describe, the SQL could look like this: -- SELECT A NEW ID SELECT Max(idField) + 1 FROM myTable But yeh, if you can set an auto id in the db I'd go with that. Dominic On 20/12/2007, JediHomer [EMAIL PROTECTED] wrote: How about an Identity field (in MS SQL) Or alternatively CreateUUID() ? On 20/12/2007, Rick Sanders [EMAIL PROTECTED] wrote: Hello list, I've done dynamic ID's before in .net and xml. I can't seem to figure out how to do it in CF. Here's what I want to do: - Query the database with the ID's. - Sort the ID's in proper numerical order - Grab the last ID (largest number which will be the last row of the query) - Then, add 1 to the number If someone has a better idea for a unique ID, I'm open to suggestions. Happy Holidays! Rick Sanders Webenergy Canada: 902-401-7689 USA: 919-799-9076 Canada: www.webenergy.ca USA: www.webenergyusa.com ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:295183 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Dynamic ID's
The database does support autonumber, What database is it? there are many times where autonumber isn't useful because when you delete a record, the database re-numbers everything Really? I've never seen such a database that re-numbers auto numbers. -- ___ REUSE CODE! Use custom tags; See http://www.contentbox.com/claude/customtags/tagstore.cfm (Please send any spam to this address: [EMAIL PROTECTED]) Thanks. ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:295187 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Dynamic ID's
Yup, this is a unique situation, it's a new application on top of an old database built in Pervasive SQL. The Primary key holds ID numbers that aren't auto generated. I think I see the situation and you're approach is valid but long winded ;). You can insert a record with a new ID with one SQL statement (and select the ID, should you need it, with another). Lets say your table has two columns, ID(int) and Title(varchar 50): cfquery name=qry_newThing datasource=#request.dsn# INSERT INTO thing (ID,Title) SELECT Max(ID) + 1, cfqueryparam cfsqltype=cf_sql_varchar value=#newTitle# maxlength=50 FROM thing !--- then select the id for reference if you like --- SELECT Max(ID) as ID FROM thing /cfquery !--- redundant line of code but you get the point: --- cfset myNewThingID = qry_newThing.ID Dominic ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:295192 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: Dynamic ID's
Beware of doing it this way... You will need cftransaction or some other way of ensuring that the ID is unique. Consider the situation where 2 inserts from 2 different people are happening nearly simultaneously. The second request for the max(ID) could occur before the first is inserted -mark -Original Message- From: Mike Kear [mailto:[EMAIL PROTECTED] Sent: Thursday, December 20, 2007 8:47 AM To: CF-Talk Subject: Re: Dynamic ID's I'd let the database create the number. But anyway, you can do it like this: cfquery name=getnumbers datasource=#variables.dsn# SELECT max(tableID) as Lastnumber from tablename /cfquery cfset newID = getNumbers.Lastnumber + 1 / I have functions where i do this, but generally you'd be best to let the databaes do it for you. Cheers Mike Kear Windsor, NSW, Australia Adobe Certified Advanced ColdFusion Developer AFP Webworks http://afpwebworks.com ColdFusion, PHP, ASP, ASP.NET hosting from AUD$15/month On 12/21/07, Cutter (CFRelated) [EMAIL PROTECTED] wrote: Your db platform doesn't support autonumber? Steve Cutter Blades Adobe Certified Professional Advanced Macromedia ColdFusion MX 7 Developer _ http://blog.cutterscrossing.com Rick Sanders wrote: Hello list, I've done dynamic ID's before in .net and xml. I can't seem to figure out how to do it in CF. Here's what I want to do: - Query the database with the ID's. - Sort the ID's in proper numerical order - Grab the last ID (largest number which will be the last row of the query) - Then, add 1 to the number If someone has a better idea for a unique ID, I'm open to suggestions. Happy Holidays! Rick Sanders Webenergy Canada: 902-401-7689 USA: 919-799-9076 Canada: www.webenergy.ca USA: www.webenergyusa.com -- ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:295195 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: Dynamic ID's
Rick, Autonumber doesn't re-number existing ids (does it??) ... Although it does mean that the numbers are not tightly sequential (there will be gaps). -mark -Original Message- From: Rick Sanders [mailto:[EMAIL PROTECTED] Sent: Thursday, December 20, 2007 8:47 AM To: CF-Talk Subject: RE: Dynamic ID's The database does support autonumber, but this is a legacy app and database. Also, there are many times where autonumber isn't useful because when you delete a record, the database re-numbers everything changing the ID which we didn't want. Rick Sanders Webenergy Canada: 902-401-7689 USA: 919-799-9076 Canada: www.webenergy.ca USA: www.webenergyusa.com -Original Message- From: Cutter (CFRelated) [mailto:[EMAIL PROTECTED] Sent: December-20-07 10:31 AM To: CF-Talk Subject: Re: Dynamic ID's Your db platform doesn't support autonumber? Steve Cutter Blades Adobe Certified Professional Advanced Macromedia ColdFusion MX 7 Developer _ http://blog.cutterscrossing.com Rick Sanders wrote: Hello list, I've done dynamic ID's before in .net and xml. I can't seem to figure out how to do it in CF. Here's what I want to do: - Query the database with the ID's. - Sort the ID's in proper numerical order - Grab the last ID (largest number which will be the last row of the query) - Then, add 1 to the number If someone has a better idea for a unique ID, I'm open to suggestions. Happy Holidays! Rick Sanders Webenergy Canada: 902-401-7689 USA: 919-799-9076 Canada: www.webenergy.ca USA: www.webenergyusa.com ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:295196 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Dynamic ID's
Beware of doing it this way... You will need cftransaction or some other way of ensuring that the ID is unique. Consider the situation where 2 inserts from 2 different people are happening nearly simultaneously. The second request for the max(ID) could occur before the first is inserted Is that definately true with the select insert as I exampled? I imagine (but that is all I do, quite willing to be wrong) that the single SQL statement must complete before another is allowed to run..? I.e. I imagine that this SQL will not run into the problem you describe: INSERT INTO myTable (id, title) SELECT Max(id) + 1, 'Some title' FROM myTable Cheers, Dominic ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:295204 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: Dynamic ID's
Dominic, Not really... Databases do a lot of things concurrently. The statement below should be safe, but locking, and other issues on a very busy database could cause deadlocks if 2 simultaneous processes are trying to update the same row. Having said that, I suspect that the identity or autonumber field is doing something quite similar to this under the hood. Perhaps Jochem or one of the other super SQL guru's could tell us more :) -Mark -Original Message- From: Dominic Watson [mailto:[EMAIL PROTECTED] Sent: Thursday, December 20, 2007 1:37 PM To: CF-Talk Subject: Re: Dynamic ID's Beware of doing it this way... You will need cftransaction or some other way of ensuring that the ID is unique. Consider the situation where 2 inserts from 2 different people are happening nearly simultaneously. The second request for the max(ID) could occur before the first is inserted Is that definately true with the select insert as I exampled? I imagine (but that is all I do, quite willing to be wrong) that the single SQL statement must complete before another is allowed to run..? I.e. I imagine that this SQL will not run into the problem you describe: INSERT INTO myTable (id, title) SELECT Max(id) + 1, 'Some title' FROM myTable Cheers, Dominic ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:295217 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Dynamic ID's
cfquery
declare @newid int
select @newid = max(id) from Members
if @newid is null
Oddly enough that's actually more complicated than is necessary if
you're using SQL Server -- i.e.
insert into members (memberid,x,y,z)
values (
(select 1+isnull(max(memberid),0) from members),
valx,
valy,
valz
)
I'm pretty certain that (or some variation) works for SQL Server...
which of course is totally unnecessary on SQL Server if you're using
identity columns. :) But then Rick replied later to say that he's not
using an identity/autonumber capable database with his legacy app...
although if the db has some equivalent of isnull() then the above might
work out fairly well. In failing that, this should work:
cftransaction isolation=serializable
cfquery name=next datasource=blah
select max(memberid) from members
/cfquery
cfquery datasource=blah
insert into members (memberid,x,y,z)
values (
cfqueryparam value=#next.memberid+1# cfsqltype=integer /,
xval,
yval,
zval
)
/cfquery
/cftransaction
The serializable transaction is important to ensure that there's not a
race condition between when the max is retrieved and when it's inserted
into the table. However I will say one other thing about this. There's
another race-condition-esque problem with this strategy that may crop
up. If a user deletes the record which is currently last in the database,
then the next record that gets inserted will have the same id as the
deleted record, which may be confusing if someone has bookmarked a page
with that id in the url and/or may cause some data related to the
deleted record to show up erroneously as having been associated to the
new record if the application doesn't use foreign key constraints (which
I personally almost always use) and doesn't do a great job of cleaning
up the data in those related tables when a record is deleted.
This may or may not be a big concern for you, given that it's a legacy
application and it may not have a huge user database and the likelyhood
of these things happening (or being problematic) may not be very high.
However if you want to avoid that situation, what I would recommend is
as at least one other guy on the list mentioned, creating a second table
just to hold the new id's. The very simple table would just contain two
columns - tablename varchar(x) currentidentity int - and instead of
select max(memberid) from members, you would use select currentidentity
from [identitytable] where tablename = 'members' (or whatever your table
is). You would also need to add a 3rd query in your transaction between
the 2 above to insert or update that row of the identity table, i.e.
cfquery dsn=blah
cfif val(next.identity)
update [identitytable] set currentidentity = currentidentity+1
where tablename = 'members'
cfelse
insert into [identitytable] (tablename,currentidentity)
values ('members',1)
/cfif
/cfquery
Although this does involved 3 queries instead of 2, its performance
won't change as the size of the database grows (as opposed to max()
getting slower as the table gets larger, which, with a legacy app is
probably not a real big issue). If you end up doing this sort of thing for
several tables you may want to create a CFC to abstract this so you
don't have so much duplicated or copy/pasted code.
hth,
ike
--
s. isaac dealey ^ new epoch
isn't it time for a change?
ph: 503.236.3691
http://onTap.riaforge.org
~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to
date
Get the Free Trial
http://ad.doubleclick.net/clk;160198600;22374440;w
Archive:
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:295223
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Dynamic ID's
The database does support autonumber, but this is a legacy app and database. Also, there are many times where autonumber isn't useful because when you delete a record, the database re-numbers everything changing the ID which we didn't want. Oh I misread that the first time... I thought you'd said it didn't support autonumbers. -- s. isaac dealey ^ new epoch isn't it time for a change? ph: 503.236.3691 http://onTap.riaforge.org ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:295224 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Dynamic ID's
Beware of doing it this way... You will need cftransaction or some other way of ensuring that the ID is unique. Consider the situation where 2 inserts from 2 different people are happening nearly simultaneously. The second request for the max(ID) could occur before the first is inserted ARGH! Why can't anyone design a *good* email client? They all suck! Is that definately true with the select insert as I exampled? I imagine (but that is all I do, quite willing to be wrong) that the single SQL statement must complete before another is allowed to run..? I.e. I imagine that this SQL will not run into the problem you describe: INSERT INTO myTable (id, title) SELECT Max(id) + 1, 'Some title' FROM myTable As far as I know, at least with SQL Server that's true, the server snapshots the environment, executes the query and then moves forward, so in theory, this single-query approach wouldn't have the race condition. I would expect that also to be true of Oracle, although I don't know for certain. MySQL I honestly would say it's up in the air -- I know MySQL does a lot of things rather differently than other databases, like allowing you to specify a different engine for each table, which I believe was a large part of the reason why it took them so gosh darned LNNN to add support for views. Similarly, their table names were at one time case-sensitive or could be (which was non-standard behavior). That being the case, I don't like to make any claims about what I expect MySQL to do. :P And similar with any other databases, most of which I've had no exposure to. -- s. isaac dealey ^ new epoch isn't it time for a change? ph: 503.236.3691 http://onTap.riaforge.org ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:295226 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
