Informix

Informix was purchased by IBM this summer and the future of Informix as a separate product is unclear. IBM has announced that they are "moving" some features of Informix server to DB2, so I suspect the long term prognosis for Informix is not good.

Ingres

miniSQL

miniSQL has an ODBC driver that is based on a driver written by Dirk Ohme in the mid 1990's. Dirk based his driver on the Open ODBC project from Unix. Unfortunately it appears to be incompatible with the Intersolv/Visigenic drivers and Driver Manager that is common on OS/2, and it is hence is incompatible with the majority of OS/2 applications. From the very limited time I spent looking at the source of Dirk's driver it appears that the ODBC setup functions Dirk wrote do not match the ODBC specification. Dirk is relying on a different file structure for storing the driver parameter from what the normal ODBC Driver Manager uses. So while the driver can be linked into an application that only uses that driver and that calls the functions directly, it cannot be used with a regular ODBC Driver Manager.

mySQL

mySQL is extremely popular right now. The OS/2 port of the latest version, 3.23.42, is an excellent port. The project is generating a lot of excitement right now. The problem for me is that mySQL is a very poor fit with ODBC.

mySQL started out as a small, very fast database. It consciously left out many traditional database features because those were considered unnecessary for the purpose of mySQL and because they would slow the database down. Basically it originally was designed way of using a minimal SQL grammar to access file system files - one mySQL web site described the database as a SQL front end to the file system. What resulted was indeed a small, fast database. And a database that was very different and very incompatible with traditional database concepts and standardized SQL capabilities. Let me give you three examples.

The first example is a quote from the manual section titled Date and Time Types.

"The date and time types are DATETIME, DATE, TIMESTAMP, TIME, and YEAR. Each of these has a range of legal values, as well as a "zero" value that is used when you specify a really illegal value. Note that MySQL allows you to store certain 'not strictly' legal date values, for example 1999-11-31. The reason for this is that we think it's the responsibility of the application to handle date checking, not the SQL servers. To make the date checking 'fast', MySQL only checks that the month is in the range of 0-12 and the day is in the range of 0-31."

You can see the priorities of the development team in this statement - speed is everything. This statement is actually not entirely accurate - or rather no longer entirely accurate. For dates the "certain `not strictly' legal date values" are all now converted to a zero date, including the 1999-11-31 value referenced above. However for TIMESTAMP columns, invalid dates, such as the 1999-11-31 are accepted and converted to what appears to be a random date. For instance entering 1999-11-31 into a TIMESTAMP column on my machine results in a value in the column ranging from 2001-08-22 to 2001-08-23. I don't know if the behavior of the TIMESTAMP column is a bug or a designed feature; lets assume for a moment that it is a bug and the intended behavior is for mySQL to accept invalid TIMESTAMP values but to store those as a zero value.

The second example is from the mySQL manual in the section titled Silent Column Specification Changes.

"You cannot store a literal NULL in a TIMESTAMP column; setting it to NULL sets it to the current date and time. Because TIMESTAMP columns behave this way, the NULL and NOT NULL attributes do not apply in the normal way and are ignored if you specify them. DESCRIBE tbl_name always reports that a TIMESTAMP column may be assigned NULL values."

This is true. If you try to set a TIMESTAMP column to a NULL value it will take the value of the current date and time. The problem is that it is impossible to distinguish between a value entered in error (which would appear as a zero value) and a row where no value has been entered. NULL in relational databases means that there is no value, either because one hasn't been entered or because one isn't appropriate for that row. And NULL is necessary in order to distinguish between a valid value (zero is a valid value) and no value.

The third example is also from the mySQL manual in the section titled Silent Column Specification Changes. It also points out the conscious design decision of the developers to trade compatibility for performance.

"If any column in a table has a variable length, the entire row is variable-length as a result. Therefore, if a table contains any variable-length columns (VARCHAR, TEXT, or BLOB), all CHAR columns longer than three characters are changed to VARCHAR columns. This doesn't affect how you use the columns in any way; in MySQL, VARCHAR is just a different way to store characters. MySQL performs this conversion because it saves space and makes table operations faster."

I said above that mySQL was a very poor fit with ODBC. ODBC is designed so that a single database application can work against multiple vendors' databases. The ODBC API is there to fix the problem of each database having its own API - and ODBC does fix that problem. The problem that then remains is how the database itself behaves. In order to write an application that works against multiple databases the application programmer must have some confidence that the SQL statements used by the application will run similarly on the different databases - that the same statement will return the same data or behave the same way on two different databases with the same data. This is not the case with mySQL. And we aren't just talking about not being in the same game, we are talking about not being in the same ballpark.

Let me give you one last example: mySQL doesn't implement foreign keys. The reasons are, to quote again from the manual:

"Foreign key constraints make life very complicated, because the foreign key definitions must be stored in a database and implementing them would destroy the whole "nice approach" of using files that can be moved, copied, and removed. The speed impact is terrible for INSERT and UPDATE statements, and in this case almost all FOREIGN KEY constraint checks are useless because you usually insert records in the right tables in the right order, anyway. "

That by itself wouldn't be that big of a deal. An ODBC application could query the database driver to determine that mySQL doesn't support foreign keys and adjust itself appropriately, as suggested above. The problem is that mySQL reports that it does support foreign keys. To quote again from the manual "The FOREIGN KEY syntax in MySQL exists only for compatibility with other SQL vendors' CREATE TABLE commands; it doesn't do anything."

My purpose here is not to slam mySQL (although that is pretty difficult to resist given the grossly over-reaching claims that mySQL makes for itself), but rather to point out that mySQL is a very poor match when compatibility to other SQL databases is an issue, which it is with ODBC. On the other hand mySQL is a very good match at tasks that fit in with the overall design and goals of mySQL; tasks like providing fast read-only access to data, which is what most web sites do. In those types of applications the database administrator would probably de-normalize the data anyway for performance reasons. Plus there is no point in going through ODBC which just adds another layer of overhead in that type of application.

mySQL has been reviewed previously a couple of times in the OS/2 eZine. The previous version, 3.23.28 had a problem that prevented most people from installing it using WarpIn. That problem has been fixed with version 3.23.42, although you will need the latest version of WarpIn to install it - which is a later version than the one that comes with eComStation.

Conclusion