I have a stored procedure which insert/update, and then returns result.

create or alter procedure sp_update_system_sticker (
    i_sticker_id integer,
    i_file_name file_name_type,
    i_sticker_name item_name_type,
    i_group_id integer)
returns (
    o_result integer)
as
begin
   update
      system_stickers s
   set
      s.file_name          = :i_file_name,
      s.name               = :i_sticker_name,
      s.fk_stickers_groups = :i_group_id
   where
      s.id = :i_sticker_id;

   o_result = -1;

   suspend;
end

I am setting it in Delphi in a IBStoredProc, and execute it as follow:

procedure TDataModule_.updateSystemSticker(stickerId, groupId: integer;
  stickerName, fileName: String);
var
   r : Integer;
begin
   with IBStoredProc_UpdateSystemSticker do
   begin
      Transaction.Active := true;

      ParamByName( 'I_STICKER_ID'   ).AsInteger  := stickerId;
      ParamByName( 'I_GROUP_ID'     ).AsInteger  := groupID;
      ParamByName( 'I_STICKER_NAME' ).AsString   := stickerName;
      ParamByName( 'I_FILE_NAME'    ).AsString   := fileName;
      ExecProc;

      Transaction.Commit;
   end;
end;

Anyway it does not commit the result into the database. If I remove the returns - it start to commit.

• How to execute and commit properly stored procedure with IBStoreProc which returns results ?

CodePudding user response：

The problem is the presence of SUSPEND. This makes your stored procedure a selectable procedure, and not an executable procedure. When you use a selectable procedure, then all work done since the previous fetched row will be undone when the cursor is closed (which happens on commit). If you fetched nothing, this means that it is as if no work was performed by the stored procedure^*.

In other words, you need to remove the SUSPEND (an executable stored procedure outputs a single row immediately on execute without having to wait for a fetch).

I don't program Delphi, so I can't comment on the specifics of getting results in Delphi.

*: ^{Recent versions of Firebird can prefetch rows, so this might not be entirely accurate}

CodePudding user response：

Since you made it a "selectable stored procedure" by adding SUSPEND PSQL statement - just do a select from it.

Use regular TIBQuery instead of TIBStoredProc with a command like

select * from StoredProcedureName( :input_param1, :input_param2, :input_param3 )

I would not recommended to use IBExpress to directly call stored procedures in Firebird. Interbase turned up to have a bug in stored procedures execution, AFAIR something wrong with errors handling. To counter it IBX team added an intentional bug of executing SPs twice (under some conditions), which usually (not always) neutralized the Interbase bug. When Firebird team fixed the server bug - this IBX counter-bug started breaking data. IBX team refused to revert to normal behavior for Firebird databases as Firebird was considered competitor to Interbase.

Equally, IBX would not support Firebird-specific changes made after Interbase 6.x/Firebird 0.9 split. For example:

• client DLL name change to avoid collision: it became fbclient.dll or fbembed.dll from gds32.dll, however IBX only supports the legacy name. It is hardcoded and can not be changed. If you have IBX sources you may patch it and recompile the library - but why bother?
• Firebird's new datatypes like 64-bit integer and boolean. Again, if you have IBX sources...
• Firebird's new APIs are not supported.

That said, there is an IBX add-on library by Dmitry Loginov, IBX FB Utils, and it has a number of rather comfy wrappers, on top of IBX. It alone might be a good pro-IBX argument.

FPC (FreePascal) folks started IBX fork they named IBX2, which would hopefully have first-class support for Firebird. I do not know about quality and development speed of it, but i suspect it might appear an easiest migration route out of IBX, or not.

Personally for Firebird-centric Delphi projects i prefer opensource UIB (Unified Interbase) library. However

• Being "lean thin API wrapper" it is not TDataSet derived, albeit having a read-only TDataSet wrapper and trying to keep API closely resembling one of TDataSet.
• being Henri's Delphi project it has little documentation (tests and examples mostly) and is abandoned by the author (albeit some other guy was adding patches later)
• it has neat features like SQL scripter component (but you might need to extend it to support all Firebird new SQL commands, at least i did it to support FB2's MERGE) and for RECORD in SQLQuery do... loop (albeit you can extract it and make it into a separate add-on over any your DB library of choice)