List of usage examples for java.sql CallableStatement getBlob
Blob getBlob(String parameterName) throws SQLException;
BLOB parameter as a java.sql.Blob object in the Java programming language. From source file:Main.java
public static void main(String[] args) throws Exception { String WRITE_OBJECT_SQL = "BEGIN " + " INSERT INTO java_objects(object_id, object_name, object_value) " + " VALUES (?, ?, empty_blob()) " + " RETURN object_value INTO ?; " + "END;"; String READ_OBJECT_SQL = "SELECT object_value FROM java_objects WHERE object_id = ?"; Connection conn = getOracleConnection(); conn.setAutoCommit(false);//from w ww. j a va2 s .com List<Object> list = new ArrayList<Object>(); list.add("This is a short string."); list.add(new Integer(1234)); list.add(new java.util.Date()); // write object to Oracle long id = 0001; String className = list.getClass().getName(); CallableStatement cstmt = conn.prepareCall(WRITE_OBJECT_SQL); cstmt.setLong(1, id); cstmt.setString(2, className); cstmt.registerOutParameter(3, java.sql.Types.BLOB); cstmt.executeUpdate(); BLOB blob = (BLOB) cstmt.getBlob(3); OutputStream os = blob.getBinaryOutputStream(); ObjectOutputStream oop = new ObjectOutputStream(os); oop.writeObject(list); oop.flush(); oop.close(); os.close(); // Read object from oracle PreparedStatement pstmt = conn.prepareStatement(READ_OBJECT_SQL); pstmt.setLong(1, id); ResultSet rs = pstmt.executeQuery(); rs.next(); InputStream is = rs.getBlob(1).getBinaryStream(); ObjectInputStream oip = new ObjectInputStream(is); Object object = oip.readObject(); className = object.getClass().getName(); oip.close(); is.close(); rs.close(); pstmt.close(); conn.commit(); // de-serialize list a java object from a given objectID List listFromDatabase = (List) object; System.out.println("[After De-Serialization] list=" + listFromDatabase); conn.close(); }
From source file:io.lightlink.types.BlobConverter.java
@Override public Object readFromCallableStatement(CallableStatement cs, int pos, RunnerContext runnerContext, String colName) throws SQLException, IOException { InputStream stream = cs.getBlob(pos).getBinaryStream(); return stream == null ? null : inputStreamToReturnValue(stream); }
From source file:com.netspective.axiom.sql.StoredProcedureParameter.java
/** * Extract the OUT parameter values from the callable statment and * assign them to the value of the parameter. *//* w w w. j a v a2 s . c o m*/ public void extract(ConnectionContext cc, CallableStatement stmt) throws SQLException { if (getType().getValueIndex() == StoredProcedureParameter.Type.IN) return; int index = this.getIndex(); QueryParameterType paramType = getSqlType(); int jdbcType = paramType.getJdbcType(); String identifier = paramType.getIdentifier(); // result sets are special if (identifier.equals(QueryParameterType.RESULTSET_IDENTIFIER)) { ResultSet rs = (ResultSet) stmt.getObject(index); QueryResultSet qrs = new QueryResultSet(getParent().getProcedure(), cc, rs); value.getValue(cc).setValue(qrs); return; } switch (jdbcType) { case Types.VARCHAR: value.getValue(cc).setTextValue(stmt.getString(index)); break; case Types.INTEGER: value.getValue(cc).setValue(new Integer(stmt.getInt(index))); break; case Types.DOUBLE: value.getValue(cc).setValue(new Double(stmt.getDouble(index))); break; case Types.CLOB: Clob clob = stmt.getClob(index); value.getValue(cc).setTextValue(clob.getSubString(1, (int) clob.length())); break; case java.sql.Types.ARRAY: Array array = stmt.getArray(index); value.getValue(cc).setValue(array); break; case java.sql.Types.BIGINT: long bigint = stmt.getLong(index); value.getValue(cc).setValue(new Long(bigint)); break; case java.sql.Types.BINARY: value.getValue(cc).setTextValue(new String(stmt.getBytes(index))); break; case java.sql.Types.BIT: boolean bit = stmt.getBoolean(index); value.getValue(cc).setValue(new Boolean(bit)); case java.sql.Types.BLOB: value.getValue(cc).setValue(stmt.getBlob(index)); break; case java.sql.Types.CHAR: value.getValue(cc).setTextValue(stmt.getString(index)); break; case java.sql.Types.DATE: value.getValue(cc).setValue(stmt.getDate(index)); break; case java.sql.Types.DECIMAL: value.getValue(cc).setValue(stmt.getBigDecimal(index)); break; case java.sql.Types.DISTINCT: value.getValue(cc).setValue(stmt.getObject(index)); break; case java.sql.Types.FLOAT: value.getValue(cc).setValue(new Float(stmt.getFloat(index))); break; case java.sql.Types.JAVA_OBJECT: value.getValue(cc).setValue(stmt.getObject(index)); break; case java.sql.Types.LONGVARBINARY: value.getValue(cc).setTextValue(new String(stmt.getBytes(index))); break; case java.sql.Types.LONGVARCHAR: value.getValue(cc).setTextValue(stmt.getString(index)); break; //case java.sql.Types.NULL: // value.getValue(cc).setValue(null); // break; case java.sql.Types.NUMERIC: value.getValue(cc).setValue(stmt.getBigDecimal(index)); break; case java.sql.Types.OTHER: value.getValue(cc).setValue(stmt.getObject(index)); break; case java.sql.Types.REAL: value.getValue(cc).setValue(new Float(stmt.getFloat(index))); break; //case java.sql.Types.REF: // Ref ref = stmt.getRef(index); // break; case java.sql.Types.SMALLINT: short sh = stmt.getShort(index); value.getValue(cc).setValue(new Short(sh)); break; case java.sql.Types.STRUCT: value.getValue(cc).setValue(stmt.getObject(index)); break; case java.sql.Types.TIME: value.getValue(cc).setValue(stmt.getTime(index)); break; case java.sql.Types.TIMESTAMP: value.getValue(cc).setValue(stmt.getTimestamp(index)); break; case java.sql.Types.TINYINT: byte b = stmt.getByte(index); value.getValue(cc).setValue(new Byte(b)); break; case java.sql.Types.VARBINARY: value.getValue(cc).setValue(stmt.getBytes(index)); break; default: throw new RuntimeException( "Unknown JDBC Type set for stored procedure parameter '" + this.getName() + "'."); } }
From source file:org.apache.oozie.tools.OozieDBCLI.java
private void convertClobToBlobInOracle(Connection conn) throws Exception { if (conn == null) { return;//from w w w. j a v a 2s . c o m } System.out.println("Converting clob columns to blob for all tables"); Statement statement = conn.createStatement(); CallableStatement tempBlobCall = conn.prepareCall("{call dbms_lob.CREATETEMPORARY(?, TRUE)}"); tempBlobCall.registerOutParameter(1, java.sql.Types.BLOB); CallableStatement dbmsLobCallStmt = conn .prepareCall("{call dbms_lob.CONVERTTOBLOB(?, ?, ?, ?, ?, 0, ?, ?)}"); dbmsLobCallStmt.registerOutParameter(1, java.sql.Types.BLOB); // Lob max size dbmsLobCallStmt.setInt(3, Integer.MAX_VALUE); dbmsLobCallStmt.registerOutParameter(4, java.sql.Types.INTEGER); // dest_offset dbmsLobCallStmt.setInt(4, 1); // src_offset dbmsLobCallStmt.registerOutParameter(5, java.sql.Types.INTEGER); dbmsLobCallStmt.setInt(5, 1); // blob_csid dbmsLobCallStmt.registerOutParameter(6, java.sql.Types.INTEGER); // lang_context dbmsLobCallStmt.setInt(6, 0); // warning dbmsLobCallStmt.registerOutParameter(7, java.sql.Types.INTEGER); dbmsLobCallStmt.setInt(7, 1); for (Map.Entry<String, List<String>> tableClobColumnMap : getTableClobColumnMap().entrySet()) { String tableName = tableClobColumnMap.getKey(); List<String> columnNames = tableClobColumnMap.getValue(); for (String column : columnNames) { statement.executeUpdate(getAddColumnQuery(tableName, TEMP_COLUMN_PREFIX + column, "blob")); } ResultSet rs = statement.executeQuery(getSelectQuery(tableName, columnNames)); while (rs.next()) { for (int i = 0; i < columnNames.size(); i++) { Clob srcClob = rs.getClob(columnNames.get(i)); if (srcClob == null || srcClob.length() < 1) { continue; } tempBlobCall.execute(); Blob destLob = tempBlobCall.getBlob(1); dbmsLobCallStmt.setBlob(1, destLob); dbmsLobCallStmt.setClob(2, srcClob); dbmsLobCallStmt.execute(); Blob blob = dbmsLobCallStmt.getBlob(1); PreparedStatement ps = conn.prepareStatement("update " + tableName + " set " + TEMP_COLUMN_PREFIX + columnNames.get(i) + "=? where id = ?"); ps.setBlob(1, blob); ps.setString(2, rs.getString(1)); ps.executeUpdate(); ps.close(); } } rs.close(); for (String column : columnNames) { statement.executeUpdate(getDropColumnQuery(tableName, column)); statement.executeUpdate(getRenameColumnQuery(tableName, TEMP_COLUMN_PREFIX + column, column)); } } dbmsLobCallStmt.close(); tempBlobCall.close(); System.out.println("Done"); }
From source file:org.wso2.carbon.dataservices.core.description.query.SQLQuery.java
private ParamValue getOutparameterValue(CallableStatement cs, String type, int ordinal) throws DataServiceFault { try {/* w w w . j a v a 2 s . c o m*/ Object elementValue; if (type.equals(DBConstants.DataTypes.STRING)) { elementValue = cs.getString(ordinal); return new ParamValue(elementValue == null ? null : elementValue.toString()); } else if (type.equals(DBConstants.DataTypes.DOUBLE)) { elementValue = cs.getDouble(ordinal); return new ParamValue( elementValue == null ? null : ConverterUtil.convertToString((Double) elementValue)); } else if (type.equals(DBConstants.DataTypes.BIGINT)) { elementValue = cs.getLong(ordinal); return new ParamValue( elementValue == null ? null : ConverterUtil.convertToString((Long) elementValue)); } else if (type.equals(DBConstants.DataTypes.INTEGER)) { elementValue = cs.getInt(ordinal); return new ParamValue( elementValue == null ? null : ConverterUtil.convertToString((Integer) elementValue)); } else if (type.equals(DBConstants.DataTypes.TIME)) { elementValue = cs.getTime(ordinal); return new ParamValue(elementValue == null ? null : this.convertToTimeString((Time) elementValue)); } else if (type.equals(DBConstants.DataTypes.DATE)) { elementValue = cs.getDate(ordinal); return new ParamValue( elementValue == null ? null : ConverterUtil.convertToString((Date) elementValue)); } else if (type.equals(DBConstants.DataTypes.TIMESTAMP)) { elementValue = cs.getTimestamp(ordinal, calendar); return new ParamValue( elementValue == null ? null : this.convertToTimestampString((Timestamp) elementValue)); } else if (type.equals(DBConstants.DataTypes.BLOB)) { elementValue = cs.getBlob(ordinal); return new ParamValue(elementValue == null ? null : this.getBase64StringFromInputStream(((Blob) elementValue).getBinaryStream())); } else if (type.equals(DBConstants.DataTypes.CLOB)) { elementValue = cs.getClob(ordinal); return new ParamValue(elementValue == null ? null : deriveValueFromClob((Clob) elementValue)); } else if (type.equals(DBConstants.DataTypes.STRUCT)) { elementValue = cs.getObject(ordinal); return new ParamValue(elementValue == null ? null : (Struct) elementValue); } else if (type.equals(DBConstants.DataTypes.ARRAY)) { Array dataArray = cs.getArray(ordinal); ParamValue paramValue = new ParamValue(ParamValue.PARAM_VALUE_ARRAY); if (dataArray != null) { this.processSQLArray(dataArray, paramValue); } return paramValue; } else if (type.equals(DBConstants.DataTypes.NUMERIC)) { elementValue = cs.getBigDecimal(ordinal); return new ParamValue( elementValue == null ? null : ConverterUtil.convertToString((BigDecimal) elementValue)); } else if (type.equals(DBConstants.DataTypes.BIT)) { elementValue = cs.getBoolean(ordinal); return new ParamValue( elementValue == null ? null : ConverterUtil.convertToString((Boolean) elementValue)); } else if (type.equals(DBConstants.DataTypes.TINYINT)) { elementValue = cs.getByte(ordinal); return new ParamValue( elementValue == null ? null : ConverterUtil.convertToString((Byte) elementValue)); } else if (type.equals(DBConstants.DataTypes.SMALLINT)) { elementValue = cs.getShort(ordinal); return new ParamValue( elementValue == null ? null : ConverterUtil.convertToString((Short) elementValue)); } else if (type.equals(DBConstants.DataTypes.REAL)) { elementValue = cs.getFloat(ordinal); return new ParamValue( elementValue == null ? null : ConverterUtil.convertToString((Float) elementValue)); } else if (type.equals(DBConstants.DataTypes.BINARY)) { elementValue = cs.getBlob(ordinal); return new ParamValue(elementValue == null ? null : this.getBase64StringFromInputStream(((Blob) elementValue).getBinaryStream())); } else { throw new DataServiceFault("Unsupported data type: " + type); } } catch (SQLException e) { throw new DataServiceFault(e, "Error in getting sql output parameter values."); } }