public void callProcAggiornaAbilitazione(
Long idVersioneInterventoTu, Long idAzioneFas, Long idProgrammaPoc, Long idObiettivoSpecifico)
throws ApplicationException {
getLogger().debug("callProcAggiornaAbilitazioni inizio");
Session session = super.getSession();
CallableStatement cs;
try {
cs = super.getConnection(session).prepareCall("{ call DBP_AGGIORNA_ABILITAZIONI(?,?,?,?) }");
cs.setLong(1, idVersioneInterventoTu.longValue());
if (idAzioneFas != null) cs.setLong(2, idAzioneFas.longValue());
else cs.setObject(2, null);
if (idProgrammaPoc != null) cs.setLong(3, idProgrammaPoc.longValue());
else cs.setObject(3, null);
if (idObiettivoSpecifico != null) cs.setLong(4, idObiettivoSpecifico.longValue());
else cs.setObject(4, null);
cs.execute();
getLogger().debug("DBP_AGGIORNA_ABILITAZIONI eseguita.");
} catch (Exception e) {
e.printStackTrace();
getLogger().debug("----------------------errore plsql callProcAggiornaAbilitazioni");
throw new ApplicationException("errore plsql callProcAggiornaAbilitazioni");
} finally {
session.close();
}
}
/**
* 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 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));
}
}
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;
}
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 setObject(String parameterName, Object x) throws SQLException {
checkOpen();
try {
_stmt.setObject(parameterName, x);
} catch (SQLException e) {
handleException(e);
}
}
public void setObject(String parameterName, Object x) throws SQLException {
checkOpen();
try {
((CallableStatement) _stmt).setObject(parameterName, x);
} catch (SQLException e) {
handleException(e);
}
}
public void setObject(String parameterName, Object x, int targetSqlType, int scale)
throws SQLException {
checkOpen();
try {
_stmt.setObject(parameterName, x, targetSqlType, scale);
} catch (SQLException e) {
handleException(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 testVarcharBool() throws Throwable {
try {
Statement stmt = con.createStatement();
stmt.execute("create temp table vartab( max_val text, min_val text)");
stmt.execute("insert into vartab values ('a','b')");
boolean ret =
stmt.execute(
"create or replace function "
+ "updatevarchar( in imax text, in imin text) returns int as "
+ "'begin "
+ "update vartab set max_val = imax;"
+ "update vartab set min_val = imin;"
+ "return 0;"
+ " end;' "
+ "language plpgsql;");
stmt.close();
} catch (Exception ex) {
fail(ex.getMessage());
throw ex;
}
try {
String str = Boolean.TRUE.toString();
CallableStatement cstmt = con.prepareCall("{ call updatevarchar(?,?) }");
cstmt.setObject(1, Boolean.TRUE, Types.VARCHAR);
cstmt.setObject(2, Boolean.FALSE, Types.VARCHAR);
cstmt.executeUpdate();
cstmt.close();
ResultSet rs = con.createStatement().executeQuery("select * from vartab");
assertTrue(rs.next());
assertTrue(rs.getString(1).equals(Boolean.TRUE.toString()));
assertTrue(rs.getString(2).equals(Boolean.FALSE.toString()));
rs.close();
} catch (Exception ex) {
fail(ex.getMessage());
} finally {
try {
Statement dstmt = con.createStatement();
dstmt.execute("drop function updatevarchar(text,text)");
} catch (Exception ex) {
}
}
}
public void testUpdateReal() throws Throwable {
try {
Statement stmt = con.createStatement();
stmt.execute(createRealTab);
boolean ret = stmt.execute(createUpdateReal);
stmt.execute(insertRealTab);
stmt.close();
} catch (Exception ex) {
fail(ex.getMessage());
throw ex;
}
try {
CallableStatement cstmt = con.prepareCall("{ call update_real_proc(?,?) }");
BigDecimal val = new BigDecimal(intValues[0]);
float x = val.floatValue();
cstmt.setObject(1, val, Types.REAL);
val = new BigDecimal(intValues[1]);
cstmt.setObject(2, val, Types.REAL);
cstmt.executeUpdate();
cstmt.close();
ResultSet rs = con.createStatement().executeQuery("select * from real_tab");
assertTrue(rs.next());
Float oVal = new Float(intValues[0]);
Float rVal = new Float(rs.getObject(1).toString());
assertTrue(oVal.equals(rVal));
oVal = new Float(intValues[1]);
rVal = new Float(rs.getObject(2).toString());
assertTrue(oVal.equals(rVal));
rs.close();
} catch (Exception ex) {
fail(ex.getMessage());
} finally {
try {
Statement dstmt = con.createStatement();
dstmt.execute(dropUpdateReal);
dstmt.close();
} 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) {
}
}
}
public void procUpdateclient(ClientModel clientModel) throws SQLException {
Connection connection = getConnection();
CallableStatement cstm =
connection.prepareCall(
"{call UpdateClient(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)}");
cstm.setInt("ClientID", clientModel.getClientID());
cstm.setInt("ClientStatusID", clientModel.getClientStatusID());
if (clientModel.getTradeShowID() != 0) cstm.setInt("TradeShowID", clientModel.getTradeShowID());
else cstm.setObject("TradeShowID", null);
if (clientModel.getReferrerID() != 0) cstm.setInt("ReferrerID", clientModel.getReferrerID());
else cstm.setObject("ReferrerID", null);
cstm.setString("ClientCompanyName", clientModel.getClientCompanyName());
cstm.setString("ClientFirstName", clientModel.getClientFirstName());
cstm.setString("ClientLastName", clientModel.getClientLastName());
cstm.setString("ClientMailingAddress1", clientModel.getClientMailingAddress1());
cstm.setString("ClientMailingAddress2", clientModel.getClientMailingAddress2());
cstm.setString("ClientMailingAddress3", clientModel.getClientMailingAddress3());
cstm.setString("ClientMailingCity", clientModel.getClientMailingCity());
cstm.setInt("MailingStateID", clientModel.getMailingStateID());
cstm.setString("ClientMailingZipCode", clientModel.getClientMailingZipCode());
cstm.setInt("MailingCountryID", clientModel.getMailingCountryID());
cstm.setString("ClientBillingAddress1", clientModel.getClientBillingAddress1());
cstm.setString("ClientBillingAddress2", clientModel.getClientBillingAddress2());
cstm.setString("ClientBillingAddress3", clientModel.getClientBillingAddress3());
cstm.setString("ClientBillingCity", clientModel.getClientBillingCity());
cstm.setInt("BillingStateID", clientModel.getBillingStateID());
cstm.setString("ClientBillingZipCode", clientModel.getClientBillingZipCode());
cstm.setInt("BillingCountryID", clientModel.getBillingCountryID());
cstm.setString("ClientPhone", clientModel.getClientPhone());
cstm.setString("ClientExtension", clientModel.getClientExtension());
cstm.setString("ClientEmail", clientModel.getClientEmail());
cstm.execute();
if (cstm != null) cstm.close();
if (connection != null) connection.close();
}
private void applyInParameters(CallableStatement statement) throws SQLException {
for (StoredProcedureParameter param : getParameters()) {
if (param.getParameterType().equals(ParameterType.IN)
|| param.getParameterType().equals(ParameterType.INOUT)) {
if (!isEmpty(param.getName())) {
statement.setObject(
param.getName(), param.getInValue(), param.getParameterValueType().getValue());
log.debug(
"Applying 'IN' parameter with name '"
+ param.getName()
+ "' and value '"
+ param.getInValue()
+ "'");
} else {
statement.setObject(
param.getOrder(), param.getInValue(), param.getParameterValueType().getValue());
log.debug(
"Applying 'IN' parameter with order '"
+ param.getOrder()
+ "' and value '"
+ param.getInValue()
+ "'");
}
}
if (param.getParameterType().equals(ParameterType.OUT)
|| param.getParameterType().equals(ParameterType.INOUT)) {
if (!isEmpty(param.getName())) {
statement.registerOutParameter(param.getName(), param.getParameterValueType().getValue());
log.debug("Registering 'OUT' parameter with name '" + param.getName() + "'");
} else {
statement.registerOutParameter(
param.getOrder(), param.getParameterValueType().getValue());
log.debug("Registering 'OUT' parameter with order '" + param.getOrder() + "'");
}
}
}
}
/** Sets the object */
@Override
public void setObject(String parameterName, Object x, int type, int scale) throws SQLException {
try {
_cstmt.setObject(parameterName, x, type, scale);
} catch (SQLException e) {
onSqlException(e);
throw e;
} catch (RuntimeException e) {
onRuntimeException(e);
throw e;
}
}
private static boolean isLTxIdCommitted(LogicalTransactionId ltxid, Connection conn)
throws SQLException {
CallableStatement cstmt = null;
cstmt = conn.prepareCall(GET_LTXID_OUTCOME);
cstmt.setObject(1, ltxid);
cstmt.registerOutParameter(2, OracleTypes.BIT);
cstmt.registerOutParameter(3, OracleTypes.BIT);
cstmt.execute();
boolean committed = cstmt.getBoolean(2);
boolean callCompleted = cstmt.getBoolean(3);
logger.info("call completed: " + callCompleted + ", committed: " + committed);
cstmt.close();
return committed;
}
/**
* Added - takes in the product id and returns a filename from the database using the
* GET_PRODUCT_IMAGE db stored function which looks up the image filename according to the
* image_id associated with the product id. The Original Forms application uses files with a TIF
* extension we changed these images to JPG
*/
public String readImageNameFromDB(Number id) {
CallableStatement cs = null;
cs = this.getDBTransaction().createCallableStatement("begin ?:=GET_PRODUCT_IMAGE(?); end;", 0);
try {
cs.registerOutParameter(1, Types.VARCHAR);
cs.setObject(2, id);
cs.execute();
Object returnVal = cs.getObject(1);
return returnVal.toString();
} catch (SQLException e) {
// print exception if SQL fails. In production systems you may want to log the
// issue and fail more gracefully e.g by returning NULL r an empty String
e.printStackTrace();
}
return "No File";
}
/**
* Simplifies calling a stored function with bind variables
*
* <p>You can use the NUMBER, DATE, and VARCHAR2 constants in this class to indicate the function
* return type for these three common types, otherwise use one of the JDBC types in the
* java.sql.Types class.
*
* <p>NOTE: If you want to invoke a stored procedure without any bind variables ==== then you can
* just use the basic getDBTransaction().executeCommand()
*
* @param sqlReturnType JDBC datatype constant of function return value
* @param stmt stored function statement
* @param bindVars Object array of parameters
* @return function return value as an Object
*/
protected Object callStoredFunction(int sqlReturnType, String stmt, Object[] bindVars) {
CallableStatement st = null;
try {
st = getDBTransaction().createCallableStatement("begin ? := " + stmt + "; end;", 0);
st.registerOutParameter(1, sqlReturnType);
if (bindVars != null) {
for (int z = 0; z < bindVars.length; z++) {
st.setObject(z + 2, bindVars[z]);
}
}
st.executeUpdate();
return st.getObject(1);
} catch (SQLException e) {
throw new JboException(e);
}
}
public static void callPro1(String sql, String[] parameters) {
try {
ct = getConnection();
cs = ct.prepareCall(sql);
if (parameters != null) {
for (int i = 0; i < parameters.length; i++) {
cs.setObject(i + 1, parameters[i]);
}
}
cs.execute();
} catch (Exception e) {
e.printStackTrace();
throw new RuntimeException(e.getMessage());
} finally {
close(rs, cs, ct);
}
}
void setObjectTest(String typeName, Object x, int type) throws Exception {
CallableStatement stmt = prepareCall("select cast(? as " + typeName + ") from dual");
stmt.setObject("@p1", x, type);
ResultSet rs = stmt.executeQuery();
rs.next();
Object result = rs.getObject(1);
if (x instanceof Number) {
assertEquals(((Number) x).doubleValue(), ((Number) result).doubleValue());
} else if (x != null && x.getClass().isArray()) {
assertJavaArrayEquals(x, result);
} else {
assertEquals(x, result);
}
}
public boolean executeCall(String sql, Object... params) {
CallableStatement cs = null;
try {
cs = connection.prepareCall(sql);
for (int i = 1; i <= params.length; i++) {
Object o = params[i - 1];
if (o instanceof Integer) {
cs.setInt(i, (int) o);
} else if (o instanceof String) {
cs.setString(i, (String) o);
} else if (o instanceof Double) {
cs.setDouble(i, (double) o);
} else if (o instanceof BufferedImage) {
InputStream is = null;
if (o != null) {
ByteArrayOutputStream os = new ByteArrayOutputStream();
ImageIO.write((BufferedImage) o, "png", os);
is = new ByteArrayInputStream(os.toByteArray());
}
cs.setBlob(4, is);
} else {
cs.setObject(i, o);
}
}
cs.executeUpdate();
return true;
} catch (Exception e) {
return false;
} finally {
try {
cs.close();
} catch (Exception e) {
logger.info(e.getMessage());
}
;
}
}
/*
* 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);
}
}
private static void callSetMethod(CallableStatement cs, int arg, int type, StringBuilder strbuf)
throws Throwable {
switch (type) {
case Types.BIT:
case Types.BOOLEAN:
strbuf.append("setBoolean(" + arg + ", true)");
cs.setBoolean(arg, true);
break;
case Types.TINYINT:
strbuf.append("setByte(" + arg + ", 6)");
cs.setByte(arg, (byte) 6);
break;
case Types.SMALLINT:
strbuf.append("setShort(" + arg + ", 66)");
cs.setShort(arg, (short) 66);
break;
case Types.INTEGER:
strbuf.append("setInt(" + arg + ", 666)");
cs.setInt(arg, 666);
break;
case Types.BIGINT:
strbuf.append("setLong(" + arg + ", 666)");
cs.setLong(arg, 666);
break;
case Types.FLOAT:
case Types.REAL:
strbuf.append("setFLoat(" + arg + ", 666)");
cs.setFloat(arg, 666);
break;
case Types.DOUBLE:
strbuf.append("setDouble(" + arg + ", 666)");
cs.setDouble(arg, 666);
break;
case Types.DECIMAL:
case Types.NUMERIC:
strbuf.append("setBigDecimal(" + arg + ", 666.666)");
BigDecimalHandler.setBigDecimalString(cs, arg, "666.666");
break;
case Types.CHAR:
case Types.VARCHAR:
case Types.LONGVARCHAR:
strbuf.append("setString(" + arg + ", \"Set via setString()\")");
cs.setString(arg, "Set via setString()");
break;
case Types.BINARY:
case Types.VARBINARY:
case Types.LONGVARBINARY:
strbuf.append("setBytes(" + arg + ", byte[])");
byte[] myarray = new byte[16];
myarray[0] = (byte) 255;
cs.setBytes(arg, myarray);
break;
case Types.DATE:
strbuf.append("setDate(" + arg + ", Date.valueOf(1999-09-09))");
cs.setDate(arg, Date.valueOf("1999-09-09"));
break;
case Types.TIME:
strbuf.append("setTime(" + arg + ", Time.valueOf(09:09:09))");
cs.setTime(arg, Time.valueOf("09:09:09"));
break;
case Types.TIMESTAMP:
strbuf.append("setTimestamp(" + arg + ", Timestamp.valueOf(1999-09-09 09:09:09.999))");
cs.setTimestamp(arg, Timestamp.valueOf("1999-09-09 09:09:09.999"));
break;
case Types.OTHER:
strbuf.append("setObject(" + arg + ", new BigInteger(666))");
cs.setObject(arg, new BigInteger("666"));
break;
default:
throw new Throwable("TEST ERROR: unexpected type " + type);
}
}
/** @see java.sql.CallableStatement#setObject(java.lang.String, java.lang.Object) */
public void setObject(String parameterName, Object x) throws SQLException {
original.setObject(parameterName, x);
}
public void setObject(String parameterName, Object x, int targetSqlType) throws SQLException {
passThru.setObject(parameterName, x, targetSqlType);
}
/** @see java.sql.CallableStatement#setObject(java.lang.String, java.lang.Object, int, int) */
public void setObject(String parameterName, Object x, int targetSqlType, int scale)
throws SQLException {
original.setObject(parameterName, x, targetSqlType, scale);
}
public void setObject(String parameterName, Object x) throws SQLException {
passThru.setObject(parameterName, x);
}
@Override
public void actionPerformed(ActionEvent e) {
// TODO Auto-generated method stub
String url = "jdbc:oracle:thin:skui/[email protected]:1521:etudom";
Connection co = OutilsJDBC.openConnection(url);
// Cas du bouton quitter
if (e.getActionCommand() == "Quitter") {
vueDepart vue;
vue = new vueDepart();
// Fermeture de la connexion de la base de données
try {
co.close();
} catch (SQLException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
}
this.dispose();
}
// _________________PROBLEME A CE NIVEAU___________________
// Cas de la requete 1
if (e.getActionCommand() == "Requete 1") {
try {
this.labelhaut.setText(titreRequete[0]);
String sql = "{? = call nbEtudiantAvecStage}";
System.out.println(sql);
CallableStatement call = co.prepareCall(sql);
call.registerOutParameter(1, java.sql.Types.INTEGER);
call.execute();
// récupération des ResultSet
ResultSet resultat1 = call.getResultSet();
System.out.println(resultat1.getInt(1));
} catch (SQLException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
}
}
// Cas de la requete 2
if (e.getActionCommand() == "Requete 2") {
this.labelhaut.setText(titreRequete[1]);
String sql = "{?=call nbEtudiantSansStage()}";
try {
CallableStatement call = co.prepareCall(sql);
call.registerOutParameter(1, java.sql.Types.INTEGER);
if (call.execute()) {
// récupération des ResultSet
ResultSet resultat1 = call.getResultSet();
while (resultat1.next()) {
for (int i = 0; i < resultat1.getMetaData().getColumnCount(); i++) {
System.out.print(resultat1.getObject(i + 1) + ", ");
System.out.println(resultat1.getInt(2));
}
}
}
} catch (SQLException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
}
}
// Cas de la requete 3
if (e.getActionCommand() == "Requete 3") {
this.labelhaut.setText(titreRequete[2]);
String sql = "{call nbEtudiantSansStageAnnee(?)}";
try {
CallableStatement call = co.prepareCall(sql);
call.setObject(1, 5);
if (call.execute()) {
// récupération des ResultSet
ResultSet resultat1 = call.getResultSet();
while (resultat1.next()) {
for (int i = 0; i < resultat1.getMetaData().getColumnCount(); i++) {
System.out.print(resultat1.getObject(i + 1) + ", ");
}
}
}
} catch (SQLException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
}
}
// Cas de la requete 4
if (e.getActionCommand() == "Requete 4") {
this.labelhaut.setText(titreRequete[3]);
String sql = "{call nbStagiaireParEntreprise(?,?)}";
try {
CallableStatement call = co.prepareCall(sql);
call.setObject(1, 5);
if (call.execute()) {
// récupération des ResultSet
ResultSet resultat1 = call.getResultSet();
while (resultat1.next()) {
for (int i = 0; i < resultat1.getMetaData().getColumnCount(); i++) {
System.out.print(resultat1.getObject(i + 1) + ", ");
}
}
}
} catch (SQLException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
}
}
// Cas de la requete 5
if (e.getActionCommand() == "Requete 5") {
this.labelhaut.setText(titreRequete[4]);
String sql = "{call nbMoyenEtudiantsDansEntreprise(?)}";
try {
CallableStatement call = co.prepareCall(sql);
call.setObject(1, 5);
if (call.execute()) {
// récupération des ResultSet
ResultSet resultat1 = call.getResultSet();
while (resultat1.next()) {
for (int i = 0; i < resultat1.getMetaData().getColumnCount(); i++) {
System.out.print(resultat1.getObject(i + 1) + ", ");
}
}
}
} catch (SQLException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
}
}
// Cas de la requete 6
if (e.getActionCommand() == "Requete 6") {
this.labelhaut.setText(titreRequete[5]);
String sql = "{call nbStagesParZone(?,?)}";
try {
CallableStatement call = co.prepareCall(sql);
call.setObject(1, "Orsay");
call.setObject(2, "91200");
if (call.execute()) {
// récupération des ResultSet
ResultSet resultat1 = call.getResultSet();
while (resultat1.next()) {
for (int i = 0; i < resultat1.getMetaData().getColumnCount(); i++) {
System.out.print(resultat1.getObject(i + 1) + ", ");
}
}
}
} catch (SQLException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
}
}
// Cas de la requete 7
if (e.getActionCommand() == "Requete 7") {
this.labelhaut.setText(titreRequete[6]);
String sql = "{call nbStagiaireZone()}";
try {
CallableStatement call = co.prepareCall(sql);
call.registerOutParameter("nb", java.sql.Types.INTEGER);
if (call.execute()) {
// récupération des ResultSet
ResultSet resultat1 = call.getResultSet();
while (resultat1.next()) {
for (int i = 0; i < resultat1.getMetaData().getColumnCount(); i++) {
System.out.print(resultat1.getObject(i + 1) + ", ");
}
}
}
} catch (SQLException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
}
}
// Cas de la requete 8
if (e.getActionCommand() == "Requete 8") {
this.labelhaut.setText(titreRequete[7]);
String sql = "{call contactEntreprise(?)}";
try {
CallableStatement call = co.prepareCall(sql);
call.setObject(1, 5);
if (call.execute()) {
// récupération des ResultSet
ResultSet resultat1 = call.getResultSet();
while (resultat1.next()) {
for (int i = 0; i < resultat1.getMetaData().getColumnCount(); i++) {
System.out.print(resultat1.getObject(i + 1) + ", ");
}
}
}
} catch (SQLException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
}
}
}
/** @see java.sql.PreparedStatement#setObject(int, java.lang.Object) */
public void setObject(int parameterIndex, Object x) throws SQLException {
original.setObject(parameterIndex, x);
}
/** @see java.sql.PreparedStatement#setObject(int, java.lang.Object, int, int) */
public void setObject(int parameterIndex, Object x, int targetSqlType, int scale)
throws SQLException {
original.setObject(parameterIndex, x, targetSqlType, scale);
}
