The Oracle has spoken

For those of you that have been wondering what I am doing lately, here is a little update. Apart from the Blog, I also develop the MS-SQL and Oracle drivers for IPB.

The MS-SQL driver for 2.1. has been completed for a while now and was ready when we released IPB 2.1.

The Oracle driver however was not done yet. I have some good news for our Oracle driver customers. I am currently working very hard to get the Oracle driver done. Apart from the upgrader, it is done. So I expect to be able to release this before the month ends.

To give you some background. Writing a DB driver can be quite frustrating. Different databases have different habbits and you can run into the unexpected problems.

For Oracle the following things need to be taken care of:

• LIMIT handling. As there is no LIMIT statement in Oracle, one needs to solve this differently.
• CLOB fields; The field type also known as Text in MSSQL/MySQL is handled different in Oracle. You cannot insert the values directly, but have to store it with a seperate command.
• NOT NULL; Oracle is very strict on the handling of NOT NULL fields. This is in fact a good thing (MSSQL is strict as well; MySQL does not enforce it for some reason). But on Oracle '' is seen as NULL. So if a field is defined as VARCHAR(xxx) NOT NULL DEFAULT ''; this will generate an error when the field is not filled or an empty value is put in it. In IPB this is used on many fields, so on Oracle these need to be defined differently.
• Autoincrement fields. Not available in Oracle. But there is an alternative, namely creating a SEQUENCE (which is a counter object) in combination with a TRIGGER on the field in question. Works perfectly, but does require a lot of extra SQL.
• Fulltext search. This is available in Oracle with the ctx library. However, Oracle does not update the fulltext indexes automatically; instead you need to create a task that updates the indexes every x minutes.
• GROUP BY's. On MySQL you can do SELECT field1, field2, count(*) FROM table GROUP BY field1. On Oracle (and MS-SQL) this is not possible (and in fact is not ANSI SQL). You have to write:SELECT field1, field2, count(*) FROM table GROUP BY field1, field2
• Field names. In Oracle all field names are returned in uppercase. However IPB expects them in lowercase. Therefor the field names in the return arrays need to be converted to lowercase.

There are other things you run into, but the above state the main problems. Luckaly all these issues where allready addressed in previous versions of the driver. So this time around I didn't have to re-invent the wheel