sa_validate() should return a language-agnostic return value

[VolkerBarth]

We use sa_validate() within system events to validate the database. As such, the procedure's return value is checked for successful runs.

The v12.0.1 doc does state the following:

The procedure returns a single column named Messages. Errors returned during validation appear in the column. If validation succeeds without error, the column contains No error detected.

Well, that's only correct when the SA system language is set to EN.

For German setups, the message is "Keine Fehler gefunden" (and in earlier versions it was "Kein Fehler gefunden"). So, as we are usually running "DBLANG DE", the event code checks for the German message to know whether the validation has succeeded.

You bet I was puzzled when a database on a test system claimed to fail validation - simply as I had run an English version here, and "No errors detected" didn't fit "Keine Fehler gefunden".

---

Suggestion: Please add a second column to sa_validate()'s result set with a numeric return code (a SQLCODE or something similar to the "software component exit codes") that allows a language-agnostic return value check. Say, if the result set would contain something like "error_code = 0" for successful runs, the check would be much easier.

I do not know whether the result set may contain several rows in case there are several validation errors (i.e. a separate entry for each message about an invalid page/whatever) - however, that would still allow each entry to have its own error code.

[MarkCulp · Accepted solution]

This request seems reasonable, and I'm a bit surprised that this issue was not addressed in the initial design (but that was too many years ago to know why it was not handled). I've added this to our list of items to be consider for some future release.

Thanks for making the suggestion.

[VolkerBarth · Accepted solution]

A very late addition: This was changed with version 17, sa_validate() now also returns the bit column "IsValid" (set to 1 for a successful validation) and the column "ObjectName" to name the object that could not be validated. Thanks!