Java Code Examples for java.sql.ResultSet#updateClob()

public void testUpdateClobLengthlessParameterName()
        throws Exception {
    Reader r1 = new java.io.StringReader(new String(BYTES1));
    // InputStream for insertion.
    Reader r2 = new java.io.StringReader(new String(BYTES2));

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

    // Update operation
    ResultSet rs1 = fetchUpd("dClob", key);
    rs1.next();
    rs1.updateClob("dClob", r2);
    rs1.updateRow();
    rs1.close();

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

/**
 * Tests the updateClob that accepts a input stream and the length of the IS
 * and the parameter name String.
 *
 * @throws an Exception
 */
public void testUpdateClobStringParameterNameWithLengthofIS()
        throws Exception {
    Reader r1 = new java.io.StringReader(str1);
    // InputStream for insertion.
    Reader r2 = new java.io.StringReader(str2);

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

    // Update operation
    ResultSet rs1 = fetchUpd("dClob", key);
    rs1.next();
    rs1.updateClob("dClob", r2, str2.length());
    rs1.updateRow();
    rs1.close();

    // Query to see whether the data that has been updated.
    rs1 = fetch("dClob", key);
    rs1.next();
    assertEquals(new StringReader(str2),
                 rs1.getCharacterStream(1));
    rs1.close();
}
 

public void testUpdateClobLengthless()
        throws Exception {
    // Life span of Clob objects are limited by the transaction.  Need
    // autocommit off so Clob objects survive execution of next statement.
    getConnection().setAutoCommit(false);

    Reader r1 = new java.io.StringReader(new String(BYTES1));
    // InputStream for insertion.
    Reader r2 = new java.io.StringReader(new String(BYTES2));

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

    // Update operation
    ResultSet rs1 = fetchUpd("dClob", key);
    rs1.next();
    rs1.updateClob(1, r2);
    rs1.updateRow();
    rs1.close();

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

/**
 * Test the Clob method that accepts a Input Stream and its length
 * as input parameter.
 *
 * @throws Exception
 */
public void testUpdateClobwithLengthofIS()
        throws Exception {
    Reader r1 = new java.io.StringReader(str1);
    // InputStream for insertion.
    Reader r2 = new java.io.StringReader(str2);

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

    // Update operation
    ResultSet rs1 = fetchUpd("dClob", key);
    rs1.next();
    rs1.updateClob(1, r2, str2.length());
    rs1.updateRow();
    rs1.close();

    // Query to see whether the data that has been updated.
    rs1 = fetch("dClob", key);
    rs1.next();
    assertEquals(new StringReader(str2),
                 rs1.getCharacterStream(1));
    rs1.close();
}
 

public void testUpdateClobLengthlessParameterName()
        throws Exception {
    Reader r1 = new java.io.StringReader(new String(BYTES1));
    // InputStream for insertion.
    Reader r2 = new java.io.StringReader(new String(BYTES2));

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

    // Update operation
    ResultSet rs1 = fetchUpd("dClob", key);
    rs1.next();
    rs1.updateClob("dClob", r2);
    rs1.updateRow();
    rs1.close();

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

public void testUpdateClobLengthless()
        throws Exception {
    // Life span of Clob objects are limited by the transaction.  Need
    // autocommit off so Clob objects survive execution of next statement.
    getConnection().setAutoCommit(false);

    Reader r1 = new java.io.StringReader(new String(BYTES1));
    // InputStream for insertion.
    Reader r2 = new java.io.StringReader(new String(BYTES2));

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

    // Update operation
    ResultSet rs1 = fetchUpd("dClob", key);
    rs1.next();
    rs1.updateClob(1, r2);
    rs1.updateRow();
    rs1.close();

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

/**
 * Tests the updateClob that accepts a input stream and the length of the IS
 * and the parameter name String.
 *
 * @throws an Exception
 */
public void testUpdateClobStringParameterNameWithLengthofIS()
        throws Exception {
    Reader r1 = new java.io.StringReader(str1);
    // InputStream for insertion.
    Reader r2 = new java.io.StringReader(str2);

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

    // Update operation
    ResultSet rs1 = fetchUpd("dClob", key);
    rs1.next();
    rs1.updateClob("dClob", r2, str2.length());
    rs1.updateRow();
    rs1.close();

    // Query to see whether the data that has been updated.
    rs1 = fetch("dClob", key);
    rs1.next();
    assertEquals(new StringReader(str2),
                 rs1.getCharacterStream(1));
    rs1.close();
}
 

/**
 * This methods tests the ResultSet interface method
 * updateClob
 *
 * @throws SQLException if some error occurs while calling the method
 */
public void testUpdateClob()
throws Exception {
    // Life span of Clob objects are limited by the transaction.  Need
    // autocommit off so Clob 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("dClob");

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

    //second insert
    int key2 = requestKey();
    ps_sb.setInt(1,key2);
    ps_sb.setAsciiStream(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 = fetchUpd("dClob", key);
    rs1.next();
    Clob clob = rs1.getClob(1);
    rs1.close();

    rs1 = fetchUpd("dClob", key2);
    rs1.next();
    rs1.updateClob(1,clob);
    rs1.updateRow();
    rs1.close();

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

    rs1 = fetch("dClob", key2);
    rs1.next();
    assertEquals(clob, rs1.getClob(1));
    rs1.close();
}
 

@Test(expected = SQLFeatureNotSupportedException.class)
public void assertUpdateClobForColumnIndex() throws SQLException {
    for (ResultSet each : resultSets) {
        each.updateClob(1, (Clob) null);
    }
}
 

@Test(expected = SQLFeatureNotSupportedException.class)
public void assertUpdateClobForColumnLabelWithInputStreamAndLength() throws SQLException {
    for (ResultSet each : resultSets) {
        each.updateClob("label", new StringReader(""), 100);
    }
}
 

private void updateClob2(String testId,
        PreparedStatement sel,
        int cloblen, int id, int updateRowId, int updateIdVal, String file)
        throws Exception {
    println("========================================");
    println("START " + testId
            + " - select and then update clob of size= "
            + cloblen + " - Uses updateClob api");

    PreparedStatement ps1 =
                    prepareStatement("SELECT * FROM CLOBTBL FOR UPDATE",
                            ResultSet.TYPE_FORWARD_ONLY,
                            ResultSet.CONCUR_UPDATABLE);
    PreparedStatement ps =
            prepareStatement("SELECT CONTENT,DLEN FROM CLOBTBL2 " +
                    "where ID =?");

    ps.setInt(1, id);
    // retrieve row from clobtbl2
    ResultSet rs = ps.executeQuery();
    rs.next();
    Clob value = rs.getClob(1);
    long l = value.length();
    long dlen = rs.getLong(2);
    if (dlen != l) {
        println("FAIL - MISMATCH LENGTHS GOT " + l + " expected "
                        + dlen + " for row in CLOBTBL2 with ID=" + id);
    }

    ResultSet rs1 = ps1.executeQuery();
    while (rs1.next()) {
        if (rs1.getInt(1) == updateRowId) {
            rs1.updateClob(4, value);
            rs1.updateInt(1, updateIdVal);
            rs1.updateInt(2, 0);
            rs1.updateLong(3, dlen);
            rs1.updateRow();
            break;
        }
    }

    commit();

    // close resultsets
    rs.close();
    rs1.close();

    // verify
    // now select and verify that update went through ok.
    sel.setInt(1, updateIdVal);
    ResultSet rs2 = sel.executeQuery();
    rs2.next();
    Clob updatedValue = rs2.getClob(1);
    assertEquals("FAIL - MISMATCH length of updated clob value ," +
            "found=" + 
            updatedValue.length() + ",expected = " + l,
            l, updatedValue.length());
    compareClobToFile(updatedValue.getCharacterStream(), file, (int) l);

    if (updatedValue.length() != l) {
        println("FAIL - MISMATCH length of updated clob value ," +
                        "found="
                        +
                        updatedValue.length() + ",expected = " + l);
    } else
        compareClobToFile(updatedValue.getCharacterStream(), file, (int) l);

    println("========================================");

}
 

@Test(expected = SQLFeatureNotSupportedException.class)
public void assertUpdateClobForColumnLabelWithInputStream() throws SQLException {
    for (ResultSet each : resultSets) {
        each.updateClob("label", new StringReader(""));
    }
}
 

@Test(expected = SQLFeatureNotSupportedException.class)
public void assertUpdateClobForColumnIndexWithInputStream() throws SQLException {
    for (ResultSet each : resultSets) {
        each.updateClob(1, new StringReader(""));
    }
}
 

/**
 * Tests a sequence of operations on a scrollable, updatable resultset.
 *
 * @throws SQLException if the test fails
 */
public void testScrollableUpdateWithLocators()
        throws SQLException {
    getConnection().setAutoCommit(false);
    Statement stmt = createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
                                     ResultSet.CONCUR_UPDATABLE);
    ResultSet rs = stmt.executeQuery(
            "select dBlob, dClob from LOBLOC_NO_NULLS");
    rs.absolute(3);
    Clob c1 = rs.getClob(2);
    final int origLength = (int)c1.length();
    final String origContent = c1.getSubString(1, origLength);
    // Do a change
    c1.setString(origLength, "FIRSTPASS");
    rs.absolute(7);
    rs.next();
    // Move back to row 3
    rs.absolute(3);
    Clob c2 = rs.getClob(2);
    assertEquals(origContent, c2.getSubString(1, (int)c2.length()));
    rs.updateRow(); // Should be a no-op
    rs.absolute(3);
    // Expect this to fail if the restriction that LOB columns cannot be
    // accessed more than once is enforced.
    Clob c3 = rs.getClob(2);
    assertEquals(origContent, c3.getSubString(1, (int)c3.length()));
    rs.previous();
    rs.next();
    Clob c4 = rs.getClob(2);
    final String newContent = "THIS IS THE NEW VALUE!";
    c4.setString(1, newContent);
    rs.updateClob(2, c4);
    rs.updateRow();
    c4.setString(1, "THIS IS NOT NOT NOT THE NEW VALUE!");
    rs.updateRow();
    rs.next();
    rs.absolute(3);
    Clob c5 = rs.getClob(2);
    assertEquals(newContent, c5.getSubString(1, (int)c5.length()));
    rollback();
    assertInvalid(c1);
    assertInvalid(c2);
    assertInvalid(c3);
    assertInvalid(c4);
    assertInvalid(c5);
}
 

@Test(expected = SQLFeatureNotSupportedException.class)
public void assertUpdateClobForColumnIndex() throws SQLException {
    for (ResultSet each : resultSets) {
        each.updateClob(1, (Clob) null);
    }
}
 

/**
 * This methods tests the ResultSet interface method
 * updateClob
 *
 * @throws SQLException if some error occurs while calling the method
 */
public void testUpdateClobStringParameterName()
throws Exception {
    // Life span of Clob objects are limited by the transaction.  Need
    // autocommit off so Clob 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("dClob");

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

    //second insert
    int key2 = requestKey();
    ps_sb.setInt(1, key2);
    ps_sb.setAsciiStream(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("dClob", key);
    rs1.next();
    Clob clob = rs1.getClob(1);
    rs1.close();

    rs1 = fetchUpd("dClob", key2);
    rs1.next();
    rs1.updateClob("dClob",clob);
    rs1.updateRow();
    rs1.close();

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

    rs1 = fetch("dClob", key2);
    rs1.next();
    assertEquals(clob, rs1.getClob(1));
    rs1.close();
}
 

/**
 * This methods tests the ResultSet interface method
 * updateClob
 *
 * @throws SQLException if some error occurs while calling the method
 */
public void testUpdateClob()
throws Exception {
    // Life span of Clob objects are limited by the transaction.  Need
    // autocommit off so Clob 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("dClob");

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

    //second insert
    int key2 = requestKey();
    ps_sb.setInt(1,key2);
    ps_sb.setAsciiStream(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 = fetchUpd("dClob", key);
    rs1.next();
    Clob clob = rs1.getClob(1);
    rs1.close();

    rs1 = fetchUpd("dClob", key2);
    rs1.next();
    rs1.updateClob(1,clob);
    rs1.updateRow();
    rs1.close();

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

    rs1 = fetch("dClob", key2);
    rs1.next();
    assertEquals(clob, rs1.getClob(1));
    rs1.close();
}
 

/**
 * Tests a sequence of operations on a scrollable, updatable resultset.
 *
 * @throws SQLException if the test fails
 */
// GemStone change: disabled since scrollable RS are not supported yet
public void DISABLED_testScrollableUpdateWithLocators()
        throws SQLException {
    getConnection().setAutoCommit(false);
    Statement stmt = createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
                                     ResultSet.CONCUR_UPDATABLE);
    ResultSet rs = stmt.executeQuery(
            "select dBlob, dClob from LOBLOC_NO_NULLS");
    rs.absolute(3);
    Clob c1 = rs.getClob(2);
    final int origLength = (int)c1.length();
    final String origContent = c1.getSubString(1, origLength);
    // Do a change
    c1.setString(origLength, "FIRSTPASS");
    rs.absolute(7);
    rs.next();
    // Move back to row 3
    rs.absolute(3);
    Clob c2 = rs.getClob(2);
    assertEquals(origContent, c2.getSubString(1, (int)c2.length()));
    rs.updateRow(); // Should be a no-op
    rs.absolute(3);
    // Expect this to fail if the restriction that LOB columns cannot be
    // accessed more than once is enforced.
    Clob c3 = rs.getClob(2);
    assertEquals(origContent, c3.getSubString(1, (int)c3.length()));
    rs.previous();
    rs.next();
    Clob c4 = rs.getClob(2);
    final String newContent = "THIS IS THE NEW VALUE!";
    c4.setString(1, newContent);
    rs.updateClob(2, c4);
    rs.updateRow();
    c4.setString(1, "THIS IS NOT NOT NOT THE NEW VALUE!");
    rs.updateRow();
    rs.next();
    rs.absolute(3);
    Clob c5 = rs.getClob(2);
    assertEquals(newContent, c5.getSubString(1, (int)c5.length()));
    rollback();
    assertInvalid(c1);
    assertInvalid(c2);
    assertInvalid(c3);
    assertInvalid(c4);
    assertInvalid(c5);
}
 

@Test(expected = SQLFeatureNotSupportedException.class)
public void assertUpdateClobForColumnIndexWithInputStream() throws SQLException {
    for (ResultSet each : resultSets) {
        each.updateClob(1, new StringReader(""));
    }
}
 

@Test(expected = SQLFeatureNotSupportedException.class)
public void assertUpdateClobForColumnLabel() throws SQLException {
    for (ResultSet each : resultSets) {
        each.updateClob("label", (Clob) null);
    }
}