SQL identity issue

[Brian Knott]

Hi everyone.

 

I have an issue with inserting orders into a database.  What I currently do is create a random number and insert that number into the database when an order is inserted.  I then query the database to get the order number (unique ID generated by SQL Server).  Using this order number I then insert the items into an item table. 

 

The issue is that its possible for 2 orders to end up with the same unique number, this is because the random function is not actually that random.  If two people are submitting orders at the same time, they get the same random number.  This means that all of the items ordered go to one customer, and the other customer gets no items. 

 

Current code for the random number is

 

<cfset session.order.uniquenumber = RandRange(1,99999999) + now()>

 

Is there a more reliable way of doing this? 

 

Brian

 

[Charlie Arehart]

Two issues there, really, it seems, Brian.

1) When you need more truly random numbers, you should call the Randomize function first, and in it you can provide both a seed and an alternative randomization algorithm. See the CF docs, such as (for cf9, which sadly comes up first in google search results):

http://help.adobe.com/en_US/ColdFusion/9.0/CFMLRef/WSc3ff6d0ea77859461172e0811cbec22c24-6d3e.html

You could also consider using the createuuid function, which creates a different form of random number (with numbers and letters), which may have value.

2) But you refer to getting the identity as a second step and using that. Are you saying THAT ends up being the same? If so, that would not be due to randomization issues, but rather a race condition.

You don’t say how you’re selecting the identity from the insert, but there are multiple ways, some better than others at reducing the likelihood of getting one from another insert. I’ll hold off on details until you clarify what you’re doing and whether this would be helpful. (Or perhaps someone else will chime in anyway.)

/charlie

--
You received this message because you are subscribed to the Google Groups "cfaussie" group.
To unsubscribe from this group and stop receiving emails from it, send an email to cfaussie+u...@googlegroups.com.
To post to this group, send email to cfau...@googlegroups.com.
Visit this group at https://groups.google.com/group/cfaussie.
For more options, visit https://groups.google.com/d/optout.

[Mike K]

Can you combine that random number with something sequential like maybe an index field?  that way you'll give the customer a random order number but still be able to keep uniqueness.      You could generate the random number,  then append the sequential index to it making a larger integer. 

Or you could use the UUID 

Cheers
Mike Kear
Windsor, NSW, Australia
Adobe Certified Advanced ColdFusion Developer
AFP Webworks
http://afpwebworks.com

-- 

[Mark King]

Hi Brian.

 

Well first of all if you are using SQL Server try this to get the new ID in the same transaction rather than doing another query;

 

<cfquery datasource="[datasource]" username="[username]" password="[password]" name="qry">

    SET NOCOUNT ON

    INSERT INTO orders

                 (

                 ** Your order fields **

                 )

    VALUES

                (

** Your data **

                 )

    SELECT @@Identity AS orderID

    SET NOCOUNT OFF

</cfquery>

<cfset attributes.orderID=qry.orderID>

 

I am not sure what you use the unique number for other than for something to put onto the receipts or something?

 

However, if you want to obscure the ID in a link you could encrypt it. I have done this is the past where I sent users and email but didn’t want them to be able to just increment the link to see someone else’s order, e.g. displayorder.cfm?orderID=1234

 

So I did this:

 

<cfset myKey="[** your generated AES key **]">

<cfset AESorderID=urlencodedformat(encrypt(attributes.orderID,myKey,"AES"))>

 

Because the orderID is unique the encrypted value should be unique as well.

 

See http://help.adobe.com/en_US/ColdFusion/9.0/CFMLRef/WSc3ff6d0ea77859461172e0811cbec22c24-7c2f.html

 

I am not sure if that is what you actually wanted but I hope it helps!

 

 

From: cfau...@googlegroups.com [mailto:cfau...@googlegroups.com] On Behalf Of Brian Knott
Sent: Wednesday, 27 January 2016 9:31 AM
To: cfau...@googlegroups.com
Subject: [cfaussie] SQL identity issue

 

Hi everyone.

--

[M@ Bourke]

You could also generate a hash of their name and address as the seed, assuming there isn't 2 customers with the same name and address (and you already have this data at this point)

[Paul Kukiel]

Same as Mikes but using the inbuilt functions and avoiding @@identity

<cfquery datasource="#DSN#" result="myResult">

INSERT INTO MyTable (col1)

VALUES ('col1')

</cfquery>

 

<!--- Tag based output --->

<cfoutput>Inserted ID is: #myResult["GENERATEDKEY"]#</cfoutput>







--

Paul Kukiel

[Brian Knott]

Returning the generated key is what I'm after. 

 

This code was originally written about 15 years ago using CF 4.5 and SQL Server 2000. Back then the ID could not be returned.  Time to update the inserts.

 

Does anyone know how reliable  the GENERATEKEY function is.

 

Brian

 

 

 

---

From: "Paul Kukiel" <kuk...@gmail.com>
Sent: Wednesday, 27 January 2016 10:29 AM
To: cfau...@googlegroups.com
Subject: Re: [cfaussie] SQL identity issue

[Paul Kukiel]

100% reliable. 

[Brian Knott]

Thanks.  Looks like the easiest way to do this.

 

Brian

 

 

 

---

From: "Paul Kukiel" <kuk...@gmail.com>
Sent: Wednesday, 27 January 2016 10:55 AM

[Mike K]

Whats the advantage of returning result.getPrefix().generatedkey  rather than using the @@identity function of the database?    

Is it just to make it database agnostic?  or is there another reason for it?

[Paul Kukiel]

Less code, and database agnostic.  Behind the scenes it probably does the same thing.

--
You received this message because you are subscribed to the Google Groups "cfaussie" group.
To unsubscribe from this group and stop receiving emails from it, send an email to cfaussie+u...@googlegroups.com.
To post to this group, send email to cfau...@googlegroups.com.
Visit this group at https://groups.google.com/group/cfaussie.
For more options, visit https://groups.google.com/d/optout.

--

Paul Kukiel

[Christophe Albrech]

By the way, if you do everything in sql, you should use "SELECT scope_identity()" instead of "SELECT @@identity". The reason being that if your insert calls a trigger on that table that in turns inserts a row in another table, scope_identity() would return the id of the new row you explicitly inserted, while @@identity would return the id of the row inserted by the trigger.