[Request] Support INSERT...RETURNING transparently

[Fr0sT]

Would be nice if Zeos supported INSERT...RETURNING clause for queries linked to datasets and visual components. For support I mean executing INSERT statement as selectable query and copying the returned values to current row cache.
I'd wish to implement it myself but Zeos structure with its interfaces and accessors is too complicated.
Reasons why it is useful:
- Retrieve values of fields that are assigned by database inside a trigger. ID is the most frequent one but there could be many other fields assigned according to some internal rules.
- Avoid exposing sequence/generators to users. I don't know about other DB engines but Firebird/Interbase DBs could be easily abused by overflowing the generator with

Code: Select all

GEN_ID(Gen_Name, 9223372036854775807)

For other engines could be used a looped

Code: Select all

NEXT VALUE FOR Gen_Name

clause. So starting with FB 3 it is possible to restrict direct access to generator. Provided that Zeos supported INSERT...RETURNING transparently, this vulnerability could be blocked.

[marsupilami]

Hello Fr0sT,

it's nice to see you here again

Hmm - currently Zeos has no infrastructure for these things. I am not a master of Zeos interfaces myself. But I think that the best way to resolve that is to extend the Resolver Interface to provide updated field values which the Zeos Query could then integrate into the row buffer. Again there is no manpower to do this because everybody is busy with work. I will talk to the guy who knows the internals best so he can make a suggestion on how to change the Zeos architecture to accomodate to this new idea.

With best regartds,

Jan

[marsupilami]

Hello Fr0sT,

I had a talk wit Egonhugeist. There already is some functionality like this implemented in Zeos. The drivers for sqlite and MySQL can get the values for autoincrement fields and pass them to the upper layers. This is implemented in the respective resolver class - TZSQLiteCachedResolver in the sqlite case.

The main change has to be done in the resolver. The idea would be to implement a special resolver for Firebird that can use the returning part of the insert / update statement to get the current values for all fields. The returned values could be passed to the upper layers like it is done in sqlite and MySQL. There should be an option in the resolver to enable this new behavior. This option could be implemented in TZQuery.Properties or in TZConnection.Properties.

Does that sound convincing to you? What do you think?

With best regards,

Jan

[mobilevil]

I think the feature long overdue and I am glad that there's finally some solid idea about it. I am interested in looking at it but it doesn't' sound like a junior task.

[marsupilami]

Hello mobilevil,

no - it probably isn't a junior task. But as I said - everybody else is tied with other projects - even other projects on Zeoslib. So if you want to try it, I could answer your questions and try to guide you. Support for GUIDs in PostgreSQL has been implemented in a similar manner.

With best regards,

Jan

[Fr0sT]

Hello Jan, nice to return here
The info you provided sounds very promising, at least I'll have a direction to dig into. I'll try to look into things.
Thanks for help!

[Fr0sT]

I kind of did it. Now if Dataset has parameter "InsertReturningFields", the INSERT SQL will contain RETURNING part. Processing implemented for Firebird.

Full commit message:

* Support of INSERT...RETURNING construction
* New Dataset option "InsertReturningFields" that has to have value of ","-separated list of fields that will be assigned after Post (f.ex. "ID,Master_ID,Something"). This value will be added to INSERT statement as is.

ZDbcGenericResolver:
+ TZGenericCachedResolver.FormInsertStatement, processes option "InsertReturningFields"

ZDbcInterbase6:
+ TZInterbase6CachedResolver, assigns values after Posting the INSERT statement

ZDbcInterbase6Statement:
+ TZInterbase6PreparedStatement.ExecuteUpdatePrepared, creates result set for INSERT...RETURNING queries and returns it in LastResultSet property

ZDbcInterbase6Utils.pas:
* GetAffectedRows, redesigned to process the buffer correctly; returns results for stExecProc StatementType

The modified files are in attachment. I tried to keep number of changes as low as possible.

How to test:
1. DB:

Code: Select all

CREATE OR ALTER TRIGGER TRG_SOMETABLE_BI0 FOR SOMETABLE
ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
  IF (NEW.Id IS NULL OR NEW.Id = 0) THEN
    NEW.Id = NEXT VALUE FOR Insertgen_SOMETABLE;
END

2. App: Grid, Dataset linked to SOMETABLE; add "InsertReturningFields=ID" to Dataset's Parameters

3. Runtime: insert a record, fill required fields EXCEPT ID, post changes. You'll see ID field filled with freshly generated value.


Questions:
1. is

NewRowAccessor.SetValue(Index, ResultSet.GetValueByName(Fieldname));

safe? This was the most compact way as I didn't like an idea of messing with cases and data types.
2. What about TZInterbase6CallableStatement.ExecuteUpdatePrepared? The method does something different than TZInterbase6PreparedStatement.ExecuteUpdatePrepared does. I'm not quite sure what this branch of classes for and whether this class could be used for executing an INSERT.

[Fr0sT]

Fix: ZDbcInterbase6 unit, TZInterbase6CachedResolver.UpdateAutoIncrementFields should be:

Code: Select all

procedure TZInterbase6CachedResolver.UpdateAutoIncrementFields(
  Sender: IZCachedResultSet; UpdateType: TZRowUpdateType; OldRowAccessor,
  NewRowAccessor: TZRowAccessor; Resolver: IZCachedResolver);
var
  I: Integer;
  RS: IZResultSet;
begin
  inherited;

  RS := InsertStatement.GetResultSet;
  if RS = nil then
    Exit;

  for I := 0 to FInsertReturningFields.Count - 1 do
    NewRowAccessor.SetValue(I{$IFNDEF GENERIC_INDEX}+1{$ENDIF}, RS.GetValueByName(FInsertReturningFields[I]));

  RS.Close; { Without Close RS keeps circular ref to Statement causing mem leak }
end;