private static void executeOutput(CallableStatement cs) throws SQLException {
for (int p = 1; p <= 5; p++) cs.registerOutParameter(p, Types.INTEGER);
for (int p = 6; p <= 10; p++) cs.registerOutParameter(p, Types.VARCHAR);
cs.execute();
for (int p = 1; p <= 5; p++) {
System.out.println(" " + p + " = " + cs.getInt(p) + " was null " + cs.wasNull());
}
for (int p = 6; p <= 10; p++) {
System.out.println(" " + p + " = " + cs.getString(p) + " was null " + cs.wasNull());
}
}
public void testGetObjectFloat() throws Throwable {
try {
Statement stmt = con.createStatement();
stmt.execute(createDecimalTab);
stmt.execute(insertDecimalTab);
boolean ret = stmt.execute(createFloatProc);
} catch (Exception ex) {
fail(ex.getMessage());
throw ex;
}
try {
CallableStatement cstmt = con.prepareCall("{ call float_proc(?,?,?) }");
cstmt.registerOutParameter(1, java.sql.Types.FLOAT);
cstmt.registerOutParameter(2, java.sql.Types.FLOAT);
cstmt.registerOutParameter(3, java.sql.Types.FLOAT);
cstmt.executeUpdate();
Double val = (Double) cstmt.getObject(1);
assertTrue(val.doubleValue() == doubleValues[0]);
val = (Double) cstmt.getObject(2);
assertTrue(val.doubleValue() == doubleValues[1]);
val = (Double) cstmt.getObject(3);
assertTrue(cstmt.wasNull());
} catch (Exception ex) {
fail(ex.getMessage());
} finally {
try {
Statement dstmt = con.createStatement();
dstmt.execute(dropFloatProc);
} catch (Exception ex) {
}
}
}
public List<UserBean> fetchRecommendedArtistforArtist(String artistId)
throws ConnectionFailureException, SQLException, IOException {
List<UserBean> recommdArtistForArtistList = new ArrayList<UserBean>();
Connection conn = DBManager.getConnection();
CallableStatement callableStatement = conn.prepareCall("call FETCH_ARTIST_LIST_PROC(?,?,?)");
callableStatement.registerOutParameter(2, OracleTypes.CURSOR);
callableStatement.registerOutParameter(3, java.sql.Types.VARCHAR);
callableStatement.setString(1, artistId);
callableStatement.execute();
String errMsg = callableStatement.getString(3);
ResultSet rs = ((OracleCallableStatement) callableStatement).getCursor(2);
if (!callableStatement.wasNull()) {
while (rs.next()) {
UserBean userBean = new UserBean();
userBean.setUserid(rs.getString(1));
userBean.setFirstName(rs.getString(2));
userBean.setLastName(rs.getString(3));
userBean.setPhoneNo(rs.getDouble(4));
userBean.setDob(rs.getDate(5));
userBean.setCity(rs.getString(6));
userBean.setState(rs.getString(7));
recommdArtistForArtistList.add(userBean);
}
}
if (null != errMsg && !errMsg.isEmpty()) {
ErrorLog.logError(errMsg);
}
if (null != rs) rs.close();
if (null != callableStatement) callableStatement.close();
if (null != conn) conn.close();
return recommdArtistForArtistList;
}
public void testNumeric() throws Throwable {
CallableStatement call = con.prepareCall("{ call Numeric_Proc(?,?,?) }");
call.registerOutParameter(1, Types.NUMERIC, 15);
call.registerOutParameter(2, Types.NUMERIC, 15);
call.registerOutParameter(3, Types.NUMERIC, 15);
call.executeUpdate();
java.math.BigDecimal ret = call.getBigDecimal(1);
assertTrue(
"correct return from getNumeric () should be 999999999999999.000000000000000 but returned "
+ ret.toString(),
ret.equals(new java.math.BigDecimal("999999999999999.000000000000000")));
ret = call.getBigDecimal(2);
assertTrue(
"correct return from getNumeric ()",
ret.equals(new java.math.BigDecimal("0.000000000000001")));
try {
ret = call.getBigDecimal(3);
} catch (NullPointerException ex) {
assertTrue("This should be null", call.wasNull());
}
}
@Override
public Object getResult(CallableStatement cs, int columnIndex) throws SQLException {
Object sqlDate = cs.getDate(columnIndex);
if (cs.wasNull()) {
return null;
}
return sqlDate;
}
public Object getResult(CallableStatement cs, int columnIndex) throws SQLException {
java.sql.Timestamp sqlTimestamp = cs.getTimestamp(columnIndex);
if (cs.wasNull()) {
return null;
} else {
return new java.util.Date(sqlTimestamp.getTime());
}
}
@Override
public E getNullableResult(CallableStatement cs, int columnIndex) throws SQLException {
int i = cs.getInt(columnIndex);
if (cs.wasNull()) {
return null;
} else {
return convert(i);
}
}
public boolean wasNull() throws SQLException {
checkOpen();
try {
return _stmt.wasNull();
} catch (SQLException e) {
handleException(e);
return false;
}
}
@Override
public T getResult(CallableStatement cs, int columnIndex) throws SQLException {
T result = getNullableResult(cs, columnIndex);
if (cs.wasNull()) {
return null;
} else {
return result;
}
}
public Object getResult(CallableStatement cs, int columnIndex)
throws SQLException {
short s = cs.getShort(columnIndex);
if (cs.wasNull()) {
return null;
} else {
return new Short(s);
}
}
@Override
public Object getResult(CallableStatement cs, int columnIndex) throws SQLException {
String s = cs.getString(columnIndex);
if (cs.wasNull()) {
return null;
} else {
Character c = s.charAt(0);
return new Character(c);
}
}
/** Returns true if the last out parameter was null. */
@Override
public boolean wasNull() throws SQLException {
try {
return _cstmt.wasNull();
} catch (SQLException e) {
onSqlException(e);
throw e;
} catch (RuntimeException e) {
onRuntimeException(e);
throw e;
}
}
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 testGetInt01() throws Throwable {
try {
Statement stmt = con.createStatement();
stmt.execute("create temp table i_tab ( max_val int, min_val int, null_val int )");
stmt.execute("insert into i_tab values (2147483647,-2147483648,null)");
boolean ret =
stmt.execute(
"create or replace function "
+ "int_proc( OUT IMAX int, OUT IMIN int, OUT INUL int) as "
+ "'begin "
+ "select max_val into imax from i_tab;"
+ "select min_val into imin from i_tab;"
+ "select null_val into inul from i_tab;"
+ " end;' "
+ "language plpgsql;");
} catch (Exception ex) {
fail(ex.getMessage());
throw ex;
}
try {
CallableStatement cstmt = con.prepareCall("{ call int_proc(?,?,?) }");
cstmt.registerOutParameter(1, java.sql.Types.INTEGER);
cstmt.registerOutParameter(2, java.sql.Types.INTEGER);
cstmt.registerOutParameter(3, java.sql.Types.INTEGER);
cstmt.executeUpdate();
assertTrue(cstmt.getInt(1) == 2147483647);
assertTrue(cstmt.getInt(2) == -2147483648);
cstmt.getInt(3);
assertTrue(cstmt.wasNull());
} catch (Exception ex) {
fail(ex.getMessage());
} finally {
try {
Statement dstmt = con.createStatement();
dstmt.execute("drop function int_proc()");
} catch (Exception ex) {
}
}
}
public void testGetByte01() throws Throwable {
try {
Statement stmt = con.createStatement();
stmt.execute("create temp table byte_tab ( max_val int2, min_val int2, null_val int2 )");
stmt.execute("insert into byte_tab values (127,-128,null)");
boolean ret =
stmt.execute(
"create or replace function "
+ "byte_proc( OUT IMAX int2, OUT IMIN int2, OUT INUL int2) as "
+ "'begin "
+ "select max_val into imax from byte_tab;"
+ "select min_val into imin from byte_tab;"
+ "select null_val into inul from byte_tab;"
+ " end;' "
+ "language plpgsql;");
} catch (Exception ex) {
fail(ex.getMessage());
throw ex;
}
try {
CallableStatement cstmt = con.prepareCall("{ call byte_proc(?,?,?) }");
cstmt.registerOutParameter(1, java.sql.Types.TINYINT);
cstmt.registerOutParameter(2, java.sql.Types.TINYINT);
cstmt.registerOutParameter(3, java.sql.Types.TINYINT);
cstmt.executeUpdate();
assertTrue(cstmt.getByte(1) == 127);
assertTrue(cstmt.getByte(2) == -128);
cstmt.getByte(3);
assertTrue(cstmt.wasNull());
} catch (Exception ex) {
fail(ex.getMessage());
} finally {
try {
Statement dstmt = con.createStatement();
dstmt.execute("drop function byte_proc()");
} catch (Exception ex) {
}
}
}
public void testGetBoolean01() throws Throwable {
try {
Statement stmt = con.createStatement();
stmt.execute(createBitTab);
stmt.execute(insertBitTab);
boolean ret =
stmt.execute(
"create or replace function "
+ "bit_proc( OUT IMAX boolean, OUT IMIN boolean, OUT INUL boolean) as "
+ "'begin "
+ "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 bit_proc(?,?,?) }");
cstmt.registerOutParameter(1, java.sql.Types.BIT);
cstmt.registerOutParameter(2, java.sql.Types.BIT);
cstmt.registerOutParameter(3, java.sql.Types.BIT);
cstmt.executeUpdate();
assertTrue(cstmt.getBoolean(1));
assertFalse(cstmt.getBoolean(2));
cstmt.getBoolean(3);
assertTrue(cstmt.wasNull());
} catch (Exception ex) {
fail(ex.getMessage());
} finally {
try {
Statement dstmt = con.createStatement();
dstmt.execute("drop function bit_proc()");
} catch (Exception ex) {
}
}
}
public void testGetLong01() throws Throwable {
try {
Statement stmt = con.createStatement();
stmt.execute("create temp table l_tab ( max_val int8, min_val int8, null_val int8 )");
stmt.execute("insert into l_tab values (9223372036854775807,-9223372036854775808,null)");
boolean ret =
stmt.execute(
"create or replace function "
+ "bigint_proc( OUT IMAX int8, OUT IMIN int8, OUT INUL int8) as "
+ "'begin "
+ "select max_val into imax from l_tab;"
+ "select min_val into imin from l_tab;"
+ "select null_val into inul from l_tab;"
+ " end;' "
+ "language plpgsql;");
} catch (Exception ex) {
fail(ex.getMessage());
throw ex;
}
try {
CallableStatement cstmt = con.prepareCall("{ call bigint_proc(?,?,?) }");
cstmt.registerOutParameter(1, java.sql.Types.BIGINT);
cstmt.registerOutParameter(2, java.sql.Types.BIGINT);
cstmt.registerOutParameter(3, java.sql.Types.BIGINT);
cstmt.executeUpdate();
assertTrue(cstmt.getLong(1) == 9223372036854775807L);
assertTrue(cstmt.getLong(2) == -9223372036854775808L);
cstmt.getLong(3);
assertTrue(cstmt.wasNull());
} catch (Exception ex) {
fail(ex.getMessage());
} finally {
try {
Statement dstmt = con.createStatement();
dstmt.execute("drop function bigint_proc()");
} catch (Exception ex) {
}
}
}
public void testGetDoubleAsReal() throws Throwable {
try {
Statement stmt = con.createStatement();
stmt.execute("create temp table d_tab ( max_val float, min_val float, null_val float )");
stmt.execute("insert into d_tab values (3.4E38,1.4E-45,null)");
boolean ret =
stmt.execute(
"create or replace function "
+ "double_proc( OUT IMAX float, OUT IMIN float, OUT INUL float) as "
+ "'begin "
+ "select max_val into imax from d_tab;"
+ "select min_val into imin from d_tab;"
+ "select null_val into inul from d_tab;"
+ " end;' "
+ "language plpgsql;");
} catch (Exception ex) {
fail(ex.getMessage());
throw ex;
}
try {
CallableStatement cstmt = con.prepareCall("{ call double_proc(?,?,?) }");
cstmt.registerOutParameter(1, java.sql.Types.REAL);
cstmt.registerOutParameter(2, java.sql.Types.REAL);
cstmt.registerOutParameter(3, java.sql.Types.REAL);
cstmt.executeUpdate();
assertTrue(cstmt.getFloat(1) == 3.4E38f);
assertTrue(cstmt.getFloat(2) == 1.4E-45f);
cstmt.getFloat(3);
assertTrue(cstmt.wasNull());
} catch (Exception ex) {
fail(ex.getMessage());
} finally {
try {
Statement dstmt = con.createStatement();
dstmt.execute("drop function double_proc()");
} catch (Exception ex) {
}
}
}
/** Test of wasNull method, of inteface java.sql.CallableStatement. */
public void testWasNull() throws Exception {
println("wasNull");
if (!isTestOutParameters()) {
return;
}
CallableStatement stmt = prepareCall("{?= call cast(null as integer)}");
try {
boolean expResult = true;
stmt.registerOutParameter(1, Types.INTEGER);
stmt.execute();
stmt.getInt(1);
boolean result = stmt.wasNull();
assertEquals(expResult, result);
} catch (Exception e) {
fail(e.getMessage());
}
}
public void retrieveOutVariables(int ColIndex, cfSession _Session, CallableStatement _stmt)
throws SQLException, cfmRunTimeException {
boolean b;
byte[] bin;
int i;
long l;
double dbl;
float flt;
java.sql.Date dt;
java.sql.Time t;
Timestamp ts;
ResultSet rs;
String str;
cfData outData = null;
if (!isOUT()) return;
switch (cfSqlType) {
case CF_SQL_BIT:
// With the Oracle JDBC driver, if we set the parameters using named parameters then
// we must retrieve them using named parameters too.
if (useNamedParameters) {
b = _stmt.getBoolean(paramName);
} else {
b = _stmt.getBoolean(ColIndex);
}
if (!_stmt.wasNull()) outData = cfBooleanData.getcfBooleanData(b);
break;
case CF_SQL_BINARY:
case CF_SQL_VARBINARY:
case CF_SQL_BLOB:
// With the Oracle JDBC driver, if we set the parameters using named parameters then
// we must retrieve them using named parameters too.
if (useNamedParameters) {
bin = _stmt.getBytes(paramName);
} else {
bin = _stmt.getBytes(ColIndex);
}
if ((!_stmt.wasNull()) && (bin != null)) {
outData = new cfBinaryData(bin);
}
break;
case CF_SQL_SMALLINT:
case CF_SQL_INTEGER:
case CF_SQL_TINYINT:
try {
// With the Oracle JDBC driver, if we set the parameters using named parameters then
// we must retrieve them using named parameters too.
if (useNamedParameters) {
i = _stmt.getInt(paramName);
} else {
i = _stmt.getInt(ColIndex);
}
if (!_stmt.wasNull()) outData = new cfNumberData(i);
} catch (NumberFormatException e) {
// With JDK 1.3 and the JDBC-ODBC bridge, the getInt() method will
// throw a number format exception for in/out params so just ignore it.
// Ignoring it allows us to retrieve the out param values.
}
break;
case CF_SQL_BIGINT:
// With the Oracle JDBC driver, if we set the parameters using named parameters then
// we must retrieve them using named parameters too.
if (useNamedParameters) {
l = _stmt.getLong(paramName);
} else {
l = _stmt.getLong(ColIndex);
}
if (!_stmt.wasNull()) outData = new cfNumberData(l);
break;
case CF_SQL_DECIMAL:
case CF_SQL_NUMERIC:
dbl = getBigDecimalAsDouble(_stmt, useNamedParameters, paramName, ColIndex);
if (!_stmt.wasNull()) outData = new cfNumberData(dbl);
break;
case CF_SQL_DOUBLE:
case CF_SQL_FLOAT:
case CF_SQL_MONEY:
case CF_SQL_MONEY4:
// With the Oracle JDBC driver, if we set the parameters using named parameters then
// we must retrieve them using named parameters too.
if (useNamedParameters) {
dbl = _stmt.getDouble(paramName);
} else {
dbl = _stmt.getDouble(ColIndex);
}
if (!_stmt.wasNull()) outData = new cfNumberData(dbl);
break;
case CF_SQL_REAL:
// With the Oracle JDBC driver, if we set the parameters using named parameters then
// we must retrieve them using named parameters too.
if (useNamedParameters) {
flt = _stmt.getFloat(paramName);
} else {
flt = _stmt.getFloat(ColIndex);
}
// For some reason casting a float to a double doesn't return a double
// that exactly matches the original float so we'll use the less efficient
// algorithm of converting the float to a string and the string to a double.
// If for some reason this fails then we'll revert to casting the float to
// a double.
if (!_stmt.wasNull()) {
try {
dbl = Double.valueOf(Float.toString(flt)).doubleValue();
} catch (Exception e) {
dbl = (double) flt;
}
outData = new cfNumberData(dbl);
}
break;
case CF_SQL_DATE:
// With the Oracle JDBC driver, if we set the parameters using named parameters then
// we must retrieve them using named parameters too.
if (useNamedParameters) {
dt = _stmt.getDate(paramName);
} else {
dt = _stmt.getDate(ColIndex);
}
if ((!_stmt.wasNull()) && (dt != null)) {
outData = new cfDateData(dt);
}
break;
case CF_SQL_TIME:
// With the Oracle JDBC driver, if we set the parameters using named parameters then
// we must retrieve them using named parameters too.
if (useNamedParameters) {
t = _stmt.getTime(paramName);
} else {
t = _stmt.getTime(ColIndex);
}
if ((!_stmt.wasNull()) && (t != null)) {
outData = new cfDateData(t);
}
break;
case CF_SQL_TIMESTAMP:
try {
// With the Oracle JDBC driver, if we set the parameters using named parameters then
// we must retrieve them using named parameters too.
if (useNamedParameters) {
ts = _stmt.getTimestamp(paramName);
} else {
ts = _stmt.getTimestamp(ColIndex);
}
if ((!_stmt.wasNull()) && (ts != null)) {
outData = new cfDateData(ts);
}
} catch (NullPointerException e) {
// With JDK 1.3 and the JDBC-ODBC bridge, the getTimestamp() method will
// throw a null ptr exception when the underlying value is null so just ignore it.
}
break;
case CF_SQL_REFCURSOR:
// This CF SQL Type is only used with Oracle for result sets returned by a
// stored procedure.
// With the Oracle JDBC driver, if we set the parameters using named parameters then
// we must retrieve them using named parameters too.
if (useNamedParameters) {
rs = (ResultSet) _stmt.getObject(paramName);
} else {
rs = (ResultSet) _stmt.getObject(ColIndex);
}
if ((!_stmt.wasNull()) && (rs != null)) {
outData = new cfQueryResultData(rs, "Stored Procedure", maxLength);
}
break;
default:
// With the Oracle JDBC driver, if we set the parameters using named parameters then
// we must retrieve them using named parameters too.
if (useNamedParameters) {
str = _stmt.getString(paramName);
} else {
str = _stmt.getString(ColIndex);
}
if ((!_stmt.wasNull()) && (str != null)) {
outData = new cfStringData(str);
}
break;
}
_Session.setData(outVariable, (outData == null ? cfNullData.NULL : outData));
}
private static <T> T checkWasNull(CallableStatement statement, T value) throws SQLException {
return statement.wasNull() ? null : value;
}
public boolean wasNull() throws SQLException {
return passThru.wasNull();
}
/** @see java.sql.CallableStatement#wasNull() */
public boolean wasNull() throws SQLException {
return original.wasNull();
}
/** {@inheritDoc} */
@Override
public Object get(CallableStatement cs, int index) throws SQLException {
Object result = cs.getBoolean(index);
if (cs.wasNull()) return null;
else return result;
}
