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;
}
public boolean insertarAlquiler(
int idtrabajador,
int idCliente,
ArrayList<Detalle_Alquiler> lista_detalle,
int identificador) {
boolean resultado = false;
Connection cnn = null;
CallableStatement cstmt = null;
CallableStatement cstm1 = null;
CallableStatement cstm2 = null;
int id_alquiler = 0;
try {
cnn = BD.getConnection();
cnn.setAutoCommit(false);
String sql = "call spI_Alquiler (?,?,?,?);";
cstmt = cnn.prepareCall(sql);
cstmt.setInt(1, 2);
cstmt.setInt(2, idCliente);
cstmt.setInt(3, idtrabajador);
cstmt.setInt(4, identificador);
ResultSet rs = cstmt.executeQuery();
if (rs.next()) {
id_alquiler = rs.getInt("int_id");
}
for (int i = 0; i < lista_detalle.size(); i++) {
String sql1 = "call spI_DetalleAlquiler(?,?,?,?,?,?,?);";
cstm1 = cnn.prepareCall(sql1);
cstm1.setInt(1, id_alquiler);
cstm1.setInt(2, lista_detalle.get(i).getMaterial_id());
cstm1.setInt(3, lista_detalle.get(i).getInt_cantidad());
cstm1.setDouble(4, lista_detalle.get(i).getDec_monto());
cstm1.setTimestamp(5, lista_detalle.get(i).getDat_fechfin());
cstm1.setTimestamp(6, lista_detalle.get(i).getDat_fechinicio());
cstm1.setInt(7, lista_detalle.get(i).getInt_horas());
cstm1.execute();
}
// Registrar Pagos
String sql2 = "call spI_Pagos_ByAlquiler(?,?);";
cstm2 = cnn.prepareCall(sql2);
cstm2.setInt(1, 1); // es el codigo del usuario cambiar despues
cstm2.setInt(2, id_alquiler);
cstm2.execute();
cnn.commit();
resultado = true;
} catch (SQLException s) {
try {
cnn.rollback();
} catch (SQLException b) {
}
System.out.println("aquí es :/ " + s);
} finally {
try {
cstmt.close();
cnn.close();
} catch (SQLException ex) {
}
}
return resultado;
}
private static void testNull(Connection conn) throws Throwable {
System.out.println("==============================================");
System.out.println("TESTING NULLS");
System.out.println("==============================================\n");
System.out.println("Test for bug 4317, passing null value for a parameter");
Statement scp = conn.createStatement();
scp.execute(
"CREATE PROCEDURE testNullBug4317(IN P1 VARCHAR(10)) "
+ "EXTERNAL NAME '"
+ CLASS_NAME
+ "testNullBug4317'"
+ " NO SQL LANGUAGE JAVA PARAMETER STYLE JAVA");
CallableStatement cs0 = conn.prepareCall("call testNullBug4317(?)");
try {
cs0.setString(1, null); // passing in null
cs0.execute();
} catch (SQLException se) {
System.out.println("cs0.execute() got unexpected exception: " + se);
}
try {
// BUG 5928 - setNull throws an exception - fixed.
cs0.setNull(1, java.sql.Types.VARCHAR); // passing in null
cs0.execute();
} catch (SQLException se) {
System.out.println("cs0.execute() got unexpected exception: " + se);
}
cs0.close();
scp.execute("DROP PROCEDURE testNullBug4317");
}
private static void test5116(Connection conn) throws Throwable {
System.out.println("==============================================");
System.out.println("TESTING FIX OF 5116 -- VAR BIT VARYING INPUT");
System.out.println("==============================================\n");
Statement stmt = conn.createStatement();
stmt.executeUpdate(
"CREATE TABLE ACTIVITY_INSTANCE_T ("
+ "AIID char(16) for bit data NOT NULL ,"
+ "KIND INTEGER NOT NULL ,"
+ "PIID char(16) for bit data NOT NULL ,"
+ "PTID char(16) for bit data NOT NULL ,"
+ "ATID char(16) for bit data NOT NULL ,"
+ "RUN_MODE INTEGER NOT NULL ,"
+ "FINISHED TIMESTAMP ,"
+ "ACTIVATED TIMESTAMP ,"
+ "STARTED TIMESTAMP ,"
+ "LAST_MODIFIED TIMESTAMP ,"
+ "LAST_STATE_CHANGE TIMESTAMP ,"
+ "STATE INTEGER NOT NULL ,"
+ "TRANS_COND_VALUES VARCHAR(66) FOR BIT DATA NOT NULL ,"
+ "NUM_CONN_ACT_EVA INTEGER NOT NULL ,"
+ "NUMBER_OF_ITERATIONS INTEGER NOT NULL ,"
+ "NUMBER_OF_RETRIES INTEGER NOT NULL ,"
+ "HAS_CUSTOM_ATTRIBUTES SMALLINT NOT NULL ,"
+ "NON_BLOCK_PTID char(16) for bit data NOT NULL ,"
+ "NON_BLOCK_PIID char(16) for bit data NOT NULL ,"
+ "EXPIRES TIMESTAMP ,"
+ "TASK_ID VARCHAR(254) ,"
+ "UNHANDLED_EXCEPTION BLOB(3993600) ,"
+ "SUB_PROCESS_PIID char(16) for bit data ,"
+ "OWNER VARCHAR(32) ,"
+ "USER_INPUT VARCHAR(130) FOR BIT DATA ,"
+ "DESCRIPTION VARCHAR(254) ,"
+ "VERSION_ID SMALLINT NOT NULL ,"
+ "PRIMARY KEY ( AIID ) )");
stmt.execute(
"CREATE PROCEDURE doInsertion(IN P1 VARCHAR(2) FOR BIT DATA) "
+ "EXTERNAL NAME '"
+ CLASS_NAME
+ "doInsertion'"
+ " MODIFIES SQL DATA LANGUAGE JAVA PARAMETER STYLE JAVA");
CallableStatement cs = conn.prepareCall("call doInsertion (?)");
cs.setNull(1, java.sql.Types.VARBINARY);
cs.execute();
byte[] b = new byte[2];
b[0] = 1;
b[1] = 2;
cs.setBytes(1, b);
cs.execute();
cs.close();
stmt.executeUpdate("DROP PROCEDURE doInsertion");
stmt.close();
}
public boolean execute() throws SQLException {
if (factory.isJDBCMonitoringEnabled()) {
ExecutionTimer timer = new ExecutionTimer(ctx);
MonitorFactory.getFacade().recordExecutionBegin(timer);
try {
return passThru.execute();
} finally {
MonitorFactory.getFacade().recordExecutionEnd(timer);
}
} else {
return passThru.execute();
}
}
@BeforeClass
public static void initDriver() throws Exception {
Class.forName("org.postgresql.Driver");
PoolingDataSource ds = new PoolingDataSource();
ds.setServerName(System.getProperty("pgsql.server", "localhost"));
ds.setPortNumber(Integer.parseInt(System.getProperty("pgsql.port", "5432")));
ds.setDatabaseName(System.getProperty("pgsql.db", "test"));
ds.setUser(System.getProperty("pgsql.user", "test"));
ds.setPassword(System.getProperty("pgsql.password", "test"));
ds.setMaxConnections(10);
ds.setInitialConnections(1);
try {
ds.initialize();
} catch (Exception e) {
skipTests = true;
System.out.println("Failed to initialize datasource. Tests will be skipped ...");
e.printStackTrace();
return;
}
datasource = ds;
schema = "xlo_test_" + UUID.randomUUID().toString().substring(0, 8);
schema_two = "xlo_test_" + UUID.randomUUID().toString().substring(0, 8);
if (schema.compareTo(schema_two) > 0) {
String tmp = schema;
schema = schema_two;
schema_two = tmp;
}
// first
try {
cleanup();
} catch (Exception e) {
// ignore
}
// create schema for the test
try (Connection c = datasource.getConnection()) {
try (CallableStatement s = c.prepareCall("create schema " + schema)) {
s.execute();
}
try (CallableStatement s = c.prepareCall("create schema " + schema_two)) {
s.execute();
}
}
createTables();
insertData();
}
@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;
}
@Override
void closeConnection(final Connection connection) throws SQLException {
if (aggressiveCompact) {
CallableStatement statement = null;
try {
LOCK.writeLock().lock();
final java.util.Date start = new java.util.Date();
LOGGER.trace("beginning shutdown compact");
statement = dbConnection.prepareCall("SHUTDOWN COMPACT");
statement.execute();
LOGGER.trace(
"completed shutdown compact in " + TimeDuration.fromCurrent(start).asCompactString());
} catch (SQLException ex) {
LOGGER.error("error during shutdown compact: " + ex.getMessage());
} finally {
close(statement);
LOCK.writeLock().unlock();
}
}
try {
connection.close();
if (driver != null) {
DriverManager.deregisterDriver(driver);
driver = null;
}
} catch (Exception e) {
LOGGER.error("error during H2 shutdown: " + e.getMessage());
}
}
@SuppressWarnings("deprecation")
protected void spSalFactD(Map params) {
try {
Connection conn = getSession().connection();
CallableStatement cs =
conn.prepareCall("{call SP_HRSAL_FACTDT(?, ?, ?, ?, ?, ?, ?, ?, ?, ?)}");
int branchId = ((Integer) params.get("branch.id")).intValue();
String hdNo = (String) params.get("fact.no");
int no = ((Integer) params.get("no")).intValue();
int itemId = ((Integer) params.get("item.id")).intValue();
int departId = ((Integer) params.get("depart.id")).intValue();
int personId = ((Integer) params.get("person.id")).intValue();
double amount = ((Double) params.get("amount")).doubleValue();
Date date = new Date(ObjectUtil.toCalendar(params.get("date")).getTimeInMillis());
String workerId = (String) params.get("person.workerId");
String type = (String) params.get("type");
int index = 1;
cs.setInt(index++, branchId);
cs.setString(index++, hdNo);
cs.setInt(index++, no);
cs.setInt(index++, itemId);
cs.setInt(index++, departId);
cs.setInt(index++, personId);
cs.setDouble(index++, amount);
cs.setDate(index++, date);
cs.setString(index++, workerId);
cs.setString(index++, type);
cs.execute();
} catch (Exception e) {
throw new HibernateException(e);
}
}
@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;
}
public static void main(String args[]) throws Exception {
// Instantiate our DAO and get connection
Dao dao = new Dao();
Connection conn = dao.getConnection();
// Prepare a callable statement
// NOTE: Curly brackets aka braces are required for the call builder
// to work correctly. Otherwise it'll actually just treat it as a
// PreparedStatement, which may or may not be what you want!
String s1 = "{CALL ADD_SERVICE(?,?,?)}";
CallableStatement cs = conn.prepareCall(s1);
// Set variables the usual way
cs.setString(1, "DUB");
cs.setString(2, "EWR");
cs.setString(3, "A380");
// Execute it
cs.execute();
// Get the result set (as usual)
ResultSet rs = cs.getResultSet();
// Go to the first result (the only one!) which came from the SELECT
// statement in the procedure.
rs.next();
// Print the flight number to the screen
System.out.println("New flight number is " + rs.getInt("flightNumber"));
}
public static void main(String[] args) {
try {
// 1.加载驱动
Class.forName("oracle.jdbc.driver.OracleDriver");
// 2.获取连接
Connection connection =
DriverManager.getConnection(
"jdbc:oracle:thin:@127.0.0.1:1521:orcl", "SCOTT", "wcy675600920");
// 3.创建CallableStatement
CallableStatement cs = connection.prepareCall("{call wwss_pro3(?,?)}");
// 4.给？赋值
cs.setString(1, "9527");
cs.setInt(2, 3000);
// 5.执行
cs.execute();
// 6.关闭
cs.close();
connection.close();
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
}
public ResultSet getResultSet(CallableStatement ps) throws SQLException {
boolean isResultSet = ps.execute();
while (!isResultSet && ps.getUpdateCount() != -1) {
isResultSet = ps.getMoreResults();
}
return ps.getResultSet();
}
@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 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 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
protected void saveUser(User u) {
/*procedure add_user(
p_id in dxdy_user.id%type,
p_username in dxdy_user.username%type,
p_status in dxdy_status.status_name%type,
p_reg_date in varchar2);
*/
super.saveUser(u);
String procedure = "{call dxdy_executable.add_user (?, ?, ?, ?)}";
CallableStatement st = null;
try {
st = connection.prepareCall(procedure);
st.setInt(1, u.ID);
st.setString(2, u.name);
st.setString(3, u.status);
st.setString(4, u.regDate);
st.execute();
// System.out.println("User ID = " + u.ID + ": OK");
} catch (SQLException e) {
e.printStackTrace();
} finally {
if (st != null) {
try {
st.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
@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();
}
}
}
}
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;
}
}
}
@Override
public void doPost(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
org.owasp.benchmark.helpers.SeparateClassRequest scr =
new org.owasp.benchmark.helpers.SeparateClassRequest(request);
String param = scr.getTheParameter("foo");
String bar = new Test().doSomething(param);
String sql = "{call verifyUserPassword('foo','" + bar + "')}";
try {
java.sql.Connection connection =
org.owasp.benchmark.helpers.DatabaseHelper.getSqlConnection();
java.sql.CallableStatement statement =
connection.prepareCall(
sql,
java.sql.ResultSet.TYPE_FORWARD_ONLY,
java.sql.ResultSet.CONCUR_READ_ONLY,
java.sql.ResultSet.CLOSE_CURSORS_AT_COMMIT);
statement.execute();
} catch (java.sql.SQLException e) {
throw new ServletException(e);
}
} // end doPost
@Override
protected Boolean vDelRow(Connection conn, ArrayList curRow) throws Exception {
ADelDialog d = new ADelDialog(null, true);
d.addPar("Наименование", curRow.get(1).toString());
d.addPar("Описание", curRow.get(2).toString());
d.setTitleIcon(
new javax.swing.ImageIcon(
getClass().getResource("/tradeterminal/icons/TT_icons/32X32/typdel.png")));
d.setVisible(true);
d.dispose();
if (d.getReturnStatus() == ADialog.RET_OK) {
int id = Integer.parseInt(curRow.get(0).toString());
CallableStatement proc = conn.prepareCall("{call rb_doc_types_delete(?)}");
proc.setInt(1, id);
proc.execute();
proc.close();
return true;
} else {
return false;
}
}
@Override
public void doPost(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
String param = "";
java.util.Enumeration<String> headerNames = request.getHeaderNames();
if (headerNames.hasMoreElements()) {
param = headerNames.nextElement(); // just grab first element
}
String bar = new Test().doSomething(param);
String sql = "{call verifyUserPassword('foo','" + bar + "')}";
try {
java.sql.Connection connection =
org.owasp.benchmark.helpers.DatabaseHelper.getSqlConnection();
java.sql.CallableStatement statement =
connection.prepareCall(
sql,
java.sql.ResultSet.TYPE_FORWARD_ONLY,
java.sql.ResultSet.CONCUR_READ_ONLY,
java.sql.ResultSet.CLOSE_CURSORS_AT_COMMIT);
statement.execute();
} catch (java.sql.SQLException e) {
throw new ServletException(e);
}
} // end doPost
@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;
}
}
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));
}
}
/**
* 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 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;
}
@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);
}
}
}
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
public void doPost(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
org.owasp.benchmark.helpers.SeparateClassRequest scr =
new org.owasp.benchmark.helpers.SeparateClassRequest(request);
String param = scr.getTheValue("foo");
String bar = "safe!";
java.util.HashMap<String, Object> map13216 = new java.util.HashMap<String, Object>();
map13216.put("keyA-13216", "a Value"); // put some stuff in the collection
map13216.put("keyB-13216", param.toString()); // put it in a collection
map13216.put("keyC", "another Value"); // put some stuff in the collection
bar = (String) map13216.get("keyB-13216"); // get it back out
String sql = "{call verifyUserPassword('foo','" + bar + "')}";
try {
java.sql.Connection connection =
org.owasp.benchmark.helpers.DatabaseHelper.getSqlConnection();
java.sql.CallableStatement statement = connection.prepareCall(sql);
statement.execute();
} catch (java.sql.SQLException e) {
throw new ServletException(e);
}
}