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
Board index Off Topic / Otros temas Unique id across multiple records
Hello
create a new table with group insert ids
start transaction
insert in groupid
retrieve last id
use the id to group insert
close transaction
create a new table with group insert ids
CREATE TABLE `groupid` (
`id` int(11) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`id`)
) ENGINE=InnoDB;start transaction
BEGINinsert in groupid
INSERT INTO groupid (id) VALUES(NULL)retrieve last id
@LASTID=LAST_INSERT_ID()use the id to group insert
INSERT INTO myTable (groupid, field1, field2) VALUES (@LASTID, 'VALUE1', 'VALUE2'), (@LASTID, 'VALUE3', 'VALUE4'), (@LASTID, 'VALUE5', 'VALUE6')close transaction
COMMITour best documentation is the source code
Isla de Margarita Venezuela.
danielgarciagil@gmail.com
http://tdolphin.blogspot.com/
https://www.dropbox.com/referrals/NTI5Njk4MDc1OQ?src=global9
Isla de Margarita Venezuela.
danielgarciagil@gmail.com
http://tdolphin.blogspot.com/
https://www.dropbox.com/referrals/NTI5Njk4MDc1OQ?src=global9
Daniel,
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 wrote:Hello
create a new table with group insert ids
CREATE TABLE `groupid` ( `id` int(11) NOT NULL AUTO_INCREMENT, PRIMARY KEY (`id`) ) ENGINE=InnoDB;
start transaction
BEGIN
insert in groupid
INSERT INTO groupid (id) VALUES(NULL)
retrieve last id
@LASTID=LAST_INSERT_ID()
use the id to group insert
INSERT INTO myTable (groupid, field1, field2) VALUES (@LASTID, 'VALUE1', 'VALUE2'), (@LASTID, 'VALUE3', 'VALUE4'), (@LASTID, 'VALUE5', 'VALUE6')
close transaction
COMMIT
Thank you. What do you exactly mean with BEGIN and COMMIT? They aren't SQL statements, as far as I know.
EMG
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
http://dev.mysql.com/doc/refman/5.0/en/commit.html
our best documentation is the source code
Isla de Margarita Venezuela.
danielgarciagil@gmail.com
http://tdolphin.blogspot.com/
https://www.dropbox.com/referrals/NTI5Njk4MDc1OQ?src=global9
Isla de Margarita Venezuela.
danielgarciagil@gmail.com
http://tdolphin.blogspot.com/
https://www.dropbox.com/referrals/NTI5Njk4MDc1OQ?src=global9
Daniel,
Sorry, I need an engine-aware solution.
EMG
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
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
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
our best documentation is the source code
Isla de Margarita Venezuela.
danielgarciagil@gmail.com
http://tdolphin.blogspot.com/
https://www.dropbox.com/referrals/NTI5Njk4MDc1OQ?src=global9
Isla de Margarita Venezuela.
danielgarciagil@gmail.com
http://tdolphin.blogspot.com/
https://www.dropbox.com/referrals/NTI5Njk4MDc1OQ?src=global9
Daniel,
What do you think about this?
Is it going to be a reasonably valid unique id?
EMG
What do you think about this?
<%
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
Enrico
yes, i guess
our best documentation is the source code
Isla de Margarita Venezuela.
danielgarciagil@gmail.com
http://tdolphin.blogspot.com/
https://www.dropbox.com/referrals/NTI5Njk4MDc1OQ?src=global9
Isla de Margarita Venezuela.
danielgarciagil@gmail.com
http://tdolphin.blogspot.com/
https://www.dropbox.com/referrals/NTI5Njk4MDc1OQ?src=global9
Daniel,
Thank you.
EMG
Daniel Garcia-Gil wrote:Enrico
yes, i guess
Thank you.
EMG
Enrico
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
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
//-------------------
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 )Rick,
I'm testing the guid (see above) right now...
EMG
Enrico
Interested to see how the guid turns out .. How are you going to apply that unique ID to your group of records ?
Rick
Rick,
I'll let you know.
cGuid = GetGuid()
INSERT ...
INSERT ...
INSERT ...
EMG
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