Overview

An Open Database Connectivity (ODBC) interface is provided with JADE. This enables you to apply Structured Query Language (SQL) statements to access a relational view of your JADE database and to maintain ad hoc indexes. You can use the ODBC interface for inquiries using products such as Crystal Reports.

You can use ad hoc indexes to optimize ad hoc queries without requiring database reorganization. The ODBC driver uses available ad hoc indexes if they exist. When it has established which, if any, indexes to use for a query, indexes are session read-locked in OID order, so that indexes are not updated while the ODBC driver is building the result set.

These indexes are used if the query requires AllInstances with a WHERE clause that includes the properties of the defined index keys; for example:

SELECT * FROM Invoice WHERE Invoice.BuyDate > ‘1/1/13’

If an ad hoc index exists for Invoice with a key of BuyDate, this index is used instead of AllInstances. In addition the, ODBC driver handles joins between different tables when there is an ad hoc index that fits the join (equality tests only); for example:

SELECT * FROM Withdrawal, Deposit

WHERE Withdrawal.date > ‘1’1’13’ AND Withdrawal.amount = Deposit.amount

If there was an ad hoc index over WithDrawal by date and another one on Deposit by amount, the ODBC driver looks up all WithDrawal instances for the date range and directly access all deposits for the WithDrawal amount via the Deposit amount ad hoc index.

The OidFieldSeparator parameter in the [JadeOdbc] section of the JADE initialization file on the server enables you to customize the OID field separator with a single punctuation or similar character (for example, @). This can be useful when the default OID String values that are produced are misinterpreted by a third‑party tool as a different ODBC type such as a Decimal.

The following sections contain information about relational views and ad hoc indexes for ODBC.

For details about ODBC inquiries, including installing an ODBC driver, see "Obtaining a Relational View of Your JADE Database", in Chapter 2 of the JADE External Interface Developer’s Reference. For details about the ODBC reserved words, see Appendix A of the JADE External Interface Developer’s Reference.