One of the most useful features of CA IDMS is its ability to access network records using SQL. These network records are often redefined and have multiple formats, causing problems when data in a record occurrence is not in the correct format for the type of the SQL column derived from the network schema record definition. In particular, decimal fields are sometimes redefined as character fields. They can contain spaces, low values, or other data that is not a valid packed or zoned decimal value. This violates the data integrity provided by the CA IDMS SQL Option, which ensures that data stored in an SQL table is valid for the column type.
In this situation, an application like the Online Command Facility (OCF), with direct access to the fetch buffer returned by CA IDMS, can display a special indicator for the value (for example, a string of asterisks). Interfaces like ODBC and JDBC, however, are expected to convert the data to the format requested by the application, and data integrity is assumed.
CA IDMS Server provides an Invalid Decimal option to handle this situation, allowing the client to specify what the ODBC and JDBC drivers should return to the application when invalid data is received from CA IDMS. Options are:
(Default) Drivers return an error to the application. The ODBC driver returns SQL_ERROR to the application, which can use the SQLError function to retrieve the associated error message. The JDBC driver returns a SQLException containing the error message. No value is returned in the output buffer provided by the application.
The drivers attempt to return NULL for the column value. The ODBC driver sets the length/indicator value to SQL_NULL_DATA. It returns an error if the pointer to the length/ indicator buffer supplied by the application is 0. The JDBC driver returns either 0 or null, as specified by the ResultSet.getXXX method, or true for ResultSet.wasNull. The drivers attempt to return NULL even if the column in the result set is NOT NULL.
The driver always returns zero. This can be useful when the application does not provide a length/indicator buffer for a column that is NOT NULL.
The value returned to the application is undefined. This option is provided for compatibility with previous versions of the ODBC driver, and is not supported by the JDBC driver.
The ODBC driver prints a message to the log when tracing is enabled, no matter which option is selected.
The Return Null option does not work if the application does not check for a NULL value when the result set column is defined as NOT NULL. By default, an SQL column returned for a network record is treated as NOT NULL because there is no NULL indicator field in the database. Since a result set column that is an expression does allow NULL values, one solution is to enclose the column name in a VALUE scalar function, as in the following example:
SELECT VALUE(WARD_TOTAL_0430) FROM EMPDEMO."HOSPITAL-CLAIM"
This forces CA IDMS to build a result set that includes a NULL indicator for the WARD-TOTAL-430 field in the HOSPITAL-CLAIM network record, and the drivers report that the column allows NULL values.
It may be convenient to define views on network records that wrap DECIMAL and NUMERIC columns in VALUE functions, at least when the database is known to contain invalid data.
|
Copyright © 2009 CA.
All rights reserved.
|
|