4.2 Engines

• Lower Case. By default, they downshift all words before parsing (unless the words are protected by quotes). This is natural for Unix users but is the inverse of the ANSI standard which upshifts all unquoted words. The standard was dominated by mainframe system vendors (IBM DB2, Oracle, SAP, etc).
• Database concept. Each instance of an Informix or PostgreSQL engine can have many databases. In contrast, IBM, Oracle, SAP, etc have only one database per engine instance. The Informix concept of separate databases is implemented on these other systems each as a SCHEMA.
• Outer Joins. These were originally a controversial concept and not defined in the 1986 SQL standard. The 1992 SQL standard added a JOIN clause to SQL SELECT statements to implement outer joins. Prior to that each database vendor had its own extension to the standard to implement outer joins.
• Temporary tables. The SQL standard did not provide for capturing the rows from a SELECT statement into a temporary table. Informix and Postgres both allow this but with differing syntax.
• SERIAL datatype. Not part of the SQL standard but an Informix extension. PostgreSQL has a SERIAL type but it is used differently. With Informix, you supply a zero as the SERIAL value, and the engine replaces the zero with the next serial number. With PostgreSQL, you don’t supply a value and the engine supplies the next serial number as a default. If you supply a zero, it is accepted!
• Functions. Informix has a number of functions TODAY, CURRENT, USER, MDY(m,d,y), EXTEND, etc which are not in the SQL standard or have different names (e,g NOW(), CURRENT_DATE, etc).
• MATCHES clause. Informix, in addition to the SQL standard LIKE clause, allows you to SELECT rows which match patterns using the Unix shell wildcard symbols ([]*?). PostgreSQL has a ~ operator which matches RE (regular expression) patterns in the manner of perl.
• Mandatory FROM clause. In Informix, the SELECT statement must have a FROM clause. PostgreSQL (and others like Sybase) does not require a FROM clause.
• MONEY datatype. A variant on DECIMAL which is suitable for financial systems.

• Informix SE, IDS 7, or IDS 9 or later. Best of breed commercial engines with full SQL92 compliance. You must purchase a licence from IBM-Informix in order to use it. Has a multi-threaded architecture which gives it a performance advantage over all of its rivals. Now that it is owned by IBM, it will gradually be absorbed into IBM’s own DB2 range of products and will gradually disappear.
• PostgreSQL a free opensource engine now with full SQL92 compliance. Fully free and opensource. Shares its origins with Ingres at UCB (University of California Berkeley). Unlike Informix IDS, it is not based on a threaded architecture and each frontend connection results in a separate backed process being spawned to service it. You can get postgresql from :
  www.postgresql.org.
  At the time of writing, the current version is 8.3. Each Linux distribution has its own RPMs which you get from the distribution site (try a Google search). Mike Aubury has created a native connection dubbed pg8 which works with this version.
  In the past there was a special version of PostgreSQL 7.4 patched to imitate the Informix behaviour mentioned above: The site for this project was:
  gborg.postgresql.org/pginformix/download/download.php
  and you could get the source tarballs there. You could get the RPMs from
  informix.postgresintl.com.
  The patch project has not been updated for PostgreSQL 8.0 onwards and we recommend that you change to using the pg8 library instead. These RPMs are known to install OK on SuSE 9.0 and you may be lucky on similar systems of equivalent vintage. The RPMS are patched from version 7.4. If you are installing the RPMs on a system with PostgreSQL RPMs already installed, you may need to add the --oldpackage argument to the rpm -Uvh command if the installled version is 7.4.1 or 7.4.2. It is hoped that future versions of PostgreSQL will fold these Informix patches into the regular distribution. The latest patched postgres version is also available from Aubit website http://www.aubit.com
• SAPDB a free and opensource engine up to version 7.4 with threaded architecture. The engine was SAP’s tried and true commercial product and was solid and very fast. Unfortunately, MySQL acquired the rights to develop the next version of SAPDB (to be renamed MAXDB) and the interfaces are no longer free (GPL but not LGPL licensed). SAPDB has dropped below the radar now. It will probably still work if you have the old library files however - but we no longer test for it or include it in the binary releases.
• SQLite3 a free and opensource embeddable engine with nearly full SQL92 compliance. A small engine (only 25K lines of C source code) which we actually deliver statically linked into our binary distributions of Aubit4GL. It supports most of the SQL92 standard but is typeless (everything is either a char type or numeric and the distinction is not enforced). Get it with your distribution or failing that from www.sqlite.org
• MySQL has been absorbed first by SUN MicroSystems then when Oracle bought out SUN, by Oracle. MySQL had a policy of free if you are free, commercial if you are commercial. Now that it is owned by Oracle who, of course, have their own proprietary database engine, who knows what will be the fate of MySQL? In the meantime, Aubit4GL works with MySQL.
• Any other database engine with an ODBC interface including PREPARE and SCROLL CURSOR statements.