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

Collapse
Posted by Sebastian Skracic on
  • Missing IFNULL(), NULLIF(), COALESCE, CASE and almost all standard SQL92 functions

    My biggest complaint (besides too short keys and inability to GROUP BY column_alias) is absence of almost all SQL functions. In "core" InterBase there is no string and math functions at all (all that is left are operators LIKE, CONTAINING, STARTING WITH, || and math arithmetic operators).

    The standard solution to this problem is library of user defined functions (UDF) written in C (on Whindows you can also use Pascal). This is very similar to PostgreSQL's user-defined functions but, unfortunately, overloading is not supported.

    That would be end of story if UDF could deal with NULL values. But they can't. Even worse, if you pass NULL as an argument to UDF, the result is unpredictable because UDFs simply ignore null indicator and grab argument value, which is just some random garbage. In any case UDF cannot return NULL. That leaves you pretty much in the dark if you need IFNULL(), or its generalized friend COALESCE().

  • No recursion alla CONNECT BY

    But recursive stored procedures are supported, so it's not too hard to write the procedure that calls itself and traverse all nodes of a tree.