Retrieving the AutoInc Id from Insert (Borland Builder 6)

[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.

[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

[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:

Code: Select all

int nNewId = MyQuery1->InsertId;

Simple when you know how!

[ben]

it doesnt work if you have more than one Insert in the query...

[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

[Tigger]

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);

[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.