java is not catching ms sql stored procedure raise error

Question

I have a stored procedure in my SQL Server 2008 database and I am devloping a java application that use a sqljdbc4 connection. Everything works fine, even procedure call, but there is one thing - in some case java does not catch raised exception throwen by the procedures and specified when there is call like a select or update (delete insert)

here is an exemple

AS
BEGIN
    DECLARE @c INT
    SELECT @c= COUNT(nomUtilisateur) 
        FROM Utilisateur2
    PRINT @c
    RAISERROR(N'error message personalise',18,1)
END

in this case java catche the exception

try{
    CallableStatement cll=con.prepareCall("EXEC f3 ");
    cll.execute();

    System.out.println("fin EXEC f3 ? ");}
catch (SQLException e) {
    System.out.println("sqlerror state: "+e.getSQLState() +" |error code: "
        + e.getErrorCode()+" |message: "+e.getMessage());
    e.printStackTrace();
} 

but when I use a select like this

ALTER PROCEDURE [dbo].[f3]
AS
BEGIN
    declare @c INT
    SELECT @c= COUNT(nomUtilisateur) 
        FROM Utilisateur2
    select * from Utilisateur2
    PRINT @c
    RAISERROR(N'error message personalise',18,1)
END

or an update, java continues excution with out catching the exception. Its like it doesn't wait for the end of the procedure.

So my question is, am I using the stored procedure incorrectly or is it a bug in jdbc driver or it prefer to use an out parameter and leave this strategy; i Googled this probleme without finding anything.

Personally, I want to use the raised error

Harakiri

What is this line: **select * from Utilisateur2** supposed to mean? It is not valid SQL syntax AFAIK, and I cannot get this procedure to compile in any of my SQL Servers. – Greeley

This is purely a guess, but is java interpreting the return value of the stored proc? what if you put a "return 1" after the raiserror? – Besides

Answer 1

Try removing PRINT statement and put "SET NOCOUNT ON" in proc's code, it might be that driver gets confused.

Frink

thank u very match i wos loking for somme thing to disable the default print done by the request and – Harakiri

@Lion site allows to change names only once a month unfortunately. But point taken. – Frink

If you iterate through the resultset by calling getResultSet() and then next() on the resultset then the exception does get thrown in my experience even if you do not have "set nocout on". The set nocount on solution is risky in my opinion as it is very error prone. – Lilt