2

What is the proper way to use the Firebird "insert ... returning" with dbxpress? I have tried the following code, but errors with "List index out of bounds", indicative of its failure to return a dataset. I have tried changing q.ExecSql to q.Open but errors with "Result set not returned by query" or something like that. Appears to be the same problem, dataset not able to return the desired resultset.

procedure TdmMain.mdp_a_sqlBeforeUpdateRecord(Sender: TObject; SourceDS:
    TDataSet; DeltaDS: TCustomClientDataSet; UpdateKind: TUpdateKind; var
    Applied: Boolean);
const
  SQL = 'insert into A_SQL( ID, FORM_NAME, TABLE_NAME, STATEMT )'#13#10+
        ' values(-1,''%s'',''%s'',''%s'') '#13#10+
        ' returning id';
var
  s : string;
  rs: TCustomSQLDataSet;
  id: Integer;
  params: TParams;
  q: TSQLQuery;
begin
  if UpdateKind = ukInsert then
  begin
    s := Format( SQL, [DeltaDS.FieldByName('FORM_NAME').AsString,
                      DeltaDS.FieldByName('TABLE_NAME').AsString,
                      DeltaDS.FieldByName('STATEMT').AsString]);
    params := TParams.Create;
    q := TSQLQuery.Create(Self);
    try
      params.CreateParam(ftInteger,'ID',ptOutput);
      q.SQL.Add(s);
      q.SQLConnection := AppConnection;
      q.Params.Assign(params);
      q.ExecSQL;
      id := q.Fields[0].AsInteger;
      DeltaDS.FieldByName('ID').AsInteger := id;
      Applied := True;
    finally
      params.Free;
      q.Free;
    end;
  end;
end;
JeffP
  • 539
  • 1
  • 5
  • 19
  • I don't know Delphi, but in Firebird an `INSERT ... RETURNING ...` behaves the same as an **executable** stored procedure. – Mark Rotteveel Dec 04 '16 at 15:00
  • @mark, that means Execsql is the correct command to execute the statement. But for some reason the resultset is not returned – JeffP Dec 04 '16 at 15:05
  • From the OLH on TSqlQuery.ExecSql: "Executes a query that does not return a set of records" – MartynA Dec 04 '16 at 15:35
  • 2
    Your return ID is `q.ParamByName('ID').Value` after the `q.ExecSQL`. – kobik Dec 04 '16 at 16:25
  • That is because an executable stored procedure (and therefor also `insert ... returning ...`) does not have a result set; it only has one or more columns result (sort of like a single row, but it is not transferred as a result set). – Mark Rotteveel Dec 05 '16 at 09:39
  • `q.ParamByName('ID').Value` is unassigned after q.ExecSQL. – JeffP Dec 06 '16 at 05:03
  • You can't use `Insert returning` in this way. It will only work in a stored procedure that was executed via TSQLStoredProc with `.. returning ID into :ID` just before the `Suspend;` [how-does-suspend-work-in-firebird](http://stackoverflow.com/questions/33119002/). Then you could examine the `TSQLStoredProc1.ParamByName('ID').Value` as suggested. – Freddie Bell Dec 07 '16 at 14:01
  • @MarkRotteveel he is using DB Express here, a COM-like language-agnostic library EMBA tried to enforce onto both Win32 and .NET worlds to unify them. This library itself passed through many versions.... And it is closed-source. Unlike Delphi RTL you just can not peep inside it and see the things happening. That library is also more caring about Big Brands, like Oracle and MS SQL. For example it still *(after passing through version 4.0 ! ) fails to use Interbase/Firebird events. – Arioch 'The Dec 07 '16 at 20:47
  • @JeffP, what is your Delphi version ? it is not in the tags. I think you have to use firebird-aware libraries. Dedicated like UIB or FIB+ or generic like AnyDAC=FireDAC or UniDAC. Oh, even IBX might do it when wrapped inside `execute block`. But with ADO or DBX I think you have little luck. As a last chance, try to PREPARE your query before calling ExecSQL. – Arioch 'The Dec 07 '16 at 20:49
  • @kobik you may be surprised but with SOME certain library families that really IS in the `FieldByName` o_O - http://www.sql.ru/forum/actualutils.aspx?action=gotomsg&tid=1227918&msg=19580073 – Arioch 'The Dec 07 '16 at 20:54
  • @JeffP, not sure why it's not working for you. see: http://forums.devart.com/viewtopic.php?t=17159 – kobik Dec 08 '16 at 14:15
  • @Arioch'The, I use Delphi 7. Tag added. ATM I cannot change libraries due to dependencies (derived classes). Preparing the statement, like `q.PrepareStatement` before `q.ExecSQL` raises the error 'Unable to run query'. I'm not prepared for archaeological tasks today so I'm trying `execute block`. – JeffP Dec 09 '16 at 03:33
  • Was there even DBX in D7 ? I made my leap from D5 to XE2, so I frankly don't remember what libs were there in D7, but... Like I said, DBX is "common least subset", so it does not support IB/FB extensions. For example our app still uses DBX for main connection and IBX for FireBird events. Yeah, chimera, there were some reasons for it. So I think in D7 the early limited DBX that could be there would have even less support for IB/FB. Bad luck. – Arioch 'The Dec 09 '16 at 10:34
  • @JeffP why do you do that `q.Params.Assign(params);` and stuff ? It looks insane to me. You kill normal parameters with your own cleanplate ones. Maybe that is the way to do it in D7 DBX, dunno, but I never saw anything remotely like that. There should be a property in TSQLQuery like ParseSQL or CheckParams or whatever. You set it to True before `q.sql.text := '...'` then after `Prepared := true` the query is expected to create params on its own, if it can. And if it can not - then - bad luck. – Arioch 'The Dec 09 '16 at 10:39
  • 'q.params.assigns(params)' is supposed to help dbx to identify id as an output parameter. – JeffP Dec 09 '16 at 22:05
  • Cannot you do in D7 DBX just plain simple `with Q.PAramByName['ID'] do begin ParamType:=ptOutput; DataType := dtInteger; end;` ? Of course, if `TCustomSQLDataSet.ParamCheck` via `TParams.ParseSQL` does not create the param, then it would fail, but then everything would... – Arioch 'The Dec 14 '16 at 12:35

0 Answers0