Foxhound is the better* Database Monitor for SQL Anywhere. *better: More thorough, more relevant, more effective. ...more Alerts, more All Clears, more details, more control in your hands.

Evaluating Validations

"How can I check to see if what the user typed in the current DataWindow column will pass the validation check? I don't want to call AcceptText()."

Most folks know that the DataWindow Describe() function can be used to obtain information about individual columns. This is true of the validation expression: If you know the column name you can get the expression into a string as follows:

   ls_validation = dw_product.describe &
      ( "unit_price.validation" )

Here's an example of what the Describe() call returns:

   "real ( GetText() ) <= 100.00"

What many folks don't know is that you can also call Describe() to dynamically evaluate DataWindow expressions. This is done by nesting a call to Evaluate() within the argument to Describe(), passing both the expression and row number to Evaluate() as follows:

   describe ( "evaluate ( '{expression}', {row} ) " )

For validation expressions the Describe-Evaluate pair will return the string "true" or "false" depending on whether the validation succeeded or failed. Here's a sample script:

   long    ll_row
   string  ls_column_name
   string  ls_get_text
   string  ls_validation
   string  ls_validation_message
   string  ls_evaluation

   ll_row         = dw_product.GetRow()
   ls_column_name = dw_product.GetColumnName()
   ls_get_text    = dw_product.GetText()

   ls_validation = dw_product.describe &
      ( ls_column_name &
      + ".validation" )

   if ( ls_validation = "?" ) &
   or ( ls_validation = "!" ) then
      ls_evaluation = "?"
   else
      ls_evaluation = dw_product.describe &
         ( "evaluate ( '" &
         + ls_validation &
         + "', " &
         + string ( ll_row ) &
         + " )"  )
   end if

   ls_validation_message = dw_product.describe &
      ( ls_column_name &
      + ".ValidationMsg" )

   ClipBoard &
      ( "row number  = " + string ( ll_row ) + "~r~n" &
      + "column name = " + ls_column_name + "~r~n" &
      + "GetText()   = " + ls_get_text + "~r~n" &
      + "validation  = " + ls_validation + "~r~n" &
      + "evaluation  = " + ls_evaluation + "~r~n" &
      + "message     = " + ls_validation_message )

Here's what the clipboard contained after the user entered 3.35 in the unit_price column:

   row number  = 1
   column name = unit_price
   GetText()   = 3.35
   validation  = real ( GetText() ) <= 100.00
   evaluation  = true
   message     = 'Unit Price must be $100.00 or lower.'

Here's an example of a properly formatted value that happens to fail the range check:

   row number  = 1
   column name = unit_price
   GetText()   = 200.00
   validation  = real ( GetText() ) <= 100.00
   evaluation  = false
   message     = 'Unit Price must be $100.00 or lower.'

Caveat Emptor!

AcceptText() does more than just evaluate the validation expression. It also checks to make sure the user didn't enter garbage into the column. By calling Evaluate() instead of AcceptText() your program bypasses this extra check. Since the Real() function returns zero when the argument is not a valid number, the validation expression returns true when the user enters "xxx" into unit_price:

   row number  = 1
   column name = unit_price
   GetText()   = xxx
   validation  = real ( GetText() ) <= 100.00
   evaluation  = true
   message     = 'Unit Price must be $100.00 or lower.'

To deal with this problem you can include type checking logic in the validation expression itself as follows:

   row number  = 1
   column name = unit_price
   GetText()   = xxx
   validation  = IsNumber ( GetText() ) and real ( GetText() ) <= 100.00
   evaluation  = false
   message     = 'Unit Price must be $100.00 or lower.'