Re: Select client from clients where name<>OK

As noted, ADDPFCST is available to add the CHECK constraint. For constraints I probably would be content to use the SQL ALTER TABLE ADD CONSTRAINT, but I personally would avoid using the ALTER against DDS PF for any changes to the format and instead use the CHGPF SRCFILE() interface.

The failed update or insert activity for the enforcement of a check constraint would be manifest as a generic check constraint error [CPF502F & SQL0545], naming the constraint that was violated. If some specific logic to determine for example what was the first byte not in the list of valid characters, that would need to be deferred to other logic; instead of a routine that validates and then denies & diagnoses the specifics, such a routine could be instead invoked only reactive to the constraint violation. The constraint would not then limit the logic to one place, but the constraint could enforce the rule against the data irrespective of interface to update the data. If the application is assumed to be the only method to change the data, it would be best to avoid having the logic in two places... and is easier to change if the logic is deemed to unforgiving or incorrect.

ADDPFCST FILE(THEFILE) TYPE(*CHKCST)
CST(APP1723bad_chars_in_name)
CHKCST('translate( name , '' '',
''-ABCDEFGHIJKLMNOPQRSTUVWXYZ&'''''' ) = '' ''')

Naming the constraint like APP1723 where that token is a message identifier for the application allows storing the message text and further details. In the above example I combined that naming with some explicit text as part of the name which details the violation.

Regards, Chuck

Vern Hamberg wrote:

FYI - you can add a check constraint to a PF - use ADDPFCST
command. Remember, SQL tables are implemented as physical files,
and there are lots of ways to work with them and get similar
results. I imagine you could even use ALTER TABLE against a
non-SQL PF and add constraints.

David FOXWELL wrote:

CRPence wrote:

FWiW the given expression can be applied to the table as a
CHECK CONSTRAINT if the rule [for what characters can be
included in the string] is important to enforce outside of
the noted RPG program control.

Chuck, it's a DDS PF. But if it was a table with CHECK
CONSTRAINT, would the program have a means of detecting why
the data entered was bad?