mysql propogate sql exception to java program

Question

i have a java program that is calling a Mysql stored procedure that is rolling back when it gets an SQLException. When i added the rollback (exit handler) to the store procedure the java program stopped getting the sql exception. i need for the java program to get a sql exception and the error message from mysql.

does any one know how this is done?

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 ;

Answer 1

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?

Answer 2

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