VALIDATE statement is causing a value not to be stored in the database

Question

I have been dealing with an issue recently and am stuck. A particular program(a compiled .p) has codes written where its trying to save values to a particular field in the db.

Below this there is a VAILDATE statement on that table. This is causing the value to revert while doing an update operation. I messaged before and after VALIDATE and can see the value updated before VALIDATE and reverted just after VALIDATE. Please can someone help me on this. Many thanks in advance.

Answer 1

From the documentation:

VALIDATE statement:
Verifies that a record complies with mandatory field and unique index definitions. 

So, if your UPDATE is reverted after VALIDATE it means that at least one the filed value is not as per constraints. See your table schema and field values. Check if they are okay. It is not clear from your question if you have access to code or not. If you have access to the code and if VALIDATE statement has NO-ERROR, try removing NO-ERROR. If you remove NO-ERROR you can see error message and it might have some clues in it.

Answer 2

I did simulate your problem. Most likely, the assign violates an index as well as change the value of the field you're monitoring. The whole transaction fails when OE resolves the VALIDATE and it doesn't pass. The record is then restored (rollback) to its last valid value, along with the old value to the field you're looking at. As far as I can tell, that is expected.

To get around that behavior, I believe you should

a) Rewrite the block and validate the record after changing ONLY the unique fields (though this doesn't really solve it. It just saves you from doing operations that will be undone later); or

b) Save the old values to a temp-table record and copy them back if you still want them when VALIDATE fails.

Hope this gives you some ideas, at least.

Answer 3

Thanks for all your helps. I am thankful to be part of such a great community.

I have now found what was causing this. The document "https://documentation.progress.com/output/ua/OpenEdge_latest/index.html#page/dvref/validate-statement.html" has the answer in the last point. I was unaware of this behaviour of VALIDATE Statement.

Thanks a lot all again.

Answer 4

Ok. So the issue was "VALIDATE STATEMENT one .p file, was causing a value rolledback. The root cause was as per the last point mentioned in the link below:

https://documentation.progress.com/output/ua/OpenEdge_latest/index.html#page/dvref/validate-statement.html

One of the triggers has a code which was intentionally rolling back the value.

As mentioned above, reason is that VALIDATE statement fires up all the related triggers.