Kbase P15800: How Progress/400 DataServer handles SQL NULL values
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
09/06/2005 |
|
Status: Verified
GOAL:
How Progress/400 DataServer handles SQL NULL values
GOAL:
AS/400 Allow PROGRESS Unknown Value with DataServer
GOAL:
Workaround for the legacy support for the Progress Unknown Value
FACT(s) (Environment):
IBM AS/400 (RISC)
Progress/400 DataServer
FIX:
Progress/400 DataServer 8.0C (7.3C30) introduces support for SQL NULL value, which works with client versions starting with 8.1A. The SQL NULL is meant to replace the previous use of the Progress Unknown Value (?). There is a new parameter ALWPROUNK "Allow PROGRESS Unknown Value" in DUPPRODB command where the user can choose the "old" or "new" behavior.
The aim was that existing schemas created with DataServer 7.3C20 or previous work with this parameter to *YES to allow backward compatibility. Unfortunately, there is a bug which makes this
behavior completely on the contrary.
The result is that unknown values stored in the database are not seen as such with the new 8.1A or later client. Or trying to create new records with ASSIGN, the client receives the error:
Unable to update field at offset <offset> in table <table>. (1735)
These and other minor problems with Unknown Values have been completely solved starting in version 8.0C40 for the DataServer and 8.1B for the client.
Progress always recommends to upgrade to the newer release, however there is a workaround which might be useful until the newer releases arrive. It consists in changing a flag in the server schema and in the schema holder(s). To fix the server schema connect the schema holder and AS/400 database and run this code:
FIND FIRST P__DB EXCLUSIVE-LOCK.
P__DB._Db-Misc1[4] = 1.
To fix schema holder, connect it and run this code:
FIND FIRST _Db WHERE _Db._Db-type = "AS400" EXCLUSIVE-LOCK.
ASSIGN _Db._Db-misc1[4] = 1.
Once upgraded to the newer Progress releases, the CVTSRVSCH utility will fix the server schema and the synchronization the schema holder.
Another error:
AS/400 data mapping error (invalid field value). (4601)
may appear even when the workaround is applied. This is caused when a field is type DATE *ISO, the initial value is ?, and the field is non Null Capable. When a CREATE statement is executed on
the table, this error is displayed.
To solve it, it is necessary to dump all data, delete the table, create it manually with the fields marked as Null Capable, and then load the data.