Obtaining a Relational View of Your JADE Database

The JADE ODBC (Open Database Connectivity) standard driver and thin client driver are provided to enable you to use SQL statements to access a relational view of your JADE database. You can use a JADE ODBC driver with any tool that accesses databases using ODBC (for example, MS Query or Crystal Reports).

The JADE Relational Views Wizard enables you to create relational views of your JADE database. For details about using this wizard to define relational views, see Chapter 9, "Defining ODBC Inquiry Relational Views and Ad Hoc Indexes", in the JADE Development Environment User’s Guide.

For guidelines about query optimization, soft attributes, and thin client and application server queries, see the Relational Queries Using ODBC white paper on the JADE Web site at https://www.jadeworld.com/jade-platform/developer-centre/documentation/white-papers.

The JADE ODBC drivers are installed as part of the JADE installation process. When you have created a relational view of your JADE database, you must then configure an ODBC data source in order to access your relational view. For details, see "Configuring an ODBC Driver", later in this chapter.

The JADE ODBC drivers are invoked by another application, often a third‑party generic tool (for example, a report writer) to access data in the JADE database. A JADE ODBC driver differs from other JADE invocations in that it has no command line arguments passed to it and it has no control over the directory from which it is invoked. It must therefore obtain all of its session parameters from the data source configuration or environment variables.

The JADE ODBC thin client establishes a TCP/IP connection with a user-defined ODBC server application running in a JADE node. The JADE ODBC standard (fat) client runs as a JADE client node, establishing communication with a JADE database server in the same way as any other JADE standard client.

The JADE ODBC driver is a Core Level implementation of an ODBC Version 3.51 driver. The ODBC driver accepts only SQL keywords that are all uppercase characters (for example, LIKE), or optionally keywords with an initial capital letter (for example, Like, but not like or liKe) or keywords that are all lowercase characters (for example, the SQL Server keywords). The case of identifier characters used in a query must be distinct from those in keywords.

The JADE ODBC drivers are available in 32‑bit and 64‑bit versions. If running on a 64‑bit machine, the driver used must match the third‑party tool being used; for example, it may be necessary to install 32‑bit JADE ODBC drivers for use with 32‑bit tools.

From version 2016.0.03 (Service Pack 2), 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.

Time attributes are handled differently in Access 97 (where they are retrieved as a character string) and Access 2000 (where they are retrieved and displayed as a time stamp). This causes differences in the display of time fields.

The ODBC driver supports SQL Minimum Grammar, including the following.

=, >, <, <=, and >= comparison operators
LIKE pattern matching operator
NOT, AND, and OR boolean operators
SELECT, FROM, WHERE, and ORDER BY clause

In addition to the SQL Minimum Grammar, the ODBC driver supports the following.

GROUP BY and HAVING clauses
The following aggregate functions
- COUNT(*) and COUNT([ALL|DISTINCT] columnName)
- SUM([ALL | DISTINCT] columnName) and AVG([ALL | DISTINCT] columnName)
  - Valid for numeric types only
  - columnType is dependent on type of columnName
  - SQL_TINYINT or SQL_INTEGER : columnType is SQL_INTEGER
  - SQL_BIGINT : columnType is SQL_BIGINT
  - SQL_DECIMAL(p, s) : columnType is SQL_DECIMAL(23, s)
- MIN([ALL | DISTINCT] columnName) and MAX([ALL | DISTINCT] columnName)
  - Valid for all types except SQL_BIT
  - columnType is the same as columnName type
Simple Joins and left and right outer Joins, but it does not support Nested Queries
UNION clause, which combines the results of two queries when the queries have the same number of columns and the selected columns are of the same data type
SELECT clause AS<column-name> column aliasing predicate that can be used in the ORDER BY clause

For SQL Server queries, you must enclose all table and field names that conflict with SQL keywords with double quotation ("") characters. (See the Query Analyzer help for details.) The third sheet of the System DSN wizard in the ODBC Data Source Administrator has the configurable Use ANSI quoted identifiers check box, which enables RPS to interrogate the RPS ODBC data source that it uses to determine if to quote SQL identifiers (that is, table, column, and procedure names). This allows reserved T‑SQL keywords to be used in SQL statements, which is useful if you want a property to have the same name as a T‑SQL reserved word.

When importing tables from JADE using Microsoft Access, do not select any field as the Unique Record Identifier. Although the object identifier (oid) seems the natural choice for this, Access performs validation that causes unnecessary problems and it does not work at all with Access 2000 and Windows 98. The Unique Record Identifier is useful to Access only when updating tables, which is not allowed in the JADE database.

When using the JADE ODBC standard client and setting the TerminateProcessOnDisconnect parameter in the [JadeClient] section of the JADE initialization file to true, unwanted side-effects may occur when the connection to the JADE database server is disconnected, as the underlying program that opened the connection is also terminated.

When using the JADE ODBC standard client, we recommend that cache coherency is used in the ODBC node, to ensure use of the latest edition.

The output of query execution tracing to the jommsg.log file is switched on or off using the QueryExecutionTraceOn parameter in the [JadeOdbc] section.

The values returned by the JADE ODBC driver for a Date attribute are listed in the following table.

Attribute Value	Returned Value
Valid date	Valid date
Value not initialized	Null value
Value invalid	Current date (with non‑fatal warning)

The values returned by the JADE ODBC driver for a Time attribute are listed in the following table.

Attribute Value	Returned Value
Valid time	Valid time
Value not initialized	00:00:00 (default value == 0)
Value invalid	00:00:00 (with non‑fatal warning)

The values returned by the JADE ODBC driver for a TimeStamp attribute are listed in the following table.

Attribute Value	Returned Value
Valid date/time	Valid date/time
Value not initialized	Current date 00:00:00
Date not set/time set	Current date and time (as in ODBC specifications)
Date set/time not set	Valid date/00:00:00
Invalid values	Current date 00:00:00 (with non‑fatal warning)