My top reasons to go for MBS Real Studio SQL Plugin

Finally MBS, that wildest beast in the RealBasic territory, got me. I was stuck with the OracleDatabase plugin, and I couldn't wait any longer for Real Software to fix the worst bugs in the module. I understand that Oracle is not very high up on their agenda, but on mine it is.

The most problematic issues I had where:

- Character set problems.
- Date handling errors.
- Prepared statements buggy.
- Insufficient BLOB support.
- And a certain lack of performance.

I had worked around several deficiencies by subclassing OracleDatabase, of course. It was this BLOB thing which let me grope for MBS.

It was a matter of minutes to change the base class of my database class from OracleDatabase to SQLDatabaseMBS:

- Prefix the connect string with "oracle:".
- Move the setting of environment variables like DYLD_LIBRARY_PATH out of the source code.
- Add one line of code for setting the pointer to libclntsh.dylib.10.1.
- Create two methods for commit and rollback, as they are not handled correctly in SQLDatabaseMBS.
- Work around the missing support for RecordSet.RecordCount (I'm not unhappy, as this feature is damnable performance-wise).

Now I can write

vRS.Field("colname").StringValue

instead of

DefineEncoding(vRS.Field("colname").StringValue, Encodings.UTF8)

I can use

SQLSelect("select sysdate from dual")
vDate = vRS.IdxField(1).DateValue

instead of

SQLSelect("select to_char(sysdate, 'YYYY-MM-DD HH24:MI:SS') from dual")
vDateString = vRS.IdxField(1).StringValue
... and convert the string to a date field ...

These improvements may not look important, but if your application contains hundreds of SQL statements, if you use stored procedures a lot, you start to think different.

Prepared statements simply work, even with BLOBs. There are a few gotchas for BLOBs: I had to use named (":colname") instead of positional ("?") bind variables, and I had to write a small stored procedure to work around the Oracle 32K limit for bind values - basically implementing "dbms_lob.append".

The performance looks better. I haven't tried hard yet, but it looks as if Christian works closer to the standard. The missing RecordCount supports is an indicator, because that would mean to either issue a "select count" first, or buffer the complete result set.

Cheers to Christian