Stored Procedure error handling using java

Question

How do you handle error condition while writing stored procedure or accessing stored procedure from java?

Related question: [How to get *everything* back from a stored procedure using JDBC](https://stackoverflow.com/questions/42169951/how-to-get-everything-back-from-a-stored-procedure-using-jdbc) — Gord Thompson, Jan 30 '19 at 18:59

score 0 · Accepted Answer · answered Nov 06 '14 at 06:08

0

stored procedure should return error code if some operation fails but if stored procedure itself fail than catching SQLException is only choice.

answered Nov 06 '14 at 06:08

user3636759

96
1
6

score 0 · Answer 2 · answered Mar 12 '15 at 17:32

0

try {
    CallableStatement stmt=con.prepareCall("{call insertR(?,?)}");  
    stmt.setInt(1,1011);  
    stmt.setString(2,"Amit");  
    stmt.execute();  
} catch(SQLException e) {
    e.printStack();
}

answered Mar 12 '15 at 17:32

Abdullah Khan

12,010
6
65
78

@user2156381 on how to handle the SQLexception – Abdullah Khan Mar 12 '15 at 17:34

score 0 · Answer 3 · answered Jul 05 '17 at 18:11

this piece of code is taken from oracle [docs][1] to answer.

public void
    createProcedureShowSuppliers()
    throws SQLException {
    String createProcedure = null;

    String queryDrop =
        "DROP PROCEDURE IF EXISTS SHOW_SUPPLIERS";

    createProcedure =
        "create procedure SHOW_SUPPLIERS() " +
        "begin " +
            "select SUPPLIERS.SUP_NAME, " +
            "COFFEES.COF_NAME " +
            "from SUPPLIERS, COFFEES " +
            "where SUPPLIERS.SUP_ID = " +
            "COFFEES.SUP_ID " +
            "order by SUP_NAME; " +
        "end";
    Statement stmt = null;
    Statement stmtDrop = null;

    try {
        System.out.println("Calling DROP PROCEDURE");
        stmtDrop = con.createStatement();
        stmtDrop.execute(queryDrop);
    } catch (SQLException e) {
        JDBCTutorialUtilities.printSQLException(e);
    } finally {
        if (stmtDrop != null)
        {
            stmtDrop.close();
        }
    }

    try {
        stmt = con.createStatement();
        stmt.executeUpdate(createProcedure);
    } catch (SQLException e) {
        JDBCTutorialUtilities.printSQLException(e);
    } finally {
        if (stmt != null) { stmt.close(); }
    }
}


  [1]: https://docs.oracle.com/javase/tutorial/jdbc/basics/storedprocedures.html

Please include some explanation with the code you're referencing, otherwise your answer isn't really yours — jhhoff02, Jul 05 '17 at 18:45

score 0 · Answer 4 · edited Jan 30 '19 at 18:39

When you call a stored procedure, it will execute in database server so if there any exception occurs that can be handled in EXCEPTION block in the stored procedure. If that stored procedure itself fails, it throws a SQL exception which can be handled by try/catch block and wrap it to your project specific exception.

Example

try {
    CallableStatement stmt = con.prepareCall("{call geNamebyId(?)}");
    stmt.setInt(1);    
    stmt.execute();    
} catch(SQLException e) {    
    e.printStack();        
}

Stored Procedure error handling using java

4 Answers4