Kbase P122289: Error 1461 when trying to query a DB2/400 table through the schema holder
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
9/17/2008 |
|
Status: Verified
SYMPTOM(s):
Error 1461 when trying to query a DB2/400 table through the schema holder
Schema holder does not match database schema -- file field . (1461)
FACT(s) (Environment):
The schema holder was created using the conversion method using Progress/400 to OpenEdge/ODBC Transformation tool
The above error is apparent with few tables only
After performing Update/Add Table Definition from the ODBC Utility, the foreign data type of the fields on the table changed from varchar to binary
IBM AS/400 (RISC)
ODBC DataServer
Windows
OpenEdge 10.x
CAUSE:
The CCSID of this table and each of the fields is set to *HEX (65535) which represents the bit data instead of "real" character data. The CCSID(65535) means that data in all such character fields should not be converted, such as from It ASCII to EBCDIC, and instead, must be treated as binary strings.
On the other hand Progress/400 is a specialized product developed only to work with IBM AS/400 (RISC). When the table is pulled to Progress/400 dictionary library, it mapped the fields foreign data type of the problem table as character. With Progress/400, there is no ODBC involved, and all the translation of the ASCII data is to EBCDIC happened inside the as/400 to a proper representation for the table. The Progress/400 to OpenEdge/ODBC Transformation tool only looks at the P__FILES in the progress/400 dictionary library on the as/400 to create the progress formatted .df file. Therefore, it formatted the foreign data type as varchar which caused the 1461 error when a 4GL query was performed against the table. Before DataServer performs query, it performs the schema check between the foreign data source table and the table defined in the schema holder. Then when the schema mis-match error is displayed.
Later when Update/Add Table Definition was performed using the ODBC DataServer utilities, it changed to the correct foreign type. Since the OpenEdge 10 ODBC DataServer uses the ODBC driver, it will treat the data as binary and no translation will be done. Even though the schema mis-match error will disappear after the update/add table definition as mentioned above, the data will be displayed as hexadecimal during the browse and during the insert and update, the error 6193 ("A binary string should be prefixed with 0x") will be apparent on the client.
FIX:
In order to fix the issue change the CCSID of the table to 37.