Invoking store procedure in ATG

The following explains how to call/execute store procedure in ATG .

1)First configure the store procedure name (pProcedureName is string parameter) at ATG component level.
xxxStoreProcedureQuery=call xxx_PROC(?, ?)
 
  Here xxx_PROC is the store procedure name at DB level.


2) get the connection.

private Connection getDBConnection() {
try {
return ((GSARepository)getOrderRepository()).getDataSource().getConnection();
} catch (SQLException exp) {
      vlogError("Exception Occurred while Getting the Connection",exp);
}
return null;
}


3)call store procedure as follows

  public String invokeStoreProcedure(String pSiteId, String pProcedureName) {
String sequenceNumber = EMPTY_STRING;
boolean rollback = true;
Connection jdbcConn  = null;
TransactionManager tm = null;
TransactionDemarcation td = null;
CallableStatement procStmt = null;

try {
tm = getTransactionManager();
td = new TransactionDemarcation();
td.begin(tm, TransactionDemarcation.REQUIRES_NEW);
jdbcConn = getConnection();
procStmt = jdbcConn.prepareCall(pProcedureName);
procStmt.setString(1, pSiteId);
procStmt.registerOutParameter(2, java.sql.Types.VARCHAR);
procStmt.executeUpdate();
sequenceNumber = procStmt.getString(2);
rollback = Boolean.FALSE;
} catch (Exception ex) {
if (isLoggingError()) {
logError("SQL Exception occurred while getting the connection object", ex);
}
} finally {
try {
if (td != null) {
td.end(rollback);
}
} catch (TransactionDemarcationException e) {
vlogError("TransactionDemarcationException while correcting the corrupted relationship", e);
}
closingConnection(null, null, jdbcConn,procStmt);
}
return sequenceNumber;
}


4)Then call wherever it is requried

  String requiredValue = invokeStoreProcedure(pSiteId, pProcedureName);