InsertID Problem: value always 0 on insert statement

Discussion of open issues, suggestions and bugs regarding MyDAC (Data Access Components for MySQL) for Delphi, C++Builder, Lazarus (and FPC)
Post Reply
Stefan Kilp
Posts: 3
Joined: Tue 07 Dec 2004 21:20

InsertID Problem: value always 0 on insert statement

Post by Stefan Kilp » Wed 08 Dec 2004 11:36

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
Posts: 1693
Joined: Thu 28 Oct 2004 13:56

Re: InsertID Problem: value always 0 on insert statement

Post by Ikar » Thu 09 Dec 2004 09:23

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
Posts: 5
Joined: Thu 24 Feb 2005 05:44

Re: InsertID Problem: value always 0 on insert statement

Post by roman » Thu 24 Feb 2005 05:50

Ikar wrote: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
Posts: 1693
Joined: Thu 28 Oct 2004 13:56

Post by Ikar » Thu 24 Feb 2005 10:13

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
Posts: 5
Joined: Thu 24 Feb 2005 05:44

Post by roman » Thu 24 Feb 2005 21:58

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
Posts: 1693
Joined: Thu 28 Oct 2004 13:56

Post by Ikar » Fri 25 Feb 2005 16:09

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

GEswin
Posts: 186
Joined: Wed 03 Nov 2004 16:57
Location: Spain
Contact:

Post by GEswin » Fri 25 Feb 2005 17:09

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
Posts: 5
Joined: Thu 24 Feb 2005 05:44

Post by roman » Sat 05 Mar 2005 05:15

Ikar wrote: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
Posts: 10
Joined: Wed 13 Jul 2005 11:48
Location: UK

me too

Post by RoyS » Wed 13 Jul 2005 16:28

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
Posts: 1693
Joined: Thu 28 Oct 2004 13:56

Post by Ikar » Thu 14 Jul 2005 12:52

Try to use MyDAC 4.0 Beta.

roman
Posts: 5
Joined: Thu 24 Feb 2005 05:44

Re: me too

Post by roman » Fri 15 Jul 2005 16:11

RoyS wrote: 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".

Post Reply