James Thornton logo
James Thornton
Web jamesthornton.com
Internet Business Consultant
Home Blog Bio Projects Contact
JamesThornton.com -\> Software -\> ColdFusion -\> nextval.cfm

Name: cf_nextval
File: nextval.cfm
Updated: 6 July 2002
Version: 1.3
Author: James Thornton

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 <thornton@cs.baylor.edu>

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

               -- 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="webmaster@electricspeed.com">
<cfset error_template="error.cfm">

<cfstoredproc procedure="nextval"
<cfprocparam  type="in"
<cfprocparam  type="out"
<cfprocresult name="nextval">

<cfif cfstoredproc.statuscode is -1>
   <cfmail to=#webmaster_email#
           subject="ERROR: cf_nextval returned a status code of -1">
cf_nextval returned a status code of -1 for #attributes.seq#
   <cferror type=request template=#error_template#>
  <cfset "caller.#attributes.seq#" = sequence_id>

Follow espeed on Twitter