Kbase P169351: SQL: Updating a character array field may set all the array elements to the unknown value ?
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
29/09/2010 |
|
Status: Verified
SYMPTOM(s):
SQL: Updating a character array field may set all the array elements to the unknown value ?
Updating some elements of a character array using the SQL UPDATE statement may corrupt all the array elements and render them all to the unknown value.
Executing the following SQL script to update the array field test-stat of a newly created record in the table t-que will set all the elements of the array to the unknown value ?:
UPDATE PUB."t-que" SET "test-stat" = PRO_ARR_ESCAPE('C') || ';' || PRO_ELEMENT("test-stat",2,10) WHERE "inv-id" = 123123
GO
UPDATE PUB."t-que" SET "test-stat" = PRO_ELEMENT("test-stat",1,1) || ';' || PRO_ARR_ESCAPE('D') || ';' || PRO_ELEMENT("test-stat",3,10) WHERE "inv-id" = 123123
GO
UPDATE PUB."t-que" SET "test-stat" = PRO_ELEMENT("test-stat",1,2) || ';' || PRO_ARR_ESCAPE('E') || ';' || PRO_ELEMENT("test-stat",4,10) WHERE "inv-id" = 123123
GO
UPDATE PUB."t-que" SET "test-stat" = PRO_ELEMENT("test-stat",1,3) || ';' || PRO_ARR_ESCAPE('F') || ';' || PRO_ELEMENT("test-stat",5,10) WHERE "inv-id" = 123123
GO
UPDATE PUB."t-que" SET "test-stat" = PRO_ELEMENT("test-stat",1,4) || ';' || PRO_ARR_ESCAPE('G') || ';' || PRO_ELEMENT("test-stat",6,10) WHERE "inv-id" = 123123
GO
UPDATE PUB."t-que" SET "test-stat" = PRO_ELEMENT("test-stat",1,5) || ';' || PRO_ARR_ESCAPE('H') || ';' || PRO_ELEMENT("test-stat",7,10) WHERE "inv-id" = 123123
GO
UPDATE PUB."t-que" SET "test-stat" = PRO_ELEMENT("test-stat",1,6) || ';' || PRO_ARR_ESCAPE('I') || ';' || PRO_ELEMENT("test-stat",8,10) WHERE "inv-id" = 123123
GO
UPDATE PUB."t-que" SET "test-stat" = PRO_ELEMENT("test-stat",1,7) || ';' || PRO_ARR_ESCAPE('J') || ';' || PRO_ELEMENT("test-stat",9,10) WHERE "inv-id" = 123123
GO
UPDATE PUB."t-que" SET "test-stat" = PRO_ELEMENT("test-stat",1,8) || ';' || PRO_ARR_ESCAPE('K') || ';' || PRO_ELEMENT("test-stat",10,10) WHERE "inv-id" = 123123
GO
UPDATE PUB."t-que" SET "test-stat" = PRO_ELEMENT("test-stat",1,9) || ';' || PRO_ARR_ESCAPE('L') WHERE "inv-id" = 123123
GO
FACT(s) (Environment):
The table records are always created using 4GL/ABL code and may be updated using 4GL/ABL or SQL code.
All Supported Operating Systems
OpenEdge 10.2B
OpenEdge 10.2B01
CAUSE:
Bug# OE00198693
FIX:
Upgrade to OpenEdge 10.2B02 or later. If upgrading to OpenEdge 10.2B02 or later is not feasible, a work around is to set the INITIAL VALUE attribute of the offending array fields to any non blank character. Alternatively, use a table CREATE trigger to initialize all the elements of the offending array field to some non blank character. For example, the following code, which may be inserted in the table CREATE trigger, initializes all the elements of the offending array field test-stat to the DOT '.' character.
DEFINE VARIABLE iLoop AS INTEGER NO-UNDO.
DO iLoop = 1 TO 10:
ASSIGN
test-stat[iLoop] = '.'.
END.