Unique id across multiple records

[Enrico Maria Giordano]

Dear friends, I need to generate one unique id for a group of newly inserted records (ie. to mark a group of record with a unique id). But I need it for SQL where I can't use explicit locking to assure id uniqueness.

Any ideas?

EMG

[Daniel Garcia-Gil]

Hello

create a new table with group insert ids

Code: Select all  Expand view

CREATE TABLE `groupid` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

start transaction

Code: Select all  Expand view

BEGIN

insert in groupid

Code: Select all  Expand view

INSERT INTO groupid (id) VALUES(NULL)

retrieve last id

Code: Select all  Expand view

@LASTID=LAST_INSERT_ID()

use the id to group insert

Code: Select all  Expand view

INSERT INTO myTable (groupid, field1, field2) VALUES (@LASTID, 'VALUE1', 'VALUE2'),  (@LASTID, 'VALUE3', 'VALUE4'),  (@LASTID, 'VALUE5', 'VALUE6')

close transaction

Code: Select all  Expand view

COMMIT

[Enrico Maria Giordano]

Daniel,

Hello

create a new table with group insert ids

Code: Select all  Expand view

CREATE TABLE `groupid` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

start transaction

Code: Select all  Expand view

BEGIN

insert in groupid

Code: Select all  Expand view

INSERT INTO groupid (id) VALUES(NULL)

retrieve last id

Code: Select all  Expand view

@LASTID=LAST_INSERT_ID()

use the id to group insert

Code: Select all  Expand view

INSERT INTO myTable (groupid, field1, field2) VALUES (@LASTID, 'VALUE1', 'VALUE2'),  (@LASTID, 'VALUE3', 'VALUE4'),  (@LASTID, 'VALUE5', 'VALUE6')

close transaction

Code: Select all  Expand view

COMMIT

Thank you. What do you exactly mean with BEGIN and COMMIT? They aren't SQL statements, as far as I know.

EMG

[Daniel Garcia-Gil]

in Mysql you can start a transaction with "BEGIN", close transaction with "COMMIT" (save the changes) or ROLLBACK to not save changes

http://dev.mysql.com/doc/refman/5.0/en/commit.html

[Enrico Maria Giordano]

Daniel,

in Mysql you can start a transaction with "BEGIN", close transaction with "COMMIT" (save the changes) or ROLLBACK to not save changes

http://dev.mysql.com/doc/refman/5.0/en/commit.html

Sorry, I need an engine-aware solution.

EMG

[Daniel Garcia-Gil]

Enrico

is only idea, a general way to do

if you use ADO

look how use transaction with ADO http://msdn.microsoft.com/en-us/library/aa905921(v=sql.80).aspx
if you connect ADO + MySql internally ADO call BEGIN, COMMIT, ROLLBACK of MySql to generate a transaction

try to find a equivalent to my sample above

[Enrico Maria Giordano]

Daniel,

What do you think about this?

Code: Select all  Expand view

<%
  Function GetGuid()
      Set TypeLib = CreateObject( "Scriptlet.TypeLib" )
      GetGuid = Replace( Mid( TypeLib.Guid, 2, 36 ), "-", "" )
      Set TypeLib = Nothing
  End Function

  Response.Write GetGuid()
%>

Is it going to be a reasonably valid unique id?

EMG

[Daniel Garcia-Gil]

Enrico

yes, i guess

[Enrico Maria Giordano]

Daniel,

Enrico

yes, i guess

Thank you.

EMG

[Rick Lipkin]

Enrico

Dear friends, I need to generate one unique id for a group of newly inserted records (ie. to mark a group of record with a unique id). But I need it for SQL where I can't use explicit locking to assure id uniqueness.

Any ideas?

EMG

Do not know if you have solved this yet .. I was thinking about the possibility of creating your group rows ( as you normally would ) and as you create them AAdd the primary row id's to an array. Create your unique value cGroupId := _GenEid() and then walk through a for\next loop of the array and find each row and Update() the rows with cGroupID.

Hope this is what you had in mind ?

Rick Lipkin

Code: Select all  Expand view

//-------------------
Static Func _GenEid()

LOCAL nRAND, cRAND
LOCAL oRs, cSQL, oERR

oRs:= TOleAuto():New( "ADODB.Recordset" )
oRs:CursorType     := 1        // opendkeyset
oRs:CursorLocation := 3        // local cache
oRs:LockType       := 3        // lockoportunistic

cSQL := "SELECT Distinct [GroupId] from [YourTable]"  // groupid is char(18)

TRY
   oRs:Open( cSQL,xCONNECT )
CATCH oErr
   MsgInfo( "Error in Opening YourTable to Create Unique EID" )
   RETURN("BOGUS")
END TRY

cRAND := 'BOGUS'

DO WHILE .T.

   nRAND := nRANDOM(10000000000000000)

   // 1 is reserved and 0 is a null key //

   IF nRAND = 1 .or. nRAND = 0 .or. nRAND = NIL
      LOOP
   ENDIF

   cRAND := STRzero(nRAND,18)

   IF oRs:eof
   ELSE
      oRs:MoveFirst()
      oRs:Find("GroupId = '"+cRAND+"'" )
   ENDIF

   IF oRs:eof
      EXIT
   ELSE
      LOOP
   ENDIF

   EXIT

ENDDO

oRs:Close()

RETURN( cRAND )
 

[Enrico Maria Giordano]

Rick,

I'm testing the guid (see above) right now...

EMG

[Rick Lipkin]

Enrico

Interested to see how the guid turns out .. How are you going to apply that unique ID to your group of records ?


Rick

[Enrico Maria Giordano]

Rick,

Enrico

Interested to see how the guid turns out ..

I'll let you know.

How are you going to apply that unique ID to your group of records ?

cGuid = GetGuid()

INSERT ...
INSERT ...
INSERT ...



EMG