Erdem
|
| Posted: 11/02/2005, 5:24 AM |
|
Hi,
I need to create a sequence so that every time end user clicks on the submit button in order to send a problem ticket, system creates a unique 8 digit ticket number. Keeping in mind that I am planning to keep the database in my local SQL server. I would really appriciate if some one can give me a hint.
Regards
|
|
|
 |
E43509
Posts: 283
|
| Posted: 11/02/2005, 7:45 AM |
|
You should use a fucntion in the database to manage this. In oracle, it's builtin thru sequences that i can call the nextval and guaranteed it is unique.
In SqlServer here's a snippet of ColdFusion code and a SqlServer procedure that emulates a sequence generator.
You may be able to adapt:
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="webmaster@electricspeed.com">
<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>
|
 |
|
E43509
Posts: 283
|
| Posted: 11/02/2005, 7:49 AM |
|
Another idea is to use the identify column in sql server. Insert a row into a dummy table and look up the identity value.
|
|
|
|
|
