Solved

Error Handling

Posted on 2011-02-22
6
893 Views
Last Modified: 2012-06-27
HI
I am porting a database from MS SQL Server to Postgres.
One of the problems I have is how to get error information resulted from running a stored procedure.
The attached code does not run in Postgres , I need equivalents to the following MS SQL Server functions:
 
ERROR_NUMBER()
ERROR_SEVERITY();
ERROR_STATE();
ERROR_LINE();
ERROR_PROCEDURE()

Thanks for your help.

CREATE OR REPLACE FUNCTION rethrowerror()
  RETURNS void AS
$BODY$
   DECLARE
   v_ErrorMessage  VARCHAR(4000);
   v_ErrorNumber  TEXT;
   v_ErrorSeverity  INTEGER;
   v_ErrorState  INTEGER;
   v_ErrorLine  INTEGER;
   v_ErrorProcedure  VARCHAR(200);
BEGIN
   IF 'Error' /*NOT SUPPORTED ERROR_NUMBER()*/IS NULL then
      RETURN;
   end if;
     
    /* Assign variables to error-handling functions that
       capture information for RAISERROR. */

   v_ErrorNumber := 'Error' /*NOT SUPPORTED ERROR_NUMBER()*/;
   v_ErrorSeverity := ERROR_SEVERITY();
   v_ErrorState := ERROR_STATE();
   v_ErrorLine := ERROR_LINE();
   v_ErrorProcedure := coalesce(ERROR_PROCEDURE(),'-'); 

    /* Building the message string that will contain original
       error information. */

   v_ErrorMessage := N'Error %d, Level %d, State %d, Procedure %s, Line %d, ' ||
   'Message: ' || 'Error'; 
   RAISE EXCEPTION '% % % % % % ',v_ErrorMessage,v_ErrorNumber,v_ErrorSeverity,v_ErrorState,v_ErrorProcedure,
   v_ErrorLine;
END; $BODY$

Open in new window

0
Comment
Question by:elimesika
  • 4
  • 2
6 Comments
 
LVL 17

Expert Comment

by:dbaSQL
ID: 34958102
good detail on postgres sql error codes:  http://www.postgresql.org/docs/8.1/static/errcodes-appendix.html
0
 
LVL 19

Author Comment

by:elimesika
ID: 34958781
Thanks
That's not answering my question.
I have asked how to get this info at runtime when an error occurs during SP execution.
0
 
LVL 17

Expert Comment

by:dbaSQL
ID: 34961622
I'm sorry, I was responding to this:

>>I need equivalents to the following MS SQL Server functions:
ERROR_NUMBER()
ERROR_SEVERITY();
ERROR_STATE();
ERROR_LINE();
ERROR_PROCEDURE()

Let me see if I can provide something more.
0
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
LVL 17

Accepted Solution

by:
dbaSQL earned 500 total points
ID: 34965277
Ok, I don't know if it is as clear as the SQL native error handling, but trapping the errors can be done with a BEGIN block, and and EXCEPTION.  It is described well in this link:  
http://www.network-theory.co.uk/docs/postgresql/vol2/TrappingErrors.html

The error conditions, howerver, is where it seems to get a little ugly.  See here:

>>>>
But if an error occurs within the statements, further processing of the statements is abandoned, and control passes to the EXCEPTION list. The list is searched for the first condition matching the error that occurred. If a match is found, the corresponding handler_statements are executed, and then control passes to the next statement after END. If no match is found, the error propagates out as though the EXCEPTION clause were not there at all: the error can be caught by an enclosing block with EXCEPTION, or if there is none it aborts processing of the function.
>>>>

This is a listing of the complete PostgreSQL Error Codes.  From what I've read, the first two digits of the error number indicate the error class, while the last three digits indicate the condition (or cause).
http://www.network-theory.co.uk/docs/postgresql/vol1/PostgreSQLErrorCodes.html

Also, I did find the example below, which is a little closer to the details you're looking for.  See here:
http://www.mt-soft.com.ar/2007/12/21/a-primer-on-postgres-exception-handling-for-the-php-developer-part-2/
CREATE OR REPLACE FUNCTION f_ex1 (
    INOUT  my_query text
) AS
$body$
BEGIN
        EXECUTE my_query INTO my_query;
    EXCEPTION
        WHEN OTHERS THEN
            RAISE NOTICE 'an EXCEPTION is about to be raised';
            RAISE EXCEPTION 'NUM:%, DETAILS:%', SQLSTATE, SQLERRM;
END;
$body$
LANGUAGE PLPGSQL;

Open in new window

0
 
LVL 19

Author Closing Comment

by:elimesika
ID: 35012700
Thanks a lot !
0
 
LVL 17

Expert Comment

by:dbaSQL
ID: 35012739
My pleasure.  Glad to have helped.  :-)
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now