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");
}
/**
* 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;
}
public void setNull(String parameterName, int sqlType, String typeName) throws SQLException {
checkOpen();
try {
_stmt.setNull(parameterName, sqlType, typeName);
} catch (SQLException e) {
handleException(e);
}
}
public void setNull(String parameterName, int sqlType) throws SQLException {
checkOpen();
try {
((CallableStatement) _stmt).setNull(parameterName, sqlType);
} catch (SQLException e) {
handleException(e);
}
}
/*
* (non-Javadoc)
*
* @see
* pe.com.viajes.negocio.dao.ServicioNoviosDao#registrarNovios(pe.com
* .logistica.bean.negocio.ProgramaNovios)
*/
@Override
public Integer registrarNovios(ProgramaNovios programaNovios) throws SQLException {
Integer codigoNovios = 0;
Connection conn = null;
CallableStatement cs = null;
String sql = "{ ? = call negocio.fn_ingresarprogramanovios(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?) }";
try {
conn = UtilConexion.obtenerConexion();
cs = conn.prepareCall(sql);
int i = 1;
cs.registerOutParameter(i++, Types.INTEGER);
cs.setInt(i++, programaNovios.getNovia().getCodigoEntero());
cs.setInt(i++, programaNovios.getNovio().getCodigoEntero());
cs.setInt(i++, programaNovios.getDestino().getCodigoEntero());
cs.setDate(i++, UtilJdbc.convertirUtilDateSQLDate(programaNovios.getFechaBoda()));
cs.setDate(i++, UtilJdbc.convertirUtilDateSQLDate(programaNovios.getFechaViaje()));
cs.setInt(i++, programaNovios.getMoneda().getCodigoEntero());
cs.setBigDecimal(i++, programaNovios.getCuotaInicial());
cs.setInt(i++, programaNovios.getNroDias());
cs.setInt(i++, programaNovios.getNroNoches());
cs.setDate(i++, UtilJdbc.convertirUtilDateSQLDate(programaNovios.getFechaShower()));
if (StringUtils.isNotBlank(programaNovios.getObservaciones())) {
cs.setString(i++, programaNovios.getObservaciones());
} else {
cs.setNull(i++, Types.VARCHAR);
}
cs.setBigDecimal(i++, programaNovios.getMontoTotalServiciosPrograma());
cs.setInt(i++, programaNovios.getIdServicio());
cs.setString(i++, programaNovios.getUsuarioCreacion());
cs.setString(i++, programaNovios.getIpCreacion());
cs.execute();
codigoNovios = cs.getInt(1);
} catch (SQLException e) {
codigoNovios = 0;
throw new SQLException(e);
} finally {
try {
if (cs != null) {
cs.close();
}
if (conn != null) {
conn.close();
}
} catch (SQLException e) {
try {
if (conn != null) {
conn.close();
}
throw new SQLException(e);
} catch (SQLException e1) {
throw new SQLException(e);
}
}
}
return codigoNovios;
}
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();
}
/* (non-Javadoc)
* @see pe.com.logistica.negocio.dao.ReporteVentasDao#reporteGeneralVentas(java.util.Date, java.util.Date)
*/
@Override
public List<DetalleServicioAgencia> reporteGeneralVentas(ReporteVentas reporteVentas)
throws SQLException {
Connection conn = null;
CallableStatement cs = null;
ResultSet rs = null;
String sql = "{ ? = call reportes.fn_re_generalventas(?,?,?) }";
List<DetalleServicioAgencia> resultado = null;
try {
conn = UtilConexion.obtenerConexion();
cs = conn.prepareCall(sql);
int i = 1;
cs.registerOutParameter(i++, Types.OTHER);
cs.setDate(i++, UtilJdbc.convertirUtilDateSQLDate(reporteVentas.getFechaDesde()));
cs.setDate(i++, UtilJdbc.convertirUtilDateSQLDate(reporteVentas.getFechaHasta()));
if (reporteVentas.getVendedor().getCodigoEntero() != null
&& reporteVentas.getVendedor().getCodigoEntero().intValue() != 0) {
cs.setInt(i++, reporteVentas.getVendedor().getCodigoEntero().intValue());
} else {
cs.setNull(i++, Types.INTEGER);
}
cs.execute();
rs = (ResultSet) cs.getObject(1);
resultado = new ArrayList<DetalleServicioAgencia>();
DetalleServicioAgencia detalle = null;
while (rs.next()) {
detalle = new DetalleServicioAgencia();
detalle.getTipoServicio().setCodigoEntero(UtilJdbc.obtenerNumero(rs, "idtiposervicio"));
detalle.getTipoServicio().setNombre(UtilJdbc.obtenerCadena(rs, "nombre"));
detalle.setCantidad(UtilJdbc.obtenerNumero(rs, "cantidad"));
detalle.setTotalAgrupados(UtilJdbc.obtenerBigDecimal(rs, "montototal"));
detalle.setMontoComision(UtilJdbc.obtenerBigDecimal(rs, "montocomision"));
resultado.add(detalle);
}
} catch (SQLException e) {
throw new SQLException(e);
} finally {
try {
if (rs != null) {
rs.close();
}
if (cs != null) {
cs.close();
}
if (conn != null) {
conn.close();
}
} catch (SQLException e) {
throw new SQLException(e);
}
}
return resultado;
}
/* 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();
}
}
}
@Override
public void traceMarker() throws Exception {
CallableStatement callableStatement =
connection.prepareCall("insert into employee values (?, ?)");
try {
callableStatement.setString(1, "jane");
callableStatement.setNull(2, Types.BINARY);
callableStatement.execute();
} finally {
callableStatement.close();
}
}
public void save(Connection conn, AccountingArMgr accMgr, int rowIndex) throws SQLException {
CallableStatement cstmt = null;
String verbEntryMode = accMgr.getEntryMode().split("-")[0];
try {
this.mCode = 0;
String sql =
"begin FM_AR_PAYMENT_TERM_DETAIL_PKG.SAVE(:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11); end;";
cstmt = conn.prepareCall(sql);
DeBug.print(sql);
cstmt.setLong(1, Long.parseLong(accMgr.getCurrentMiscellaneousInvoice().getInvoiceId()));
cstmt.setLong(2, Long.parseLong(this.getPaymentTerm().getAttributeCode()));
cstmt.setDouble(3, Double.parseDouble(this.getPercentage()));
cstmt.setString(
4, Decoder.convertSetString(accMgr.getCurrentMiscellaneousInvoice().getStartDate()));
cstmt.setNull(5, Types.VARCHAR);
cstmt.setNull(6, Types.VARCHAR);
cstmt.setString(
7, Decoder.convertSetString(accMgr.getCurrentMiscellaneousInvoice().getSourceType()));
cstmt.setString(8, Decoder.convertSetString(verbEntryMode));
cstmt.setLong(9, rowIndex);
cstmt.registerOutParameter(10, Types.INTEGER);
cstmt.registerOutParameter(11, Types.VARCHAR);
cstmt.execute();
int returnCode = cstmt.getInt(10);
String returnMessage = cstmt.getString(11);
if (returnCode == -2) {
// Violate unique constraint in database
this.mCode = UNIQUE_VIOLATION;
throw new SQLException(returnMessage);
} else if (returnCode != 0) {
// Non-manageable error.
this.mCode = SAVE_ERROR;
throw new SQLException(returnMessage);
}
} catch (Exception e) {
this.mCode = SAVE_ERROR;
e.printStackTrace();
throw new SQLException(e.getMessage());
} finally {
if (cstmt != null) {
cstmt.close();
}
cstmt = null;
}
}
/** Sets the null */
@Override
public void setNull(String parameterName, int sqlType, String typeName) throws SQLException {
try {
_cstmt.setNull(parameterName, sqlType, typeName);
} catch (SQLException e) {
onSqlException(e);
throw e;
} catch (RuntimeException e) {
onRuntimeException(e);
throw e;
}
}
public void testSetObjectBit() throws Throwable {
try {
Statement stmt = con.createStatement();
stmt.execute(createBitTab);
stmt.execute(insertBitTab);
boolean ret =
stmt.execute(
"create or replace function "
+ "update_bit( in IMAX boolean, in IMIN boolean, in INUL boolean) returns int as "
+ "'begin "
+ "update bit_tab set max_val = imax;"
+ "update bit_tab set min_val = imin;"
+ "update bit_tab set min_val = inul;"
+ " return 0;"
+ " end;' "
+ "language plpgsql;");
} catch (Exception ex) {
fail(ex.getMessage());
throw ex;
}
try {
CallableStatement cstmt = con.prepareCall("{ call update_bit(?,?,?) }");
cstmt.setObject(1, "true", Types.BIT);
cstmt.setObject(2, "false", Types.BIT);
cstmt.setNull(3, Types.BIT);
cstmt.executeUpdate();
cstmt.close();
ResultSet rs = con.createStatement().executeQuery("select * from bit_tab");
assertTrue(rs.next());
assertTrue(rs.getBoolean(1));
assertFalse(rs.getBoolean(2));
rs.getBoolean(3);
assertTrue(rs.wasNull());
} catch (Exception ex) {
fail(ex.getMessage());
} finally {
try {
Statement dstmt = con.createStatement();
dstmt.execute("drop function update_bit(boolean, boolean, boolean)");
} catch (Exception ex) {
}
}
}
public void testInOut() throws Throwable {
try {
Statement stmt = con.createStatement();
stmt.execute(createBitTab);
stmt.execute(insertBitTab);
boolean ret =
stmt.execute(
"create or replace function "
+ "insert_bit( inout IMAX boolean, inout IMIN boolean, inout INUL boolean) as "
+ "'begin "
+ "insert into bit_tab values( imax, imin, inul);"
+ "select max_val into imax from bit_tab;"
+ "select min_val into imin from bit_tab;"
+ "select null_val into inul from bit_tab;"
+ " end;' "
+ "language plpgsql;");
} catch (Exception ex) {
fail(ex.getMessage());
throw ex;
}
try {
CallableStatement cstmt = con.prepareCall("{ call insert_bit(?,?,?) }");
cstmt.setObject(1, "true", Types.BIT);
cstmt.setObject(2, "false", Types.BIT);
cstmt.setNull(3, Types.BIT);
cstmt.registerOutParameter(1, Types.BIT);
cstmt.registerOutParameter(2, Types.BIT);
cstmt.registerOutParameter(3, Types.BIT);
cstmt.executeUpdate();
assertTrue(cstmt.getBoolean(1));
assertFalse(cstmt.getBoolean(2));
cstmt.getBoolean(3);
assertTrue(cstmt.wasNull());
} finally {
try {
Statement dstmt = con.createStatement();
dstmt.execute("drop function insert_bit(boolean, boolean, boolean)");
} catch (Exception ex) {
}
}
}
@Override
public boolean registrarInvitado(Cliente invitado, Integer idnovios, Connection conn)
throws SQLException, Exception {
boolean resultado = false;
CallableStatement cs = null;
String sql = "{ ? = call negocio.fn_ingresainvitado(?,?,?,?,?,?,?,?,?) }";
try {
cs = conn.prepareCall(sql);
int i = 1;
cs.registerOutParameter(i++, Types.BOOLEAN);
cs.setString(i++, invitado.getNombres());
cs.setString(i++, invitado.getApellidoPaterno());
cs.setString(i++, invitado.getApellidoMaterno());
if (invitado.getFechaNacimiento() != null) {
cs.setDate(i++, UtilJdbc.convertirUtilDateSQLDate(invitado.getFechaNacimiento()));
} else {
cs.setNull(i++, Types.DATE);
}
cs.setString(i++, invitado.getTelefonoInvitadoNovios());
cs.setString(i++, invitado.getCorreoElectronico());
cs.setInt(i++, idnovios);
cs.setString(i++, invitado.getUsuarioCreacion());
cs.setString(i++, invitado.getIpCreacion());
cs.execute();
resultado = true;
} 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 void saveUpdateWoFactory(Connection conn) throws SQLException {
CallableStatement cstmt = null;
try {
// this.mCode = 0;
String sql = "begin MFG_WO_PKG.MODIFY_WO_FACTORY(:1,:2,:3,:4); end;";
cstmt = conn.prepareCall(sql);
DeBug.print(sql);
if (this.getWoId() == null) cstmt.setNull(1, Types.VARCHAR);
else cstmt.setString(1, Decoder.convertSetString(this.getWoId()));
cstmt.setString(2, this.getSelectFactory().getAttributeId());
cstmt.registerOutParameter(3, Types.INTEGER);
cstmt.registerOutParameter(4, Types.VARCHAR);
cstmt.execute();
int returnCode = cstmt.getInt(3);
String returnMessage = cstmt.getString(4);
DeBug.print("in Save : the returnCode : ****" + returnCode);
DeBug.print("in Save : the returnMessage : ****" + returnMessage);
if (returnCode == -2) {
// Violate unique constraint in database
// this.mCode = UNIQUE_VIOLATION;
throw new SQLException(returnMessage);
} else if (returnCode != 0) {
// Non-manageable error.
// this.mCode = SAVE_ERROR;
throw new SQLException(returnMessage);
}
} catch (Exception e) {
// this.mCode = SAVE_ERROR;
e.printStackTrace();
throw new SQLException(e.getMessage());
} finally {
if (cstmt != null) {
cstmt.close();
}
cstmt = null;
}
}
public void bind(VariableTable vt) throws java.sql.SQLException {
int rows;
Object val;
try {
stmt.clearParameters();
} catch (java.sql.SQLException sqle) {
}
if (paramNames.length > 0) {
for (rows = 0; rows < paramNames.length; rows++) {
val = vt.getValue(paramNames[rows]);
if (paramTypes[rows].equals("IN")) {
if (val == null) stmt.setNull(rows + 1, 1);
else {
if (vt.getType(paramNames[rows]) != java.sql.Types.LONGVARCHAR
&& vt.getType(paramNames[rows]) != java.sql.Types.LONGVARBINARY)
stmt.setObject(rows + 1, val);
else if (vt.getType(paramNames[rows]) == java.sql.Types.LONGVARCHAR) {
java.io.StringReader long_var = new java.io.StringReader(val.toString());
stmt.setCharacterStream(rows + 1, long_var, 16384);
// long_var.close();
} else if (vt.getType(paramNames[rows]) == java.sql.Types.LONGVARBINARY) {
java.io.File os_file = new java.io.File(val.toString());
if (os_file.exists() && os_file.isFile() && os_file.canRead()) {
try {
java.io.FileInputStream long_var = new java.io.FileInputStream(os_file);
stmt.setBinaryStream(rows + 1, long_var, 16384);
// long_var.close();
} catch (java.io.IOException ioe) {
}
} else stmt.setNull(rows + 1, 1);
}
}
} else if (paramTypes[rows].equals("OUT")) {
stmt.registerOutParameter(rows + 1, vt.getType(paramNames[rows]));
} else if (paramTypes[rows].equals("INOUT")) {
if (val == null) stmt.setNull(rows + 1, 1);
else {
if (vt.getType(paramNames[rows]) != java.sql.Types.LONGVARCHAR
&& vt.getType(paramNames[rows]) != java.sql.Types.LONGVARBINARY)
stmt.setObject(rows + 1, val);
else if (vt.getType(paramNames[rows]) != java.sql.Types.LONGVARCHAR) {
java.io.StringReader long_var = new java.io.StringReader(val.toString());
stmt.setCharacterStream(rows + 1, long_var, 16384);
// long_var.close();
} else if (vt.getType(paramNames[rows]) != java.sql.Types.LONGVARBINARY) {
java.io.File os_file = new java.io.File(val.toString());
if (os_file.exists() && os_file.isFile() && os_file.canRead()) {
try {
java.io.FileInputStream long_var = new java.io.FileInputStream(os_file);
stmt.setBinaryStream(rows + 1, long_var, 16384);
// long_var.close();
} catch (java.io.IOException ioe) {
}
} else stmt.setNull(rows + 1, 1);
}
}
stmt.registerOutParameter(rows + 1, vt.getType(paramNames[rows]));
}
}
}
}
public Customer updateCustomer(Customer customer) {
Connection connection = null;
CallableStatement callableStatement = null;
ResultSet resultSet = null;
boolean hasResults;
String id;
try {
connection = dataSource.getConnection(); // get connection from dataSource
callableStatement = connection.prepareCall(updateCustomerSql); // prepare callable statement
id = customer.getId();
if (id == null) {
callableStatement.setNull(1, Types.INTEGER);
} else {
callableStatement.setInt(1, Integer.parseInt(id));
}
if (customer.isInactive()) {
callableStatement.setNull(2, Types.VARCHAR);
callableStatement.setNull(3, Types.VARCHAR);
callableStatement.setNull(4, Types.VARCHAR);
callableStatement.setNull(5, Types.VARCHAR);
callableStatement.setNull(6, Types.VARCHAR);
callableStatement.setNull(7, Types.VARCHAR);
callableStatement.setNull(8, Types.VARCHAR);
callableStatement.setNull(9, Types.VARCHAR);
callableStatement.setNull(10, Types.VARCHAR);
callableStatement.setNull(11, Types.VARCHAR);
callableStatement.setByte(12, (byte) 1);
} else {
callableStatement.setString(2, customer.getFirstName());
callableStatement.setString(3, customer.getLastName());
callableStatement.setString(4, customer.getStreetAddress());
callableStatement.setString(5, customer.getAptAddress());
callableStatement.setString(6, customer.getCity());
callableStatement.setString(7, customer.getState());
callableStatement.setString(8, customer.getZip());
callableStatement.setString(9, customer.getPhone());
callableStatement.setString(10, customer.getEmail());
callableStatement.setString(11, customer.getNotes());
callableStatement.setByte(12, (byte) 0);
}
hasResults = callableStatement.execute();
if (hasResults) {
resultSet = callableStatement.getResultSet();
if (resultSet.next()) {
customer.setId(resultSet.getString(1));
} else {
throw new SQLException("Unable to update customer.");
}
} else {
throw new SQLException("Unable to update customer.");
}
} catch (SQLException se) {
log.error("SQL error: ", se);
return null;
} finally {
try {
resultSet.close();
} catch (Exception se) {
log.error("Unable to close resultSet: ", se);
}
try {
callableStatement.close();
} catch (Exception se) {
log.error("Unable to close callableStatement: ", se);
}
try {
connection.close();
} catch (Exception se) {
log.error("Unable to close connection: ", se);
}
}
return customer;
}
public List<Customer> findCustomers(String id, String match, int limit) {
Connection connection = null;
CallableStatement callableStatement = null;
ResultSet resultSet = null;
boolean hasResults;
List<Customer> customers = new ArrayList<Customer>();
try {
connection = dataSource.getConnection(); // get connection from dataSource
connection.setTransactionIsolation(
Connection.TRANSACTION_READ_COMMITTED); // prevent dirty reads
callableStatement = connection.prepareCall(listCustomersSql); // prepare callable statement
if (id == null) {
callableStatement.setNull(1, Types.INTEGER);
} else {
callableStatement.setInt(1, Integer.parseInt(id));
}
if (match == null) {
callableStatement.setNull(2, Types.VARCHAR);
} else {
callableStatement.setString(2, match);
}
callableStatement.setInt(3, limit);
callableStatement.setNull(4, Types.INTEGER);
hasResults = callableStatement.execute();
if (hasResults) {
resultSet = callableStatement.getResultSet();
if (resultSet.isBeforeFirst()) { // customers have been returned
while (resultSet.next()) {
customers.add(
new Customer(
resultSet.getString("id"),
resultSet.getString("first_name"),
resultSet.getString("last_name"),
resultSet.getString("street_address"),
resultSet.getString("apt_address"),
resultSet.getString("city"),
resultSet.getString("state"),
resultSet.getString("zip"),
resultSet.getString("phone"),
resultSet.getString("email"),
resultSet.getString("notes")));
}
} else {
log.debug("No customers returned.");
}
} else {
log.debug("No customers returned.");
}
} catch (SQLException se) {
log.error("SQL error: ", se);
return null;
} finally {
try {
resultSet.close();
} catch (Exception se) {
log.error("Unable to close resultSet: ", se);
}
try {
callableStatement.close();
} catch (Exception se) {
log.error("Unable to close callableStatement: ", se);
}
try {
connection.close();
} catch (Exception se) {
log.error("Unable to close connection: ", se);
}
}
return customers;
}
@Override
public List<ProgramaNovios> consultarNovios(ProgramaNovios programaNovios, Connection conn)
throws SQLException {
List<ProgramaNovios> resultado = null;
CallableStatement cs = null;
ResultSet rs = null;
String sql = "{ ? = call negocio.fn_consultarnovios(?,?,?,?)}";
try {
cs = conn.prepareCall(sql);
int i = 1;
cs.registerOutParameter(i++, Types.OTHER);
if (programaNovios.getCodigoEntero() != null
&& programaNovios.getCodigoEntero().intValue() != 0) {
cs.setInt(i++, programaNovios.getCodigoEntero().intValue());
} else {
cs.setNull(i++, Types.INTEGER);
}
if (StringUtils.isNotBlank(programaNovios.getCodigoNovios())) {
cs.setString(i++, programaNovios.getCodigoNovios());
} else {
cs.setNull(i++, Types.VARCHAR);
}
if (programaNovios.getNovia().getCodigoEntero() != null
&& programaNovios.getNovia().getCodigoEntero().intValue() != 0) {
cs.setInt(i++, programaNovios.getNovia().getCodigoEntero().intValue());
} else {
cs.setNull(i++, Types.INTEGER);
}
if (programaNovios.getNovio().getCodigoEntero() != null
&& programaNovios.getNovio().getCodigoEntero().intValue() != 0) {
cs.setInt(i++, programaNovios.getNovio().getCodigoEntero().intValue());
} else {
cs.setNull(i++, Types.INTEGER);
}
cs.execute();
rs = (ResultSet) cs.getObject(1);
resultado = new ArrayList<ProgramaNovios>();
ProgramaNovios programaNovios2 = null;
while (rs.next()) {
programaNovios2 = new ProgramaNovios();
programaNovios2.setCodigoEntero(UtilJdbc.obtenerNumero(rs, "id"));
programaNovios2.setCodigoNovios(UtilJdbc.obtenerCadena(rs, "codigonovios"));
programaNovios2.getNovia().setCodigoEntero(UtilJdbc.obtenerNumero(rs, "idnovia"));
programaNovios2
.getNovia()
.getDocumentoIdentidad()
.getTipoDocumento()
.setCodigoEntero(UtilJdbc.obtenerNumero(rs, "tipodocnovia"));
programaNovios2
.getNovia()
.getDocumentoIdentidad()
.setNumeroDocumento(UtilJdbc.obtenerCadena(rs, "numdocnovia"));
programaNovios2.getNovia().setNombres(UtilJdbc.obtenerCadena(rs, "nomnovia"));
programaNovios2.getNovia().setApellidoPaterno(UtilJdbc.obtenerCadena(rs, "apepatnovia"));
programaNovios2.getNovia().setApellidoMaterno(UtilJdbc.obtenerCadena(rs, "apematnovia"));
programaNovios2.getNovio().setCodigoEntero(UtilJdbc.obtenerNumero(rs, "idnovio"));
programaNovios2
.getNovio()
.getDocumentoIdentidad()
.getTipoDocumento()
.setCodigoEntero(UtilJdbc.obtenerNumero(rs, "tipodocnovio"));
programaNovios2
.getNovio()
.getDocumentoIdentidad()
.setNumeroDocumento(UtilJdbc.obtenerCadena(rs, "numdocnovio"));
programaNovios2.getNovio().setNombres(UtilJdbc.obtenerCadena(rs, "nomnovio"));
programaNovios2.getNovio().setApellidoPaterno(UtilJdbc.obtenerCadena(rs, "apepatnovio"));
programaNovios2.getNovio().setApellidoMaterno(UtilJdbc.obtenerCadena(rs, "apematnovio"));
programaNovios2.getDestino().setCodigoEntero(UtilJdbc.obtenerNumero(rs, "iddestino"));
programaNovios2.getDestino().setDescripcion(UtilJdbc.obtenerCadena(rs, "descdestino"));
programaNovios2.getDestino().setCodigoIATA(UtilJdbc.obtenerCadena(rs, "codigoiata"));
programaNovios2
.getDestino()
.getPais()
.setDescripcion(UtilJdbc.obtenerCadena(rs, "descpais"));
programaNovios2.setFechaBoda(UtilJdbc.obtenerFecha(rs, "fechaboda"));
programaNovios2.setFechaViaje(UtilJdbc.obtenerFecha(rs, "fechaviaje"));
programaNovios2.getMoneda().setCodigoEntero(UtilJdbc.obtenerNumero(rs, "idmoneda"));
programaNovios2.setCuotaInicial(UtilJdbc.obtenerBigDecimal(rs, "cuotainicial"));
programaNovios2.setNroDias(UtilJdbc.obtenerNumero(rs, "dias"));
programaNovios2.setNroNoches(UtilJdbc.obtenerNumero(rs, "noches"));
programaNovios2.setFechaShower(UtilJdbc.obtenerFecha(rs, "fechashower"));
programaNovios2.setObservaciones(UtilJdbc.obtenerCadena(rs, "observaciones"));
programaNovios2.setUsuarioCreacion(UtilJdbc.obtenerCadena(rs, "usuariocreacion"));
programaNovios2.setFechaCreacion(UtilJdbc.obtenerFecha(rs, "fechacreacion"));
programaNovios2.setIpCreacion(UtilJdbc.obtenerCadena(rs, "ipcreacion"));
programaNovios2.setCantidadInvitados(UtilJdbc.obtenerNumero(rs, "cantidadInvitados"));
programaNovios2.setIdServicio(UtilJdbc.obtenerNumero(rs, "idservicio"));
programaNovios2.getVendedor().setCodigoEntero(UtilJdbc.obtenerNumero(rs, "idvendedor"));
String nombre =
UtilJdbc.obtenerCadena(rs, "nomvendedor")
+ " "
+ UtilJdbc.obtenerCadena(rs, "apepatvendedor")
+ " "
+ UtilJdbc.obtenerCadena(rs, "apematvendedor");
programaNovios2.getVendedor().setNombre(nombre);
programaNovios2.setMontoTotalComision(UtilJdbc.obtenerBigDecimal(rs, "montocomisiontotal"));
programaNovios2.setMontoTotalServicios(UtilJdbc.obtenerBigDecimal(rs, "montototal"));
programaNovios2.setMontoTotalFee(UtilJdbc.obtenerBigDecimal(rs, "montototalfee"));
resultado.add(programaNovios2);
}
} catch (SQLException e) {
resultado = null;
throw new SQLException(e);
} finally {
try {
if (rs != null) {
rs.close();
}
if (cs != null) {
cs.close();
}
} catch (SQLException e) {
throw new SQLException(e);
}
}
return resultado;
}
/** saveResign()* */
public void saveResign(ResignMgr resignMgr, HttpServletRequest request) throws SQLException {
this.mCode = 0;
CallableStatement cstmt = null;
Connection conn = null;
conn = OracleGate.getConnection();
conn.setAutoCommit(false);
try {
// this.mCode = 0;
String sql =
"begin HR_RESIGN_PKG.SAVE_RESIGN(:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13); end;";
cstmt = conn.prepareCall(sql);
DeBug.print(sql);
if (this.getAttributeId() == null) {
cstmt.setNull(1, Types.VARCHAR);
} else {
cstmt.setString(1, Decoder.convertSetString(this.getAttributeId()));
}
cstmt.setString(2, Decoder.convertSetString(this.getResignEmployee().getAttributeId()));
cstmt.setString(3, Decoder.convertSetString(this.getEmployeeStatus().getAttributeId()));
cstmt.setString(4, Decoder.convertSetString(this.getDescription()));
cstmt.setString(5, Decoder.convertSetString(this.getExpectResignDate()));
cstmt.setString(6, Decoder.convertSetString(this.getActualResignDate()));
cstmt.setString(7, Decoder.convertSetString(this.getSpecialLeaveDays()));
cstmt.setString(8, Decoder.convertSetString(this.getSpecialLeaveHours()));
cstmt.setString(9, Decoder.convertSetString(this.getDayOrHour()));
cstmt.setString(
10,
Decoder.convertSetString(
this.getCurrentResignSalaryItem().getCurrentSalaryItem().getAttributeId()));
cstmt.setString(11, Decoder.convertSetString(resignMgr.getEntryMode()));
cstmt.registerOutParameter(1, Types.VARCHAR);
cstmt.registerOutParameter(12, Types.INTEGER);
cstmt.registerOutParameter(13, Types.VARCHAR);
cstmt.execute();
int returnCode = cstmt.getInt(12);
String returnMessage = cstmt.getString(13);
DeBug.print("in Save : the returnCode : " + returnCode);
DeBug.print("in Save : the 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 {
this.setAttributeId(cstmt.getString(1));
conn.commit();
this.isSaved = true;
}
} 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();
}
}
}
/** @see java.sql.PreparedStatement#setNull(int, int, java.lang.String) */
public void setNull(int paramIndex, int sqlType, String typeName) throws SQLException {
original.setNull(paramIndex, sqlType, typeName);
}
/** @see java.sql.PreparedStatement#setNull(int, int) */
public void setNull(int parameterIndex, int sqlType) throws SQLException {
original.setNull(parameterIndex, sqlType);
}
/** @see java.sql.CallableStatement#setNull(java.lang.String, int, java.lang.String) */
public void setNull(String parameterName, int sqlType, String typeName) throws SQLException {
original.setNull(parameterName, sqlType, typeName);
}
@Override
public int registrarDireccion(Direccion direccion, Connection conexion) throws SQLException {
int resultado = 0;
CallableStatement cs = null;
String sql = "{ ? = call negocio.fn_ingresardireccion(?,?,?,?,?,?,?,?,?,?,?,?) }";
try {
cs = conexion.prepareCall(sql);
int i = 1;
cs.registerOutParameter(i++, Types.INTEGER);
cs.setInt(i++, direccion.getVia().getCodigoEntero());
if (StringUtils.isNotBlank(direccion.getNombreVia())) {
cs.setString(i++, UtilJdbc.convertirMayuscula(direccion.getNombreVia()));
} else {
cs.setNull(i++, Types.VARCHAR);
}
if (StringUtils.isNotBlank(direccion.getNumero())) {
cs.setString(i++, direccion.getNumero());
} else {
cs.setNull(i++, Types.VARCHAR);
}
if (StringUtils.isNotBlank(direccion.getInterior())) {
cs.setString(i++, direccion.getInterior());
} else {
cs.setNull(i++, Types.VARCHAR);
}
if (StringUtils.isNotBlank(direccion.getManzana())) {
cs.setString(i++, UtilJdbc.convertirMayuscula(direccion.getManzana()));
} else {
cs.setNull(i++, Types.VARCHAR);
}
if (StringUtils.isNotBlank(direccion.getLote())) {
cs.setString(i++, UtilJdbc.convertirMayuscula(direccion.getLote()));
} else {
cs.setNull(i++, Types.VARCHAR);
}
cs.setString(i++, direccion.isPrincipal() ? "S" : "N");
if (StringUtils.isNotBlank(direccion.getUbigeo().getCodigoCadena())) {
cs.setString(i++, direccion.getUbigeo().getCodigoCadena());
} else {
cs.setNull(i++, Types.VARCHAR);
}
if (StringUtils.isNotBlank(direccion.getUsuarioCreacion())) {
cs.setString(i++, UtilJdbc.convertirMayuscula(direccion.getUsuarioCreacion()));
} else {
cs.setNull(i++, Types.VARCHAR);
}
if (StringUtils.isNotBlank(direccion.getIpCreacion())) {
cs.setString(i++, direccion.getIpCreacion());
} else {
cs.setNull(i++, Types.VARCHAR);
}
if (StringUtils.isNotBlank(direccion.getObservaciones())) {
cs.setString(i++, UtilJdbc.convertirMayuscula(direccion.getObservaciones()));
} else {
cs.setNull(i++, Types.VARCHAR);
}
if (StringUtils.isNotBlank(direccion.getReferencia())) {
cs.setString(i++, UtilJdbc.convertirMayuscula(direccion.getReferencia()));
} else {
cs.setNull(i++, Types.VARCHAR);
}
cs.execute();
resultado = cs.getInt(1);
} catch (SQLException e) {
resultado = 0;
throw new SQLException(e);
} finally {
try {
if (cs != null) {
cs.close();
}
} catch (SQLException e) {
throw new SQLException(e);
}
}
return resultado;
}
public void save(MaintainingSetupMgr maintainingSetupMgr, Login login) throws SQLException {
this.mCode = 0;
CallableStatement cstmt = null;
Connection conn = OracleGate.getConnection();
conn.setAutoCommit(false);
try {
cstmt =
conn.prepareCall("begin MM_DATAMAINTAIN_PKG.MODIFY_TYPE(:1,:2,:3,:4,:5,:6,:7,:8); end;");
cstmt.setString(1, Decoder.convertSetString(this.getAttributeCode()));
cstmt.setString(2, Decoder.convertSetString(this.getAttributeName()));
cstmt.setString(3, Decoder.convertSetString(this.getDescription()));
cstmt.setString(
6, Decoder.convertSetString(maintainingSetupMgr.getFunctionMode().split("-")[0]));
cstmt.setString(7, Decoder.convertSetString(login.getEmployee().getEmployeeId()));
if (this.getAttributeId() != null && !this.getAttributeId().equals("")) {
cstmt.setString(8, Decoder.convertSetString(this.getAttributeId()));
} else {
cstmt.setNull(8, Types.VARCHAR);
}
cstmt.registerOutParameter(4, Types.INTEGER);
cstmt.registerOutParameter(5, Types.VARCHAR);
cstmt.registerOutParameter(8, Types.VARCHAR);
DeBug.print("Start Save MechanicalType DeBug.Print: ");
DeBug.print("1,this.getAttributeCode(): " + this.getAttributeCode());
DeBug.print("2,this.getAttributeName(): " + this.getAttributeName());
DeBug.print("3,this.getDescription(): " + this.getDescription());
DeBug.print(
"6,maintainingSetupMgr.getFunctionMode().split(-)[0]: "
+ maintainingSetupMgr.getFunctionMode().split("-")[0]);
DeBug.print("7,login.getEmployee().getEmployeeId(): " + login.getEmployee().getEmployeeId());
DeBug.print("8,this.getAttributeId(): " + this.getAttributeId());
DeBug.print("-----------END-----------");
cstmt.execute();
int returnCode = cstmt.getInt(4);
String returnMessage = cstmt.getString(5);
DeBug.print(String.valueOf(returnCode), "(4,in Save : the returnCode )");
DeBug.print(returnMessage, "(5,in Save : the 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();
this.setAttributeId(String.valueOf(cstmt.getLong(8)));
this.isSaved = true;
}
} catch (Exception e) {
conn.rollback();
this.isSaved = false;
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();
}
}
}
/*
* prepareStatement
* This method follows the logic of preparedDataCommon.prepareStatement() except that since it is only
* called for CFPROCPARAM's it doesn't need to iterate over the data VectorArrayList. Instead it only
* needs to extract the one value.
*/
private void prepareStatement(String paramName, CallableStatement CallStatmt, Connection _conn)
throws dataNotSupportedException, cfmRunTimeException, SQLException {
// Map the CFML type to a JDBC type
int jType = getJdbcType(CallStatmt, cfSqlType);
paramName = paramName.replace("@", "");
if (passAsNull) {
// JDBC drivers don't recognize ORACLE_NCLOB so we need to pass it in as a Types.CHAR
if (jType == ORACLE_NCLOB) CallStatmt.setNull(paramName, Types.CHAR);
else CallStatmt.setNull(paramName, jType);
return;
}
// Get the value associated with this CFPROCPARAM
cfData _data = data.get(0);
switch (jType) {
// for MS SQL Server via JDBC-ODBC Bridge, if you try to use setString()
// instead of setObject(), it will pad VARCHAR columns when it shouldn't
case Types.CHAR:
case Types.VARCHAR:
CallStatmt.setObject(paramName, _data.getString(), jType);
break;
case Types.LONGVARCHAR:
CallStatmt.setObject(paramName, _data.getString(), jType);
break;
case ORACLE_NCLOB:
CallStatmt.setObject(paramName, _data.getString(), jType);
break;
case Types.BINARY:
case Types.VARBINARY:
case Types.LONGVARBINARY:
CallStatmt.setObject(paramName, ((cfBinaryData) _data).getByteArray(), jType);
break;
case Types.TINYINT:
case Types.SMALLINT:
case Types.INTEGER:
if (_data.getNumber().isInt()) {
CallStatmt.setInt(paramName, _data.getInt());
break;
}
// if not an int, fall through to next case
case Types.BIGINT:
double d = _data.getDouble();
if (d <= Long.MAX_VALUE) {
if (isSetLongSupported(_conn)) {
CallStatmt.setLong(paramName, _data.getLong());
} else {
CallStatmt.setDouble(paramName, d);
}
} else {
CallStatmt.setDouble(paramName, d);
}
break;
case Types.DECIMAL:
case Types.NUMERIC:
try {
// NOTE: if a customer is complaining about losing decimal places then make sure they
// are setting the scale properly in cfqueryparam. The default value for scale
// is 0 which causes all decimal places to be removed.
CallStatmt.setBigDecimal(
paramName,
new BigDecimal(_data.getDouble()).setScale(scale, BigDecimal.ROUND_HALF_UP));
break;
} catch (Exception e) {
// fall through to next case
}
case Types.FLOAT:
case Types.DOUBLE:
CallStatmt.setDouble(paramName, _data.getDouble());
break;
case Types.REAL:
CallStatmt.setFloat(paramName, new Float(_data.getDouble()).floatValue());
break;
case Types.DATE:
long date =
(_data.getDataType() == cfData.CFDATEDATA
? _data.getLong()
: _data.getDateData().getLong());
try {
CallStatmt.setDate(paramName, new java.sql.Date(date));
} catch (SQLException e) { // JDBC-ODBC Bridge doesn't support setDate() for MS SQL Server
CallStatmt.setString(paramName, com.nary.util.Date.formatDate(date, "dd-MMM-yy"));
}
break;
case Types.TIME:
long time =
(_data.getDataType() == cfData.CFDATEDATA
? _data.getLong()
: _data.getDateData().getLong());
try {
CallStatmt.setTime(paramName, new java.sql.Time(time));
} catch (SQLException e) { // JDBC-ODBC Bridge doesn't support setTime() for MS SQL Server
CallStatmt.setString(paramName, com.nary.util.Date.formatDate(time, "hh:mm aa"));
}
break;
case Types.TIMESTAMP:
long ts =
(_data.getDataType() == cfData.CFDATEDATA
? _data.getLong()
: _data.getDateData().getLong());
CallStatmt.setTimestamp(paramName, new java.sql.Timestamp(ts));
break;
case Types.BIT:
CallStatmt.setBoolean(paramName, _data.getBoolean());
break;
case Types.NULL:
CallStatmt.setNull(paramName, getJdbcType(CallStatmt, cfSqlType));
break;
default:
throw newRunTimeException("Unsupported CFSQLTYPE: " + sqlType);
}
}
public void setNull(String parameterName, int sqlType) throws SQLException {
passThru.setNull(parameterName, sqlType);
}
