Extract user-defined error message from exception

Question

Inside my stored procedure, I just checked for a specific condition, and I will throw an Exception if that condition fails. as shown below

Raise Exception '%', 'Hello world'; 

It works fine, but the Hello world error message also comes with some other error code, such as the ones below in my interface,

 DA00014:ERROR: P0001: Hello world |___________________| | I really dont want this part to get displayed in the front end. 

Is there a suitable way to filter / retrieve the correct message from the generated exception.?

[ Note: Please do not invite me to perform some string manipulations.]

 DBMS : POSTGRESQL 9.0.3 Driver : Npgsql 1.0 Front End : VB.net 

Answer 1

Npgsql fastpath

If NpgsqlException.Errors is null in your exception handler, you most likely got an error in the Npgsql Fastpath (I still consider this an error). Here is the violation line in version 2, but the same problem exists in version 1.

It basically boils down to this code, where the correct NpgsqlError built, and then thrown when an exception is raised, calling ToString() on it.

 NpgsqlError e = new NpgsqlError(conn.BackendProtocolVersion, stream); throw new NpgsqlException(e.ToString()); 

A fix would be as simple as changing the code to this and using the already supported ability of NpgsqlException to take a list of NpgsqlError in the constructor.

 NpgsqlError e = new NpgsqlError(conn.BackendProtocolVersion, stream); throw new NpgsqlException(new ArrayList(e)); 

So, you cannot do this without string manipulations unless you compile your own version of Npgsql, fixing the problem.

Npgsql

If you are not using Fastpath, then the created NpgsqlException object contains the Errors property, which must be non-null. This is an example of extracting a message from the first error.

 (ex.Errors[0] as NpgsqlError).Message 

Answer 2

try it..
Inside your store procedure, add an extra character to the message. eg

 Raise Exception '%', '|Hello world'; 

On the front side, split your message on character "|", and display the second index of the array. eg

  Try ' your code.. Catch ex As Exception Dim arr As String() = ex.Message.Split("|") If arr.Count > 1 Then MessageBox.Show(Convert.ToString(arr(1))) Else MessageBox.Show(Convert.ToString(ex.Message)) End If End Try 

Answer 3

If I get it, you can try it once.

 IF condition match -- (your condition) BEGIN RAISERROR('Hello world', 16,16) return END 

and in the end

can be used as

 catch(exception ex) { // ex.Message // ex.InnerException.Message }