Java Code Examples for java.sql.PreparedStatement#setBinaryStream()

public void testUpdateBlobWithStreamLengthlessParameterName()
        throws Exception {
    InputStream is1 = new java.io.ByteArrayInputStream(BYTES1);
    // InputStream for insertion.
    InputStream is2 = new java.io.ByteArrayInputStream(BYTES2);

    // Prepared Statement used to insert the data
    PreparedStatement ps_sb = prep("dBlob");
    ps_sb.setInt(1, key);
    ps_sb.setBinaryStream(2, is1);
    ps_sb.executeUpdate();
    ps_sb.close();

    // Update operation
    ResultSet rs1 = fetchUpd("dBlob", key);
    rs1.next();
    rs1.updateBlob("dBlob", is2);
    rs1.updateRow();
    rs1.close();

    // Query to see whether the data that has been updated.
    rs1 = fetch("dBlob", key);
    rs1.next();
    assertEquals(new ByteArrayInputStream(BYTES2), rs1.getBinaryStream(1));
    rs1.close();
}
 

public void testUpdateBlobLengthless()
        throws Exception {
    InputStream is1 = new java.io.ByteArrayInputStream(BYTES1);
    // InputStream for insertion.
    InputStream is2 = new java.io.ByteArrayInputStream(BYTES2);

    // Prepared Statement used to insert the data
    PreparedStatement ps_sb = prep("dBlob");
    ps_sb.setInt(1, key);
    ps_sb.setBinaryStream(2, is1);
    ps_sb.executeUpdate();
    ps_sb.close();

    // Update operation
    ResultSet rs1 = fetchUpd("dBlob", key);
    rs1.next();
    rs1.updateBlob(1, is2);
    rs1.updateRow();
    rs1.close();

    // Query to see whether the data that has been updated.
    rs1 = fetch("dBlob", key);
    rs1.next();
    assertEquals(new ByteArrayInputStream(BYTES2), rs1.getBinaryStream(1));
    rs1.close();
}
 

public void nullSafeSet(final PreparedStatement st, final Object value, final int index)
        throws HibernateException, SQLException {

    checkInitialization();
    
    if (value == null) {
        st.setNull(index, sqlType);
    } else {
        final byte[] encryptedValue = this.encryptor.encrypt((byte[]) value);
        if (Environment.useStreamsForBinary()) {
            st.setBinaryStream(
                    index, 
                    new ByteArrayInputStream(encryptedValue), 
                    encryptedValue.length);
        } else {
            st.setBytes(index, encryptedValue);
        }
    }
    
}
 

public void nullSafeSet(final PreparedStatement st, final Object value, final int index)
        throws HibernateException, SQLException {

    checkInitialization();
    
    if (value == null) {
        st.setNull(index, sqlType);
    } else {
        final byte[] encryptedValue = this.encryptor.encrypt((byte[]) value);
        if (Environment.useStreamsForBinary()) {
            st.setBinaryStream(
                    index, 
                    new ByteArrayInputStream(encryptedValue), 
                    encryptedValue.length);
        } else {
            st.setBytes(index, encryptedValue);
        }
    }
    
}
 

/**
 * Create table, insert row and set debug property.
 */
public void setUp()
        throws Exception {
    // Create the table.
    Statement createTableSt = createStatement();
    createTableSt.execute(
            "create table TEST_TABLE( TEST_COL blob( 65536 ))");
    createTableSt.close();
    // Insert a row.
    PreparedStatement insertLobSt = prepareStatement(
            "insert into TEST_TABLE (TEST_COL) values (?)");
    int lobLength = 65536;
    insertLobSt.setBinaryStream(1,
            new LoopingAlphabetStream(lobLength), lobLength);
    insertLobSt.executeUpdate();
    insertLobSt.close();
    setSystemProperty("derby.debug.suicideOfLayerBStreaming", "true");
}
 

/**
 * There was a defect (DERBY-121) where the server and client
 * were processing lob lengths incorrectly.  For lob lengths
 * that are represented by 24 or more bits, the server and
 * Derby client were doing incorrect bit-shifting.  This
 * test makes sure that problem no longer occurs.
 */
public void testLongLobLengths() throws Exception
{
    PreparedStatement pSt = prepareStatement(
        "insert into lobTable100M(bl) values (?)");

    // The error we're testing occurs when the server
    // is shifting bits 24 and higher of the lob's
    // length (in bytes).  This means that, in order
    // to check for the error, we have to specify a
    // lob length (in bytes) that requires at least
    // 24 bits to represent.  Thus for a blob the
    // length of the test data must be specified as
    // at least 2^24 bytes (hence the '16800000' in
    // the next line).
    int lobSize = 16800000;
    pSt.setBinaryStream(1,
        new LoopingAlphabetStream(lobSize), lobSize);

    // Now try the insert; this is where the server processes
    // the lob length.
    pSt.execute();
    pSt.close();
    commit();
}
 

/**
 * {@inheritDoc}
 */
protected void setStatementParameterValue(PreparedStatement statement, int sqlIndex, int typeCode, Object value) throws SQLException
{
    if (value != null)
    {
        if ((value instanceof byte[]) &&
            ((typeCode == Types.BINARY) || (typeCode == Types.VARBINARY) || (typeCode == Types.BLOB)))
        {
            byte[]               bytes  = (byte[])value;
            ByteArrayInputStream stream = new ByteArrayInputStream(bytes);

            statement.setBinaryStream(sqlIndex, stream, bytes.length);
            return;
        }
        else if ((value instanceof String) && ((typeCode == Types.CLOB) || (typeCode == Types.LONGVARCHAR)))
        {
            // Clob is not supported directly
            statement.setString(sqlIndex, (String)value);
            return;
        }
    }
    super.setStatementParameterValue(statement, sqlIndex, typeCode, value);
}
 

@Override
public void setBlobAsBinaryStream(
		PreparedStatement ps, int paramIndex, @Nullable InputStream binaryStream, int contentLength)
		throws SQLException {

	if (streamAsLob) {
		if (binaryStream != null) {
			if (contentLength >= 0) {
				ps.setBlob(paramIndex, binaryStream, contentLength);
			}
			else {
				ps.setBlob(paramIndex, binaryStream);
			}
		}
		else {
			ps.setBlob(paramIndex, (Blob) null);
		}
	}
	else if (wrapAsLob) {
		if (binaryStream != null) {
			ps.setBlob(paramIndex, new PassThroughBlob(binaryStream, contentLength));
		}
		else {
			ps.setBlob(paramIndex, (Blob) null);
		}
	}
	else if (contentLength >= 0) {
		ps.setBinaryStream(paramIndex, binaryStream, contentLength);
	}
	else {
		ps.setBinaryStream(paramIndex, binaryStream);
	}
	if (logger.isDebugEnabled()) {
		logger.debug(binaryStream != null ? "Set binary stream for BLOB with length " + contentLength :
				"Set BLOB to null");
	}
}
 

public void testJarInstallThroughVTI() throws Exception {
  Connection conn = TestUtil.getConnection();
  Statement stmt = conn.createStatement();
  String sql = "call SQLJ.INSTALL_JAR_BYTES(?, ?)";
  PreparedStatement ps = conn.prepareStatement(sql);
  ps.setBinaryStream(1, new FileInputStream(myjar));
  ps.setString(2, "app.sample1");
  ps.executeUpdate();

  String ddl = "create table EMP.PARTITIONTESTTABLE (ID int NOT NULL,"
      + " SECONDID int not null, THIRDID varchar(10) not null,"
      + " PRIMARY KEY (SECONDID, THIRDID)) PARTITION BY COLUMN (ID)";

  stmt.execute(ddl);

  stmt.execute("INSERT INTO EMP.PARTITIONTESTTABLE VALUES (2, 2, '3')");
  stmt.execute("INSERT INTO EMP.PARTITIONTESTTABLE VALUES (3, 3, '3')");

  stmt.execute("INSERT INTO EMP.PARTITIONTESTTABLE VALUES (2, 2, '4')");

  stmt.execute("CREATE PROCEDURE MergeSort () "
      + "LANGUAGE JAVA PARAMETER STYLE JAVA "
      + "READS SQL DATA DYNAMIC RESULT SETS 1 "
      + "EXTERNAL NAME 'myexamples.MergeSortProcedure.mergeSort' ");

  stmt.execute("CREATE ALIAS MergeSortProcessor FOR 'myexamples.MergeSortProcessor'");

  sql = "CALL MergeSort() " + "WITH RESULT PROCESSOR MergeSortProcessor "
      + "ON TABLE EMP.PARTITIONTESTTABLE WHERE 1=1";

  CallableStatement cs = conn.prepareCall(sql);
  cs.execute();

  ResultSet rs = cs.getResultSet();

  while (rs.next()) {
    System.out.println(rs.getObject(1));
  }
}
 

/**
 * Set given string as Blob for the given index into the prepared-statement.
 *
 * @param value    string value to be converted to blob
 * @param prepStmt Prepared statement
 * @param index    Column index
 * @throws SQLException
 * @throws IOException
 */
private void setBlobValue(String value, PreparedStatement prepStmt, int index) throws SQLException, IOException {

    if (value != null) {
        InputStream inputStream = new ByteArrayInputStream(value.getBytes(StandardCharsets.UTF_8));
        prepStmt.setBinaryStream(index, inputStream, inputStream.available());
    } else {
        prepStmt.setBinaryStream(index, new ByteArrayInputStream(new byte[0]), 0);
    }
}
 

/**
 * Test <code>updateBinaryStream</code> on a BINARY column, without
 * specifying length of inputstream.
 */
public void testUpdateBinaryStreamLengthless()
        throws IOException, SQLException {
    InputStream is1 = new java.io.ByteArrayInputStream(BYTES1);
    // InputStream used for update.
    InputStream is2 = new java.io.ByteArrayInputStream(BYTES2);

    //Prepared Statement used to insert the data
    PreparedStatement ps_sb = prep("dLongBit");
    ps_sb.setInt(1, key);
    ps_sb.setBinaryStream(2, is1);
    ps_sb.executeUpdate();
    ps_sb.close();

    //Update operation
    ResultSet rs1 = fetchUpd("dLongBit", key);
    rs1.next();
    rs1.updateBinaryStream(1, is2);
    rs1.updateRow();
    rs1.close();

    //Query to see whether the data that has been updated
    //using the updateBinaryStream method is the same
    //data that we expected

    rs1 = fetch("dLongBit", key);
    rs1.next();
    assertEquals(new ByteArrayInputStream(BYTES2), rs1.getBinaryStream(1));
    rs1.close();
}
 

/**
    * {@inheritDoc}
    */
protected void setStatementParameterValue(PreparedStatement statement, int sqlIndex, int typeCode, Object value) throws SQLException
{
       if ((typeCode == Types.BLOB) || (typeCode == Types.LONGVARBINARY))
       {
           // jConnect doesn't like the BLOB type, but works without problems with LONGVARBINARY
           // even when using the Blob class
           if (value instanceof byte[])
           {
               byte[] data = (byte[])value;

               statement.setBinaryStream(sqlIndex, new ByteArrayInputStream(data), data.length);
           }
           else
           {
               // Sybase doesn't like the BLOB type, but works without problems with LONGVARBINARY
               // even when using the Blob class
               super.setStatementParameterValue(statement, sqlIndex, Types.LONGVARBINARY, value);
           }
       }
	else if (typeCode == Types.CLOB)
	{
		// Same for CLOB and LONGVARCHAR
		super.setStatementParameterValue(statement, sqlIndex, Types.LONGVARCHAR, value);
	}
	else
	{
		super.setStatementParameterValue(statement, sqlIndex, typeCode, value);
	}
}
 

/**
 * <p>
 * Update the job detail record.
 * </p>
 * 
 * @param conn
 *          the DB Connection
 * @param job
 *          the job to update
 * @return number of rows updated
 * @throws IOException
 *           if there were problems serializing the JobDataMap
 */
public int updateJobDetail(Connection conn, JobDetail job)
    throws IOException, SQLException {
    //log.debug( "Updating job detail " + job );
    ByteArrayOutputStream baos = serializeJobData(job.getJobDataMap());
    int len = baos.toByteArray().length;
    ByteArrayInputStream bais = new ByteArrayInputStream(baos.toByteArray());

    PreparedStatement ps = null;

    int insertResult = 0;

    try {
        ps = conn.prepareStatement(rtp(UPDATE_JOB_DETAIL));
        ps.setString(1, job.getDescription());
        ps.setString(2, job.getJobClass().getName());
        setBoolean(ps, 3, job.isDurable());
        setBoolean(ps, 4, job.isVolatile());
        setBoolean(ps, 5, job.isStateful());
        setBoolean(ps, 6, job.requestsRecovery());
        ps.setBinaryStream(7, bais, len);
        ps.setString(8, job.getName());
        ps.setString(9, job.getGroup());

        insertResult = ps.executeUpdate();
    } finally {
        closeStatement(ps);
    }

    if (insertResult > 0) {
        deleteJobListeners(conn, job.getName(), job.getGroup());

        String[] jobListeners = job.getJobListenerNames();
        for (int i = 0; jobListeners != null && i < jobListeners.length; i++) {
            insertJobListener(conn, job, jobListeners[i]);
        }
    }

    return insertResult;
}
 

@Override
public void setBinaryStream(final int parameterIndex, final InputStream x,
		final int length) throws SQLException {
	ParameterCallback callback = new ParameterCallbackAction(
			parameterIndex, x) {

		@Override
		public void call(PreparedStatement preparedStatement)
				throws SQLException {
			preparedStatement.setBinaryStream(parameterIndex(), (InputStream)getParameter(), length);
		}
	};
	addParameterCallback(callback);
}
 

@Override
public void setBlobAsBinaryStream(
		PreparedStatement ps, int paramIndex, @Nullable InputStream binaryStream, int contentLength)
		throws SQLException {

	if (streamAsLob) {
		if (binaryStream != null) {
			if (contentLength >= 0) {
				ps.setBlob(paramIndex, binaryStream, contentLength);
			}
			else {
				ps.setBlob(paramIndex, binaryStream);
			}
		}
		else {
			ps.setBlob(paramIndex, (Blob) null);
		}
	}
	else if (wrapAsLob) {
		if (binaryStream != null) {
			ps.setBlob(paramIndex, new PassThroughBlob(binaryStream, contentLength));
		}
		else {
			ps.setBlob(paramIndex, (Blob) null);
		}
	}
	else if (contentLength >= 0) {
		ps.setBinaryStream(paramIndex, binaryStream, contentLength);
	}
	else {
		ps.setBinaryStream(paramIndex, binaryStream);
	}
	if (logger.isDebugEnabled()) {
		logger.debug(binaryStream != null ? "Set binary stream for BLOB with length " + contentLength :
				"Set BLOB to null");
	}
}
 

private void insertLoopingAlphabetStreamData(PreparedStatement ps,
    int lobLength) throws Exception {
  ps.setBinaryStream(1, new LoopingAlphabetStream(lobLength), lobLength);
  ps.setInt(2, lobLength);
  ps.setLong(3, getStreamCheckSum(new LoopingAlphabetStream(lobLength)));
  ps.executeUpdate();
}
 

@Override
public void setByteArrayAsBinaryStream(PreparedStatement ps, int index, InputStream is) throws SQLException {
    ps.setBinaryStream(index, is);
}
 

private void insertNewHouse(House house) {
	Connection con = null;
	try {
		con = DatabaseFactory.getConnection();
		PreparedStatement stmt = con.prepareStatement(ADD_HOUSE_QUERY);

		stmt.setInt(1, house.getObjectId());
		stmt.setInt(2, house.getAddress().getId());
		stmt.setInt(3, house.getBuilding().getId());
		stmt.setInt(4, house.getOwnerId());
		if (house.getAcquiredTime() == null) {
			stmt.setNull(5, Types.TIMESTAMP);
		}
		else {
			stmt.setTimestamp(5, house.getAcquiredTime());
		}

		stmt.setInt(6, house.getPermissions());
		stmt.setString(7, house.getStatus().toString());
		stmt.setInt(8, house.isFeePaid() ? 1 : 0);

		if (house.getNextPay() == null) {
			stmt.setNull(9, Types.TIMESTAMP);
		}
		else {
			stmt.setTimestamp(9, house.getNextPay());
		}

		if (house.getSellStarted() == null) {
			stmt.setNull(10, Types.TIMESTAMP);
		}
		else {
			stmt.setTimestamp(10, house.getSellStarted());
		}

		byte[] signNotice = house.getSignNotice();
		if (signNotice.length == 0) {
			stmt.setNull(11, Types.BINARY);
		}
		else {
			stmt.setBinaryStream(11, new ByteArrayInputStream(signNotice));
		}

		stmt.execute();
		stmt.close();
		house.setPersistentState(PersistentState.UPDATED);
	}
	catch (Exception e) {
		log.error("Could not store studio data. " + e.getMessage(), e);
		return;
	}
	finally {
		DatabaseFactory.close(con);
	}
	return;
}
 

/**
 * This methods tests the ResultSet interface method
 * updateBlob
 *
 * @throws SQLException if some error occurs while calling the method
 */
public void testUpdateBlobStringParameterName()
throws Exception {
    // Life span of Blob objects are limited by the transaction.  Need
    // autocommit off so Blob objects survive execution of next statement.
    getConnection().setAutoCommit(false);

    //Byte array in which the returned bytes from
    //the Database after the update are stored. This
    //array is then checked to determine if it
    //has the same elements of the Byte array used for
    //the update operation

    byte[] bytes_ret = new byte[10];

    //1 Input Stream for insertion
    InputStream is1 = new java.io.ByteArrayInputStream(BYTES1);

    //2 Input Stream for insertion
    InputStream is2 = new java.io.ByteArrayInputStream(BYTES2);

    //Prepared Statement used to insert the data
    PreparedStatement ps_sb = prep("dBlob");

    //first insert
    ps_sb.setInt(1, key);
    ps_sb.setBinaryStream(2,is1,BYTES1.length);
    ps_sb.executeUpdate();

    //second insert
    int key2 = requestKey();
    ps_sb.setInt(1, key2);
    ps_sb.setBinaryStream(2,is2,BYTES2.length);
    ps_sb.executeUpdate();

    ps_sb.close();

    //Update operation
    //use a different ResultSet variable so that the
    //other tests can go on unimpacted
    //we do not have set methods on Clob and Blob implemented
    //So query the first Clob from the database
    //update the second result set with this
    //Clob value

    ResultSet rs1 = fetch("dBlob", key);
    rs1.next();
    Blob blob = rs1.getBlob(1);
    rs1.close();

    rs1 = fetchUpd("dBlob", key2);
    rs1.next();
    rs1.updateBlob("dBlob",blob);
    rs1.updateRow();
    rs1.close();

    //Query to see whether the data that has been updated
    //using the updateBlob method is the same
    //data that we expected

    rs1 = fetch("dBlob", key2);
    rs1.next();
    assertEquals(blob, rs1.getBlob(1)); 
    rs1.close();
}
 

/**
 * This methods tests the ResultSet interface method
 * updateBlob
 *
 * @throws SQLException if some error occurs while calling the method
 */
public void testUpdateBlobStringParameterName()
throws Exception {
    // Life span of Blob objects are limited by the transaction.  Need
    // autocommit off so Blob objects survive execution of next statement.
    getConnection().setAutoCommit(false);

    //Byte array in which the returned bytes from
    //the Database after the update are stored. This
    //array is then checked to determine if it
    //has the same elements of the Byte array used for
    //the update operation

    byte[] bytes_ret = new byte[10];

    //1 Input Stream for insertion
    InputStream is1 = new java.io.ByteArrayInputStream(BYTES1);

    //2 Input Stream for insertion
    InputStream is2 = new java.io.ByteArrayInputStream(BYTES2);

    //Prepared Statement used to insert the data
    PreparedStatement ps_sb = prep("dBlob");

    //first insert
    ps_sb.setInt(1, key);
    ps_sb.setBinaryStream(2,is1,BYTES1.length);
    ps_sb.executeUpdate();

    //second insert
    int key2 = requestKey();
    ps_sb.setInt(1, key2);
    ps_sb.setBinaryStream(2,is2,BYTES2.length);
    ps_sb.executeUpdate();

    ps_sb.close();

    //Update operation
    //use a different ResultSet variable so that the
    //other tests can go on unimpacted
    //we do not have set methods on Clob and Blob implemented
    //So query the first Clob from the database
    //update the second result set with this
    //Clob value

    ResultSet rs1 = fetch("dBlob", key);
    rs1.next();
    Blob blob = rs1.getBlob(1);
    rs1.close();

    rs1 = fetchUpd("dBlob", key2);
    rs1.next();
    rs1.updateBlob("dBlob",blob);
    rs1.updateRow();
    rs1.close();

    //Query to see whether the data that has been updated
    //using the updateBlob method is the same
    //data that we expected

    rs1 = fetch("dBlob", key2);
    rs1.next();
    assertEquals(blob, rs1.getBlob(1)); 
    rs1.close();
}