Data from insert

[QPRocky]

Hi.

Is it possible get data during insert?

Code: Select all

+-------+------------------+-----+-----------------+
| Field | Type             | Key | Extra           |
+-------+------------------+-----+-----------------+
|  id   | int(10) unsigned | PRI | auto_increment  |
|  name | varchar(15)      |     |                 |
+-------+------------------+-----+-----------------+


//this returns just 0 or 1, but it would be nice to get actual id
//is it possible?
var id: integer;
.
.
id:=Statement.ExecuteUpdate('INSERT INTO table(name) VALUES "name")');

At moment I insert something first and then serch (select) it to get id.

[btrewern]

You could try using a TZSequence component. It's used for situations like this.

Ben

[mdaems]

Actually, I think you better do a 'SELECT LAST_INSERT_ID()' query after the update call. Because of it's simpicity and because using the TZSequence would become a components-based solution, while here the dbc approach is used.

Mark

[QPRocky]

That SELECT LAST_INSERT_ID() saved my day.
Thanks a lot for both answers.

[uuid]

ZQuery with SQL =

Code: Select all

BEGIN
  INSERT INTO table_profession_rubric (key_profession, key_rubric)
  VALUES (:arg_key_profession, :arg_key_rubric);
  SELECT LAST_INSERT_ID() AS 'out_id';
END

doesn't work as expected. Is it possible not use 2 ZQueries? I'm not sure whether "SELECT LAST_INSERT_ID()" is specific to connection or to something else. I'd like to keep it near the INSERT.

[designshouse]

try this

with ZQUERY1 do
begin
SQL.Clear;
SQL.Add('INSERT INTO table_profession_rubric (key_profession, key_rubric) ');
SQL.Add('VALUES (:arg_key_profession, :arg_key_rubric); ');
ParamByName('arg_key_profession')...
ParamByName('arg_key_profession')...
ExecSQL;
Close;
SQL.Clear;
SQL.Add('SELECT LAST_INSERT_ID() AS 'out_id'');
Open;
Last_ID := FieldByName('out_id').AsInteger;

end;