CreateUUID

[coldfused]

Guys I have an ACcess database without about 3400 records that I am trying to create "new" unique id's for.

My code is duplicating the same UUID about 30 or so times instead of making them all unique.

Im using this script to access the db and update the records:

<CFQUERY Name="Customers" datasource="#request.dsn#" username="#request.dsnUsername#" password="#request.dsnPassword#">
SELECT cst_ID, cst_Zip, cst_FirstName, cst_LastName FROM tbl_all
</cfquery>

<cfoutput>#Customers.recordCount# records returned.

</cfoutput>
<cfset counterNum = 0>
<!--- This code adds the UUID to each imported record.  Run this first. --->
<cfloop query="Customers" startrow=1 endrow=#Customers.RecordCount#> 
<!--- This is here so that if you have issues with SQL timeouts, you can limit which data it using, breaking the DB into managable chunks. --->
<cfset counterNum = counterNum + 1>
<!--- Create UUID --->
<cfset IDstring = CreateUUID()>
<cfset ThisCustomerID = Left(IDstring,9)&LSDateFormat(Now(),'MM-DD-YY')>
<!--- Provide some feedback while running the updater --->
<cfoutput>#cst_ID# (#cst_LastName#, #cst_FirstName#) will be changed to> #ThisCustomerID#
</cfoutput>
<cfflush> 
<!--- Flush the output so you see an update every time the loop runs --->
<CFQUERY Name="CustomersUpdate" datasource="#request.dsn#" username="#request.dsnUsername#" password="#request.dsnPassword#">
UPDATE tbl_all SET cst_ID = '#ThisCustomerID#' WHERE cst_ID = 
'#cst_ID#'
</cfquery>
<!--- Show a status message telling that the record update is complete.  I added the counter here, so that if you did run into a SQL timeout, 
you could see which record it aborted on. --->
<cfoutput>Update Completed (#counterNum#)
</cfoutput>
</cfloop>

Any ideas why it is not creating UUID's for all 3400 records?

UUID's are created at records 24,74,244,268,etc...More sporadic as it goes along. But always sets of atleast 10 records with the same UUID.

----------------------------------------

http://www.orlandomediasolutions.com

http://www.fcss.org

http://www.woodsgroup.com

 

[Sarky78]

The reason you are getting duplicate ID is probably because of this bit of code:

<cfset IDstring = CreateUUID()>
<cfset ThisCustomerID = Left(IDstring,9)&LSDateFormat(Now(),'MM-DD-YY')>

The CreateUUID function will return a unique string of numbers and characters, but you are then taking the first 9 characters and adding the days date to it. Why not just use the UUID generated as this is guarenteed to be a unique number. So your code will look like:

<CFQUERY Name="CustomersUpdate" datasource="#request.dsn#" username="#request.dsnUsername#" password="#request.dsnPassword#">
UPDATE tbl_all
SET cst_ID = '#CreateUUID()#'
WHERE cst_ID = '#cst_ID#'
</cfquery>

Hope this helps!

Tony

 

[coldfused]

Thanks Tony I'll let ya know how it works out.

----------------------------------------

http://www.orlandomediasolutions.com

http://www.fcss.org

http://www.woodsgroup.com