How to catch the SqlExceptionHelper in spring boot?

Question

I could not capture that error that comes from the stored procedure that I created.

create procedure sp_users
@option int = 0,
@nom_user varchar(50) = null,
@ema_user varchar(50) = null
as
begin
 set nocount on
  begin try
   if @option = 2
    begin
     if not exists (select * from tb_users where ema_user = @ema_user)
      begin
       insert into tb_users values (@nom_user,@ema_user)
      end
     else
      begin
       raiserror('The email already exists',16,1)
      end
    end
  end try
  begin catch
   declare @errorMessage varchar(100), @errorSeverity int, @errorState int
   set @errorMessage = error_message()
   set @errorSeverity = error_severity()
   set @errorState = error_state()
   raiserror(@errorMessage,@errorSeverity,@errorState)
  end catch
end

when I enter an email that already exists, it shows me the message.

Msg 50000, Level 16, State 1, Procedure sp_users, Line 71 [Batch Start Line 81]
The email already exists

this is the code that I made in spring boot

@Service
public class CrudUserService implements CrudUserDAO{


    @PersistenceContext
    private EntityManager em;

    @SuppressWarnings({ "unchecked" })
    @Override
    public ValidateApiResponse MntUser(Integer op, String nom, String ema) {
        ValidateApiResponse vapi = new ValidateApiResponse();
        Session session = em.unwrap(Session.class);
        try {
            ProcedureCall call = session.createStoredProcedureCall("sp_users");
            call.registerParameter(1, Integer.class, ParameterMode.IN);
            call.registerParameter(2, String.class, ParameterMode.IN).enablePassingNulls(true);
            call.registerParameter(3, String.class, ParameterMode.IN).enablePassingNulls(true);
            call.setParameter(1, op);
            call.setParameter(2, nom);
            call.setParameter(3, ema);
            call.execute();
            vapi.dTable = call.getResultList();
            vapi.Attr = "OK";
        } catch (Exception e) {
            //vapi.Attr = "";
            //vapi.Error = e.getMessage();
            System.out.println(e.getMessage());
        } finally {
            session.close();
        }
        return vapi;
    }
}

when I try to capture the error in spring boot I get this in the console. it shows me the message, but I can't capture that message from SqlExceptionHelper.

2019-12-30 19:19:22.892  WARN 10504 --- [nio-8090-exec-2] o.h.engine.jdbc.spi.SqlExceptionHelper   : SQL Error: 50000, SQLState: S0001
2019-12-30 19:19:22.893 ERROR 10504 --- [nio-8090-exec-2] o.h.engine.jdbc.spi.SqlExceptionHelper   : The email already exists
org.hibernate.exception.SQLGrammarException: Error calling CallableStatement.getMoreResults

This is the same problem with yours. Check that answer : https://stackoverflow.com/a/54323882/2445028 — Volkan Albayrak, Jan 03 '20 at 07:24
what you told me didn't work, but I'm still looking for someone to help me — makoto, Feb 24 '20 at 00:47

score 0 · Answer 1 · answered Jan 03 '20 at 01:32

I have already solved it, but creating an output parameter.

procedure

create proc sp_users
@option int = 0,
@nom_user varchar(50) = null,
@ema_user varchar(50) = null,
@message_error varchar(50) = null output
as
begin 
 set nocount on
 begin try
   if @option = 2
     begin
       if not exists(select * from tb_users where ema_user = @ema_user)
         begin
           insert into tb_users values(@nom_user,@ema_user)
         end
       else
         begin
           set @message_error = 'The email already exists'
           raiserror(@message,16,1)
         end
     end
 end try
 begin catch
   print error_message()
 end catch
end

spring boot

@Service
public class CrudUserService implements CrudUserDAO {

    @PersistenceContext
    private EntityManager em;

    @SuppressWarnings({ "unchecked" })
    @Override
    public ValidateApiResponse MntUser(Integer op, String nom, String ema) {
        ValidateApiResponse vapi = new ValidateApiResponse();
        Session session = em.unwrap(Session.class);
        ProcedureCall call = session.createStoredProcedureCall("sp_users");
        call.registerParameter(1, Integer.class, ParameterMode.IN);
        call.registerParameter(2, String.class, ParameterMode.IN).enablePassingNulls(true);
        call.registerParameter(3, String.class, ParameterMode.IN).enablePassingNulls(true);
        call.registerParameter(4, String.class, ParameterMode.OUT).enablePassingNulls(true);
        call.setParameter(1, op);
        call.setParameter(2, nom);
        call.setParameter(3, ema);
        call.execute();
        String message = (String) call.getOutputParameterValue(4);
        if (message == "" || message == null) {
            vapi.dTable = call.getResultList();
            vapi.Attr = "OK";
        } else {
            vapi.Attr = "";
            vapi.Error = message;
        }
        return vapi;
    }
}

This is the message I get when I enter an email that already exists.

"Msj": "Internal Server Error",
"MsjDetail": "The email already exists",
"dtCollection": [],
"MsjCode": 500

How to catch the SqlExceptionHelper in spring boot?

1 Answers1