Page 1 of 1
Retrieving the AutoInc Id from Insert (Borland Builder 6)
Posted: Tue 15 Aug 2006 13:34
by Tigger
Hi,
I would like to request some assistance with learning the id which was auto-inc generated by the table when new descriptions are inserted.
For Example, the table:
Code: Select all
CREATE TABLE `myDatabase`.`myTable` (
`id` INTEGER UNSIGNED AUTO_INCREMENT,
`description` VARCHAR(45) NOT NULL DEFAULT '',
PRIMARY KEY(`id`)
)
ENGINE = InnoDB;
The creation of a new record:
Code: Select all
if(!MyConnection1->InTransaction)
{
MyQuery1->SQL->Text = "insert into myDatabase.mytable (description) values ('red');";
MyQuery1->Execute();
}
Once this is performed how can I find out what number was given to 'red'?
I can't just look up the last record as another user may have added a new colour simultaneously.
Many thanks in advance.
Posted: Wed 16 Aug 2006 01:01
by teunis
Maybe this can help you. It is the way I do it in Delphi.
Querry.SQL.Clear;
Querry.SQL.Add(' SELECT last_insert_id()');
Querry.Execute;
integer_value1:= Querry.Fields[0].asInteger;
It should be executed just after an insert command containing an autoincrement field.
Teunis
Posted: Wed 16 Aug 2006 03:43
by Tigger
Many thanks for you reply Teunis.
You got me looking in the right place.
The solution is as follows for others who read this asking the same question.
After the Execute statemant add:
Simple when you know how!
Posted: Tue 22 Aug 2006 21:35
by ben
it doesnt work if you have more than one Insert in the query...
Posted: Wed 23 Aug 2006 00:15
by teunis
??? What do you mean ???
As far as I know:
Only 1 insert can be executed in a query
For executing several SQL's as one TMyScript can be used.
Even then if you ask for the last_insert_id You will get the last autoincremented
value
Posted: Wed 23 Aug 2006 01:08
by Tigger
ben wrote:it doesnt work if you have more than one Insert in the query...
I have just tested this and it does indeed still work fine. However it returns the LAST id that was inserted.
My best suggestion therefore is to record the number of simultaneous inserts and calculate the id with something like:
Code: Select all
int nFirstNewId = MyQuery1->InsertId - (nNumberOfInserts - 1);
Posted: Wed 23 Aug 2006 12:36
by Antaeus
Possible, the following will be useful:
Code: Select all
INSERT INTO dept (DName, Loc) VALUES ('test', 'test');
SET @a = LAST_INSERT_ID();
INSERT INTO dept (DName, Loc) VALUES ('test', 'test');
INSERT INTO dept (DName, Loc) VALUES ('test', 'test');
INSERT INTO dept (DName, Loc) VALUES ('test', 'test');
SELECT @a AS first_LAST_INSERT_ID, LAST_INSERT_ID() AS last_LAST_INSERT_ID;
So, you can get LAST_INSERT_ID after any INSERT statement.