|Internet Business Consultant|
|Home||Blog||Bio||Projects||Contact||Latest Blog (new site): How to Get to Genius|
Simulating Oracle Sequences in Microsoft SQL Server & ColdFusion
The problem with using identity columns as primary keys for Web development is that identities are generated at the time of insert, and you can't pull them into your application code prior to inserting the row. This means that a user could double click the submit button resulting in duplicate data. If you could pull the ID into your application code, as you can Oracle sequences, and a user double-clicks the submit button, it would generate an error because it would try to insert the primary id twice.
In addition, some pseudo-object-oriented data models, such as the one used in MIT Course 6.916 Problem Set 4, require that every row in the database have a unique primary key. You can't accomplish this with the identity type because the keys are local to each table.
Global Unique Identifiers (GUIDs) may be a solution, but they are rather large and aren't guaranteed to be unique; however, you can pull them into your application code prior to inserting.
Another possibility is to create a two-column "sequences" table with an integer primary key and a stored procedure to lock the table and increment the value. Also, instead of updating the row, you could insert a new row and define a foreign key constraint on all the other tables' primary keys to reference the sequence table -- this could help ensure that the sequences don't get deleted, but it's probably not necessary (see below).
You obviously want to be careful to not truncate the sequences table, but if someone does, you'll know about it on the next insert because your code will generate an error when it tries to insert a null value into the primary key column. If this happens, simply reset the sequence_id column to a value greater than the max before truncation (if you don't have any idea what that was, set it really, really high). Ideally you should write some code to notify you if the stored procedure returns a null value.
Needed Table, Stored Procedure, & ColdFusion Custom Tag
<!--- Name: cf_nextval File: nextval.cfm Created: 5 July 2002 Version: 1.4 Author: James Thortnon <email@example.com> Usage: <cf_nextval seq=your_sequence> Precondition: seq is a valid sequence in the table sequences Postcondition: a variable with the same name as the sequence you passed in is set in the caller scope to the next available sequence_id Description: Simulates an Oracle sequence. Returns a unique identifier that can be used across multiple tables. Sequence table: CREATE TABLE sequences ( -- sequence is a reserved word seq varchar(100) primary key, sequence_id int ); MS SQL Server stored procedure: CREATE PROCEDURE nextval @sequence varchar(100), @sequence_id INT OUTPUT AS -- return an error if sequence does not exist -- so we will know if someone truncates the table set @sequence_id = -1 UPDATE sequences SET @sequence_id = sequence_id = sequence_id + 1 WHERE seq = @sequence RETURN @sequence_id ---> <cfset datasource="mcs"> <cfset webmaster_email="firstname.lastname@example.org"> <cfset error_template="error.cfm"> <cfstoredproc procedure="nextval" datasource=#datasource# returncode="yes"> <cfprocparam type="in" variable="sequence" cfsqltype="cf_sql_varchar" value=#attributes.seq#> <cfprocparam type="out" variable="sequence_id" cfsqltype="cf_sql_integer"> <cfprocresult name="nextval"> </cfstoredproc> <cfif cfstoredproc.statuscode is -1> <cfmail to=#webmaster_email# from=#webmaster_email# subject="ERROR: cf_nextval returned a status code of -1"> cf_nextval returned a status code of -1 for #attributes.seq# </cfmail> <cferror type=request template=#error_template#> <cfelse> <cfset "caller.#attributes.seq#" = sequence_id> </cfif>
|Electric Speed: Internet Marketers|