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(); } }