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

public  void    test_blob_Blob_String()
    throws Exception
{
    Connection  conn = getConnection();

    declareAndRunFunction
        ( "blob_Blob_String", "blob", new String[] { "varchar( 10 )" }, "'3'", "33" );

    // now test blob arguments
    declareFunction( conn, "varchar_Blob_Blob", "varchar( 10 )", new String[] { "blob" } );

    runFunction( conn, "varchar_Blob_Blob", " \"blob_Blob_String\"( 'abc' )", "abc", null );

    // make sure that you can set lob-typed ? parameters
    PreparedStatement ps = chattyPrepare( conn, "values ( \"varchar_Blob_Blob\"( ? ) )" );
    String expectedValue = "34";
    Blob blob = AnsiSignatures.blob_Blob_String( expectedValue );
    ps.setBlob( 1, blob );
    String actualValue = getScalarString( ps );
    assertTrue( expectedValue.equals( actualValue ) );
}
 

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

	if (binaryStream != null) {
		Blob blob = ps.getConnection().createBlob();
		try {
			FileCopyUtils.copy(binaryStream, blob.setBinaryStream(1));
		}
		catch (IOException ex) {
			throw new DataAccessResourceFailureException("Could not copy into LOB stream", ex);
		}
		this.temporaryBlobs.add(blob);
		ps.setBlob(paramIndex, blob);
	}
	else {
		ps.setBlob(paramIndex, (Blob) null);
	}

	if (logger.isDebugEnabled()) {
		logger.debug(binaryStream != null ?
				"Copied binary stream into temporary BLOB with length " + contentLength :
				"Set BLOB to null");
	}
}
 

@Override
public void setBlobAsBytes(PreparedStatement ps, int paramIndex, byte[] content)
		throws SQLException {

	if (content != null) {
		Blob blob = ps.getConnection().createBlob();
		blob.setBytes(1, content);
		this.temporaryBlobs.add(blob);
		ps.setBlob(paramIndex, blob);
	}
	else {
		ps.setBlob(paramIndex, (Blob) null);
	}

	if (logger.isDebugEnabled()) {
		logger.debug(content != null ? "Copied bytes into temporary BLOB with length " + content.length :
				"Set BLOB to null");
	}
}
 

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

	Blob blob = ps.getConnection().createBlob();
	try {
		FileCopyUtils.copy(binaryStream, blob.setBinaryStream(1));
	}
	catch (IOException ex) {
		throw new DataAccessResourceFailureException("Could not copy into LOB stream", ex);
	}

	this.temporaryBlobs.add(blob);
	ps.setBlob(paramIndex, blob);

	if (logger.isDebugEnabled()) {
		logger.debug(binaryStream != null ?
				"Copied binary stream into temporary BLOB with length " + contentLength :
				"Set BLOB to null");
	}
}
 

@Override
public void setBlobAsBytes(PreparedStatement ps, int paramIndex, byte[] content)
		throws SQLException {

	if (streamAsLob) {
		if (content != null) {
			ps.setBlob(paramIndex, new ByteArrayInputStream(content), content.length);
		}
		else {
			ps.setBlob(paramIndex, (Blob) null);
		}
	}
	else if (wrapAsLob) {
		if (content != null) {
			ps.setBlob(paramIndex, new PassThroughBlob(content));
		}
		else {
			ps.setBlob(paramIndex, (Blob) null);
		}
	}
	else {
		ps.setBytes(paramIndex, content);
	}
	if (logger.isDebugEnabled()) {
		logger.debug(content != null ? "Set bytes for BLOB with length " + content.length :
				"Set BLOB to null");
	}
}
 

@Override
public void setBlob(final int parameterIndex, final Blob x)
		throws SQLException {
	ParameterCallback callback = new ParameterCallbackAction(
			parameterIndex, x) {

		@Override
		public void call(PreparedStatement preparedStatement)
				throws SQLException {
			preparedStatement.setBlob(parameterIndex(), x);
		}
	};
	addParameterCallback(callback);
}
 

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

	if (streamAsLob) {
		if (binaryStream != null) {
			ps.setBlob(paramIndex, binaryStream, contentLength);
		}
		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 {
		ps.setBinaryStream(paramIndex, binaryStream, contentLength);
	}
	if (logger.isDebugEnabled()) {
		logger.debug(binaryStream != null ? "Set binary stream for BLOB with length " + contentLength :
				"Set BLOB to null");
	}
}
 

@Test
public void assertSetBlob() throws SQLException, IOException {
    for (PreparedStatement each : preparedStatements) {
        try (InputStream inputStream = new ByteArrayInputStream(new byte[]{})) {
            each.setBlob(1, (Blob) null);
            each.setBlob(2, inputStream);
            each.setBlob(3, inputStream, 100L);
            assertParameter(each, 1, null);
            assertParameter(each, 2, inputStream);
            assertParameter(each, 3, inputStream);
        }
    }
}
 

@Test(expected = SQLException.class)
public void testMultiHostWithException() throws SQLException {
    when(mysqlPreparedStatement.executeQuery()).thenThrow(new SQLException());
    try {
        PreparedStatement preparedStatement = multiHostConnection.prepareStatement("SELECT * FROM test WHERE a = ? or b = ? or c=? or d = ? or e=?");
        preparedStatement.setBigDecimal(1, new BigDecimal(10000));
        preparedStatement.setBlob(2, inputStream);
        preparedStatement.setBlob(3, inputStream, 1000000L);
        preparedStatement.setByte(3, (byte) 1);
        preparedStatement.setBytes(4, new byte[] {
            1,
            2
        });
        preparedStatement.setLong(5, 100L);

        ResultSet resultSet = preparedStatement.executeQuery();

        preparedStatement.close();
    } finally {
        verify(mysqlPreparedStatement).executeQuery();
        verify(mysqlPreparedStatement, times(0)).close();
        verify(mysqlPreparedStatement).setBigDecimal(anyInt(), any(BigDecimal.class));
        verify(mysqlPreparedStatement).setBlob(anyInt(), any(InputStream.class));
        verify(mysqlPreparedStatement).setBlob(anyInt(), any(InputStream.class), anyLong());
        verify(mysqlPreparedStatement).setByte(anyInt(), anyByte());

        TraceSegment traceSegment = segmentStorage.getTraceSegments().get(0);
        List<AbstractTracingSpan> spans = SegmentHelper.getSpans(traceSegment);
        assertThat(spans.size(), is(1));
        assertDBSpan(spans.get(0), "Mysql/JDBI/PreparedStatement/executeQuery", "SELECT * FROM test WHERE a = ? or b = ? or c=? or d = ? or e=?");

        List<LogDataEntity> logData = SpanHelper.getLogs(spans.get(0));
        Assert.assertThat(logData.size(), is(1));
        assertThat(logData.size(), is(1));
        assertDBSpanLog(logData.get(0));
    }

}
 

@Override
public void setBlobAsBinaryStream(
		PreparedStatement ps, int paramIndex, 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");
	}
}
 

protected int insertToTable(PreparedStatement stmt, int cid, int sid, 
    int qty, BigDecimal sub, Blob data, int tid, boolean isPut) throws SQLException {
  String blob = null;
  if (data != null) {
    if (data.length() == 0) blob = "empty data";
    else if (useMD5Checksum) blob = ResultSetHelper.convertByteArrayToChecksum(data.getBytes(1, 
        (int)data.length()), data.length());
    else blob = ResultSetHelper.convertByteArrayToString(data.getBytes(1, 
        (int)data.length()));
  }
  
  Log.getLogWriter().info((isPut ? "putting" : "inserting") + " into table trade.portfoliov1 cid is " + cid +
      " sid is "+ sid + " qty is " + qty + " availQty is " + qty + " subTotal is " + sub
      + " data is " + blob);
  String driverName = stmt.getConnection().getMetaData().getDriverName();
  stmt.setInt(1, cid);
  stmt.setInt(2, sid);
  stmt.setInt(3, qty);
  stmt.setInt(4, qty); //availQty is the same as qty during insert
  stmt.setBigDecimal(5, sub);   
  stmt.setInt(6, tid);
  stmt.setBlob(7, data);
  int rowCount = stmt.executeUpdate();
  SQLWarning warning = stmt.getWarnings(); //test to see there is a warning
  if (warning != null) {
    SQLHelper.printSQLWarning(warning);
  } 

  //doing second put that may or may not successfull.
  if ( driverName.toLowerCase().contains("gemfirexd") && isPut) {
    Log.getLogWriter().info((isPut ? "putting" : "inserting") + " into table trade.portfoliov1 cid is " + cid +
        " sid is "+ sid + " qty is " + qty + " availQty is " + qty + " subTotal is " + sub + 
        " data is " + blob + " stmt " + stmt.toString());
    
   rowCount = stmt.executeUpdate();
   warning = stmt.getWarnings(); //test to see there is a warning   
   if (warning != null) {
     SQLHelper.printSQLWarning(warning);
   } 
  }
  return rowCount;
}
 

/**
 * Tests the BlobOutputStream.write(byte  b[], int off, int len) method
 **/
public void testBlobWrite3Param() throws Exception {
    InputStream streamIn = new LoopingAlphabetStream(streamSize[0]);
    assertTrue("FAIL -- file not found", streamIn != null);

    PreparedStatement stmt3 = prepareStatement(
        "SELECT b FROM testBlobX1 WHERE a = 1");
    ResultSet rs3 = stmt3.executeQuery();
    rs3.next();
    Blob blob = rs3.getBlob(1);

    assertTrue ("FAIL -- blob is NULL", (blob != null));

    int count = 0;
    byte[] buffer = new byte[1024];
    OutputStream outstream = blob.setBinaryStream(1L);
    while ((count = streamIn.read(buffer)) != -1) {
        outstream.write(buffer, 0, count);
    }
    outstream.close();
    streamIn.close();

    PreparedStatement stmt4 = prepareStatement(
        "UPDATE testBlobX1 SET b = ? WHERE a = 1");
    stmt4.setBlob(1,  blob);
    stmt4.executeUpdate();
    stmt4.close();
    rs3.close();
    // GemStone changes BEGIN
    getConnection().commit();
    // GemStone changes END

    rs3 = stmt3.executeQuery();
    assertTrue("FAIL -- blob not found", rs3.next());

    long new_length = rs3.getBlob(1).length();
    assertEquals("FAIL -- wrong blob length;",
            streamSize[0], new_length);

    // Check contents ...
    InputStream fStream = new LoopingAlphabetStream(streamSize[0]);
    InputStream lStream = rs3.getBlob(1).getBinaryStream();
    assertTrue("FAIL - Blob and file contents do not match",
            compareLob2File(fStream, lStream));

    fStream.close();
    lStream.close();
    rs3.close();
    stmt3.close();
}
 

protected int insertToTable(PreparedStatement stmt, int cid, int sid, 
    int qty, BigDecimal sub, Blob data, int tid, boolean isPut) throws SQLException {
  String blob = null;
  if (data != null) {
    if (data.length() == 0) blob = "empty data";
    else if (useMD5Checksum) blob = ResultSetHelper.convertByteArrayToChecksum(data.getBytes(1, 
        (int)data.length()), data.length());
    else blob = ResultSetHelper.convertByteArrayToString(data.getBytes(1, 
        (int)data.length()));
  }
  
  Log.getLogWriter().info((isPut ? "putting" : "inserting") + " into table trade.portfoliov1 cid is " + cid +
      " sid is "+ sid + " qty is " + qty + " availQty is " + qty + " subTotal is " + sub
      + " data is " + blob);
  String driverName = stmt.getConnection().getMetaData().getDriverName();
  stmt.setInt(1, cid);
  stmt.setInt(2, sid);
  stmt.setInt(3, qty);
  stmt.setInt(4, qty); //availQty is the same as qty during insert
  stmt.setBigDecimal(5, sub);   
  stmt.setInt(6, tid);
  stmt.setBlob(7, data);
  int rowCount = stmt.executeUpdate();
  SQLWarning warning = stmt.getWarnings(); //test to see there is a warning
  if (warning != null) {
    SQLHelper.printSQLWarning(warning);
  } 

  //doing second put that may or may not successfull.
  if ( driverName.toLowerCase().contains("gemfirexd") && isPut) {
    Log.getLogWriter().info((isPut ? "putting" : "inserting") + " into table trade.portfoliov1 cid is " + cid +
        " sid is "+ sid + " qty is " + qty + " availQty is " + qty + " subTotal is " + sub + 
        " data is " + blob + " stmt " + stmt.toString());
    
   rowCount = stmt.executeUpdate();
   warning = stmt.getWarnings(); //test to see there is a warning   
   if (warning != null) {
     SQLHelper.printSQLWarning(warning);
   } 
  }
  return rowCount;
}
 

/**
 * tests set bytes method of blob in memory only mode (less than 4k)
 */
public void testSetBytesSmallBlob() 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 (?,?)");
  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");
  stmt.close();
}
 

/** Assigns a value to a dynamic parameter in a prepared statement, calling
 * the appropriate {@code setXxx} method based on the type of the value. */
private static void setDynamicParam(PreparedStatement preparedStatement,
    int i, Object value) throws SQLException {
  if (value == null) {
    preparedStatement.setObject(i, null, SqlType.ANY.id);
  } else if (value instanceof Timestamp) {
    preparedStatement.setTimestamp(i, (Timestamp) value);
  } else if (value instanceof Time) {
    preparedStatement.setTime(i, (Time) value);
  } else if (value instanceof String) {
    preparedStatement.setString(i, (String) value);
  } else if (value instanceof Integer) {
    preparedStatement.setInt(i, (Integer) value);
  } else if (value instanceof Double) {
    preparedStatement.setDouble(i, (Double) value);
  } else if (value instanceof java.sql.Array) {
    preparedStatement.setArray(i, (java.sql.Array) value);
  } else if (value instanceof BigDecimal) {
    preparedStatement.setBigDecimal(i, (BigDecimal) value);
  } else if (value instanceof Boolean) {
    preparedStatement.setBoolean(i, (Boolean) value);
  } else if (value instanceof Blob) {
    preparedStatement.setBlob(i, (Blob) value);
  } else if (value instanceof Byte) {
    preparedStatement.setByte(i, (Byte) value);
  } else if (value instanceof NClob) {
    preparedStatement.setNClob(i, (NClob) value);
  } else if (value instanceof Clob) {
    preparedStatement.setClob(i, (Clob) value);
  } else if (value instanceof byte[]) {
    preparedStatement.setBytes(i, (byte[]) value);
  } else if (value instanceof Date) {
    preparedStatement.setDate(i, (Date) value);
  } else if (value instanceof Float) {
    preparedStatement.setFloat(i, (Float) value);
  } else if (value instanceof Long) {
    preparedStatement.setLong(i, (Long) value);
  } else if (value instanceof Ref) {
    preparedStatement.setRef(i, (Ref) value);
  } else if (value instanceof RowId) {
    preparedStatement.setRowId(i, (RowId) value);
  } else if (value instanceof Short) {
    preparedStatement.setShort(i, (Short) value);
  } else if (value instanceof URL) {
    preparedStatement.setURL(i, (URL) value);
  } else if (value instanceof SQLXML) {
    preparedStatement.setSQLXML(i, (SQLXML) value);
  } else {
    preparedStatement.setObject(i, value);
  }
}
 

/**
 * TABLE XML_DOC_1
 *   XML_DOC_ID_NBR DECIMAL(19) NOT NULL,
 *   STRUCTURE_ID_NBR DECIMAL(22) NOT NULL,
 *   CREATE_MINT_CD CHAR(1) NOT NULL,
 *   MSG_PAYLOAD_QTY DECIMAL(22) NOT NULL,
 *   MSG_PAYLOAD1_IMG BLOB(2000) NOT NULL,
 *   MSG_PAYLOAD2_IMG BLOB(2000),
 *   MSG_PAYLOAD_SIZE_NBR DECIMAL(22),
 *   MSG_PURGE_DT DATE,
 *   DELETED_FLG CHAR(1) NOT NULL,
 *   LAST_UPDATE_SYSTEM_NM VARCHAR(30),
 *   LAST_UPDATE_TMSTP TIMESTAMP NOT NULL,
 *   MSG_MAJOR_VERSION_NBR DECIMAL(22),
 *   MSG_MINOR_VERSION_NBR DECIMAL(22),
 *   OPT_LOCK_TOKEN_NBR DECIMAL(22) DEFAULT 1,
 *   PRESET_DICTIONARY_ID_NBR DECIMAL(22) DEFAULT 0 NOT NULL
 */
private void useCase2LoadData() throws SQLException {
  long currentTime = System.currentTimeMillis();
  Date date = new Date(currentTime);
  Timestamp timestamp = new Timestamp(currentTime);
  SerialBlob blob = new SerialBlob("12345678452984560289456029847609487234785012934857109348156034650234560897628900985760289207856027895602785608560786085602857602985760206106110476191087345601456105610478568347562686289765927868972691785634975604562056104762978679451308956205620437861508561034756028475180756917856190348756012876510871789546913485620720476107856479238579385923847934".getBytes(Charset.forName("UTF-8")));

  String stmt = "INSERT INTO XML_DOC_1 ("
              + " LAST_UPDATE_SYSTEM_NM,"    //  1
              + " XML_DOC_ID_NBR,"           //  2
              + " MSG_PAYLOAD1_IMG,"         //  3
              + " MSG_PAYLOAD_QTY,"          //  4
              + " MSG_MINOR_VERSION_NBR,"    //  5
              + " PRESET_DICTIONARY_ID_NBR," //  6
              + " CREATE_MINT_CD,"           //  7
              + " MSG_PAYLOAD_SIZE_NBR,"     //  8
              + " DELETED_FLG,"              //  9
              + " STRUCTURE_ID_NBR,"         // 10
              + " MSG_PURGE_DT,"             // 11
              + " OPT_LOCK_TOKEN_NBR,"       // 12
              + " MSG_MAJOR_VERSION_NBR,"    // 13
              + " LAST_UPDATE_TMSTP"         // 14
              + ") VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
  PreparedStatement pstmt = this.connection.prepareStatement(stmt);

  int numBatches = POCPrms.getNumBatches();
  int batchSize = POCPrms.getBatchSize();

  Log.getLogWriter().info("Generating " + numBatches + " batches of " + batchSize);
  for (int i = 0; i < numBatches; i++) {
    for (int j = 0; j < batchSize; j++) {
      pstmt.setString(1, USECASE2_APP);
      pstmt.setInt(2, i * batchSize + j);
      pstmt.setBlob(3, blob);
      pstmt.setBigDecimal(4, BIG_DECIMAL_1);
      pstmt.setBigDecimal(5, BIG_DECIMAL_1);
      pstmt.setBigDecimal(6, BIG_DECIMAL_1);
      pstmt.setString(7, N_FLAG);
      pstmt.setBigDecimal(8, BIG_DECIMAL_1);
      pstmt.setString(9, N_FLAG);
      pstmt.setBigDecimal(10, BIG_DECIMAL_9);
      pstmt.setDate(11, date);
      pstmt.setBigDecimal(12, BIG_DECIMAL_1);
      pstmt.setBigDecimal(13, BIG_DECIMAL_1);
      pstmt.setTimestamp(14, timestamp);
      pstmt.addBatch();
    }
    pstmt.executeBatch();
    pstmt.clearBatch();
  }
  pstmt.close();
}
 

/**
 * Tests large blob (more than 4k) to ensure LOBStreamControl uses file.
 */
public void testSetBytesLargeBlob() 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 (?,?)");
  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();
}
 

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

private void setParameter(PreparedStatement preparedStatement, Object[] args) throws
    SQLException {
  if (args == null || args.length == 0) {
    return;
  }
  for (int i = 0; i < args.length; i++) {
    Object arg = args[i];
    if (TypeUtil.isInt(arg)) {
      preparedStatement.setInt(i + 1, (Integer) arg);
    } else if (TypeUtil.isString(arg)) {
      preparedStatement.setString(i + 1, (String) arg);
    } else if (TypeUtil.isLong(arg)) {
      preparedStatement.setLong(i + 1, (Long) arg);
    } else if (TypeUtil.isDouble(arg)) {
      preparedStatement.setDouble(i + 1, (Double) arg);
    } else if (TypeUtil.isFloat(arg)) {
      preparedStatement.setFloat(i + 1, (Float) arg);
    } else if (TypeUtil.isBoolean(arg)) {
      preparedStatement.setBoolean(i + 1, (Boolean) arg);
    } else if (TypeUtil.isByte(arg)) {
      preparedStatement.setByte(i + 1, (Byte) arg);
    } else if (TypeUtil.isDate(arg)) {
      preparedStatement.setDate(i + 1, (Date) arg);
    } else if (TypeUtil.isShort(arg)) {
      preparedStatement.setShort(i + 1, (Short) arg);
    } else if (TypeUtil.isArray(arg)) {
      preparedStatement.setArray(i + 1, (Array) arg);
    } else if (TypeUtil.isInputStream(arg)) {
      preparedStatement.setAsciiStream(i + 1, (InputStream) arg);
    } else if (TypeUtil.isBigDecimal(arg)) {
      preparedStatement.setBigDecimal(i + 1, (BigDecimal) arg);
    } else if (TypeUtil.isBlob(arg)) {
      preparedStatement.setBlob(i + 1, (Blob) arg);
    } else if (TypeUtil.isBytes(arg)) {
      preparedStatement.setBytes(i + 1, (byte[]) arg);
    } else if (TypeUtil.isClob(arg)) {
      preparedStatement.setClob(i + 1, (Clob) arg);
    } else if (TypeUtil.isNClob(arg)) {
      preparedStatement.setNClob(i + 1, (NClob) arg);
    } else {
      throw new IllegalArgumentException(
          "Type:" + arg.getClass().getName() + " is not supported");
    }
  }
}
 

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