public void testGetBytes02() throws Throwable {
byte[] testdata = "TestData".getBytes();
try {
Statement stmt = con.createStatement();
stmt.execute("create temp table longvarbinary_tab ( vbinary bytea, null_val bytea )");
boolean ret =
stmt.execute(
"create or replace function "
+ "longvarbinary_proc( OUT pcn bytea, OUT nval bytea) as "
+ "'begin "
+ "select vbinary into pcn from longvarbinary_tab;"
+ "select null_val into nval from longvarbinary_tab;"
+ " end;' "
+ "language plpgsql;");
stmt.close();
PreparedStatement pstmt = con.prepareStatement("insert into longvarbinary_tab values (?,?)");
pstmt.setBytes(1, testdata);
pstmt.setBytes(2, null);
pstmt.executeUpdate();
pstmt.close();
} catch (Exception ex) {
fail(ex.getMessage());
throw ex;
}
try {
CallableStatement cstmt = con.prepareCall("{ call longvarbinary_proc(?,?) }");
cstmt.registerOutParameter(1, Types.LONGVARBINARY);
cstmt.registerOutParameter(2, Types.LONGVARBINARY);
cstmt.executeUpdate();
byte[] retval = cstmt.getBytes(1);
for (int i = 0; i < testdata.length; i++) {
assertTrue(testdata[i] == retval[i]);
}
retval = cstmt.getBytes(2);
assertTrue(retval == null);
} catch (Exception ex) {
fail(ex.getMessage());
} finally {
try {
Statement dstmt = con.createStatement();
dstmt.execute("drop function longvarbinary_proc()");
} catch (Exception ex) {
}
}
}
public byte[] getBytes(String parameterName) throws SQLException {
checkOpen();
try {
return _stmt.getBytes(parameterName);
} catch (SQLException e) {
handleException(e);
return null;
}
}
/** The bytes value */
@Override
public byte[] getBytes(String name) throws SQLException {
try {
return _cstmt.getBytes(name);
} catch (SQLException e) {
onSqlException(e);
throw e;
} catch (RuntimeException e) {
onRuntimeException(e);
throw e;
}
}
/** Test of getBytes method, of inteface java.sql.CallableStatement. */
public void testGetBytes() throws Exception {
println("getBytes");
if (!isTestOutParameters()) {
return;
}
CallableStatement stmt;
byte[] expResult = new byte[] {(byte) 0xca, (byte) 0xfe, (byte) 0xba, (byte) 0xbe};
byte[] result = null;
try {
stmt = prepRegAndExec("{?= call cast(X'cafebabe' as binary(4))}", 1, Types.BINARY);
result = stmt.getBytes(1);
} catch (Exception ex) {
fail(ex.getMessage());
}
assertEquals(expResult, result);
}
@SuppressWarnings("unchecked")
public static <T> T getFromStatement(ExecuteContext ctx, Class<? extends T> type, int index)
throws SQLException {
CallableStatement stmt = (CallableStatement) ctx.statement();
if (type == Blob.class) {
return (T) stmt.getBlob(index);
} else if (type == Boolean.class) {
return (T) checkWasNull(stmt, Boolean.valueOf(stmt.getBoolean(index)));
} else if (type == BigInteger.class) {
BigDecimal result = stmt.getBigDecimal(index);
return (T) (result == null ? null : result.toBigInteger());
} else if (type == BigDecimal.class) {
return (T) stmt.getBigDecimal(index);
} else if (type == Byte.class) {
return (T) checkWasNull(stmt, Byte.valueOf(stmt.getByte(index)));
} else if (type == byte[].class) {
return (T) stmt.getBytes(index);
} else if (type == Clob.class) {
return (T) stmt.getClob(index);
} else if (type == Date.class) {
return (T) stmt.getDate(index);
} else if (type == Double.class) {
return (T) checkWasNull(stmt, Double.valueOf(stmt.getDouble(index)));
} else if (type == Float.class) {
return (T) checkWasNull(stmt, Float.valueOf(stmt.getFloat(index)));
} else if (type == Integer.class) {
return (T) checkWasNull(stmt, Integer.valueOf(stmt.getInt(index)));
} else if (type == Long.class) {
return (T) checkWasNull(stmt, Long.valueOf(stmt.getLong(index)));
} else if (type == Short.class) {
return (T) checkWasNull(stmt, Short.valueOf(stmt.getShort(index)));
} else if (type == String.class) {
return (T) stmt.getString(index);
} else if (type == Time.class) {
return (T) stmt.getTime(index);
} else if (type == Timestamp.class) {
return (T) stmt.getTimestamp(index);
} else if (type == YearToMonth.class) {
if (ctx.getDialect() == POSTGRES) {
Object object = stmt.getObject(index);
return (T) (object == null ? null : PostgresUtils.toYearToMonth(object));
} else {
String string = stmt.getString(index);
return (T) (string == null ? null : YearToMonth.valueOf(string));
}
} else if (type == DayToSecond.class) {
if (ctx.getDialect() == POSTGRES) {
Object object = stmt.getObject(index);
return (T) (object == null ? null : PostgresUtils.toDayToSecond(object));
} else {
String string = stmt.getString(index);
return (T) (string == null ? null : DayToSecond.valueOf(string));
}
} else if (type == UByte.class) {
String string = stmt.getString(index);
return (T) (string == null ? null : UByte.valueOf(string));
} else if (type == UShort.class) {
String string = stmt.getString(index);
return (T) (string == null ? null : UShort.valueOf(string));
} else if (type == UInteger.class) {
String string = stmt.getString(index);
return (T) (string == null ? null : UInteger.valueOf(string));
} else if (type == ULong.class) {
String string = stmt.getString(index);
return (T) (string == null ? null : ULong.valueOf(string));
}
// The type byte[] is handled earlier. byte[][] can be handled here
else if (type.isArray()) {
return (T) convertArray(stmt.getObject(index), (Class<? extends Object[]>) type);
} else if (ArrayRecord.class.isAssignableFrom(type)) {
return (T) getArrayRecord(ctx, stmt.getArray(index), (Class<? extends ArrayRecord<?>>) type);
} else if (EnumType.class.isAssignableFrom(type)) {
return getEnumType(type, stmt.getString(index));
} else if (MasterDataType.class.isAssignableFrom(type)) {
return (T) getMasterDataType(type, stmt.getString(index));
} else if (UDTRecord.class.isAssignableFrom(type)) {
switch (ctx.getDialect()) {
case POSTGRES:
return (T) pgNewUDTRecord(type, stmt.getObject(index));
}
return (T) stmt.getObject(index, DataTypes.udtRecords());
} else if (Result.class.isAssignableFrom(type)) {
ResultSet nested = (ResultSet) stmt.getObject(index);
return (T) getNewFactory(ctx).fetch(nested);
} else {
return (T) stmt.getObject(index);
}
}
@Override
public byte[] getNullableResult(CallableStatement cs, int columnIndex) throws SQLException {
return cs.getBytes(columnIndex);
}
/** @see java.sql.CallableStatement#getBytes(int) */
public byte[] getBytes(int parameterIndex) throws SQLException {
return original.getBytes(parameterIndex);
}
/** @see java.sql.CallableStatement#getBytes(java.lang.String) */
public byte[] getBytes(String parameterName) throws SQLException {
return original.getBytes(parameterName);
}
private static void callGetMethod(
CallableStatement cs, int arg, int type, int paramType, StringBuilder strbuf)
throws Throwable {
switch (type) {
case Types.BIT:
case Types.BOOLEAN:
strbuf.append("getBoolean(" + arg + ") = ");
strbuf.append(cs.getBoolean(arg));
break;
case Types.TINYINT:
strbuf.append("getByte(" + arg + ") = ");
strbuf.append(Byte.toString(cs.getByte(arg)));
break;
case Types.SMALLINT:
strbuf.append("getShort(" + arg + ") = ");
strbuf.append(Short.toString(cs.getShort(arg)));
break;
case Types.INTEGER:
strbuf.append("getInt(" + arg + ") = ");
strbuf.append(Integer.toString(cs.getInt(arg)));
break;
case Types.BIGINT:
strbuf.append("getLong(" + arg + ") = ");
strbuf.append(Long.toString(cs.getLong(arg)));
break;
case Types.FLOAT:
case Types.REAL:
strbuf.append("getFloat(" + arg + ") = ");
strbuf.append(Float.toString(cs.getFloat(arg)));
break;
case Types.DOUBLE:
strbuf.append("getDouble(" + arg + ") = ");
strbuf.append(Double.toString(cs.getDouble(arg)));
break;
case Types.DECIMAL:
case Types.NUMERIC:
strbuf.append("getBigDecimal(" + arg + ") = ");
strbuf.append(BigDecimalHandler.getBigDecimalString(cs, arg, paramType));
break;
case Types.CHAR:
case Types.VARCHAR:
case Types.LONGVARCHAR:
strbuf.append("getString(" + arg + ") = ");
String s = cs.getString(arg);
if (s.startsWith("[B@")) s = "byte[] reference";
strbuf.append(s);
break;
case Types.BINARY:
case Types.VARBINARY:
case Types.LONGVARBINARY:
strbuf.append("getBytes(" + arg + ") = ");
byteArrayToString(cs.getBytes(arg), strbuf);
break;
case Types.DATE:
strbuf.append("getDate(" + arg + ") = ");
Date date = cs.getDate(arg);
strbuf.append(date == null ? "null" : date.toString());
break;
case Types.TIME:
strbuf.append("getTime(" + arg + ") = ");
Time time = cs.getTime(arg);
strbuf.append(time == null ? "null" : time.toString());
break;
case Types.TIMESTAMP:
strbuf.append("getTimestamp(" + arg + ") = ");
Timestamp timestamp = cs.getTimestamp(arg);
strbuf.append(timestamp == null ? "null" : timestamp.toString());
break;
case Types.OTHER:
strbuf.append("getObject(" + arg + ") = ");
Object o = cs.getObject(arg);
if (o == null) {
strbuf.append("null");
} else if (o instanceof byte[]) {
byteArrayToString((byte[]) o, strbuf);
} else {
strbuf.append(o.toString());
}
break;
default:
throw new Throwable("TEST ERROR: unexpected type " + type);
}
}
public byte[] getBytes(String parameterName) throws SQLException {
return passThru.getBytes(parameterName);
}
public byte[] getBytes(int parameterIndex) throws SQLException {
return passThru.getBytes(parameterIndex);
}
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));
}
public void sampleCode() {
// Variables for simple test case
String procname = "sp_timestampSample";
/* *CREATE PROCEDURE*
String createQuery1 =
"create table spt_timestampSample(f1 int, f2 char(5), f3 timestamp )";
String insertQuery1 =
"insert spt_timestampSample(f1,f2) values(1, 'Hello')";
// Sample Stored Procedure
String dropProc = "drop proc " + procname;
String createProc =
"create proc " + procname +
"(@p1 int, @p2 timestamp out)" +
" as " +
"select 'p1=' + convert(varchar(10),@p1) " +
"select @p2 = f3 from spt_timestampSample where f1=1" +
"select * from spt_timestampSample " +
"return 21";
*/
String sproc = "{? = call " + procname + "(?,?)}";
try {
/* *CREATE PROCEDURE*
// We will create a temp table which contains a timestamp column
// and we will insert a row. We will then execute a stored
// procedure which will returnt the timestamp column as an OUTPUT
// parameter
// Create our table
execDDL( createQuery1);
// Insert our row
execDDL( insertQuery1);
// Now create the Proc
execDDL( createProc);
*/
// Now execute our Sproc
CallableStatement cstmt = _con.prepareCall(sproc);
output("Executing: " + sproc + "\n");
// Declare the IN Params. Note, you must skip the Return Status
cstmt.setInt(2, 1961);
// Now declare our OUT Params
cstmt.registerOutParameter(1, Types.INTEGER);
cstmt.registerOutParameter(3, Types.VARBINARY);
boolean results = cstmt.execute();
int rsnum = 0; // Number of Result Sets processed
int rowsAffected = 0;
do {
if (results) {
ResultSet rs = cstmt.getResultSet();
output("\n\nDisplaying ResultSet: " + rsnum + "\n");
dispResultSet(rs);
rsnum++;
rs.close();
} else {
rowsAffected = cstmt.getUpdateCount();
if (rowsAffected >= 0) output(rowsAffected + " rows Affected.\n");
}
results = cstmt.getMoreResults();
} while (results || rowsAffected != -1);
String s = cstmt.getString(1);
String s2 = cstmt.getString(3);
// Now grab the same output parameter as VARBINARY
byte[] ts = cstmt.getBytes(3);
// Display the Output Parameters
output("OUT Param1=" + s + "\n");
output("OUT Param2 as String=" + s2 + "\n");
output("OUT Param2 as byte[]=" + toHexString(ts) + "\n");
cstmt.close();
/* *CREATE PROCEDURE*
// Drop our sproc
execDDL( dropProc);
*/
} catch (SQLException ex) {
displaySQLEx(ex);
} catch (java.lang.Exception ex) {
// Got some other type of exception. Dump it.
ex.printStackTrace();
}
}
