Java Code Examples for java.sql.Connection#createBlob()

@Override
public void changePassword(String user, HashedPassword password, Connection con) throws SQLException {
    OptionalInt authId = retrieveIdFromTable(user, con);
    if (authId.isPresent()) {
        final int id = authId.getAsInt();
        // Insert password in the correct table
        String sql = "UPDATE " + xfPrefix + "user_authenticate SET data=? WHERE " + col.ID + "=?;";
        try (PreparedStatement pst = con.prepareStatement(sql)) {
            String serializedHash = XfBCrypt.serializeHash(password.getHash());
            byte[] bytes = serializedHash.getBytes();
            Blob blob = con.createBlob();
            blob.setBytes(1, bytes);
            pst.setBlob(1, blob);
            pst.setInt(2, id);
            pst.executeUpdate();
        }

        // ...
        sql = "UPDATE " + xfPrefix + "user_authenticate SET scheme_class=? WHERE " + col.ID + "=?;";
        try (PreparedStatement pst = con.prepareStatement(sql)) {
            pst.setString(1, XfBCrypt.SCHEME_CLASS);
            pst.setInt(2, id);
            pst.executeUpdate();
        }
    }
}
 

/**
 * Tests fix for Bug#56122 - JDBC4 functionality failure when using replication connections.
 */
public void testBug56122() throws Exception {
    for (final Connection testConn : new Connection[] { this.conn, getFailoverConnection(), getLoadBalancedConnection(),
            getMasterSlaveReplicationConnection() }) {
        testConn.createClob();
        testConn.createBlob();
        testConn.createNClob();
        testConn.createSQLXML();
        testConn.isValid(12345);
        testConn.setClientInfo(new Properties());
        testConn.setClientInfo("NAME", "VALUE");
        testConn.getClientInfo();
        testConn.getClientInfo("CLIENT");
        assertThrows(SQLFeatureNotSupportedException.class, new Callable<Void>() {
            public Void call() throws Exception {
                testConn.createArrayOf("A_TYPE", null);
                return null;
            }
        });
        assertThrows(SQLFeatureNotSupportedException.class, new Callable<Void>() {
            public Void call() throws Exception {
                testConn.createStruct("A_TYPE", null);
                return null;
            }
        });
    }
}
 

/**
 * Load only one record
 */
public static void loadAllTypesData(Connection conn, String tablename) throws SQLException {
    try (PreparedStatement statement = conn
            .prepareStatement("insert into " + tablename + " values(?,?,?,?,?,?,?,?,?,?,?,?,?,?)")) {
        statement.setShort(1, (short) 32767);
        statement.setInt(2, 2147483647);
        statement.setLong(3, 9223372036854775807l);
        statement.setFloat(4, 1.11111111f);
        statement.setDouble(5, 2.222222222);
        statement.setBigDecimal(6, new BigDecimal("1234567890.1234567890"));
        statement.setString(7, "abcd");
        statement.setString(8, "abcdefg");

        Blob blob = conn.createBlob();
        byte[] bytes = { 0, 1, 2, 3, 4, 5, 6, 7, 8, 9 };
        blob.setBytes(1, bytes);
        statement.setBlob(9, blob);

        Clob clob = conn.createClob();
        clob.setString(1, "abcdefg");
        statement.setClob(10, clob);

        statement.setDate(11, Date.valueOf("2016-12-28"));
        statement.setTime(12, Time.valueOf("14:30:33"));
        statement.setTimestamp(13, Timestamp.valueOf("2016-12-28 14:31:56.12345"));
        statement.setBoolean(14, true);

        statement.executeUpdate();
    }

    if (!conn.getAutoCommit()) {
        conn.commit();
    }
}
 

@Override
public Blob executeOnConnection(Connection connection) throws SQLException {
	return connection.createBlob();
}
 

/**
 * tests set bytes method of blob in memory only mode (less than 4k)
 */
public void testSetBytesSmallBlob () throws SQLException {
    Connection con = getConnection();
    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();
    Statement 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();
}
 

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

/**
 * Updates the xenforo tables after a player auth has been saved.
 *
 * @param auth the player auth which was saved
 * @param id the account id
 * @param con connection to the database
 */
private void updateXenforoTablesOnSave(PlayerAuth auth, int id, Connection con) throws SQLException {
    // Insert player password, salt in xf_user_authenticate
    String sql = "INSERT INTO " + xfPrefix + "user_authenticate (user_id, scheme_class, data) VALUES (?,?,?)";
    try (PreparedStatement pst = con.prepareStatement(sql)) {
        pst.setInt(1, id);
        pst.setString(2, XfBCrypt.SCHEME_CLASS);
        String serializedHash = XfBCrypt.serializeHash(auth.getPassword().getHash());
        byte[] bytes = serializedHash.getBytes();
        Blob blob = con.createBlob();
        blob.setBytes(1, bytes);
        pst.setBlob(3, blob);
        pst.executeUpdate();
    }
    // Update player group in xf_users
    sql = "UPDATE " + tableName + " SET " + tableName + ".user_group_id=? WHERE " + col.NAME + "=?;";
    try (PreparedStatement pst = con.prepareStatement(sql)) {
        pst.setInt(1, xfGroup);
        pst.setString(2, auth.getNickname());
        pst.executeUpdate();
    }
    // Update player permission combination in xf_users
    sql = "UPDATE " + tableName + " SET " + tableName + ".permission_combination_id=? WHERE " + col.NAME + "=?;";
    try (PreparedStatement pst = con.prepareStatement(sql)) {
        pst.setInt(1, xfGroup);
        pst.setString(2, auth.getNickname());
        pst.executeUpdate();
    }
    // Insert player privacy combination in xf_user_privacy
    sql = "INSERT INTO " + xfPrefix + "user_privacy (user_id, allow_view_profile, allow_post_profile, "
        + "allow_send_personal_conversation, allow_view_identities, allow_receive_news_feed) VALUES (?,?,?,?,?,?)";
    try (PreparedStatement pst = con.prepareStatement(sql)) {
        pst.setInt(1, id);
        pst.setString(2, "everyone");
        pst.setString(3, "members");
        pst.setString(4, "members");
        pst.setString(5, "everyone");
        pst.setString(6, "everyone");
        pst.executeUpdate();
    }
    // Insert player group relation in xf_user_group_relation
    sql = "INSERT INTO " + xfPrefix + "user_group_relation (user_id, user_group_id, is_primary) VALUES (?,?,?)";
    try (PreparedStatement pst = con.prepareStatement(sql)) {
        pst.setInt(1, id);
        pst.setInt(2, xfGroup);
        pst.setString(3, "1");
        pst.executeUpdate();
    }
}
 

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

/**
 * Test the createBlob method implementation in the Connection interface
 *
 * @exception  SQLException, FileNotFoundException, Exception if error occurs
 */
public void testCreateBlob() throws   SQLException,
        FileNotFoundException,
        IOException,
        Exception{

    Connection conn = getConnection();
    int b, c;
    Blob blob;

    Statement s = createStatement();
    PreparedStatement ps =
            prepareStatement("insert into blobtable2 (n, blobcol)" + " values(?,?)");
    ps.setInt(1,1000);
    blob = conn.createBlob();

    try {
        is = (FileInputStream) AccessController.doPrivileged(
                new PrivilegedExceptionAction() {
            public Object run() throws FileNotFoundException {
                return new FileInputStream("extin/short.txt");
            }
        });
    } catch (PrivilegedActionException e) {
        // e.getException() should be an instance of FileNotFoundException,
        // as only "checked" exceptions will be "wrapped" in a
        // PrivilegedActionException.
        throw (FileNotFoundException) e.getException();
    }

    OutputStream os = blob.setBinaryStream(1);
    ArrayList beforeUpdateList = new ArrayList();

    int actualLength = 0;
    c = is.read();
    while(c>0) {
        os.write(c);
        beforeUpdateList.add(c);
        c = is.read();
        actualLength ++;
    }
    ps.setBlob(2, blob);
    ps.executeUpdate();

    Statement stmt = createStatement();
    ResultSet rs =
            stmt.executeQuery("select blobcol from blobtable2 where n = 1000");
    assertTrue(rs.next());

    blob = rs.getBlob(1);
    assertEquals(beforeUpdateList.size(), blob.length());

    //Get the InputStream from this Blob.
    InputStream in = blob.getBinaryStream();
    ArrayList afterUpdateList = new ArrayList();

    b = in.read();

    while (b > -1) {
        afterUpdateList.add(b);
        b = in.read();
    }

    assertEquals(beforeUpdateList.size(), afterUpdateList.size());

    //Now check if the two InputStreams
    //match
    for (int i = 0; i < blob.length(); i++) {
        assertEquals(beforeUpdateList.get(i), afterUpdateList.get(i));
    }

    os.close();
    is.close();
}
 

protected void setUp() throws Exception {
    Connection conn = getConnection();
    blob = conn.createBlob();
    in = blob.getBinaryStream();
    out = blob.setBinaryStream (1);
}
 

/**
 * Tests large blob (more than 4k) to ensure LOBStreamControl uses file.
 */
public void testSetBytesLargeBlob () throws SQLException {
    Connection con = getConnection();
    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 ();
    Statement 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();
}
 

/**
 * Test the createBlob method implementation in the Connection interface
 *
 * @exception  SQLException, FileNotFoundException, Exception if error occurs
 */
public void testCreateBlob() throws   SQLException,
        FileNotFoundException,
        IOException,
        Exception{

    Connection conn = getConnection();
    int b, c;
    Blob blob;

    Statement s = createStatement();
    PreparedStatement ps =
            prepareStatement("insert into blobtable2 (n, blobcol)" + " values(?,?)");
    ps.setInt(1,1000);
    blob = conn.createBlob();

    try {
        is = AccessController.doPrivileged(
                new PrivilegedExceptionAction<FileInputStream>() {
            public FileInputStream run() throws FileNotFoundException {
                return new FileInputStream("extin/short.txt");
            }
        });
    } catch (PrivilegedActionException e) {
        // e.getException() should be an instance of FileNotFoundException,
        // as only "checked" exceptions will be "wrapped" in a
        // PrivilegedActionException.
        throw (FileNotFoundException) e.getException();
    }

    OutputStream os = blob.setBinaryStream(1);
    ArrayList<Integer> beforeUpdateList = new ArrayList<Integer>();

    int actualLength = 0;
    c = is.read();
    while(c>0) {
        os.write(c);
        beforeUpdateList.add(c);
        c = is.read();
        actualLength ++;
    }
    ps.setBlob(2, blob);
    ps.executeUpdate();

    Statement stmt = createStatement();
    ResultSet rs =
            stmt.executeQuery("select blobcol from blobtable2 where n = 1000");
    assertTrue(rs.next());

    blob = rs.getBlob(1);
    assertEquals(beforeUpdateList.size(), blob.length());

    //Get the InputStream from this Blob.
    InputStream in = blob.getBinaryStream();
    ArrayList<Integer> afterUpdateList = new ArrayList<Integer>();

    b = in.read();

    while (b > -1) {
        afterUpdateList.add(b);
        b = in.read();
    }

    assertEquals(beforeUpdateList.size(), afterUpdateList.size());

    //Now check if the two InputStreams
    //match
    for (int i = 0; i < blob.length(); i++) {
        assertEquals(beforeUpdateList.get(i), afterUpdateList.get(i));
    }

    os.close();
    is.close();
}
 

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

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

/**
 * Test the createBlob method implementation in the Connection interface
 *
 * @exception  SQLException, FileNotFoundException, Exception if error occurs
 */
public void testCreateBlob() throws   SQLException,
        FileNotFoundException,
        IOException,
        Exception{

    Connection conn = getConnection();
    int b, c;
    Blob blob;

    Statement s = createStatement();
    PreparedStatement ps =
            prepareStatement("insert into blobtable2 (n, blobcol)" + " values(?,?)");
    ps.setInt(1,1000);
    blob = conn.createBlob();

    try {
        is = (FileInputStream) AccessController.doPrivileged(
                new PrivilegedExceptionAction() {
            public Object run() throws FileNotFoundException {
                return new FileInputStream("extin/short.txt");
            }
        });
    } catch (PrivilegedActionException e) {
        // e.getException() should be an instance of FileNotFoundException,
        // as only "checked" exceptions will be "wrapped" in a
        // PrivilegedActionException.
        throw (FileNotFoundException) e.getException();
    }

    OutputStream os = blob.setBinaryStream(1);
    ArrayList beforeUpdateList = new ArrayList();

    int actualLength = 0;
    c = is.read();
    while(c>0) {
        os.write(c);
        beforeUpdateList.add(c);
        c = is.read();
        actualLength ++;
    }
    ps.setBlob(2, blob);
    ps.executeUpdate();

    Statement stmt = createStatement();
    ResultSet rs =
            stmt.executeQuery("select blobcol from blobtable2 where n = 1000");
    assertTrue(rs.next());

    blob = rs.getBlob(1);
    assertEquals(beforeUpdateList.size(), blob.length());

    //Get the InputStream from this Blob.
    InputStream in = blob.getBinaryStream();
    ArrayList afterUpdateList = new ArrayList();

    b = in.read();

    while (b > -1) {
        afterUpdateList.add(b);
        b = in.read();
    }

    assertEquals(beforeUpdateList.size(), afterUpdateList.size());

    //Now check if the two InputStreams
    //match
    for (int i = 0; i < blob.length(); i++) {
        assertEquals(beforeUpdateList.get(i), afterUpdateList.get(i));
    }

    os.close();
    is.close();
}
 

protected void setUp() throws Exception {
    Connection conn = getConnection();
    blob = conn.createBlob();
    in = blob.getBinaryStream();
    out = blob.setBinaryStream (1);
}
 

/**
 * Tests large blob (more than 4k) to ensure LOBStreamControl uses file.
 */
public void testSetBytesLargeBlob () throws SQLException {
    Connection con = getConnection();
    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 ();
    Statement 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 = getConnection();
    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();
    Statement 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();
}