Insert with returning and zquery open

The BETA tester's 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: 588
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 Betatest Forum”

Who is online

Users browsing this forum: No registered users and 1 guest