Results 1 to 4 of 4

Thread: Catching errors

  1. #1
    Join Date
    Jun 2004
    Posts
    57

    Unanswered: Catching errors

    I did some plpgsql functions. I have my own user table containing some custom errors. My intention is to throw this errors to the application. To do so, I must catch the original PostGreSQL error and, based oin this ID, to send the custom error with a raise or something. In SQL Server it was very simple becuase I get the @@error from sysmessages and, based on that value, I sent the custom error. In Oracle and DB2 this kind of error treatment was working too. I am not sure how can I do this in PostGreSQL. Does anyone know how can I catch the original PostGreSQL error?

    I will give an example from SQL Server:
    CREATE PROCEDURE NMSBin_rid (@v_id [NUMERIC](38, 0))
    AS

    DECLARE @ERROR INT
    SELECT @ERROR = 0

    IF NOT EXISTS (SELECT * FROM NMSBin WHERE id = @v_id)
    BEGIN -- This 50013 is a custom error.
    RAISERROR (50013, 16, 1, 'NMSBin_rid')
    RETURN 1
    END
    ELSE
    BEGIN
    SELECT * FROM NMSBin WHERE id = @v_id
    SELECT @ERROR = @@ERROR

    IF @ERROR <> 0
    BEGIN -- This procedure raise the custom error based on @@ERROR
    EXEC ERR_Handle_Errors@ERROR, 'SELECT', 'NMSBin_rid', 'NMSBin'
    RETURN 1
    END
    ELSE
    RETURN 0
    END

  2. #2
    Join Date
    Jun 2004
    Posts
    57

    Catching SQL State value

    For my custom treatment of errors I must catch the sql state value and, based on this value, to sent to the application my custom value. Does anyone can give me an example (in a function) of using the concrete value of sql state (not generally terms like not found or something else)?
    In DB2 or SQL Server I can based on the exactly values of errors (sql state or @@error).
    I found the postgre sql state values in the postgresql documentation but I cannot find any example of using these values. The documentation is a little short and with few and incomplete examples or I mabe didn't descover them.
    Thanks in advanced for your help.

    Stefan

  3. #3
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    Maybe the RAISE statement...

    Code:
    RAISE NOTICE "50013: Custom Error from PostgreSQL Server"
    I'm just starting with PostgreSQL, too, so this was a WAG.
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


  4. #4
    Join Date
    Jun 2004
    Posts
    57

    Trick

    Thanks. Finally this trick came into my mind too, but is not very elegant.
    In JDBC will be extracted the ID (50013) but raise will return the same error id, so in fact this is not a classic raise. A classic raise means, first of all, an ID and this ID to be catch by the application. But in PostGreSQL I don't think is possible. So the only think to be done is to based on the string and catch the id generated in the string.
    Any way thanks for your help.

    Stefan

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •