Forum OpenACS Development: Response to Overview of InterBase's limitations

IB doesn't support user-defined functions written in PL, only stored procedures. Its PL is very weak compared to Oracle or PG. Dynamic SQL (i.e. SQL not known at compile time) is not supported, so you're pretty much stuck with zillion of IF's and branching all over the code if you want to write anything more advanced than simple selects and looping.

CURSORs are not available in PL. You have simple looping constructs with FOR SELECT ..., which loops over each row in result set, and that's it.

On a positive side, stored procedures can return multiple rows *and* columns. Very interesting (and highly IB-specific) feature: stored procedure share the same namespace with tables and views, so it is quite common to

CREATE PROCEDURE stored_proc (
  param1        INTEGER,
  param2        VARCHAR(100)
) RETURNS (
  output_field1 INTEGER,
  output_field2 NUMERIC(15,2),
  output_field3 TIMESTAMP
)
AS
BEGIN
  ...
END ;

SELECT output_field1, output_field3
  FROM stored_proc(10, 'test')
  WHERE output_field2 > 14500.75 ;

output_field1   |  output_field3
----------------+---------------------
          123   | 2000-10-26 17:48:00
           17   | 2000-01-01 09:12:01
   7832465123   | 2000-09-09 09:09:09

So, because stored procedure's output behaves like normal result set, you can perform any operations with it as you would with 'normal' result set, for example you can apply GROUP BY to it, and you can JOIN it to another table/view/stored procedure as well!

Packages are not implemented. Since we have no true IB functions, ACS objects' constructors will have to use stored procedures that return single row/column.

SELECT acs_attribute_create('blah', 'trah', 'gnah', NULL, ...)
   FROM rdb$database;

Next, default values cannot be specified and there is no such thing as Oracle's nifty '=>' syntax.

For destructors, which generally don't return anything, we should use stored procedures with no result set. Those must be invoked with

  EXECUTE PROCEDURE acs_attribute_destroy(...);

Oracle:

    select forum_id, short_name, moderated_p,
           acs_permission.permission_p(forum_id, :user_id,
                                       'bboard_write_forum') as write_p
      from acs_objects o, bboard_forums f
      where object_id = forum_id
        and context_id = :package_id
        and acs_permission.permission_p(object_id,:user_id,'bboard_read_forum')
= 't'
    order by short_name
  

InterBase:

CREATE PROCEDURE acs_permission (
  object_id     INTEGER,
  party_id      INTEGER,
  privilege     VARCHAR(100)
) RETURNS (
  permission_p  CHAR
)
AS
BEGIN
    ...
END ;

    select forum_id, short_name, moderated_p,
           (SELECT permission_p
           FROM acs_permission(forum_id, ?, 'bboard_write_forum') as write_p
      from acs_objects o, bboard_forums f
      where object_id = forum_id
        and context_id = ?
        and
        (SELECT permission_p
         FROM acs_permission(object_id, ?, 'bboard_read_forum')) = 't'
    order by short_name