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?

Have a look at exception handlers, in particular the DECLARE EXCEPTION HANDLER statement.