Caution
Revelation Software's ODBC driver is read-only!

The ODBC Driver can only be used to gain access to OpenInsight's Linear Hash data and, whilst this is perfect for linking to something like Crystal Reports, it is not the correct solution for extracting data from a Revelation Software based application.

OpenInsight includes special tools to facilitate this need. Please contact us for more details.

OpenInsight ODBC Driver Practical Tips

Using the ODBC Driver with OpenInsight prior to 7.1 and Advanced Revelation

The Revelation ODBC Driver is for OpenInsight versions 7.1 and above. Those wishing to provide ODBC access to earlier OpenInsight and Advanced Revelation (ARev) data may do so by taking advantage of the fact that OpenInsight can access ARev version 3.1 and higher data tables with no change or affects on the data, providing that there are no dots (.) in the file or field names. In our experience, the only difficulty has been with those applications that have field or column names that are reserved SQL keywords. The documentation provided with the Revelation ODBC Driver lists some of the more commonly used SQL keywords in Revelation systems, such as DESC, ALL, and GROUP.

Dictionary Settings

In the Revelation world, we have been spoiled for many years because we've lived in a datatype-less environment. Datatypes 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 datatyped correctly, or errors can occur. So, some tips are listed below:

Datatyping 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'll 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.

ABSOLUTE	DESC	LANGUAGE	PRIVILEGES	TRANSACTION
ACTION	DOMAIN	LAST	PUT	UPDATE
ADMIN	END	LEVEL	READ	UPPER
ALIAS	EXCEPTION	MAX	REFERENCES	USER
ASC	EXISTS	MIN	RELATIVE	VALUE
AUTHORIZATION	FIRST	MINUTE	RIGHT	VALUES
AVG	FOREIGN	MODULE	ROWS	VIEW
BEGIN	FOUND	MONTH	SCHEMA	WHERE
CASE	GRANT	NAMES	SECOND	WRITE
CHECK	HOUR	NULL	SECTION	YEAR
COMMIT	IDENTITY	NUMERIC	SESSION	ZONE
COUNT	INDEX	OPTION	SIZE	CURRENT
INDICATOR	POSITION	SYSTEM_USER	CURRENT_DATE	INTERVAL
PRIMARY	TIME	DATE	KEY	PRIOR
TIMESTAMP

This is only a partial list of keywords.

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:

Microsoft Query screen shot.

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.

Microsoft Query Wizard - choose columns screen shot.

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

Microsoft Query Wizard - table options screen shot.

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