InsertID Problem: value always 0 on insert statement

InsertID Problem: value always 0 on insert statement

Postby 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
Stefan Kilp
 
Posts: 3
Joined: Tue 07 Dec 2004 21:20

Re: InsertID Problem: value always 0 on insert statement

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

Re: InsertID Problem: value always 0 on insert statement

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

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

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

Postby Ikar » Fri 25 Feb 2005 16:09

We'll consider your suggestion and probably support this behaviour in the next version of MyDAC.
Ikar
 
Posts: 1693
Joined: Thu 28 Oct 2004 13:56

Postby 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.
GEswin
 
Posts: 186
Joined: Wed 03 Nov 2004 16:57
Location: Spain

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

me too

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

Postby Ikar » Thu 14 Jul 2005 12:52

Try to use MyDAC 4.0 Beta.
Ikar
 
Posts: 1693
Joined: Thu 28 Oct 2004 13:56

Re: me too

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


Return to MySQL Data Access Components