Unique id across multiple records

Unique id across multiple records

Postby Enrico Maria Giordano » Thu Jun 13, 2013 6:04 pm

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
User avatar
Enrico Maria Giordano
 
Posts: 8243
Joined: Thu Oct 06, 2005 8:17 pm
Location: Roma - Italia

Re: Unique id across multiple records

Postby Daniel Garcia-Gil » Thu Jun 13, 2013 7:16 pm

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
User avatar
Daniel Garcia-Gil
 
Posts: 2365
Joined: Wed Nov 02, 2005 11:46 pm
Location: Isla de Margarita

Re: Unique id across multiple records

Postby Enrico Maria Giordano » Thu Jun 13, 2013 8:15 pm

Daniel,

Daniel Garcia-Gil wrote: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
User avatar
Enrico Maria Giordano
 
Posts: 8243
Joined: Thu Oct 06, 2005 8:17 pm
Location: Roma - Italia

Re: Unique id across multiple records

Postby Daniel Garcia-Gil » Thu Jun 13, 2013 8:24 pm

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
User avatar
Daniel Garcia-Gil
 
Posts: 2365
Joined: Wed Nov 02, 2005 11:46 pm
Location: Isla de Margarita

Re: Unique id across multiple records

Postby Enrico Maria Giordano » Thu Jun 13, 2013 9:28 pm

Daniel,

Daniel Garcia-Gil wrote: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
User avatar
Enrico Maria Giordano
 
Posts: 8243
Joined: Thu Oct 06, 2005 8:17 pm
Location: Roma - Italia

Re: Unique id across multiple records

Postby Daniel Garcia-Gil » Thu Jun 13, 2013 9:51 pm

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
User avatar
Daniel Garcia-Gil
 
Posts: 2365
Joined: Wed Nov 02, 2005 11:46 pm
Location: Isla de Margarita

Re: Unique id across multiple records

Postby Enrico Maria Giordano » Thu Jun 13, 2013 10:04 pm

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
User avatar
Enrico Maria Giordano
 
Posts: 8243
Joined: Thu Oct 06, 2005 8:17 pm
Location: Roma - Italia

Re: Unique id across multiple records

Postby Daniel Garcia-Gil » Thu Jun 13, 2013 10:24 pm

Enrico

yes, i guess
User avatar
Daniel Garcia-Gil
 
Posts: 2365
Joined: Wed Nov 02, 2005 11:46 pm
Location: Isla de Margarita

Re: Unique id across multiple records

Postby Enrico Maria Giordano » Thu Jun 13, 2013 11:15 pm

Daniel,

Daniel Garcia-Gil wrote:Enrico

yes, i guess


Thank you.

EMG
User avatar
Enrico Maria Giordano
 
Posts: 8243
Joined: Thu Oct 06, 2005 8:17 pm
Location: Roma - Italia

Re: Unique id across multiple records

Postby Rick Lipkin » Sat Jun 15, 2013 2:25 pm

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 )
 
User avatar
Rick Lipkin
 
Posts: 2606
Joined: Fri Oct 07, 2005 1:50 pm
Location: Columbia, South Carolina USA

Re: Unique id across multiple records

Postby Enrico Maria Giordano » Sat Jun 15, 2013 5:33 pm

Rick,

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

EMG
User avatar
Enrico Maria Giordano
 
Posts: 8243
Joined: Thu Oct 06, 2005 8:17 pm
Location: Roma - Italia

Re: Unique id across multiple records

Postby Rick Lipkin » Sat Jun 15, 2013 7:03 pm

Enrico

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


Rick
User avatar
Rick Lipkin
 
Posts: 2606
Joined: Fri Oct 07, 2005 1:50 pm
Location: Columbia, South Carolina USA

Re: Unique id across multiple records

Postby Enrico Maria Giordano » Sat Jun 15, 2013 7:47 pm

Rick,

Rick Lipkin wrote:Enrico

Interested to see how the guid turns out ..


I'll let you know.

Rick Lipkin wrote:How are you going to apply that unique ID to your group of records ?


cGuid = GetGuid()

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

:-)

EMG
User avatar
Enrico Maria Giordano
 
Posts: 8243
Joined: Thu Oct 06, 2005 8:17 pm
Location: Roma - Italia


Return to Off Topic / Otros temas

Who is online

Users browsing this forum: No registered users and 4 guests