public String getKnownShipperDetails(String locationCode, String shipperId) {
Connection connection = null;
CallableStatement cStmt = null;
ResultSet rs = null;
String Code = null;
try {
connection = getConnection();
cStmt = connection.prepareCall("{? = call ETRANS_UTIL.GETKNOWNSHIPPERSTATUS(?,?,?) }");
cStmt.setString(2, locationCode);
cStmt.setString(3, shipperId);
cStmt.setString(4, "OPR");
cStmt.registerOutParameter(1, java.sql.Types.VARCHAR);
cStmt.execute();
Code = cStmt.getString(1);
} catch (SQLException e) {
e.printStackTrace();
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
ConnectionUtil.closeConnection(connection, cStmt, rs);
} catch (Exception ex) {
ex.printStackTrace();
}
}
return Code;
}
@Override
public int save(java.sql.Connection connection) {
int i = 0;
int nParam = 1;
CallableStatement callableStatement = null;
mnLastDbActionResult = SLibConstants.UNDEFINED;
try {
callableStatement =
connection.prepareCall("{ CALL mfgu_line_save(" + "?, ?, ?, ?, ?, " + "?, ?, ?) }");
callableStatement.setInt(nParam++, mnPkMfgLineId);
callableStatement.setString(nParam++, msMfgLine);
callableStatement.setBoolean(nParam++, mbIsDeleted);
callableStatement.setInt(nParam++, mnFkCostCenterId);
callableStatement.setInt(nParam++, mbIsRegistryNew ? mnFkUserNewId : mnFkUserEditId);
callableStatement.registerOutParameter(nParam++, java.sql.Types.INTEGER);
callableStatement.registerOutParameter(nParam++, java.sql.Types.SMALLINT);
callableStatement.registerOutParameter(nParam++, java.sql.Types.VARCHAR);
callableStatement.execute();
mnPkMfgLineId = callableStatement.getInt(nParam - 3);
mnDbmsErrorId = callableStatement.getInt(nParam - 2);
msDbmsError = callableStatement.getString(nParam - 1);
mbIsRegistryNew = false;
mnLastDbActionResult = SLibConstants.DB_ACTION_SAVE_OK;
} catch (java.lang.Exception e) {
mnLastDbActionResult = SLibConstants.DB_ACTION_SAVE_ERROR;
SLibUtilities.printOutException(this, e);
}
return mnLastDbActionResult;
}
public void testGetObjectFloat() throws Throwable {
try {
Statement stmt = con.createStatement();
stmt.execute(createDecimalTab);
stmt.execute(insertDecimalTab);
boolean ret = stmt.execute(createFloatProc);
} catch (Exception ex) {
fail(ex.getMessage());
throw ex;
}
try {
CallableStatement cstmt = con.prepareCall("{ call float_proc(?,?,?) }");
cstmt.registerOutParameter(1, java.sql.Types.FLOAT);
cstmt.registerOutParameter(2, java.sql.Types.FLOAT);
cstmt.registerOutParameter(3, java.sql.Types.FLOAT);
cstmt.executeUpdate();
Double val = (Double) cstmt.getObject(1);
assertTrue(val.doubleValue() == doubleValues[0]);
val = (Double) cstmt.getObject(2);
assertTrue(val.doubleValue() == doubleValues[1]);
val = (Double) cstmt.getObject(3);
assertTrue(cstmt.wasNull());
} catch (Exception ex) {
fail(ex.getMessage());
} finally {
try {
Statement dstmt = con.createStatement();
dstmt.execute(dropFloatProc);
} catch (Exception ex) {
}
}
}
public void testNumeric() throws Throwable {
CallableStatement call = con.prepareCall("{ call Numeric_Proc(?,?,?) }");
call.registerOutParameter(1, Types.NUMERIC, 15);
call.registerOutParameter(2, Types.NUMERIC, 15);
call.registerOutParameter(3, Types.NUMERIC, 15);
call.executeUpdate();
java.math.BigDecimal ret = call.getBigDecimal(1);
assertTrue(
"correct return from getNumeric () should be 999999999999999.000000000000000 but returned "
+ ret.toString(),
ret.equals(new java.math.BigDecimal("999999999999999.000000000000000")));
ret = call.getBigDecimal(2);
assertTrue(
"correct return from getNumeric ()",
ret.equals(new java.math.BigDecimal("0.000000000000001")));
try {
ret = call.getBigDecimal(3);
} catch (NullPointerException ex) {
assertTrue("This should be null", call.wasNull());
}
}
/**
* Overrides the parent method to set replaceable parameters, if any, into the CallableStatement
* object. CallableStatements may have to register OUT parameters, as well as set IN and INOUT
* parameters.
*
* @param cs the Statement object.
* @param params the array of Param objects.
* @return a Statement object which should be cast to CallableStatement.
* @exception Exception if there was a problem setting the parameters.
*/
protected final Statement setParameters(final Statement cs, final Param[] params)
throws Exception {
CallableStatement tcs = (CallableStatement) cs;
for (int i = 0; i < params.length; i++) {
// :NOTE: order is important here. For INOUT which will enter
// both blocks, we need to set the value and then register the
// parameter
if (params[i].isInParameter()) {
if (params[i].isNull()) {
tcs.setNull(i + 1, params[i].getSQLType());
} else {
Object convertedParamValue =
TypeUtils.convertToObject(params[i].getValue(), params[i].getType());
tcs.setObject(i + 1, convertedParamValue, params[i].getSQLType());
}
}
if (params[i].isOutParameter()) {
if (SymbolTable.isVariableName(params[i].getValue())) {
SymbolTable.setValue(
SymbolTable.OUT_PARAM + tcs.hashCode() + ":" + i + "}", params[i].getValue());
}
String className = TypeMapper.findClassByName(params[i].getType());
if (BigDecimalType.class.getName().equals(className)) {
int scale = params[i].getScale();
tcs.registerOutParameter(i + 1, params[i].getSQLType(), scale);
} else if (params[i].getTypeName() != null) {
tcs.registerOutParameter(i + 1, params[i].getSQLType(), params[i].getTypeName());
} else {
tcs.registerOutParameter(i + 1, params[i].getSQLType());
}
}
}
return tcs;
}
/*
* prepareStatement
* Supports named parameters.
*/
public int prepareStatement(int ColIndex, CallableStatement CallStatmt, Connection _conn)
throws dataNotSupportedException, cfmRunTimeException, SQLException {
if (useNamedParameters) {
paramName = paramName.replace("@", "");
if (isOUT()) {
int jType = getJdbcType(CallStatmt, cfSqlType);
if ((jType == java.sql.Types.DECIMAL) || (jType == java.sql.Types.NUMERIC))
CallStatmt.registerOutParameter(paramName, jType, scale);
else CallStatmt.registerOutParameter(paramName, jType);
}
if (isIN()) {
prepareStatement(paramName, CallStatmt, _conn);
}
return ColIndex + 1;
}
if (isOUT()) {
int jType = getJdbcType(CallStatmt, cfSqlType);
if ((jType == java.sql.Types.DECIMAL) || (jType == java.sql.Types.NUMERIC))
CallStatmt.registerOutParameter(ColIndex, jType, scale);
else CallStatmt.registerOutParameter(ColIndex, jType);
}
return (isIN()
? prepareStatement(ColIndex, (PreparedStatement) CallStatmt, _conn)
: ColIndex + 1);
}
public List<UserBean> fetchRecommendedArtistforArtist(String artistId)
throws ConnectionFailureException, SQLException, IOException {
List<UserBean> recommdArtistForArtistList = new ArrayList<UserBean>();
Connection conn = DBManager.getConnection();
CallableStatement callableStatement = conn.prepareCall("call FETCH_ARTIST_LIST_PROC(?,?,?)");
callableStatement.registerOutParameter(2, OracleTypes.CURSOR);
callableStatement.registerOutParameter(3, java.sql.Types.VARCHAR);
callableStatement.setString(1, artistId);
callableStatement.execute();
String errMsg = callableStatement.getString(3);
ResultSet rs = ((OracleCallableStatement) callableStatement).getCursor(2);
if (!callableStatement.wasNull()) {
while (rs.next()) {
UserBean userBean = new UserBean();
userBean.setUserid(rs.getString(1));
userBean.setFirstName(rs.getString(2));
userBean.setLastName(rs.getString(3));
userBean.setPhoneNo(rs.getDouble(4));
userBean.setDob(rs.getDate(5));
userBean.setCity(rs.getString(6));
userBean.setState(rs.getString(7));
recommdArtistForArtistList.add(userBean);
}
}
if (null != errMsg && !errMsg.isEmpty()) {
ErrorLog.logError(errMsg);
}
if (null != rs) rs.close();
if (null != callableStatement) callableStatement.close();
if (null != conn) conn.close();
return recommdArtistForArtistList;
}
public void testSomeInOut() throws Throwable {
CallableStatement call = con.prepareCall("{ call test_somein_someout(?,?,?) }");
call.registerOutParameter(2, Types.VARCHAR);
call.registerOutParameter(3, Types.BIGINT);
call.setInt(1, 20);
call.execute();
}
/** * others method ** */
public void save() throws SQLException {
Connection conn = OracleGate.getConnection();
CallableStatement cstmt = null;
this.mCode = 0;
try {
conn.setAutoCommit(false);
String sql = "begin BOM_PARAMETER_PKG.SAVE_PARAMETER(:1,:2,:3,:4,:5,:6,:7,:8,:9,:10); end;";
DeBug.print(sql);
cstmt = conn.prepareCall(sql);
cstmt.setString(1, this.getWeightBase());
cstmt.setString(2, this.getPacking());
cstmt.setString(3, this.getElectrolysis());
cstmt.setString(4, this.getManagement());
cstmt.setString(5, this.getSaraly());
cstmt.setString(6, this.getScript());
cstmt.setString(7, this.getAssemblybase());
cstmt.setString(8, this.getProfit());
cstmt.registerOutParameter(9, Types.INTEGER);
cstmt.registerOutParameter(10, Types.VARCHAR);
cstmt.execute();
int returnCode = cstmt.getInt(9);
String returnMessage = cstmt.getString(10);
DeBug.print(" returnCode ::" + returnCode);
DeBug.print(" returnMessage ::" + returnMessage);
if (returnCode == -2) {
// Violate unique constraint in database
conn.rollback();
this.mCode = UNIQUE_VIOLATION;
} else if (returnCode != 0) {
// Non-manageable error.
conn.rollback();
this.mCode = SAVE_ERROR;
} else {
conn.commit();
}
} catch (Exception e) {
conn.rollback();
e.printStackTrace();
this.mCode = SAVE_ERROR;
} finally {
conn.setAutoCommit(true);
if (conn != null) {
OracleGate.freeConnection(conn);
}
try {
if (cstmt != null) {
cstmt.close();
}
cstmt = null;
} catch (Exception e) {
e.printStackTrace();
}
}
}
/* Business type 2 */
public void saveQuoteItemDtl(QuotationMgr quotationMgr, Connection conn, String quoteId)
throws SQLException {
CallableStatement cstmt = null;
try {
this.mCode = 0;
conn.setAutoCommit(false);
String sql = "begin OM_QUOTATION_PKG.SAVE_QUOTE_ITEM_DTL(:1,:2,:3,:4,:5,:6,:7,:8,:9); end;";
cstmt = conn.prepareCall(sql);
DeBug.print(sql);
if (quoteId == null || quoteId.equals("")) return;
cstmt.setString(1, Decoder.convertSetString(quoteId));
cstmt.setNull(2, Types.VARCHAR);
cstmt.setString(3, Decoder.convertSetString(this.getCustomerId()));
cstmt.setString(4, Decoder.convertSetString(this.getCurrentSite().getSiteId()));
cstmt.setString(5, Decoder.convertSetString(this.getCurrentContact().getContactId()));
cstmt.setString(6, Decoder.convertSetString(this.getQuotePrice()));
cstmt.setString(7, Decoder.convertSetString("CREATE"));
cstmt.registerOutParameter(2, Types.VARCHAR);
cstmt.registerOutParameter(8, Types.INTEGER);
cstmt.registerOutParameter(9, Types.INTEGER);
cstmt.execute();
int returnCode = cstmt.getInt(8);
String returnMessage = cstmt.getString(9);
DeBug.print(String.valueOf(returnCode), "(in Save : the returnCode )");
DeBug.print(returnMessage, "(in Save : the returnMessage )");
if (returnCode != 0) {
// Non-manageable error.
conn.rollback();
this.mCode = SAVE_ERROR;
} else {
this.setQuoteDtlId(cstmt.getString(2));
}
} catch (Exception e) {
conn.rollback();
e.printStackTrace();
this.mCode = SAVE_ERROR;
} finally {
try {
if (cstmt != null) {
cstmt.close();
}
cstmt = null;
} catch (Exception e) {
e.printStackTrace();
}
}
}
/**
*
*
* <PRE>
* Desc : Excute Procedure SP_INSERT_CUST_FROM_SEG
* </PRE>
*
* @param RetrieveModel
* @return RetrieveModel
*/
public RetrieveModel execProcedure(Connection con, RetrieveModel retrieve) throws StoreException {
CallableStatement cstmt = null;
ResultSet rset = null;
try {
cstmt = con.prepareCall("begin SP_INSERT_CUST_FROM_SEG(?, ?, ?, '', ?, ?, ?, ?, ?); end;");
int index = 1;
cstmt.setString(index++, retrieve.getString("flag"));
cstmt.setString(index++, retrieve.getString("promo_no"));
cstmt.setString(index++, retrieve.getString("segment_code"));
cstmt.setString(index++, retrieve.getString("proc_date"));
cstmt.setString(index++, retrieve.getString("user_id"));
cstmt.registerOutParameter(index++, java.sql.Types.INTEGER);
cstmt.registerOutParameter(index++, java.sql.Types.INTEGER);
cstmt.registerOutParameter(index++, java.sql.Types.VARCHAR);
// = log Save Data ---------------------
StringBuffer logString = new StringBuffer();
logString.append(retrieve.getString("flag"));
logString.append("/");
logString.append(retrieve.getString("promo_no"));
logString.append("/");
logString.append(retrieve.getString("segment_code"));
logString.append("/");
logString.append(retrieve.getString("proc_date"));
logString.append("/");
logString.append(retrieve.getString("user_id"));
logString.append("/");
logSave.info(logString.toString());
// == 프로시져를 실행합니다.
cstmt.executeUpdate();
retrieve.put("out_proc_cnt", cstmt.getInt(6));
retrieve.put("out_rtn", cstmt.getInt(7));
retrieve.put("out_rtn_msg", ComUtils.NVL(cstmt.getString(8), ""));
} catch (SQLException se) {
logSave.error(
"[PromoSegmentSvc.execProcedure() SQLException : ERR-" + se.getErrorCode() + ":" + se);
throw new StoreException(se.getMessage());
} catch (Exception e) {
logSave.error("[PromoSegmentSvc.execProcedure() Exception : ERR-" + e.getMessage());
throw new StoreException(e.getMessage());
} finally {
DBUtils.freeConnection(null, null, null, cstmt, null, rset);
}
return retrieve;
}
public int saveMemo(Connection conn, Login login) throws SQLException {
CallableStatement cstmt = null;
try {
this.mCode = 0;
String sql = "begin FM_AR_SEND_TO_FM_PKG.SAVE_CREDIT_MEMO_TO_GL(:1,:2,:3,:4,:5); end;";
cstmt = conn.prepareCall(sql);
DeBug.print(sql);
int returnCode;
String returnMessage;
cstmt.setString(1, this.getMemoId());
cstmt.setString(2, Decoder.convertSetString(login.getLoginName()));
cstmt.registerOutParameter(3, Types.VARCHAR);
cstmt.registerOutParameter(4, Types.INTEGER);
cstmt.registerOutParameter(5, Types.VARCHAR);
cstmt.execute();
returnCode = cstmt.getInt(4);
returnMessage = cstmt.getString(5);
DeBug.print(returnCode + ":" + returnMessage);
if (returnCode != 0) {
this.mCode = SAVE_ERROR;
return this.mCode;
} else if (returnCode == -5) {
this.mCode = TAX_ID_NOT_FOUND;
return this.mCode;
} else if (returnCode != 0) {
this.mCode = SAVE_ERROR;
return this.mCode;
} else {
this.setVoucherNo(cstmt.getString(3));
this.setIsSavedFlag("Y");
return this.mCode;
}
} catch (Exception e) {
this.mCode = SAVE_ERROR;
e.printStackTrace();
throw new SQLException(e.getMessage());
} finally {
if (cstmt != null) {
cstmt.close();
}
cstmt = null;
}
}
@Override
public int save(java.sql.Connection connection) {
int nParam = 1;
CallableStatement callableStatement = null;
mnLastDbActionResult = SLibConstants.UNDEFINED;
try {
callableStatement =
connection.prepareCall("{ CALL erp.finu_tax_bas_save(" + "?, ?, ?, ?, ?, ?, ?) }");
callableStatement.setInt(nParam++, mnPkTaxBasicId);
callableStatement.setString(nParam++, msTaxBasic);
callableStatement.setBoolean(nParam++, mbIsDeleted);
callableStatement.setInt(nParam++, mbIsRegistryNew ? mnFkUserNewId : mnFkUserEditId);
callableStatement.registerOutParameter(nParam++, java.sql.Types.SMALLINT);
callableStatement.registerOutParameter(nParam++, java.sql.Types.SMALLINT);
callableStatement.registerOutParameter(nParam++, java.sql.Types.CHAR);
callableStatement.execute();
mnPkTaxBasicId = callableStatement.getInt(nParam - 3);
mnDbmsErrorId = callableStatement.getInt(nParam - 2);
msDbmsError = callableStatement.getString(nParam - 1);
if (mnDbmsErrorId != 0) {
throw new Exception(msDbmsError);
} else {
// Save aswell the taxes
nParam = 1;
for (int i = 0; i < mvDbmsTaxes.size(); i++) {
mvDbmsTaxes.get(i).setPkTaxBasicId(mnPkTaxBasicId);
if (mvDbmsTaxes.get(i).save(connection) != SLibConstants.DB_ACTION_SAVE_OK) {
throw new Exception(SLibConstants.MSG_ERR_DB_REG_SAVE_DEP);
}
}
mbIsRegistryNew = false;
mnLastDbActionResult = SLibConstants.DB_ACTION_SAVE_OK;
}
} catch (java.sql.SQLException e) {
mnLastDbActionResult = SLibConstants.DB_ACTION_SAVE_ERROR;
SLibUtilities.printOutException(this, e);
} catch (java.lang.Exception e) {
mnLastDbActionResult = SLibConstants.DB_ACTION_SAVE_ERROR;
SLibUtilities.printOutException(this, e);
}
return mnLastDbActionResult;
}
public void testAllInOut() throws Throwable {
CallableStatement call = con.prepareCall("{ call test_allinout(?,?,?) }");
call.registerOutParameter(1, Types.INTEGER);
call.registerOutParameter(2, Types.VARCHAR);
call.registerOutParameter(3, Types.BIGINT);
call.setInt(1, 20);
call.setString(2, "hi");
call.setInt(3, 123);
call.execute();
call.getInt(1);
call.getString(2);
call.getLong(3);
}
private static void executeOutput(CallableStatement cs) throws SQLException {
for (int p = 1; p <= 5; p++) cs.registerOutParameter(p, Types.INTEGER);
for (int p = 6; p <= 10; p++) cs.registerOutParameter(p, Types.VARCHAR);
cs.execute();
for (int p = 1; p <= 5; p++) {
System.out.println(" " + p + " = " + cs.getInt(p) + " was null " + cs.wasNull());
}
for (int p = 6; p <= 10; p++) {
System.out.println(" " + p + " = " + cs.getString(p) + " was null " + cs.wasNull());
}
}
@Override
public int call(java.sql.Connection connection) {
int nParam = 1;
java.sql.CallableStatement callableStatement = null;
mnLastDbActionResult = SLibConstants.UNDEFINED;
try {
callableStatement = connection.prepareCall("{ CALL mfg_ltime_cob_val(?, ?, ?) }");
callableStatement.setInt(nParam, (Integer) mvParamsIn.get(nParam - 1));
nParam++;
callableStatement.setInt(nParam, (Integer) mvParamsIn.get(nParam - 1));
nParam++;
callableStatement.registerOutParameter(nParam, java.sql.Types.INTEGER);
callableStatement.execute();
mvParamsOut.clear();
mvParamsOut.add(callableStatement.getInt(nParam));
mnLastDbActionResult = SLibConstants.DB_PROCEDURE_OK;
} catch (java.sql.SQLException e) {
mnLastDbActionResult = SLibConstants.DB_PROCEDURE_ERROR;
SLibUtilities.printOutException(this, e);
} catch (java.lang.Exception e) {
mnLastDbActionResult = SLibConstants.DB_PROCEDURE_ERROR;
SLibUtilities.printOutException(this, e);
}
return mnLastDbActionResult;
}
public static Long dbGenerarNumero(Connection conn, String pkModulo) {
String sql = "{ call ? := PK_NUMERACION_UTIL.FN_MODULO_NEXT_NUM ( ? ) }";
try {
// String sql = "{ call ? := PF_SUMINISTROS_UTIL.FN_OBTENER_NUMERACION( CO_NUMERACION,
// usua_modi, nuip_modi, usso_modi, nopc_modi ) }";
// ApplicationUser appUser = SPApplication.getAppUser();
// Connection conn = sessionImplementor.connection();
CallableStatement stmt = conn.prepareCall(sql);
stmt.registerOutParameter(1, OracleTypes.VARCHAR);
stmt.setObject(2, pkModulo);
// stmt.setObject(3, padSize);//padSize
// stmt.setObject(4, appUser.getUsername());//"USUMODI"
// stmt.setObject(5, appUser.getIpAddress()); //"IPMODI"
// stmt.setObject(6, appUser.getOS()); //"SOMODI"
// stmt.setObject(7, appUser.getHostName()); //"PCMODI"
logger.info(" PK_NUMERACION_UTIL.FN_MODULO_CURR_NUM:" + pkModulo);
stmt.execute();
String returnValue = (String) stmt.getObject(1);
logger.debug("Numero generado:" + returnValue);
stmt.close();
return new Long(returnValue);
} catch (SQLException e) {
logger.error("SQL:" + sql);
throw new AWDeveloperException(AWBusinessException.wrapUnhandledException(logger, e));
}
}
/**
* Control de transacción en el procedimiento almacenado
*
* @param prod
*/
@Override
public void create2(Producto prod) {
Connection cn = null;
try {
cn = AccesoDB.getConnection();
cn.setAutoCommit(true);
String query = "{call usp_crea_producto(?,?,?,?,?)}";
CallableStatement cstm = cn.prepareCall(query);
cstm.registerOutParameter(1, Types.INTEGER);
cstm.setInt(2, prod.getIdcat());
cstm.setString(3, prod.getNombre());
cstm.setDouble(4, prod.getPrecio());
cstm.setInt(5, prod.getStock());
cstm.executeUpdate();
prod.setIdprod(cstm.getInt(1));
cstm.close();
} catch (SQLException e) {
throw new RuntimeException(e.getMessage());
} catch (Exception e) {
throw new RuntimeException("No se puede crear el producto.");
} finally {
try {
cn.close();
} catch (Exception e) {
}
}
}
public long nextTSecGroupMemberIdGen(
CFSecurityAuthorization Authorization, CFSecurityTenantPKey PKey) {
final String S_ProcName = "nextTSecGroupMemberIdGen";
if (!schema.isTransactionOpen()) {
throw CFLib.getDefaultExceptionFactory()
.newUsageException(getClass(), S_ProcName, "Not in a transaction");
}
Connection cnx = schema.getCnx();
long Id = PKey.getRequiredId();
CallableStatement stmtSelectNextTSecGroupMemberIdGen = null;
try {
String sql = "{ call sp_next_tsecgroupmemberidgen( ?" + ", " + "?" + " ) }";
stmtSelectNextTSecGroupMemberIdGen = cnx.prepareCall(sql);
int argIdx = 1;
stmtSelectNextTSecGroupMemberIdGen.registerOutParameter(argIdx++, java.sql.Types.BIGINT);
stmtSelectNextTSecGroupMemberIdGen.setLong(argIdx++, Id);
stmtSelectNextTSecGroupMemberIdGen.execute();
long nextId = stmtSelectNextTSecGroupMemberIdGen.getLong(1);
return (nextId);
} catch (SQLException e) {
throw CFLib.getDefaultExceptionFactory().newDbException(getClass(), S_ProcName, e);
} finally {
if (stmtSelectNextTSecGroupMemberIdGen != null) {
try {
stmtSelectNextTSecGroupMemberIdGen.close();
} catch (SQLException e) {
}
stmtSelectNextTSecGroupMemberIdGen = null;
}
}
}
public static CallableStatement callPro2(
String sql, String[] inparameters, Integer[] outparameters) {
try {
ct = getConnection();
cs = ct.prepareCall(sql);
if (inparameters != null) {
for (int i = 0; i < inparameters.length; i++) {
cs.setObject(i + 1, inparameters[i]);
}
}
if (outparameters != null) {
for (int i = 0; i < outparameters.length; i++) {
cs.registerOutParameter(inparameters.length + 1 + i, outparameters[i]);
}
}
cs.execute();
} catch (Exception e) {
e.printStackTrace();
throw new RuntimeException(e.getMessage());
} finally {
}
return cs;
}
@Override
protected void saveModer(ModeratorMessage mm, int mID) {
/* function add_moderation (
p_message_id in dxdy_moderation.message_id%type,
p_move_from in dxdy_forum.forum_name%type,
p_move_to in dxdy_forum.forum_name%type,
p_user in dxdy_user.username%type,
p_message_type in dxdy_notice_type.notice_name%type) return varchar2; */
String proc = "{? = call dxdy_executable.add_moderation(?, ?, ?, ?, ?)}";
CallableStatement st = null;
try {
st = connection.prepareCall(proc);
st.registerOutParameter(1, java.sql.Types.VARCHAR);
st.setInt(2, mID);
st.setString(3, mm.moveFrom);
st.setString(4, mm.moveTo);
st.setString(5, mm.moderator);
st.setString(6, mm.messageType);
st.execute();
// System.out.println("Link ID = " + st.getString(1));
} catch (SQLException e) {
e.printStackTrace();
} finally {
if (st != null) {
try {
st.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
@Override
public boolean getForumFullScan(String forum) {
/* function get_forum_scan (p_forum in varchar2) return number; */
boolean result = true;
String proc = "{? = call dxdy_executable.get_forum_scan (?)}";
CallableStatement st = null;
try {
st = connection.prepareCall(proc);
st.registerOutParameter(1, Types.INTEGER);
st.setString(2, forum);
st.execute();
result = (st.getInt(1) == 1);
} catch (SQLException e) {
e.printStackTrace();
} finally {
if (st != null) {
try {
st.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
return result;
}
@Override
protected ArrayList vAddRow(Connection conn) throws Exception {
RBDocTypesDPanel p = new RBDocTypesDPanel();
AUniversalAddDialog d = new AUniversalAddDialog(p, null, true);
d.setTitleIcon(
new javax.swing.ImageIcon(
getClass().getResource("/tradeterminal/icons/TT_icons/32X32/typplus.png")));
d.setVisible(true);
d.dispose();
if (d.getReturnStatus() == ADialog.RET_OK) {
CallableStatement proc = conn.prepareCall("{? = call rb_doc_types_insert(?,?)}");
proc.registerOutParameter(1, Types.INTEGER);
proc.setString(2, p.get_Name());
proc.setString(3, p.getDescription());
proc.execute();
int id = proc.getInt(1);
proc.close();
ArrayList newRow = new ArrayList();
newRow.add(id);
newRow.add(p.get_Name());
newRow.add(p.getDescription());
return newRow;
} else {
return null;
}
}
@Override
protected String getParam(String name) {
/* function get_setting (
p_key in dxdy_spider_setting.key%type) return varchar2;
*/
String proc = "{? = call dxdy_executable.get_setting (?)}";
CallableStatement st = null;
String result = null;
try {
st = connection.prepareCall(proc);
st.registerOutParameter(1, java.sql.Types.VARCHAR);
st.setString(2, name);
st.execute();
result = st.getString(1);
} catch (SQLException e) {
e.printStackTrace();
} finally {
if (st != null) {
try {
st.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
return result;
}
@Override
public String getLastTopicDate(int tID) {
/* function get_topic_last_update (p_topic_id in dxdy_topic.id%type) return varchar2; */
String proc = "{? = call dxdy_executable.get_topic_last_update(?)}";
CallableStatement st = null;
String res = "";
try {
// System.out.println("Request date for topic ID = " + tID);
st = connection.prepareCall(proc);
st.registerOutParameter(1, Types.VARCHAR);
st.setInt(2, tID);
st.execute();
res = st.getString(1);
// System.out.println("Requested date = " + res);
} catch (SQLException e) {
System.out.println("Error while querying last message date of topic ID = " + tID);
e.printStackTrace();
} finally {
if (st != null) {
try {
st.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
return res;
}
@Override
public void registrarPersonaDireccion(
int idPersona, int idTipoPersona, int idDireccion, Connection conexion) throws SQLException {
CallableStatement cs = null;
String sql = "{ ? = call negocio.fn_ingresarpersonadireccion(?,?,?) }";
try {
cs = conexion.prepareCall(sql);
int i = 1;
cs.registerOutParameter(i++, Types.BOOLEAN);
cs.setInt(i++, idPersona);
cs.setInt(i++, idTipoPersona);
cs.setInt(i++, idDireccion);
cs.execute();
} catch (SQLException e) {
throw new SQLException(e);
} finally {
try {
if (cs != null) {
cs.close();
}
} catch (SQLException e) {
throw new SQLException(e);
}
}
}
/**
* Function to create temp visit dimension table using stored proc.
*
* @param tempTableName
* @throws Exception
*/
public void createTempTable(String tempEncounterMappingTableName) throws I2B2Exception {
Connection conn = null;
try {
// smuniraju: Postgres requires only the IN arguments to be supplied in the call to proc.
// CallableStatement callStmt = conn.prepareCall("{call "+ getDbSchemaName() +
// "CREATE_TEMP_EID_TABLE(?,?)}");
String prepareCallString = "";
if (dataSourceLookup.getServerType().equalsIgnoreCase(DataSourceLookupDAOFactory.POSTGRES)) {
prepareCallString = "{call " + getDbSchemaName() + "CREATE_TEMP_EID_TABLE(?)}";
} else {
prepareCallString = "{call " + getDbSchemaName() + "CREATE_TEMP_EID_TABLE(?,?)}";
}
conn = getDataSource().getConnection();
CallableStatement callStmt = conn.prepareCall(prepareCallString);
callStmt.setString(1, tempEncounterMappingTableName);
callStmt.registerOutParameter(2, java.sql.Types.VARCHAR);
callStmt.execute();
this.getSQLServerProcedureError(dataSourceLookup.getServerType(), callStmt, 2);
} catch (SQLException sqlEx) {
sqlEx.printStackTrace();
throw new I2B2Exception("SQLException occured" + sqlEx.getMessage(), sqlEx);
} catch (Exception ex) {
ex.printStackTrace();
throw new I2B2Exception("Exception occured" + ex.getMessage(), ex);
} finally {
if (conn != null) {
try {
conn.close();
} catch (SQLException sqlEx) {
sqlEx.printStackTrace();
log.error("Error while closing connection", sqlEx);
}
}
}
}
@Override
public ResultSet ExecuteSproc(String sprocName, String[] parameters, int[] returnColumnTypes)
throws SQLException {
if (_conn.isClosed()) {
throw new SQLException("Connection was already closed");
}
try {
String paramsList = "";
int listSize = parameters.length;
for (int i = 0; i < listSize; i++) {
paramsList += parameters[i];
if (i != listSize - 1) {
paramsList += ", ";
}
}
CallableStatement statement =
_conn.prepareCall("{call " + sprocName + "(" + paramsList + ")}");
for (int i = 0; i < returnColumnTypes.length; i++) {
statement.registerOutParameter(i + 1, returnColumnTypes[i]);
}
return statement.executeQuery();
} catch (SQLException ex) {
throw ex;
}
}
@Override
public int getLastTopicPage(int tID) {
String proc = "{? = call dxdy_executable.get_topic_last_page(?)}";
CallableStatement st = null;
int res = 0;
try {
st = connection.prepareCall(proc);
st.registerOutParameter(1, java.sql.Types.INTEGER);
st.setInt(2, tID);
st.execute();
res = st.getInt(1);
} catch (SQLException e) {
System.out.println("Error while querying last page of topic ID = " + tID);
e.printStackTrace();
} finally {
if (st != null) {
try {
st.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
return res;
}
@Override
public boolean eliminarDireccionPersona(Persona persona, Connection conexion)
throws SQLException {
boolean resultado = false;
CallableStatement cs = null;
String sql = "{ ? = call negocio.fn_eliminardirecciones(?,?,?) }";
try {
cs = conexion.prepareCall(sql);
int i = 1;
cs.registerOutParameter(i++, Types.BOOLEAN);
cs.setInt(i++, persona.getCodigoEntero().intValue());
cs.setString(i++, UtilJdbc.convertirMayuscula(persona.getUsuarioModificacion()));
cs.setString(i++, persona.getIpModificacion());
cs.execute();
resultado = cs.getBoolean(1);
} catch (SQLException e) {
resultado = false;
throw new SQLException(e);
} finally {
try {
if (cs != null) {
cs.close();
}
} catch (SQLException e) {
throw new SQLException(e);
}
}
return resultado;
}
