How to propagate MySQL exceptions to Java program?

Question

I have a java program that is calling a MySQL stored procedure that is rolled back when it gets an SQLEXCEPTION. When I added the rollback (exit handler) to the stored procedure the Java program stopped getting the SQL exception.

How can I make sure the SQL exception and MySQL error message are propagated back to the Java program?

Here is my store procedure:

DELIMITER $$

DROP PROCEDURE IF EXISTS up_OMS_insertParticipantOmsOrderOwner $$
CREATE PROCEDURE up_OMS_insertParticipantOmsOrderOwner(
                 IN PID int,
                 IN OwnerName varchar(50),
                 IN DisplayName varchar(50),
                 IN Enabled tinyint(1))

BEGIN
declare exit handler for SQLException
  BEGIN
    rollback;
  END;
start transaction;

if (DisplayName<>'') then
  insert OmsOrderOwner (ParticipantID, OmsOrderOwnerName, DisplayName, Enabled)
  value (PID, OwnerName,DisplayName, Enabled);
else
  insert OmsOrderOwner(ParticipantID, OmsOrderOwnerName, DisplayName, Enabled)
  value (PID, OwnerName,null, Enabled);
end if;

set @OwnerID := @@identity;

insert UserOmsOrderOwnerSubscription (UserID, ParticipantID, OmsOrderOwnerID, Enabled)
select
  userOrderSub.UserId, PID, @OwnerID, 1
from
  Users u,
  UserOmsOrderSubscription userOrderSub
where
  userOrderSub.UserID = u.UserID and
  u.ParticipantID = PID;

commit;

END $$

DELIMITER ;

ChssPly76 · Accepted Answer · 2009-09-17 17:01:24Z

3

Use RESIGNAL statement in your exit handler to rethrow the error.

That said, do you REALLY need to explicitly begin / commit / rollback transaction within your stored procedure? JDBC call will be (should be) done within its own transaction anyway, can you instead rely on it to handle the error / rollback and save yourself some trouble, perhaps?

answered Sep 17, 2009 at 17:01

ChssPly76

101k25 gold badges210 silver badges196 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

richs Over a year ago

yeah we may move the commit and rollback in our java code. unfortunately we can't do that in the short term. Also unfortunate is that we are on version 5.0x of mysql so resignal doesn't work. I can write a signalFunct myself but i won't get the mysql error message.

ZZ Coder · Accepted Answer · 2009-09-17 17:10:12Z

-1

Since you handled the error in STP, it's not an exception anymore. It should be just a normal return status of your call. You should return something from the exit handler, like

declare exit handler for SQLException
  BEGIN
    rollback;
    select 1;
  END;
start transaction;

1 or whatever will be error code for rollback.

If you still think this is an exception, you can use resignal in MySQL 6.0. In earlier version , you can just trigger an error by calling a non-existant function like this,

 call ROLLED_BACK_EXCEPTION();

answered Sep 17, 2009 at 17:10

ZZ Coder

75.7k30 gold badges139 silver badges170 bronze badges

1 Comment

ChssPly76 Over a year ago

Resignal works starting with 5.4, not 6.0. And creating a fake error (by calling non-existent function) instead of returning an actual one is less than ideal for many reasons.

Collectives™ on Stack Overflow

How to propagate MySQL exceptions to Java program?

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related