Equivalent functions in MySQL that exist in SQL Server

Question

In an EXIT HANDLER block, what are the MySQL equivalents of

OBJECT_SCHEMA_NAME(@@PROCID) + '.' + OBJECT_NAME(@@PROCID)
ERROR_MESSAGE()

Edit

What I want to do. In SQL Server I'd have

CREATE PROC ...
AS
SET NOCOUNT, XACT_ABORT ON;

BEGIN TRY
 ...
END TRY
BEGIN CATCH
   ROLLBACK etc
   EXEC someGenericProc @@PROCID, @errmsg OUTPUT;
   RAISERROR ( @errmsg, 16, 1);
END CATCH
GO

CREATE PROC someGenericProc
   @LoggingProcID int
AS
..
SELECT
    @LoggingObject = OBJECT_SCHEMA_NAME(@LoggingProcID ) + '.' + OBJECT_NAME(@LoggingProcID),
    @ErrorObject = ERROR_PROCEDURE(),
    @ErrorMessage = ERROR_MESSAGE();

-- some processing to deal with nesting etc

INSERT SomeErrorTable (...)
VALUES (@LoggingObject, @ErrorObject, @ErrorMessage)
...
GO

Cheers

not sure there are equivalents. Are signals ( http://dev.mysql.com/doc/refman/5.5/en/signal.html#signal-condition-information-items ) and handlers( http://dev.mysql.com/doc/refman/5.5/en/declare-handler.html )what you're trying to accomplish? — Derek Downey, Sep 14 '11 at 13:42
@Dtest: Looks lioke. The handlers are there but I wanted to have some logging and automation. I've found code that errors with no idea of what failed, but then logs start, maybe success itself using a string literal from the code that was copy/pasted — gbn, Sep 14 '11 at 15:09

Derek Downey · Accepted Answer · 2011-09-15T12:46:25.923

I believe SIGNAL (introduced in MySQL 5.5) is what you want, for automatic logging.

However, the current implementation cannot use the various conditions such as SCHEMA_NAME after the SIGNAL executes:

Other condition information items can be set, but currently have no effect, in the sense that they are not accessible from error returns. For example, you can set CLASS_ORIGIN in a SIGNAL statement, but cannot see it after SIGNAL executes

The only available information is the MYSQL_ERRNO and MESSAGE_TEXT, so if you want the schema_name, you'll have to include that in your MESSAGE_TEXT.

Update From the SIGNAL/RESIGNAL worklog

Since /include/mysqld_error.h says "#define ER_NO_DB_ERROR 1046" MESSAGE_TEXT will be 'No database selected'. Or, if language = German, 'Keine Datenbank ausgewhlt'. Things get trickier if the default message has '%s' in it. We won't support the above.

So you can set a signal to: SIGNAL SQLSTATE '22012' SET MYSQL_ERRNO=1365, MESSAGE_TEXT='Division by 0' explicitly, but it won't automatically inherit the text and errornumber just from the sqlstate.

Caveat: I was able to get some ERROR information using RESIGNAL. Consider this:

DROP PROCEDURE IF EXISTS p;
DELIMITER |
CREATE PROCEDURE p ()
BEGIN
  DECLARE EXIT HANDLER FOR SQLEXCEPTION  
  DROP TABLE xx;
END|
DELIMITER ;
CALL p();

Query OK, 0 rows affected (0.00 sec)

However with this:

DROP PROCEDURE IF EXISTS p;
DELIMITER |
CREATE PROCEDURE p ()
BEGIN
  DECLARE EXIT HANDLER FOR SQLEXCEPTION
  BEGIN
    RESIGNAL;
  END;
  DROP TABLE xx;
END|
DELIMITER ;
CALL p();

ERROR 1051 (42S02): Unknown table 'xx'

Example (slightly modified) found from RESIGNAL documentation

Update2

Alas, it's not yet possible to create a generic exception handler. Here is a 6-year-old feature request for it: http://bugs.mysql.com/bug.php?id=11660

The (relatively) good news is, the fix is to create the ability to view the diagnostics stack. This is good because the pre-requisite for this feature is the SIGNAL/RESIGNAL mentioned above. The bad part about it is the worklog for the diagnostics stack is not publicly visible yet.

So for now you'd have to create an exception handler for each state your procedure would come across to log into a table, and only log the error number (hard-coded, as it's not available by the handler) and maybe the hard-coded message text.

Thanks, will take a look. Can MESSAGE_TEXT be set by the system? And not by my code. eg "unique key violation" or "divide by zero" etc — gbn, Sep 14 '11 at 17:55
Thanks I also was trying show errors, say in a function or such, then write from that — gbn, Sep 14 '11 at 19:20
@gbn write to what? (btw, for division by zero, set sql_mode='ERROR_FOR_DIVISION_BY_ZERO' to get a warning and the procedure will return that without any handlers if you issue SELECT 1/0; ) — Derek Downey, Sep 14 '11 at 19:31
A table so the actual error is logged for later use. Or for when I get "it's broken" from a user and nothing else. — gbn, Sep 15 '11 at 04:26
Thanks (RE)SIGNAL is useful. I'm finding MySQL has more "cons" than "pros" compared to other engines... — gbn, Sep 17 '11 at 16:49

Equivalent functions in MySQL that exist in SQL Server

Edit

1 Answers1

Linked