Wednesday, May 19, 2010

Parsing a Custom Error String

You can create your own exception messages within an SQL script using the RAISE keyword. These custom messages are appended to the end of the Advantage Exception that is returned to the client. For example, this script gets an order reference number from the table based on the Order ID supplied to the Stored Procedure. If no reference number is found then the specified Order ID is invalid and a custom error is raised.

IF @OrdRef IS NULL THEN
  RAISE PROCESS_ORDER_ERROR( 1001, 'Order Number ''' + cInput.OrdID
                             + ''' does not exist');
END IF;

If an invalid order number was passed in the following error would be returned.

poQuery: Error 7200: AQE Error: State = HY000; NativeError = 5154;[iAnywhere Solutions][Advantage SQL][ASA] Error 5154: Execution of the stored procedure failed. Error 7200: AQE/>Error: State = 42000; NativeError = 2224; [iAnywhere Solutions][Advantage SQL Engine]An exception is raised in the SQL script. {[PROCESS_ORDER_ERROR] 1001 : Order Number 'C645D242-6912-11DF-8CBD-7659DFD72085' does not exist} ** Script error information: -- Location of error in the SQL statement is: 1203 (line: 49 column: 9)

This very detailed error message is very useful when creating and debugging SQL scripts. However, there are a couple of potential issues. First whenever a stored procedure fails it will return the 5154 native error code. So you need a way to determine if it was an error you raised or if it was a problem with the stored procedure itself. Second if it is a custom error then you only want to return the custom error message.

If you take a quick look you will notice that the custom error message is delimited by curly braces ( { } ). Making it very easy to parse the error message and extract the custom error string. Here is a simple C# function that does just that.

// Gets the custom error message from a stored procedure error 
// if no error then an empty string is returned
private string ParseSPError(string ErrorMessage)
{
  int iStart = 0;
  int iEnd = 0;

  iStart = ErrorMessage.IndexOf("{");
  iEnd = ErrorMessage.IndexOf("}");

  if (iStart > 0 && iEnd > 0)
     return ErrorMessage.Substring(iStart + 1, iEnd - iStart - 1);
  else
     return "";
}

You could also use a Regular Expression to parse out the message. Be sure to use the SingleLine option to ignore any newline characters in the error message

// Gets the custom error message from a stored procedure error 
// if no error then an empty string is returned
private string ParseSPError(string ErrorMessage)
{
  Match match = Regex.Match(ErrorMessage, (?<={).*?(?=}), 
                            RegexOptions.Singleline);
  if (match.Success)
    return match.Value;
  else
    return "";
}

2 comments:

Joachim Dürr said...

if (iStart > 0 && iEnd > 0)
I wouldn't use that since it could fail if the braces are in different order }any text{. Better compare iEnd against iStart instead of 0:
if (iStart > 0 && iEnd > iStart)

Chris Franz said...

Thanks for the feedback, it makes sense to ensure that the curly braces are in the correct order. In this case the string we are parsing is an Advantage error message which will always be formated with the correct pattern.

Checking the order would be much more important if this were a more generic function.