Java Code Examples for java.sql.Blob#getBytes()

@Override
public Object convert(Object src, Class destClass) {
    if (Blob.class.isInstance(src) && destClass.equals(byte[].class)) {
        if (src == null) {
            return null;
        } else {
            try {
                Blob blob = (Blob) src;
                return blob.getBytes(0, (int) blob.length());
            } catch (SQLException e) {
                throw new ConvertorException(e);
            }
        }
    }

    throw new ConvertorException("Unsupported convert: [" + src.getClass().getName() + ","
                                 + destClass.getName() + "]");
}
 

public byte[] asBlob (Object o) {
	if (o == null) {
		return null;
	} else if (o.getClass ().getName ().equals ("[B")) {
		return (byte[]) o;
	} else {
		Blob	blob = (Blob) o;

		try {
			return blob == null ? null : blob.getBytes (1, (int) blob.length ());
		} catch (SQLException e) {
			failure ("blob parse", e);
		}
		return null;
	}
}
 

@Override
public Object convert(Object src, Class destClass) {
    if (Blob.class.isInstance(src) && destClass.equals(byte[].class)) {
        if (src == null) {
            return null;
        } else {
            try {
                Blob blob = (Blob) src;
                return blob.getBytes(0, (int) blob.length());
            } catch (SQLException e) {
                throw new ConvertorException(e);
            }
        }
    }

    return src != null ? src.toString() : null;
}
 

@Override
public byte[] getNullableResult(ResultSet rs, int columnIndex)
    throws SQLException {
  Blob blob = rs.getBlob(columnIndex);
  byte[] returnValue = null;
  if (null != blob) {
    returnValue = blob.getBytes(1, (int) blob.length());
  }
  return returnValue;
}
 

public static void pmap(Blob in, Blob[] inout, Blob[] out) throws SQLException {
    int    leftLength = (int) in.length();
    int    rightLength = (int) inout[0].length();
    byte[] left = in.getBytes( 1L, leftLength );
    byte[] right = inout[0].getBytes( 1L, rightLength );
    byte[] retval = new byte[ leftLength + rightLength ];
    System.arraycopy( left, 0, retval, 0, leftLength );
    System.arraycopy( right, 0, retval, leftLength, rightLength );
    inout[0] = new HarmonySerialBlob( retval );
    
    out[0] = new HarmonySerialBlob( new byte[] { (byte) 1, (byte) 2, (byte) 3 } );
}
 

public Object getValidateSource(String id, ResultSet rs) throws SQLException
{
	ResultSetMetaData metadata = rs.getMetaData();
	byte[] rv = null;
	switch(metadata.getColumnType(1))
	{
	case Types.BLOB:
		Blob blob = rs.getBlob(1);
		if(blob != null)
		{
			rv = blob.getBytes(1L, (int) blob.length());
		}
		else
		{
			log.info("getValidateSource(" + id + ") blob is null" );
		}
		break;
	case Types.CLOB:
		Clob clob = rs.getClob(1);
		if(clob != null)
		{
			rv = clob.getSubString(1L, (int) clob.length()).getBytes();
		}
		break;
	case Types.CHAR:
	case Types.LONGVARCHAR:
	case Types.VARCHAR:
		rv = rs.getString(1).getBytes();
		break;
	case Types.BINARY:
	case Types.VARBINARY:
	case Types.LONGVARBINARY:
		rv = rs.getBytes(1);
		break;
	}
	return rv;
}
 

public Blob convert(Blob obj) throws ConversionException {
    try {
        return new javax.sql.rowset.serial.SerialBlob(obj.getBytes(1, (int) obj.length()));
    } catch (Exception e) {
        throw new ConversionException(e);
    }
}
 

public byte[] getContent() throws SQLException {
  if (content == null) {
    Connection c = null;
    Statement s = null;
    ResultSet rs = null;
    try {
      c = dataSource.getConnection();
      s = c.createStatement();
      s.setQueryTimeout(60);
      rs = s.executeQuery("SELECT content FROM filestore WHERE id = '" + id + "'");
      if (rs.next()) {
        Blob blob = rs.getBlob("content");
        content = blob.getBytes(1, (int) blob.length());
      }
    } finally {
      if (rs != null) {
        rs.close();
      }
      if (s != null) {
        s.close();
      }
      if (c != null) {
        c.close();
      }
    }
    if (content == null) {
      content = new byte[0];
    }
  }
  return content;
}
 

@Override
public void extendAuth(PlayerAuth auth, int id, Connection con) throws SQLException {
    try (PreparedStatement pst = con.prepareStatement(
        "SELECT data FROM " + xfPrefix + "user_authenticate WHERE " + col.ID + "=?;")) {
        pst.setInt(1, id);
        try (ResultSet rs = pst.executeQuery()) {
            if (rs.next()) {
                Blob blob = rs.getBlob("data");
                byte[] bytes = blob.getBytes(1, (int) blob.length());
                auth.setPassword(new HashedPassword(XfBCrypt.getHashFromBlob(bytes)));
            }
        }
    }
}
 

@Override
public byte[] getNullableResult(ResultSet rs, String columnName)
    throws SQLException {
  Blob blob = rs.getBlob(columnName);
  byte[] returnValue = null;
  if (null != blob) {
    returnValue = blob.getBytes(1, (int) blob.length());
  }
  return returnValue;
}
 

@Override
public byte[] getBlobAsBytes(ResultSet rs, int columnIndex) throws SQLException {
	logger.debug("Returning BLOB as bytes");
	if (this.wrapAsLob) {
		Blob blob = rs.getBlob(columnIndex);
		return blob.getBytes(1, (int) blob.length());
	}
	else {
		return rs.getBytes(columnIndex);
	}
}
 

public Object getValidateSource(String id, ResultSet rs) throws SQLException
{
	ResultSetMetaData metadata = rs.getMetaData();
	byte[] rv = null;
	switch(metadata.getColumnType(1))
	{
	case Types.BLOB:
		Blob blob = rs.getBlob(1);
		if(blob != null)
		{
			rv = blob.getBytes(1L, (int) blob.length());
		}
		else
		{
			log.info("getValidateSource(" + id + ") blob ==  null" );
		}
		break;
	case Types.CLOB:
		Clob clob = rs.getClob(1);
		if(clob != null)
		{
			rv = clob.getSubString(1L, (int) clob.length()).getBytes();
		}
		break;
	case Types.CHAR:
	case Types.LONGVARCHAR:
	case Types.VARCHAR:
		rv = rs.getString(1).getBytes();
		break;
	case Types.BINARY:
	case Types.VARBINARY:
	case Types.LONGVARBINARY:
		rv = rs.getBytes(1);
		break;
	}
	return rv;
}
 

/**
 * Get a value from a result set column based on the current value metadata
 *
 * @param iDatabase the database metadata to use
 * @param resultSet         The JDBC result set to read from
 * @param index             The column index (1-based)
 * @return The Hop native data type based on the value metadata
 * @throws HopDatabaseException in case something goes wrong.
 */
@Override
public Object getValueFromResultSet( IDatabase iDatabase, ResultSet resultSet, int index )
  throws HopDatabaseException {
  try {
    Object data = null;

    switch ( getType() ) {
      case IValueMeta.TYPE_BOOLEAN:
        data = Boolean.valueOf( resultSet.getBoolean( index + 1 ) );
        break;
      case IValueMeta.TYPE_NUMBER:
        data = new Double( resultSet.getDouble( index + 1 ) );
        break;
      case IValueMeta.TYPE_BIGNUMBER:
        data = resultSet.getBigDecimal( index + 1 );
        break;
      case IValueMeta.TYPE_INTEGER:
        data = Long.valueOf( resultSet.getLong( index + 1 ) );
        break;
      case IValueMeta.TYPE_STRING:
        if ( isStorageBinaryString() ) {
          data = resultSet.getBytes( index + 1 );
        } else {
          data = resultSet.getString( index + 1 );
        }
        break;
      case IValueMeta.TYPE_BINARY:
        if ( iDatabase.supportsGetBlob() ) {
          Blob blob = resultSet.getBlob( index + 1 );
          if ( blob != null ) {
            data = blob.getBytes( 1L, (int) blob.length() );
          } else {
            data = null;
          }
        } else {
          data = resultSet.getBytes( index + 1 );
        }
        break;

      case IValueMeta.TYPE_DATE:
        if ( getPrecision() != 1 && iDatabase.supportsTimeStampToDateConversion() ) {
          data = resultSet.getTimestamp( index + 1 );
          break; // Timestamp extends java.util.Date
        } else if ( iDatabase.isNetezzaVariant() ) {
          // PDI-10877 workaround for IBM netezza jdbc 'special' implementation
          data = getNetezzaDateValueWorkaround( iDatabase, resultSet, index + 1 );
          break;
        } else {
          data = resultSet.getDate( index + 1 );
          break;
        }
      default:
        break;
    }
    if ( resultSet.wasNull() ) {
      data = null;
    }
    return data;
  } catch ( SQLException e ) {
    throw new HopDatabaseException( "Unable to get value '" + toStringMeta() + "' from database resultset, index "
      + index, e );
  }

}
 

/**
 * tests set bytes method of blob in memory only mode (less than 4k)
 */
public void testSetBytesSmallBlob () throws SQLException {
  Connection con = TestUtil.getConnection();
  Statement stmt = con.createStatement();
  stmt.execute ("create table blobtest (id integer, data Blob)"+ getSuffix());
  stmt.close();
  
  con.setAutoCommit (false);
  PreparedStatement pstmt = con.prepareStatement("insert into " +
                                                 "blobtest (id, data) values (?,?)");
  pstmt.setInt (1,1);
  Blob blob = con.createBlob();
  //add 1024 bytes
  byte [] data = new byte [BUFFER_SIZE];
  for (int i = 0; i < BUFFER_SIZE; i++) {
    data [i] = (byte) (i % 255);
  }
  blob.setBytes (1, data);
  assertEquals (BUFFER_SIZE, blob.length());
  pstmt.setBlob (2, blob);
  pstmt.executeUpdate();
  
  stmt = con.createStatement();
  ResultSet rs = stmt.executeQuery("select data from blobtest where id = 1");
  assertEquals(true, rs.next());
  blob = rs.getBlob (1);
  assertEquals (BUFFER_SIZE, blob.length());
  //update blob in the middle
  byte [] data1 = new byte [UPDATE_SIZE];
  for (int i = 0; i < UPDATE_SIZE; i++)
    data1 [i] = 120;//just any value
  blob.setBytes (UPDATE_SIZE, data1);
  byte [] data2 = blob.getBytes (100, UPDATE_SIZE);
  for (int i = 0; i < UPDATE_SIZE; i++)
    assertEquals (data1 [i], data2 [i]);
 
  //update it at the end
  blob.setBytes (BUFFER_SIZE + 1, data1);
  assertEquals (BUFFER_SIZE + UPDATE_SIZE, blob.length());
  data2 = blob.getBytes (BUFFER_SIZE + 1, UPDATE_SIZE);
  for (int i = 0; i < UPDATE_SIZE; i++)
    assertEquals (data1 [i], data2 [i]);
  
  //insert the blob and test again
  pstmt.setInt (1, 2);
  pstmt.setBlob (2, blob);
  pstmt.executeUpdate();
  
  rs = stmt.executeQuery("select data from blobtest where " +
                         "id = 2");
  assertEquals(true, rs.next());
  blob = rs.getBlob (1);
  assertEquals (BUFFER_SIZE + UPDATE_SIZE, blob.length());
  data2 = blob.getBytes (100, UPDATE_SIZE);
  for (int i = 0; i < UPDATE_SIZE; i++)
    assertEquals (data1 [i], data2 [i]);
  data2 = blob.getBytes (BUFFER_SIZE + 1, UPDATE_SIZE);
  for (int i = 0; i < UPDATE_SIZE; i++)
    assertEquals (data1 [i], data2 [i]);
  
  //test truncate on small size blob
  blob = con.createBlob();
  data = new byte [100];
  for (int i = 0; i < 100; i++) {
    data [i] = (byte) i;
  }
  blob.setBytes (1, data);
  assertEquals (blob.length(), 100);
  blob.truncate (50);
  assertEquals (blob.length(), 50);
  blob.setBytes (1, data);
  assertEquals ("set failed", blob.length(), 100);
  blob.truncate (50);
  assertEquals ("truncation failed", blob.length(), 50);
  rs.close();
  con.commit();
  stmt.close();
  pstmt.close();
  
  stmt = con.createStatement();
  stmt.execute ("drop table blobtest");
  this.waitTillAllClear();
  stmt.close();
}
 

/**
 * Tests large blob (more than 4k) to ensure LOBStreamControl uses file.
 */
public void testSetBytesLargeBlob() throws Exception {
  startVMs(1, 4);

  Connection con = TestUtil.getConnection();
  Statement stmt = con.createStatement();
  stmt.execute("create table blobtest (id integer, data Blob)");
  stmt.close();

  con.setAutoCommit(false);
  PreparedStatement pstmt = con.prepareStatement("insert into "
      + "blobtest (id, data) values (?,?)");
  Blob blob = con.createBlob();
  byte[] data = new byte[BUFFER_SIZE];
  for (int i = 0; i < BUFFER_SIZE; i++) {
    data[i] = (byte)(i % 255);
  }
  // now add more than 4k so file get in use
  for (int i = 0; i < 5; i++)
    blob.setBytes(i * BUFFER_SIZE + 1, data);
  assertEquals(BUFFER_SIZE * 5, blob.length());
  // update blob in the middle
  byte[] data1 = new byte[UPDATE_SIZE];
  for (int i = 0; i < UPDATE_SIZE; i++)
    data1[i] = 120;// just any value
  blob.setBytes(BUFFER_SIZE + 1, data1);
  blob.setBytes(BUFFER_SIZE * 5 + 1, data1);
  assertEquals(5 * BUFFER_SIZE + UPDATE_SIZE, blob.length());
  // insert it into table
  pstmt.setInt(1, 3);
  pstmt.setBlob(2, blob);
  pstmt.executeUpdate();

  stmt = con.createStatement();
  ResultSet rs = stmt.executeQuery("select data from blobtest where "
      + "id = 3");
  assertEquals(true, rs.next());
  blob = rs.getBlob(1);
  byte[] data2 = blob.getBytes(BUFFER_SIZE + 1, UPDATE_SIZE);
  assertEquals(5 * BUFFER_SIZE + UPDATE_SIZE, blob.length());
  for (int i = 0; i < UPDATE_SIZE; i++)
    assertEquals(data1[i], data2[i]);
  data2 = blob.getBytes(5 * BUFFER_SIZE + 1, UPDATE_SIZE);
  for (int i = 0; i < UPDATE_SIZE; i++)
    assertEquals(data1[i], data2[i]);
  // test truncate
  blob.truncate(BUFFER_SIZE);
  assertEquals("truncate failed", BUFFER_SIZE, blob.length());
  rs.close();
  con.commit();
  stmt.close();
  pstmt.close();
}
 

/**
 * Actually read a BlobRef instance from the ResultSet and materialize
 * the data either inline or to a file.
 *
 * @param colNum the column of the ResultSet's current row to read.
 * @param r the ResultSet to read from.
 * @return a BlobRef encapsulating the data in this field.
 * @throws IOException if an error occurs writing to the FileSystem.
 * @throws SQLException if an error occurs reading from the database.
 */
public com.cloudera.sqoop.lib.BlobRef readBlobRef(int colNum, ResultSet r)
    throws IOException, InterruptedException, SQLException {

  long maxInlineLobLen = conf.getLong(
      MAX_INLINE_LOB_LEN_KEY,
      DEFAULT_MAX_LOB_LENGTH);

  Blob b = r.getBlob(colNum);
  if (null == b) {
    return null;
  } else if (b.length() > maxInlineLobLen) {
    // Deserialize very large BLOBs into separate files.
    long len = b.length();
    LobFile.Writer lobWriter = getBlobWriter();

    long recordOffset = lobWriter.tell();
    InputStream is = null;
    OutputStream os = lobWriter.writeBlobRecord(len);
    try {
      is = b.getBinaryStream();
      copyAll(is, os);
    } finally {
      if (null != os) {
        os.close();
      }

      if (null != is) {
        is.close();
      }

      // Mark the record as finished.
      lobWriter.finishRecord();
    }

    return new com.cloudera.sqoop.lib.BlobRef(
        getRelativePath(curBlobWriter), recordOffset, len);
  } else {
    // This is a 1-based array.
    return new com.cloudera.sqoop.lib.BlobRef(
        b.getBytes(1, (int) b.length()));
  }
}
 

private static String getBlobValue( Blob c ) throws Exception
{
    byte[] bytes = c.getBytes( 1L, (int) c.length() );

    return new String( bytes, UTF8 );
}
 

/**
 * This method allows a database dialect to convert database specific data types to Kettle data types.
 *
 * @param resultSet
 *          The result set to use
 * @param valueMeta
 *          The description of the value to retrieve
 * @param index
 *          the index on which we need to retrieve the value, 0-based.
 * @return The correctly converted Kettle data type corresponding to the valueMeta description.
 * @throws KettleDatabaseException
 */
@Override
public Object getValueFromResultSet( ResultSet rs, ValueMetaInterface val, int i ) throws KettleDatabaseException {
  Object data = null;

  try {
    switch ( val.getType() ) {
      case ValueMetaInterface.TYPE_BOOLEAN:
        data = Boolean.valueOf( rs.getBoolean( i + 1 ) );
        break;
      case ValueMetaInterface.TYPE_NUMBER:
        data = new Double( rs.getDouble( i + 1 ) );
        break;
      case ValueMetaInterface.TYPE_BIGNUMBER:
        data = rs.getBigDecimal( i + 1 );
        break;
      case ValueMetaInterface.TYPE_INTEGER:
        data = Long.valueOf( rs.getLong( i + 1 ) );
        break;
      case ValueMetaInterface.TYPE_STRING:
        if ( val.isStorageBinaryString() ) {
          data = rs.getBytes( i + 1 );
        } else {
          data = rs.getString( i + 1 );
        }
        break;
      case ValueMetaInterface.TYPE_BINARY:
        if ( supportsGetBlob() ) {
          Blob blob = rs.getBlob( i + 1 );
          if ( blob != null ) {
            data = blob.getBytes( 1L, (int) blob.length() );
          } else {
            data = null;
          }
        } else {
          data = rs.getBytes( i + 1 );
        }
        break;
      case ValueMetaInterface.TYPE_TIMESTAMP:
      case ValueMetaInterface.TYPE_DATE:
        if ( val.getOriginalColumnType() == java.sql.Types.TIME ) {
          // Neoview can not handle getDate / getTimestamp for a Time column
          data = rs.getTime( i + 1 );
          break; // Time is a subclass of java.util.Date, the default date
                 // will be 1970-01-01
        } else if ( val.getPrecision() != 1 && supportsTimeStampToDateConversion() ) {
          data = rs.getTimestamp( i + 1 );
          break; // Timestamp extends java.util.Date
        } else {
          data = rs.getDate( i + 1 );
          break;
        }
      default:
        break;
    }
    if ( rs.wasNull() ) {
      data = null;
    }
  } catch ( SQLException e ) {
    throw new KettleDatabaseException( "Unable to get value '"
      + val.toStringMeta() + "' from database resultset, index " + i, e );
  }

  return data;
}
 

/**
 * Search for the position in this Blob at which a specified pattern begins,
 * starting at a specified position within the Blob.
 * 
 * @param pattern
 *            a Blob containing the pattern of data to search for in this
 *            Blob
 * @param start
 *            the position within this Blob to start the search, where the
 *            first position in the Blob is 1
 * @return a long value with the position at which the pattern begins. -1 if
 *         the pattern is not found in this Blob.
 * @throws SQLException
 *             if an error occurs accessing the Blob
 * @throws SQLException
 *             if an error is encountered
 */
public long position(Blob pattern, long start) throws SQLException {
    byte[] patternBytes = pattern.getBytes(1, (int) pattern.length());
    return position(patternBytes, start);
}
 

public  static  String  varchar_Blob_Blob( Blob a ) throws Exception { return new String( a.getBytes( 1L, (int) a.length() ), "UTF-8"  ); }