SQLInsert + LAST_INSERT_ID() + Delphi 2007

[viking.new]

Hi!

I have a problem with getting ID value from autoincrement field during insertind data by TmyQuery.SQLInsert.

TmyQuery.SQL.Text = 'select ID, NAME, NOTE from table1'
TmyQuery.SQLInsert.Text = 'CALL P_TABLE1_INSERT(:NAME, :NOTE, @ID); SELECT CAST(@ID AS SIGNED) AS '@ID''

I use Delphi 2007 + MySQL v5.0.45 + MyDAC v5.10.

Server Part:

Table

Code: Select all

CREATE TABLE `table1` (
  `ID` int(16) NOT NULL AUTO_INCREMENT,
  `NAME` varchar(255),
  `NOTE` varchar(255),
  PRIMARY KEY (`ID`),
  UNIQUE KEY `ID` (`ID`),
  UNIQUE KEY `CODE` (`NAME`)
)

Процедура

Code: Select all

CREATE PROCEDURE `P_TABLE1_INSERT`(
IN  pNAME varchar(255),
IN  pNOTE varchar(255),
OUT pID int(16)
)
BEGIN

  INSERT INTO table1
    (NAME, NOTE)
  VALUES
    (pNAME, pNOTE);

  set pID := LAST_INSERT_ID(); 
    
END;

I cannot get ID value in out-parameter from stored proc!

Where is error?
Please help me with subject!

[Dimon]

It is impossible to get the ID value as a parameter of the stored procedure, because MySQL server returns its value only by recordset.
But you can use the TMyQuery.InsertId property to get the ID generated for an AUTO_INCREMENT column by the last query.
You can find more detailed information about this property in the MyDAC help.

[viking.new]

It is impossible to get the ID value as a parameter of the stored procedure, because MySQL server returns its value only by recordset.
But you can use the TMyQuery.InsertId property to get the ID generated for an AUTO_INCREMENT column by the last query.
You can find more detailed information about this property in the MyDAC help.

Thanks a lot for answer.

I tried to use TMyQuery.InsertId but I had 0.
Does it work for TMyQuery? Or for TMyTable only?

May be I must use TMyQuery.InsertId on client side only and don't use
LAST_INSERT_ID() on server-side, isn't it?

[viking.new]

I always have TMyQuery.InsertId = 0.

May be I have error with some parameters of TMyQuery?

[Dimon]

The TMyQuery.InsertId property gets the ID generated only by the last query that changes data.
You should use an INSERT query instead of the CALL query in the TMyQuery.SQLInsert property for getting correct InsertId.

Also, if you process data in a stored procedure and can not use INSERT query directly, you can use an additional dataset that gets the LAST_INSERT_ID value.
For this you can use the following code:

Code: Select all

MyQuery.SQL.Text := 'SELECT LAST_INSERT_ID() as ID;';
MyQuery.Open;
ID := MyQuery.FieldByName('ID').AsInteger;

[viking.new]

Thanks!

Sorry for my incomprehension!
It really works!

I have 2 queries:

Code: Select all

MasterQuery.SQL.Text = select * from table1
MasterQuery.SQLInsert.Text = INSERT INTO table1
                                          (NAME, NOTE)
                                      VALUES
                                      (:NAME, :NOTE)

I need to last inserted ID and I try to get it as

Code: Select all

MyQuery.SQL.Text =SELECT LAST_INSERT_ID() as ID

Delphi code is

Code: Select all

procedure TForm1.MasterQueryAfterPost(DataSet: TDataSet);
begin
  inherited;
  MyQuery.Close;
  MyQuery.Open;
  MessageDlg(MyQuery.FieldByName('ID').AsString , mtInformation, [mbOK], 0);
end;

[Dimon]

Note, if you use only the INSERT statement in SQLInsert , you can use the TMyQuery.InsertId property to get the last ID.