Thanks to Thomas Gutzmann for sending us his report on his experience using our plugin with Oracle database server:
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
I can use
SQLSelect("select sysdate from dual")
vDate = vRS.IdxField(1).DateValue
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