CODEWITHSUNDEEP
javasqlmysqljdbc
richs
richs

Reputation: 4769

How to propagate MySQL exceptions to Java program?

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 ;

Upvotes: 2

Views: 841

Answers (2)

ZZ Coder
ZZ Coder

Reputation: 75456

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();

Upvotes: -1

ChssPly76
ChssPly76

Reputation: 100706

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?

Upvotes: 3

Related Questions