InsertID Problem: value always 0 on insert statement

[Stefan Kilp]

Hi,

i try to retrieve InsertID after a insert statement. here is my test app:

- take a form, place a edit, memo, navigator, dbgrid. dataset, conncetion, myquery and monitor on it

- add afterpost event and monitor it with

procedure TForm1.MyQuery1AfterPost(DataSet: TDataSet);
begin
edit1.text := inttostr(myquery1.insertid);
end;

procedure TForm1.MySQLMonitor1SQL(Sender: TObject; Text: String;
Flag: TDATraceFlag);
begin
memo1.lines.text := text;
end;

- now click on insert on the navigator, insert test data, leave autoinf field free

- click on post
new record with next autoinc value is inserted correctly
- but in "afterpost" i retriev insertid = 0?

what am i doing wrong?

regards
stefan

[Ikar]

InsertId is filled only if INSERT statement was set in TMyQuery.SQL. If SELECT statement is set, as in this case, InsertId property won't be filled and a value of autoinc-field will be set to the corresponding field of TMyQuery.

[roman]

If SELECT statement is set, as in this case,

Could you please explain on this? The situation Stefan describes is an INSERT, not a SELECT.

Thanks

[Ikar]

Let we have Test table:

Code: Select all

CREATE TABLE Test (
 uid INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
 c_int INT
) TYPE = MyISAM;

An example of retrieving autoincrement field on using INSERT statement

Code: Select all

 MyQuery1.SQL.Text := 'INSERT INTO Test(c_int) VALUES (5)'
 MyQuery1.Execute;
 Caption := IntToStr(MyQuery1.InsertId);

An example of retrieving autoincrement field on using SELECT statement

Code: Select all

MyQuery1.SQL.Text := 'SELECT * FROM Test'
 MyQuery1.Execute;
 MyQuery1.Append;
 MyQuery1.FieldByName('c_int').AsInteger := 5;
 MyQuery1.Post;
 Caption := IntToStr(MyQuery1.FieldByName('uid').AsInteger);

[roman]

Thanks for answering, I see the difference now.

Anyway, as far as I understand, MySql handles the last insert id per connection and libmysql always yields this value no matter how the insertion is done.

Wouldn't it be more appropiate to have the InsertId in the TMyConnection object instead of TMyQuery or TMyTable and to fill this property whenever an insertion is performed?

Either way, directly using INSERT or by Append-Post, an INSERT statement is generated.

Just a suggestion.

[Ikar]

We'll consider your suggestion and probably support this behaviour in the next version of MyDAC.

[GEswin]

Wouldn't it be more appropiate to have the InsertId in the TMyConnection object instead of TMyQuery or TMyTable and to fill this property whenever an insertion is performed?

Well, add it to TMyConnection, but don't drop it from TMyQuery etc otherwise who used it will have to change lot's of code.

[roman]

We'll consider your suggestion and probably support this behaviour in the next version of MyDAC.

Thanks for considering it.

Please allow me to follow on this discussion a little bit to describe a situation in which the mentioned property would be desirable.

Consider your second example but accesing the MyQuery component through a ClientDataSet-DataSetProvider couple.

To provide the last insert id to the ClientDataSet I use the DataSetProvider's AfterUpdateRecord event:

DeltaDS.FieldByName('uid').NewValue := LastId;

having set DataSetProvider's poPropagateChanges option to true. But neither MyQuery.FieldByName nor MyQuery.InsertId yields the result.

I can directly get the value if I perform ClientDataSet.Refresh but that means another trip to the server. More over, if I use a query like

"select * from table where uid = :uid"

which is the one I am actually using, then not even the Refresh method works.

The solution I have found so far has been to implement a LastId function that directly sends the "select last_insert_id()" query to the server. It still means another trip to the server but at least works even in the latter case.

Thus, having the value given by libmysql available would be much appreciated.

The reason I need this id is because I use autoinc fields as primary keys so I need the value to be able to insert details of a master table.

Have a nice day!

[RoyS]

Hi,

I have the same problem connecting cds through a provider to a MyQuery.

I would have expected to be able to retrieve the myQuery.insertid after cds.applyupdates but all I get is zero.

I don't like the 'lastId' solution as I don;t think that it is robust enough for multi-user systems (happy to be told that I am wrong).

can anyone help?

Hopeful Thanks,

Roy

[Ikar]

Try to use MyDAC 4.0 Beta.

[roman]

I don't like the 'lastId' solution as I don;t think that it is robust enough for multi-user systems (happy to be told that I am wrong).

I think you are wrong because AKAIK the last insert id is given in a per-connection basis, thus different clients won't get the same id.

In oher words, "select last_insert_id()" does not present the same problem than something like "select max(id) from table".