The Revelation ODBC Driver Practical Tips (General)

                                                                                                                                                Updated1/05

In late 2002,Revelation Software released a read-only version of their ODBC driver. This driver, which allows other applications, such as MS Access or Crystal Reports,access to Revelation s multivalued, Linear Hash data, requires that developers examine their dictionaries that will be accessed by the ODBC driver.  The following describes some of the more common issues that have been encountered. The ODBC Driver will only work on OpenInsight versions 7.0 & above.

Dictionary Settings

In the Revelation world, we have been spoiled for many years because we ve lived in a datatype-less environment. Data types for fields were not required until OpenInsight.  With the advent of the ODBC driver, tables and columns (or files and fields, if you prefer) must be data typed correctly, or errors can occur.  So, some tips are listed below:

            Data typing requires the setting aside of memory when data is transferred between programs.  So, if your dictionary datatypes are set to VARCHAR(65531),each field in each record will require about 64k worth of memory, so you will likely run out of memory very quickly. The workaround is to use a smaller value in the data type.  This will not affect the data in your existing database, only the data in the SQL process.

            Formatting can also cause problems.  In some versions of Excel,output conversions containing dollar signs or dashes can cause issues.  The workaround is to create synonym fields with different output conversions.

SQL Keywords

There are keywords that the developer needs to be aware of when recommending and implementing the use of the ODBC Driver. The OpenInsight SQL statement processor does not check for keywords. It will process the ODBC request and pass back errors.  If the error is due to a keyword, the Invalid Syntax message is returned to the calling application.

The problem will manifest itself when Linear Hash field names are the same as SQL keywords.  For example, a Linear Hash field may be named DESC (short for DESCRIPTION).  DESC is a SQL keyword for DESCENDING (used in sorting). If a SQL statement were passed via the ODBC Driver an Invalid Syntax error would be returned.

Here is a partial list of keywords.  For a full list of keywords click here.

SQL Calls

ODBC processing uses SQL commands to request information from the OpenInsight engine.  We have received some reports of SQL calls that put double quotes around column names and table names.  The OpenInsight SQL processor cannot handle this format in releases earlier than OpenInsight version 4.1.3. The workaround is to modify the SQL statement so that the column and table are not surrounded by quotes.

General Debugging Tips

            If ODBC seems to fail, and no specific messages are given, turn on ODBC tracing.  This can be found in the ODBC Administrator tool, in the control panel. The logs can provide specific details on where the process fails.

Using Excel

In addition to the above stated formatting issue, there is also an issue with tables being visible in the MS Query wizard used by Excel. The following message is received after requesting a New Database Query and selecting the Revelation ODBC data source:

Currently, there isn't a workaround in Office XP.  In Excel 2000, there is a workaround and it is as follows:

Click OK and the Query Wizard will display without the available tables list populated.

Click the Options button and select or deselect any of the options (if you deselect Tables then none of your data tables will display).

The Query Wizard will display valid data as shown below and the normal Query process can continue.