This section describes the ways you can manipulate validation rules in the Database painter.
To create a new validation rule
-
In the Extended Attributes view in the Database painter, right-click Validation Rules and select New from the pop-up menu.
The Validation Rule view displays in the Properties view.
-
Assign a name to the rule, select the datatype of the columns to which it applies, and customize the error message (if desired).
For information, see Customizing the error message.
-
Click the Definition tab and define the expression for the rule.
For information, see Defining the expression.
You can use this rule with any column of the appropriate datatype in the database.
To modify a validation rule
-
In the Database painter, open the Extended Attributes view.
-
In the Extended Attributes view, open the list of validation rules.
-
Double-click the validation rule you want to modify.
-
In the Validation Rule view, modify the validation rule as desired.
For information, see Defining the expression and Customizing the error message.
To associate a validation rule with a column in the Database painter
-
In the Database painter (Objects view), position the pointer on the column, select Properties from the column's pop-up menu, and select the Validation tab.
-
Select a validation rule from the Validation Rule drop-down list.
The column now has the selected validation rule associated with it in the extended attribute system tables. Whenever you use this column in a DataWindow object, it will use this validation rule unless you override it in the DataWindow painter.
To remove a validation rule from a column in the Database painter
-
In the Database painter (Objects view), position the pointer on the column, select Properties from its pop-up menu, and select the Validation tab in the Properties view.
-
Select (None) from the list in the Validation Rule drop-down list.
The validation rule is no longer associated with the column.
A validation rule is a boolean expression. PowerBuilder applies the boolean expression to an entered value. If the expression returns "true", the value is accepted. Otherwise, the value is not accepted and an ItemError event is triggered.
What expressions can contain
You can use any valid DataWindow expression in validation rules.
Validation rules can include most DataWindow expression functions. A DataWindow object that will be used in PowerBuilder can also include user-defined functions. DataWindow expression functions are displayed in the Functions list and can be pasted into the definition.
For information about these functions, see DataWindow Expression Functions in DataWindow Reference.
Use the notation @placeholder (where placeholder is any group of characters) to indicate the current column in the rule. When you define a validation rule in the Database painter, PowerBuilder stores it in the extended attribute system tables with the placeholder name. At runtime, PowerBuilder substitutes the value of the column for placeholder.
Pasting the placeholder
The @col can be easily used as the placeholder. A button in the Paste area is labeled with @col. You can click the button to paste the @col into the validation rule.
An example
For example, to make sure that both Age and Salary are greater than zero using a single validation rule, define the validation rule as follows:
@col > 0
Then associate the validation rule with both the Age and Salary columns. At runtime, PowerBuilder substitutes the appropriate values for the column data when the rule is applied.
If you are defining the validation rule for a character column, you can use the Match button on the Definition page of the Validation Rule view. This button lets you define a match pattern for matching the contents of a column to a specified text pattern (for example, ^[0-9]+$ for all numbers and ^[A-Za-z]+$ for all letters).
To specify a match pattern for character columns
-
Click the Match button on the Definition page of the Validation Rule view.
The Match Pattern dialog box displays.
-
Enter the text pattern you want to match the column to, or select a displayed pattern.
-
(Optional) Enter a test value and click the Test button to test the pattern.
-
Click OK when you are satisfied that the pattern is correct.
For more on the Match function and text patterns, see the section called “Match” in DataWindow Reference.
When you define a validation rule, PowerBuilder automatically creates the error message that displays by default when users enter an invalid value:
'Item ~'' + @Col + '~' does not pass validation test.'
You can edit the string expression to create a custom error message.
Different syntax in the DataWindow painter
If you are working in the DataWindow painter, you can enter a string expression for the message, but you do not use the @ sign for placeholders. For example, this is the default message:
'Item ~'' + ColumnName + '~' does not pass validation test.'
A validation rule for the Salary column in the Employee table might have the following custom error message associated with it:
Please enter a salary greater than $10,000.
If users enter a salary less than or equal to $10,000, the custom error message displays.
