Page 1 of 1
InsertID Problem: value always 0 on insert statement
Posted: Wed 08 Dec 2004 11:36
by 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
Re: InsertID Problem: value always 0 on insert statement
Posted: Thu 09 Dec 2004 09:23
by 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.
Re: InsertID Problem: value always 0 on insert statement
Posted: Thu 24 Feb 2005 05:50
by roman
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
Posted: Thu 24 Feb 2005 10:13
by 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);
Posted: Thu 24 Feb 2005 21:58
by 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.
Posted: Fri 25 Feb 2005 16:09
by Ikar
We'll consider your suggestion and probably support this behaviour in the next version of MyDAC.
Posted: Fri 25 Feb 2005 17:09
by 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.
Posted: Sat 05 Mar 2005 05:15
by roman
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!
me too
Posted: Wed 13 Jul 2005 16:28
by 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
Posted: Thu 14 Jul 2005 12:52
by Ikar
Try to use MyDAC 4.0 Beta.
Re: me too
Posted: Fri 15 Jul 2005 16:11
by roman
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".