public void testGetObjectLongVarchar() throws Throwable {
try {
Statement stmt = con.createStatement();
stmt.execute("create temp table longvarchar_tab ( t text, null_val text )");
stmt.execute("insert into longvarchar_tab values ('testdata',null)");
boolean ret =
stmt.execute(
"create or replace function "
+ "longvarchar_proc( OUT pcn text, OUT nval text) as "
+ "'begin "
+ "select t into pcn from longvarchar_tab;"
+ "select null_val into nval from longvarchar_tab;"
+ " end;' "
+ "language plpgsql;");
ret =
stmt.execute(
"create or replace function "
+ "lvarchar_in_name( IN pcn text) returns int as "
+ "'begin "
+ "update longvarchar_tab set t=pcn;"
+ "return 0;"
+ " end;' "
+ "language plpgsql;");
} catch (Exception ex) {
fail(ex.getMessage());
throw ex;
}
try {
CallableStatement cstmt = con.prepareCall("{ call longvarchar_proc(?,?) }");
cstmt.registerOutParameter(1, Types.LONGVARCHAR);
cstmt.registerOutParameter(2, Types.LONGVARCHAR);
cstmt.executeUpdate();
String val = (String) cstmt.getObject(1);
assertTrue(val.equals("testdata"));
val = (String) cstmt.getObject(2);
assertTrue(val == null);
cstmt.close();
cstmt = con.prepareCall("{ call lvarchar_in_name(?) }");
String maxFloat = "3.4E38";
cstmt.setObject(1, new Float(maxFloat), Types.LONGVARCHAR);
cstmt.executeUpdate();
cstmt.close();
Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery("select * from longvarchar_tab");
assertTrue(rs.next());
String rval = (String) rs.getObject(1);
assertEquals(rval.trim(), maxFloat.trim());
} catch (Exception ex) {
fail(ex.getMessage());
} finally {
try {
Statement dstmt = con.createStatement();
dstmt.execute("drop function longvarchar_proc()");
dstmt.execute("drop function lvarchar_in_name(text)");
} catch (Exception ex) {
}
}
}
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) {
}
}
}
/**
* Descripción de Método
*
* @param ProcedureName
* @return
*/
private boolean startDBProcess(String ProcedureName) {
// execute on this thread/connection
log.fine(ProcedureName + "(" + m_pi.getAD_PInstance_ID() + ")");
String sql = "{call " + ProcedureName + "(?)}";
try {
CallableStatement cstmt = DB.prepareCall(sql); // ro??
cstmt.setInt(1, m_pi.getAD_PInstance_ID());
cstmt.executeUpdate();
cstmt.close();
} catch (Exception e) {
log.log(Level.SEVERE, sql, e);
m_pi.setSummary(Msg.getMsg(Env.getCtx(), "ProcessRunError") + " " + e.getLocalizedMessage());
m_pi.setError(true);
return false;
}
// log.fine(Log.l4_Data, "ProcessCtl.startProcess - done");
return true;
} // startDBProcess
public static boolean actualizar(entPuerto entidad) throws Exception {
boolean rpta = false;
Connection conn = null;
CallableStatement stmt = null;
try {
String sql =
"UPDATE puerto SET nombre = ?,estado= ?,"
+ "usuario_responsable = ?,fecha_modificacion = GETDATE() WHERE id_puerto = ?;";
conn = ConexionDAO.getConnection();
stmt = conn.prepareCall(sql);
stmt.setString(1, entidad.getNombre());
stmt.setBoolean(2, entidad.getEstado());
stmt.setString(3, entidad.getUsuario_responsable());
stmt.setInt(4, entidad.getId_puerto());
rpta = stmt.executeUpdate() == 1;
} catch (Exception e) {
throw new Exception("Error Actualizar " + e.getMessage(), e);
} finally {
try {
stmt.close();
conn.close();
} catch (SQLException e) {
}
}
return rpta;
}
public boolean insertCurfew(
String stuId, String stuName, String curfew, String rmark, String dMid)
throws DataBaseException, QueryResultIsNullException {
try {
CallableStatement cst = connection.prepareCall("{call proc_Curfew(?,?,?,?,?)}");
cst.setString(1, stuId);
cst.setString(2, stuName);
cst.setString(3, curfew);
cst.setString(4, rmark);
cst.setString(5, dMid);
int r = cst.executeUpdate();
cst.close();
if (r > 0) {
return true;
} else {
return false;
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
return false;
}
}
/**
* 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 boolean cambiarEstado(orgen_ta_subdependencia obj, String idUsu) {
boolean exito = false;
try {
cn = ds.getConnection();
cn.setAutoCommit(false);
String sql = "{ CALL SP_ORGEN_TA_SUBDEPENDENCIA_ELI(?,?,?) }";
CallableStatement cstm = cn.prepareCall(sql);
cstm.setInt("P_IN_CODIGO_SUBDEPENDENCIA", obj.getIn_codigo_subdependencia());
cstm.setString("P_CH_ESTADO", obj.getCh_estado());
cstm.setInt("P_IN_CODIGO_USUARIO", Integer.parseInt(idUsu));
cstm.executeUpdate();
cn.commit();
cstm.close();
exito = true;
} catch (Exception e) {
exito = false;
System.out.println("Failed to execute a JDBC task: " + e);
} finally {
try {
cn.close();
} catch (Exception ex) {
System.out.println("finally - Failed to finalize JDBC task: " + ex);
}
}
return exito;
}
// test: do we get an appropriate update count when using ?=call?
private static void testUpdate(Connection conn) throws Throwable {
System.out.println("==============================================");
System.out.println("TESTING UPDATE COUNT");
System.out.println("==============================================\n");
Statement scp = conn.createStatement();
scp.execute(
"CREATE FUNCTION returnsIntegerP(P1 INT) RETURNS INTEGER "
+ "EXTERNAL NAME '"
+ CLASS_NAME
+ "returnsIntegerP'"
+ " NO SQL LANGUAGE JAVA PARAMETER STYLE JAVA");
CallableStatement cs = conn.prepareCall("? = call returnsIntegerP(0)");
cs.registerOutParameter(1, Types.INTEGER);
try {
int updCount = cs.executeUpdate();
System.out.println("executeUpdate on ? = call returnsIntegerP returned " + updCount);
System.out.println("getString(1) returned " + cs.getString(1));
} catch (SQLException se) {
System.out.println("cs.execute() got unexpected exception: " + se);
}
cs.close();
scp.execute("DROP FUNCTION returnsIntegerP");
scp.close();
}
/** @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response) */
protected void doPost(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
// / TODO Auto-generated method stub
PrintWriter out = response.getWriter();
String mail = request.getParameter("Correo");
DBConnection ping = new DBConnection();
Connection con = ping.makeConnection("root", "sharPedo319");
String resp = null;
try {
CallableStatement stmnt = con.prepareCall("{call obtenerPProducto(?)}");
stmnt.setString(1, mail);
// resp = stmnt.getString(1);
stmnt.executeUpdate();
ResultSet rs = stmnt.getResultSet();
while (rs.next()) {
json.put("activo", rs.getString(1));
json.put("imagen", rs.getString(2));
json.put("nombre", rs.getString(3));
json.put("ubicacionInt", rs.getString(4));
// resp = resp.concat("\nActivo:" + rs.getString(1) + ", imagen:" + rs.getString(2) + ",
// nombre:" + rs.getString(3) + ", ubicacionInt" + rs.getString(4));
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
resp = e.getMessage();
} catch (JSONException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
ping.closeConnection();
}
out.print(resp);
// doGet(request, response);
}
public boolean guardarSubDependencia(
orgen_ta_subdependencia obj, String nomUsuario, int idUsuario) {
boolean estado = false;
try {
cn = ds.getConnection();
cn.setAutoCommit(false);
String sql = "{ CALL SP_ORGEN_TA_SUBDEPENDENCIA_INS(?,?,?,?,?) }";
CallableStatement cstm = cn.prepareCall(sql);
cstm.setString("P_VC_NOMBRE", obj.getVc_nombre().toUpperCase());
cstm.setString("P_VC_DESCRIPCION", obj.getVc_descripcion().toUpperCase());
cstm.setString("P_VC_USUARIO_CREA", nomUsuario);
cstm.setInt("P_IN_CODIGO_USUARIO", idUsuario);
cstm.setInt("P_IN_CODIGO_DEP", obj.getIn_codigo_dependecia());
cstm.executeUpdate();
cn.commit();
estado = true;
cstm.close();
} catch (Exception e) {
System.out.println("Failed to execute a JDBC task: " + e);
estado = false;
} finally {
try {
cn.close();
} catch (Exception ex) {
System.out.println("finally - Failed to finalize JDBC task: " + ex);
}
return estado;
}
}
public User insertUser(User rec) {
User flag = null;
try {
DBCON ob = new DBCON();
Connection con = null;
CallableStatement cs = null;
con = ob.createConnection();
cs = con.prepareCall("{call trs_srilanka.insertUser(?,?,?,?,?,?,?,?,?,?)}");
cs.setLong(1, rec.getUSID());
cs.setString(2, rec.getFname());
cs.setString(3, rec.getLname());
cs.setString(4, rec.getAgeRange());
cs.setString(5, rec.getGender());
cs.setString(6, rec.getEmail());
cs.setString(7, rec.getPassword());
cs.setString(8, rec.getCountry());
cs.setString(9, rec.getUsertype());
cs.setString(10, rec.getAccountStatus());
int res = cs.executeUpdate();
if (res > 0) {
flag = rec;
} else {
flag = null;
}
} catch (Exception ex) {
ex.printStackTrace();
}
return flag;
}
public Tour insertTour(Tour rec) {
Tour flag = null;
try {
DBCON ob = new DBCON();
Connection con = null;
CallableStatement cs = null;
con = ob.createConnection();
cs = con.prepareCall("{call trs_srilanka.insertTour(?,?,?,?,?,?,?)}");
cs.setLong(1, rec.getTRID());
cs.setString(2, rec.getTitle());
cs.setString(3, rec.getItinary());
cs.setString(4, rec.getNoOfDays());
cs.setString(5, rec.getAccomadationType());
cs.setString(6, rec.getBasis());
cs.setLong(7, rec.getGEOID());
int res = cs.executeUpdate();
if (res > 0) {
flag = rec;
} else {
flag = null;
}
} catch (Exception ex) {
ex.printStackTrace();
}
return flag;
}
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());
}
}
public boolean insertLCRecord(String stuId, String stuName, String data, String lC, String mark)
throws DataBaseException, QueryResultIsNullException {
// System.out.println("***********---");
try {
CallableStatement cst = connection.prepareCall("{call proc_LeaveComeStu(?,?,?,?,?)}");
cst.setString(1, stuId);
cst.setString(2, stuName);
cst.setString(3, data);
cst.setString(4, lC);
cst.setString(5, mark);
// System.out.println("***********---");
int r = cst.executeUpdate();
cst.close();
if (r > 0) {
return true;
} else {
return false;
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
return false;
}
}
public String getGarageFromResp(
String profile, Integer userId, Integer respId, Integer respApplId) {
String garage = null;
CallableStatement cs = null;
String statement = "BEGIN ? := FND_PROFILE.VALUE_SPECIFIC(?, ?, ?, ?); END;";
try {
cs = getDBTransaction().createCallableStatement(statement, 0);
cs.registerOutParameter(1, Types.VARCHAR);
cs.setString(2, profile);
cs.setInt(3, userId.intValue());
cs.setInt(4, respId.intValue());
cs.setInt(5, respApplId.intValue());
cs.executeUpdate();
garage = cs.getString(1);
} catch (SQLException e) {
_logger.severe("SQL Exception setGarageFromResp", e);
} finally {
try {
if (cs != null) {
cs.close();
}
} catch (SQLException e) {
_logger.severe("SQL Exception setGarageFromResp", e);
}
}
return garage;
}
/**
* Executes a stored Procedure that will update Fields in DB (Ask DBM for SP names)
*
* @param spName Stores Procedure's name
* @param param integer parameter needed by the SP
* @return return 1 if update worked
* @throws SQLException mostly if SP does not exist in DB
*/
public boolean updateStoredProcedure(String spName, int param) throws SQLException {
// preparing callable statement
cs = conn.prepareCall("{call " + spName + "(?)}");
cs.setInt(1, param);
return (cs.executeUpdate() == 1);
}
public boolean outStu(String stuId, String rmark) throws DataBaseException {
Statement stmt;
stmt = DB.CreateStatement();
String sql = "select StuId from StuDormRoom where StuId='" + stuId + "'";
try {
ResultSet rs = stmt.executeQuery(sql);
if (rs != null && !rs.next()) {
return false;
}
} catch (SQLException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
}
try {
CallableStatement cst = connection.prepareCall("{call proc_StuInOut_SDM_out(?,?)}");
cst.setString(1, stuId);
cst.setString(2, rmark);
int r = cst.executeUpdate();
cst.close();
if (r > 0) {
return true;
} else {
return false;
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
return false;
}
}
public static void main(String[] args) {
// Realiza una función en MySql que dado un curso, devuelva el nombre del tutor. Haz un programa
// en java que llame a dicha función y muestre por consola el resultado.
String codOe, codCur;
Connection conexion = BddConexion.newConexion("horario");
String sql = "{ ? = call dameTutor(?, ?)}";
CallableStatement llamada;
System.out.print("Indique oferta educativa: ");
codOe = Teclado.leerPalabra();
System.out.print("Indique curso: ");
codCur = Teclado.leerPalabra();
try {
llamada = conexion.prepareCall(sql);
llamada.registerOutParameter(1, Types.VARCHAR);
llamada.setString(2, codOe);
llamada.setString(3, codCur);
llamada.executeUpdate();
System.out.println(llamada.getString(1));
llamada.close();
} catch (SQLException e) {
e.printStackTrace();
}
BddConexion.closeConexion(conexion);
}
public void nonQueryStoredProcedure(String SPName, int param1, int param2) throws SQLException {
// preparing callable statement
cs = conn.prepareCall("{call " + dbName + "." + SPName + "(?,?)}");
cs.setInt(1, param1);
cs.setInt(2, param2);
cs.executeUpdate();
}
/**
* Inserts a new mapping from method id to word id to the methods_words table of the database.
* Each row represents one instance of a word existing in the method. Unlike the other inserts,
* duplicate entries are allowed since each word can exist more than once in a method.
*
* @param methodId The method's database ID
* @param wordId The word's ID
*/
public void insertMethodToWordMapping(int methodId, int wordId) {
try {
sp_insertMethodToWordMapping.setInt(1, methodId);
sp_insertMethodToWordMapping.setInt(2, wordId);
sp_insertMethodToWordMapping.executeUpdate();
} catch (SQLException e) {
eLog.logException(e);
}
}
public String[] callplBtnprocess(
String processId, String programId, String username, int org, String preD, String sentD) {
String[] results = null;
Connection dbConnection = null;
CallableStatement callableStatement = null;
_log.info("##into PL");
String MR_SP_ZZZZ_RESTORE_PROCESS_Sql = "{call MR_STP_PREPARE_AND_SEND_DATA(?,?,?,?,?,?,?,?)}";
try {
dbConnection = DBManager.getNoJtaDataSource();
callableStatement = dbConnection.prepareCall(MR_SP_ZZZZ_RESTORE_PROCESS_Sql);
/*
p_status OUT VARCHAR2,
p_result OUT VARCHAR2,
p_user_name IN VARCHAR2,
p_org_id IN NUMBER,
p_process_id IN VARCHAR2,
p_program_id IN VARCHAR2,
p_prepare_data IN VARCHAR2, 7
p_send_data IN VARCHAR2 8
*/
callableStatement.registerOutParameter(1, java.sql.Types.VARCHAR);
callableStatement.registerOutParameter(2, java.sql.Types.VARCHAR);
callableStatement.setString(3, username);
callableStatement.setInt(4, 1);
callableStatement.setString(5, processId);
callableStatement.setString(6, programId);
callableStatement.setString(7, preD);
callableStatement.setString(8, sentD);
callableStatement.executeUpdate();
String status = callableStatement.getString(1);
String result = callableStatement.getString(2);
results = new String[] {status, result};
_log.info("status 2: " + status);
_log.info("result 2: " + result);
} catch (Exception e) {
e.printStackTrace();
} finally {
if (callableStatement != null) {
try {
callableStatement.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (dbConnection != null) {
try {
dbConnection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
return results;
}
/**
* Check that System.exit() cannot be called directly from a procedure.
*
* @throws SQLException
*/
public void testSystemExit() throws SQLException {
CallableStatement cs = prepareCall("CALL DENIAL_OF_SERVICE(?)");
cs.setInt(1, -1);
try {
cs.executeUpdate();
fail("Tough to get here since exit would have been called.");
} catch (SQLException e) {
assertSecurityException(e);
}
cs.setInt(1, 0);
try {
cs.executeUpdate();
fail("Tough to get here since exit would have been called.");
} catch (SQLException e) {
assertSecurityException(e);
}
cs.close();
}
/**
* Updates an existing word and word count for a given method. This method should be only used if
* the row already exists in the database. Existence can be verified using the
* getWordCountFromMethodIdAndWordId(int, int) method. If the method does not exist,
* insertWordCountForMethodIdAndWordId(int, int) should be used instead.
*
* @param methodId The method's database id
* @param wordId The word's database id
* @param wordCount The number of times the word occurs in the given method
*/
public void updateWordCountForMethodIdAndWordId(int methodId, int wordId, int wordCount) {
try {
sp_updateWordCountForMethodIdAndWordId.setInt(1, methodId);
sp_updateWordCountForMethodIdAndWordId.setInt(2, wordId);
sp_updateWordCountForMethodIdAndWordId.setInt(3, wordCount);
sp_updateWordCountForMethodIdAndWordId.executeUpdate();
} catch (SQLException e) {
eLog.logException(e);
}
}
/**
* Inserts a new word to the words table of the database. If a word already exists, no rows are
* added or modified in the database.
*
* @param word The word to add
*/
public void insertWord(String word) {
try {
sp_insertWord.setString(1, word.toLowerCase());
sp_insertWord.executeUpdate();
} catch (MySQLIntegrityConstraintViolationException e) {
// Ignore, this is to handle duplicate entries
} catch (SQLException e) {
eLog.logException(e);
}
}
public void ramasserObjet() {
boolean vide = true;
try {
ResultSet rset =
stmt.executeQuery(
"select idObjet from objet where positionX="
+ positionX
+ " and positionY="
+ positionY);
Hashtable<String, String> tabObj = new Hashtable<String, String>();
while (rset.next()) {
vide = false;
String idObj = rset.getString("idObjet");
System.out.println(idObj);
tabObj.put(idObj, idObj);
}
rset.close();
if (!vide) {
System.out.println("Quel objet voulez vous ramasser ? : ");
String obj = IO.lireChaine();
if (tabObj.containsKey(obj)) {
proc = conn.prepareCall("{call rammasser(?,?)}");
proc.setInt(1, idTroll);
proc.setString(2, obj);
proc.executeUpdate();
proc.close();
rset = stmt.executeQuery("select typeObjet from objet where idObjet='" + obj + "'");
rset.first();
String type = rset.getString("typeObjet");
if (type.equals("potion")) {
menu.supprimerPopo(obj);
} else {
menu.supprimerObjet(obj);
}
rset = stmt.executeQuery("select paRestants from troll where idTroll=" + idTroll);
int ancVal = 0;
while (rset.next()) {
ancVal = rset.getInt("paRestants");
}
stmt.executeUpdate(
"update troll SET paRestants = " + (ancVal - 1) + " where idTroll=" + idTroll);
paRestants = paRestants - 1;
} else {
System.out.println("Cet objet ne se trouve pas sur votre case !");
}
} else {
System.out.println("Il n'y a aucun objet sur votre case");
}
} catch (SQLException E) {
System.err.println("SQLException: " + E.getMessage());
System.err.println("SQLState: " + E.getSQLState());
}
}
/**
* Inserts a method's data to the methods table of the database. If the method's key already
* exists in the database, the new information is ignored and no database rows are added or
* modified.
*
* @param methodData The method to insert
*/
public void insertMethod(MethodData methodData) {
try {
sp_insertMethod.setString(1, methodData.getKey());
sp_insertMethod.setString(2, methodData.getName());
sp_insertMethod.setString(3, methodData.getPath());
sp_insertMethod.executeUpdate();
} catch (MySQLIntegrityConstraintViolationException e) {
// Ignore, this is to handle duplicate entries
} catch (SQLException e) {
eLog.logException(e);
}
}
/**
* Function deleteReservation takes in a reservation id and seat id and deletes the reservation at
* the values.
*
* @param resId
* @param seatId
*/
void deleteReservation(int resId, int seatId) {
CallableStatement cs = null;
try {
String call = "{call delete_res(?, ?)}";
cs = en.conn.prepareCall(call);
cs.setInt(1, resId);
cs.setInt(2, seatId);
cs.executeUpdate();
en.conn.commit();
} catch (SQLException e) {
System.err.println("Drop Reservation: deleteReservation - " + e.getMessage());
}
}
/**
*
*
* <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 spExecute(String sql) {
Connection con = null;
CallableStatement stat = null;
try {
con = cpds.getConnection();
stat = con.prepareCall(sql);
int num = stat.executeUpdate();
return num;
} catch (SQLException e) {
throw new RuntimeException(e);
} finally {
close(stat, con, null);
}
}
public boolean Delete(PublisherDA aPublisherDA) {
try {
CallableStatement call = conn.prepareCall("{call spd_Publisher_Delete(?)}");
call.setInt(1, aPublisherDA.getIDPublisher());
if (call.executeUpdate() == 1) {
return true;
} else {
return false;
}
} catch (SQLException ex) {
Logger.getLogger(PublisherBO.class.getName()).log(Level.SEVERE, null, ex);
return false;
}
}