This article was written based on experiences with Borland Delphi 5, ODBCExpress 6.0 and Mimer SQL version 9 and, all the samples provided with the ODBCExpress installation works well in this configuration.
There are, however, some problems that might occur, particularly when using older Mimer SQL servers.
Also, some of the samples use Microsoft SQL Server stored procedures that will not work with Mimer SQL since Mimer SQL supports standardized ISO Stored Procedures only.
Problems and workarounds
The following sections describe some problems that may arise and the suggested ways to get around them.
Updatable result sets
As of Mimer SQL version 9, the ODBC driver does not support updatable result sets. This means that the functions SQLBulkOperations and SQLSetPos cannot be used by ODBCExpress to update rows in the result set. Updates to the result set have to be undertaken using other means.
ODBCExpress defines its behavior into four levels of "SQL Generation". The highest level (1) uses, whenever possible, the unsupported functions mentioned above to update result sets. SQL Generation levels 3 and 4 uses features supported by Mimer SQL. Search for SQL Generation in the ODBCExpress documentation to learn how to update result sets using Mimer ODBC Drivers.
[Microsoft][ODBC Driver Manager] Fetch type out of range
This error is seen when the driver does not support ODBC bookmarks for the selected cursor type, and the client side caching is turned off. As of version 9.2.1 Mimer ODBC does not support bookmarks, so if you encounter this error you have to use client side caching to make things work. The attribute to control this is called "Cached" in TCustomOEDataSet (and all classes derived from that, like TOEQuery and TOEDataset).
The ODBCExpress behavior in this regard has been reported to the developers. We hope that future releases of ODBCExpress wont exhibit these symptoms.
[MIMER][ODBC Mimer Driver]Option type out of range
This error is due to a bug in Mimer ODBC drivers. A workaround is the enable client side caching of result sets, see the above problem on how to do this. This problem is corrected in Mimer SQL 9.2.1b and later releases.
[MIMER][ODBC Mimer Driver][MIMER/DB]Syntax error, ( ) assumed missing
This error may be seen when an OESchema object is used to create a database schema on Mimer SQL 8.2 servers or older and the programmer has specified a VARCHAR or a VARBINARY column with the default length of 0. ODBCExpress will in these cases generate SQL similar to:
create table OUR_TABLE (OUR_COLUMN CHARACTER VARYING)
That is, the parenthesis to specify the column length is omitted. Omitting precision specifiers on CHARACTER VARYING is a non-standard feature that Mimer SQL 8.2 and older servers do not support.
The corrections are either to upgrade to at least Mimer SQL 9.1 or make sure a precision is always specified for these column types (preferably using the value 1 since that is what omitting the precision will default to).
Do not expect the Mimer SQL INTERVAL data types to work at the client level. If you have an INTERVAL column in your database, make sure you cast the column to a character or an integer before returning it. A symptom of this problem is the error [MIMER][ODBC Mimer Driver]Invalid buffer length. For example:
create table OUR_TABLE (OUR_INTERVAL interval year to month)
select cast(OUR_INTERVAL as char(10)) from OUR_TABLE
create table OUR_TABLE2 (OUR_SINGLE_FIELD_INTERVAL interval day(5))
select cast(OUR_SINGLE_FIELD_INTERVAL as INTEGER) from OUR_TABLE2
In the same manner, input parameters also have to be specifically casted to avoid exposing INTERVAL data types to ODBCExpress. For example:
insert into OUR_TABLE values (cast(cast(? as char(10)) as interval year to month))
Article round up
We at Mimer Information Technology are no ODBCExpress experts and any input to this article is welcome.