Insert with returning and zquery open

The forum for ZeosLib 7.2 Report problems. Ask for help, post proposals for the new version and Zeoslib 7.2 features here. This is a forum that will be edited once the 7.2.x version goes into RC/stable!!

My personal intention for 7.2 is to speed up the internals as optimal a possible for all IDE's. Hope you can help?! Have fun with testing 7.2
danielsap
Fresh Boarder
Fresh Boarder
Posts: 3
Joined: 12.04.2017, 06:12

Insert with returning and zquery open

Postby danielsap » 28.05.2017, 08:05

Hi,

I'm trying to insert a new record in table and get the generated by sequence value of Id column.
So, I found that the right approach is to use insert with returning.
I did it but after executing 2 things happen
1 - I receive the newly generated id
2 - There is no new record in the table.

It looks like it performs some action but at the end inserted data is removed.
I tried to use commitupdates but with no success.

Here is the code

query := TZQuery.Create(nil);
query.Connection := dbConnection;
query.SQL.Add('INSERT INTO BODY_TABLE (Name, Comment) VALUES (:pName, :pComment) returning TableId');
query.ParamByName('pName').Value := szName;
query.ParamByName('pComment').Value := szInfo;
query.Open;

// query.First;
newId := query.FieldByName('TableId').AsInteger;
if newId = 0 then
ShowMessage('Newid is 0');
query.Close;
query.Free;

I'm using
Zeos 7.2.1-rc
Firebird-3.0.2.32703-0_Win32
Delphi XE3

Thanks
Daniel

marsupilami
Gold Boarder
Gold Boarder
Posts: 671
Joined: 17.01.2011, 14:17

Re: Insert with returning and zquery open

Postby marsupilami » 30.05.2017, 08:08

Hello Daniel,

I tested this yesterday. It seems to be some kind of transaction handling issue with Zeos. The following code should work for you:

Code: Select all

query := TZQuery.Create(nil);
query.Connection := dbConnection;
query.SQL.Add('INSERT INTO BODY_TABLE (Name, Comment) VALUES (:pName, :pComment) returning TableId');
query.ParamByName('pName').Value := szName;
query.ParamByName('pComment').Value := szInfo;
query.Connection.StartTransaction;
try
  query.Open;

  // query.First;
  newId := query.FieldByName('TableId').AsInteger;
  if newId = 0 then
  ShowMessage('Newid is 0');
  query.Close;
  query.Connection.Commit;
except
  query.Connection.Rollback;
end;
query.Free;


Maybe it makes more sense to use standalone generators/sequences and TZSequence?

With best regards,

Jan


Return to “ZeosLib 7.2 Forum”

Who is online

Users browsing this forum: No registered users and 1 guest