Results 1 to 2 of 2
  1. #1
    Join Date
    Jan 2003
    Posts
    47

    Unanswered: How to handle exceptions in SQL based stored procedures

    Hi,

    I am migrating the java based stored procedures to SQL based. Can some one help how to handle exception handling in SQL based stored procedures.

    Java Based:

    public class ADDACCESSLOG
    {
    public static final String ADD_ACCESSLOG_SQL = " INSERT INTO DB2INST1.ACCESSLOG " +
    " ( USERID, PARAMETERS, COMMENTS, RULENUMBER, ACCOUNTNUMBER, " +
    " IPADDRESS, LOGTIME, SOURCEOFACTIVITY, ORGANIZATIONID ) " +
    " VALUES ( ?, ?, ?, ?, ?, ?, ?, ?, ? ) ";

    public static final String OLD_ACCESSLOG_SQL = " INSERT INTO DB2INST1.SWAPACCESSLOG " +
    " ( USERID, PARAMETERS, COMMENTS, RULENUMBER, ACCOUNTNUMBER, " +
    " IPADDRESS, LOGTIME, SOURCEOFACTIVITY, ORGANIZATIONID ) " +
    " VALUES ( ?, ?, ?, ?, ?, ?, ?, ?, ? ) ";

    public static void aDDACCESSLOG ( long iUserId,
    String iParameters,
    String iComments,
    int iRuleNumber,
    String iIPAddress,
    long iOrganizationId,
    ResultSet[] rs ) throws SQLException, Exception
    {
    // Get connection to the database
    Connection con = DriverManager.getConnection("jdbc:default:connecti on");
    PreparedStatement pstmt = null;
    String sql;
    try {

    pstmt = con.prepareStatement( ADD_ACCESSLOG_SQL );
    pstmt.setLong(1,iUserId);
    pstmt.setString(2,iParameters);
    pstmt.setString(3,iComments);
    pstmt.setInt(4,iRuleNumber);
    pstmt.setString(5,"");
    pstmt.setString(6,iIPAddress);
    Timestamp _curtime = new java.sql.Timestamp(new java.util.Date().getTime());
    pstmt.setTimestamp(7,_curtime);
    pstmt.setString(8,"I");
    pstmt.setLong(9,iOrganizationId);
    pstmt.executeUpdate();
    if(pstmt != null) pstmt.close();
    con.commit();
    }catch(SQLException se){
    String errorMsg = se.getMessage();
    if( ( errorMsg.indexOf("SQL0204N") > 0 ) ||
    ( errorMsg.indexOf("SQL0727N") > 0 )) {

    pstmt=null;
    pstmt = con.prepareStatement( OLD_ACCESSLOG_SQL );
    pstmt.setLong(1,iUserId);
    pstmt.setString(2,iParameters);
    pstmt.setString(3,iComments);
    pstmt.setInt(4,iRuleNumber);
    pstmt.setString(5,"");
    pstmt.setString(6,iIPAddress);
    Timestamp _curtime = new java.sql.Timestamp(new java.util.Date().getTime());
    pstmt.setTimestamp(7,_curtime);
    pstmt.setString(8,"I");
    pstmt.setLong(9,iOrganizationId);
    pstmt.executeUpdate();
    if(pstmt != null) pstmt.close();
    con.commit();

    }else throw se;


    }
    finally{
    if (con != null) con.close();
    }


    }

    }

    SQL based :


    CREATE PROCEDURE DB2INST1.ADDACCESSLOG_SQL ( IN iUserId BIGINT,
    IN iParameters VARCHAR(200),
    IN iComments VARCHAR(100),
    IN iRuleNumber INTEGER,
    IN iIPAddress VARCHAR(50),
    IN iOrganizationId BIGINT )
    DYNAMIC RESULT SETS 1
    ------------------------------------------------------------------------
    -- SQL Stored Procedure
    -- iUserId
    -- iParameters
    -- iComments
    -- iRuleNumber
    -- iIPAddress
    -- iOrganizationId
    ------------------------------------------------------------------------
    P1: BEGIN
    -- Declare variable
    INSERT INTO DB2INST1.ACCESSLOG ( USERID, PARAMETERS, COMMENTS, RULENUMBER, IPADDRESS, SOURCEOFACTIVITY, ORGANIZATIONID,LOGTIME )
    VALUES ( iUserId,iParameters,iComments,iRuleNumber,iIPAddre ss, 'I',iOrganizationId,CURRENT TIMESTAMP );



    END P1


    How to handle exceptions in SQL stored procedures , Any help is appreciated?
    p.srinivasarao

  2. #2
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Have a look at exception handlers, in particular the DECLARE EXCEPTION HANDLER statement.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •