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. A more complete list of keywords can be found on Signal42's web site.
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.
