Re: [cfaussie] SQL identity issue

[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.

On Wed, Jan 27, 2016 at 12:09 PM, Paul Kukiel  wrote:

> Less code, and database agnostic.  Behind the scenes it probably does the
> same thing.
>
> On Wed, Jan 27, 2016 at 12:01 PM, Mike K  wrote:
>
>> 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?
>>
>>
>> Cheers
>> Mike Kear
>> Windsor, NSW, Australia
>> Adobe Certified Advanced ColdFusion Developer
>> AFP Webworks
>> http://afpwebworks.com
>>
>> --
>> 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+unsubscr...@googlegroups.com.
>> To post to this group, send email to cfaussie@googlegroups.com.
>> Visit this group at https://groups.google.com/group/cfaussie.
>> For more options, visit https://groups.google.com/d/optout.
>>
>
>
>
> --
> Paul Kukiel
>
> --
> 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+unsubscr...@googlegroups.com.
> To post to this group, send email to cfaussie@googlegroups.com.
> Visit this group at https://groups.google.com/group/cfaussie.
> For more options, visit https://groups.google.com/d/optout.
>

-- 
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+unsubscr...@googlegroups.com.
To post to this group, send email to cfaussie@googlegroups.com.
Visit this group at https://groups.google.com/group/cfaussie.
For more options, visit https://groups.google.com/d/optout.

RE: [cfaussie] SQL identity issue

[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

From: cfaussie@googlegroups.com [mailto:cfaussie@googlegroups.com] On Behalf Of 
Brian Knott
Sent: Tuesday, January 26, 2016 5:31 PM
To: cfaussie@googlegroups.com
Subject: [cfaussie] SQL identity issue

 

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

 



 

Is there a more reliable way of doing this? 

 

Brian

 

-- 
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+unsubscr...@googlegroups.com.
To post to this group, send email to cfaussie@googlegroups.com.
Visit this group at https://groups.google.com/group/cfaussie.
For more options, visit https://groups.google.com/d/optout.

-- 
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+unsubscr...@googlegroups.com.
To post to this group, send email to cfaussie@googlegroups.com.
Visit this group at https://groups.google.com/group/cfaussie.
For more options, visit https://groups.google.com/d/optout.

Re: [cfaussie] SQL identity issue

[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

On Wed, Jan 27, 2016 at 10:31 AM, Brian Knott  wrote:

> 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
>
> 
>
> Is there a more reliable way of doing this?
>
> Brian
>
>
> --
>

-- 
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+unsubscr...@googlegroups.com.
To post to this group, send email to cfaussie@googlegroups.com.
Visit this group at https://groups.google.com/group/cfaussie.
For more options, visit https://groups.google.com/d/optout.

RE: [cfaussie] SQL identity issue

[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;

 



SET NOCOUNT ON

INSERT INTO orders

 (

 ** Your order fields **

 )

VALUES

(

** Your data **

 )

SELECT @@Identity AS orderID

SET NOCOUNT OFF





 

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:

 





 

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: cfaussie@googlegroups.com [mailto:cfaussie@googlegroups.com] On Behalf Of 
Brian Knott
Sent: Wednesday, 27 January 2016 9:31 AM
To: cfaussie@googlegroups.com
Subject: [cfaussie] SQL identity issue

 

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

 



 

Is there a more reliable way of doing this? 

 

Brian

 

-- 
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+unsubscr...@googlegroups.com 
 .
To post to this group, send email to cfaussie@googlegroups.com 
 .
Visit this group at https://groups.google.com/group/cfaussie.
For more options, visit https://groups.google.com/d/optout.

-- 
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+unsubscr...@googlegroups.com.
To post to this group, send email to cfaussie@googlegroups.com.
Visit this group at https://groups.google.com/group/cfaussie.
For more options, visit https://groups.google.com/d/optout.

Re: [cfaussie] SQL identity issue

[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)
On 27 Jan 2016 7:05 a.m., "Mike K"  wrote:

> 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
>
> On Wed, Jan 27, 2016 at 10:31 AM, Brian Knott  wrote:
>
>> 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
>>
>> 
>>
>> Is there a more reliable way of doing this?
>>
>> Brian
>>
>>
>> --
>>
>
> --
> 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+unsubscr...@googlegroups.com.
> To post to this group, send email to cfaussie@googlegroups.com.
> Visit this group at https://groups.google.com/group/cfaussie.
> For more options, visit https://groups.google.com/d/optout.
>

-- 
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+unsubscr...@googlegroups.com.
To post to this group, send email to cfaussie@googlegroups.com.
Visit this group at https://groups.google.com/group/cfaussie.
For more options, visit https://groups.google.com/d/optout.

Re: [cfaussie] SQL identity issue

[Paul Kukiel]

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


INSERT INTO MyTable (col1)
VALUES ('col1')



Inserted ID is: #myResult["GENERATEDKEY"]#



On Wed, Jan 27, 2016 at 11:06 AM, Mark King  wrote:

> 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;
>
>
>
>  password="[password]" name="qry">
>
> SET NOCOUNT ON
>
> INSERT INTO orders
>
>  (
>
>  ** Your order fields **
>
>  )
>
> VALUES
>
> (
>
> ** Your data **
>
>  )
>
> SELECT @@Identity AS orderID
>
> SET NOCOUNT OFF
>
> 
>
> 
>
>
>
> 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:
>
>
>
> 
>
>  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:* cfaussie@googlegroups.com [mailto:cfaussie@googlegroups.com] *On
> Behalf Of *Brian Knott
> *Sent:* Wednesday, 27 January 2016 9:31 AM
> *To:* cfaussie@googlegroups.com
> *Subject:* [cfaussie] SQL identity issue
>
>
>
> 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
>
>
>
> 
>
>
>
> Is there a more reliable way of doing this?
>
>
>
> Brian
>
>
>
> --
> 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+unsubscr...@googlegroups.com.
> To post to this group, send email to cfaussie@googlegroups.com.
> Visit this group at https://groups.google.com/group/cfaussie.
> For more options, visit https://groups.google.com/d/optout.
>
> --
> 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+unsubscr...@googlegroups.com.
> To post to this group, send email to cfaussie@googlegroups.com.
> Visit this group at https://groups.google.com/group/cfaussie.
> For more options, visit https://groups.google.com/d/optout.
>



-- 
Paul Kukiel

-- 
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+unsubscr...@googlegroups.com.
To post to this group, send email to cfaussie@googlegroups.com.
Visit this group at https://groups.google.com/group/cfaussie.
For more options, visit https://groups.google.com/d/optout.

Re: [cfaussie] SQL identity issue

[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" <kuki...@gmail.com>
Sent: Wednesday, 27 January 2016 10:29 AM
To: cfaussie@googlegroups.com
Subject: Re: [cfaussie] SQL identity issue   
 Same as Mikes but using the inbuilt functions and avoiding @@identity  
  
 INSERT INTO MyTable (col1)
 VALUES ('col1')
 
  
 
 Inserted ID is: #myResult["GENERATEDKEY"]#
  
  
   On Wed, Jan 27, 2016 at 11:06 AM, Mark King <m...@ampersand.net.au> wrote:   
  

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;  

   

  

SET NOCOUNT ON  

INSERT INTO orders  

 (  

 ** Your order fields **  

 )  

VALUES  

(  

** Your data **  

 )  

SELECT @@Identity AS orderID  

SET NOCOUNT OFF  

  

  

   

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:  

   

  

  

   

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: cfaussie@googlegroups.com [mailto:cfaussie@googlegroups.com] On Behalf Of 
Brian Knott
Sent: Wednesday, 27 January 2016 9:31 AM
To: cfaussie@googlegroups.com
Subject: [cfaussie] SQL identity issue  



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 

  

 

  

Is there a more reliable way of doing this?  

  

Brian 

  

--
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+unsubscr...@googlegroups.com.
To post to this group, send email to cfaussie@googlegroups.com.
Visit this group at https://groups.google.com/group/cfaussie.
For more options, visit https://groups.google.com/d/optout. 

  --
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+unsubscr...@googlegroups.com.
To post to this group, send email to cfaussie@googlegroups.com.
Visit this group at https://groups.google.com/group/cfaussie.
For more options, visit https://groups.google.com/d/optout.


--  Paul Kukiel

  --
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+unsubscr...@googlegroups.com.
To post to this group, send email to cfaussie@googlegroups.com.
Visit this group at https://groups.google.com/group/cfaussie.
For more options, visit https://groups.google.com/d/optout.
 

-- 
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+unsubscr...@googlegroups.com.
To post to this group, send email to cfaussie@googlegroups.com.
Visit this group at https://groups.google.com/group/cfaussie.
For more options, visit https://groups.google.com/d/optout.

Re: [cfaussie] SQL identity issue

[Paul Kukiel]

100% reliable.

On Wed, Jan 27, 2016 at 11:52 AM, Brian Knott <bkn...@jbk.com.au> wrote:

> 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" <kuki...@gmail.com>
> *Sent*: Wednesday, 27 January 2016 10:29 AM
> *To*: cfaussie@googlegroups.com
> *Subject*: Re: [cfaussie] SQL identity issue
>
> Same as Mikes but using the inbuilt functions and avoiding @@identity
>
> 
> INSERT INTO MyTable (col1)
> VALUES ('col1')
> 
>
> 
> Inserted ID is: #myResult["GENERATEDKEY"]#
>
>
>
> On Wed, Jan 27, 2016 at 11:06 AM, Mark King <m...@ampersand.net.au> wrote:
>>
>> 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;
>>
>>
>>
>> > password="[password]" name="qry">
>>
>> SET NOCOUNT ON
>>
>> INSERT INTO orders
>>
>>  (
>>
>>  ** Your order fields **
>>
>>  )
>>
>> VALUES
>>
>> (
>>
>> ** Your data **
>>
>>  )
>>
>> SELECT @@Identity AS orderID
>>
>> SET NOCOUNT OFF
>>
>> 
>>
>> 
>>
>>
>>
>> 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:
>>
>>
>>
>> 
>>
>> > 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:* cfaussie@googlegroups.com [mailto:cfaussie@googlegroups.com] *On
>> Behalf Of *Brian Knott
>> *Sent:* Wednesday, 27 January 2016 9:31 AM
>> *To:* cfaussie@googlegroups.com
>> *Subject:* [cfaussie] SQL identity issue
>>
>>
>>
>> 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
>>
>>
>>
>> 
>>
>>
>>
>> Is there a more reliable way of doing this?
>>
>>
>>
>> Brian
>>
>>
>>
>> --
>> 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+unsubscr...@googlegroups.com.
>> To post to this group, send email to cfaussie@googlegroups.com.
>> Visit this group at https://groups.google.com/group/cfaussie.
>> For more options, visit https://groups.google.com/d/optout.
>>
>>
>> --
>> 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+unsubscr...@googlegroups.com.
>>

Re: [cfaussie] SQL identity issue

[Brian Knott]

Thanks.  Looks like the easiest way to do this.
  
 Brian
  
  
  


 From: "Paul Kukiel" <kuki...@gmail.com>
Sent: Wednesday, 27 January 2016 10:55 AM
To: cfaussie@googlegroups.com
Subject: Re: [cfaussie] SQL identity issue   
 100% reliable. 
   On Wed, Jan 27, 2016 at 11:52 AM, Brian Knott <bkn...@jbk.com.au> wrote:   
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" <kuki...@gmail.com>
Sent: Wednesday, 27 January 2016 10:29 AM
To: cfaussie@googlegroups.com
Subject: Re: [cfaussie] SQL identity issue
   Same as Mikes but using the inbuilt functions and avoiding @@identity

 INSERT INTO MyTable (col1)
 VALUES ('col1')
 
  
 
 Inserted ID is: #myResult["GENERATEDKEY"]#
  
  
 On Wed, Jan 27, 2016 at 11:06 AM, Mark King <m...@ampersand.net.au> wrote: 
 

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;  

   

  

SET NOCOUNT ON  

INSERT INTO orders  

 (  

 ** Your order fields **  

 )  

VALUES  

(  

** Your data **  

 )  

SELECT @@Identity AS orderID  

SET NOCOUNT OFF  

  

  

   

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:  

   

  

  

   

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: cfaussie@googlegroups.com [mailto:cfaussie@googlegroups.com] On Behalf Of 
Brian Knott
Sent: Wednesday, 27 January 2016 9:31 AM
To: cfaussie@googlegroups.com
Subject: [cfaussie] SQL identity issue  



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 

  

 

  

Is there a more reliable way of doing this?  

  

Brian 

  

--
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+unsubscr...@googlegroups.com.
To post to this group, send email to cfaussie@googlegroups.com.
Visit this group at https://groups.google.com/group/cfaussie.
For more options, visit https://groups.google.com/d/optout. 

  --
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+unsubscr...@googlegroups.com.
To post to this group, send email to cfaussie@googlegroups.com.
Visit this group at https://groups.google.com/group/cfaussie.
For more options, visit https://groups.google.com/d/optout.

  
 --   Paul Kukiel

   --
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+unsubscr...@googlegroups.com.
To post to this group, send email to cfaussie@googlegroups.com.
Visit this group at https://groups.google.com/group/cfaussie.
For more options, visit https://groups.google.com/d/optout.
  

  --
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+unsubscr...@googlegroups.com.
To post to this group, send email to cfaussie@googlegroups.com.
Visit this group at https://groups.google.com/group/cfaussie

Re: [cfaussie] SQL identity issue

[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?


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

-- 
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+unsubscr...@googlegroups.com.
To post to this group, send email to cfaussie@googlegroups.com.
Visit this group at https://groups.google.com/group/cfaussie.
For more options, visit https://groups.google.com/d/optout.