Java Code Examples for java.sql.Clob#getAsciiStream()

public HarmonySerialClob(Clob clob) throws SQLException {
    Reader characterStream;

    if (clob == null) { throw new IllegalArgumentException(); }
    if ((characterStream = clob.getCharacterStream()) == null
            && clob.getAsciiStream() == null) { throw new IllegalArgumentException(); }

    this.clob = clob;
    origLen = clob.length();
    len = origLen;
    buf = new char[(int) len];
    try {
        characterStream.read(buf);
    } catch (IOException e) {

        throw new SQLException("SerialClob: "
                + e.getMessage(), e);
    }
}
 

@Override
public InputStream getClobAsAsciiStream(ResultSet rs, int columnIndex) throws SQLException {
	logger.debug("Returning CLOB as ASCII stream");
	if (this.wrapAsLob) {
		Clob clob = rs.getClob(columnIndex);
		return clob.getAsciiStream();
	}
	else {
		return rs.getAsciiStream(columnIndex);
	}
}
 

@Override
public InputStream getClobAsAsciiStream(ResultSet rs, int columnIndex) throws SQLException {
	logger.debug("Returning Oracle CLOB as ASCII stream");
	Clob clob = rs.getClob(columnIndex);
	initializeResourcesBeforeRead(rs.getStatement().getConnection(), clob);
	InputStream retVal = (clob != null ? clob.getAsciiStream() : null);
	releaseResourcesAfterRead(rs.getStatement().getConnection(), clob);
	return retVal;
}
 

@Override
public InputStream getClobAsAsciiStream(ResultSet rs, int columnIndex) throws SQLException {
	logger.debug("Returning CLOB as ASCII stream");
	if (this.wrapAsLob) {
		Clob clob = rs.getClob(columnIndex);
		return clob.getAsciiStream();
	}
	else {
		return rs.getAsciiStream(columnIndex);
	}
}
 

@Override
public InputStream getClobAsAsciiStream(ResultSet rs, int columnIndex) throws SQLException {
	logger.debug("Returning Oracle CLOB as ASCII stream");
	Clob clob = rs.getClob(columnIndex);
	initializeResourcesBeforeRead(rs.getStatement().getConnection(), clob);
	InputStream retVal = (clob != null ? clob.getAsciiStream() : null);
	releaseResourcesAfterRead(rs.getStatement().getConnection(), clob);
	return retVal;
}
 

@Override
public InputStream getClobAsAsciiStream(ResultSet rs, int columnIndex) throws SQLException {
	logger.debug("Returning CLOB as ASCII stream");
	if (this.wrapAsLob) {
		Clob clob = rs.getClob(columnIndex);
		return clob.getAsciiStream();
	}
	else {
		return rs.getAsciiStream(columnIndex);
	}
}
 

@Override
public InputStream getClobAsAsciiStream(ResultSet rs, int columnIndex) throws SQLException {
	logger.debug("Returning CLOB as ASCII stream");
	if (this.wrapAsLob) {
		Clob clob = rs.getClob(columnIndex);
		return clob.getAsciiStream();
	}
	else {
		return rs.getAsciiStream(columnIndex);
	}
}
 

private String clobToString(Clob clob) throws SQLException, IOException {
	if (clob == null)
		return null;

	InputStream in = clob.getAsciiStream();
	Reader read = new InputStreamReader(in);
	StringWriter w = new StringWriter();

	int c = -1;
	while ((c = read.read()) != -1) {
		w.write(c);
	}
	w.flush();
	return StringUtils.trim(w.toString());
}
 

public InputStream getAsciiStream(int columnIndex) throws SQLException {
    Clob clob = getClob(columnIndex);

    if (clob == null) {
        return null;
    }

    return clob.getAsciiStream();
}
 

/**
 * Tests that the Clob can handle multiple streams and the length call
 * multiplexed.
 * <p>
 * This test was written after bug DERBY-2806 was reported, where getting
 * the length of the Clob after fetching a stream from it would exhaust
 * the stream and cause the next read to return -1.
 * <p>
 * The test is written to work on a Clob that operates on streams from
 * the store, which currently means that it must be over a certain size
 * and that no modifying methods can be called on it.
 */
public void testMultiplexedOperationProblem()
        throws IOException, SQLException {
    getConnection().setAutoCommit(false);
    int length = 266000;
    PreparedStatement ps = prepareStatement(
            "insert into updateClob (id, data) values (?,?)");
    ps.setInt(1, length);
    ps.setCharacterStream(2, new LoopingAlphabetReader(length), length);
    assertEquals(1, ps.executeUpdate());
    ps.close();
    PreparedStatement psFetchClob = prepareStatement(
            "select data from updateClob where id = ?");
    psFetchClob.setInt(1, length);
    ResultSet rs = psFetchClob.executeQuery();
    assertTrue("No Clob of length " + length + " in database", rs.next());
    Clob clob = rs.getClob(1);
    assertEquals(length, clob.length());
    Reader r = clob.getCharacterStream();
    int lastReadChar = r.read();
    lastReadChar = assertCorrectChar(lastReadChar, r.read());
    lastReadChar = assertCorrectChar(lastReadChar, r.read());
    assertEquals(length, clob.length());
    // Must be bigger than internal buffers might be.
    int nextChar;
    for (int i = 2; i < 160000; i++) {
        nextChar = r.read();
        // Check manually to report position where it fails.
        if (nextChar == -1) {
            fail("Failed at position " + i + ", stream should not be" +
                    " exhausted now");
        }
        lastReadChar = assertCorrectChar(lastReadChar, nextChar);
    }
    lastReadChar = assertCorrectChar(lastReadChar, r.read());
    lastReadChar = assertCorrectChar(lastReadChar, r.read());
    InputStream ra = clob.getAsciiStream();
    assertEquals(length, clob.length());
    int lastReadAscii = ra.read();
    lastReadAscii = assertCorrectChar(lastReadAscii, ra.read());
    lastReadAscii = assertCorrectChar(lastReadAscii, ra.read());
    assertEquals(length, clob.length());
    lastReadAscii = assertCorrectChar(lastReadAscii, ra.read());
    lastReadChar = assertCorrectChar(lastReadChar, r.read());
    // Close resources.
    r.close();
    ra.close();
    rs.close();
    psFetchClob.close();
}
 

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

    PreparedStatement stmt3 = prepareStatement(
        "SELECT c FROM testBlobX1 WHERE a = 1");
    ResultSet rs3 = stmt3.executeQuery();
    rs3.next();
    Clob clob = rs3.getClob(1);

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

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

    PreparedStatement stmt4 = prepareStatement(
        "UPDATE testBlobX1 SET c = ? WHERE a = 1");
    stmt4.setClob(1,  clob);
    stmt4.executeUpdate();
    stmt4.close();

    rs3.close();
    rs3 = stmt3.executeQuery();

    assertTrue("FAIL -- clob not found", rs3.next());

    clob = rs3.getClob(1);
    long new_length = clob.length();
    assertEquals("FAIL -- wrong clob length",
            streamSize[0], new_length);
    // Check contents ...
    InputStream fStream = new LoopingAlphabetStream(streamSize[0]);
    InputStream lStream = clob.getAsciiStream();
    assertTrue("FAIL - Clob and file contents do not match",
            compareLob2File(fStream, lStream));

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

public static InputStream getClobInputStream(Clob clob) throws SQLException, JdbcException {
	return clob.getAsciiStream();
}
 

/**
 * Tests the ClobOutputStream.write(int b) method
 **/
public void testClobAsciiWrite1Param() throws Exception
{
    InputStream streamIn = new LoopingAlphabetStream(streamSize[1]);

    PreparedStatement stmt3 = prepareStatement(
        "SELECT c FROM testBlobX1 WHERE a = 1");
    ResultSet rs3 = stmt3.executeQuery();
    rs3.next();
    Clob clob = rs3.getClob(1);

    assertTrue("FAIL -- clob is NULL", clob != null);
    int buffer;
    OutputStream outstream = clob.setAsciiStream(1L);
    while ((buffer = streamIn.read()) != -1) {
        outstream.write(buffer);
    }
    outstream.close();
    streamIn.close();

    PreparedStatement stmt4 = prepareStatement(
        "UPDATE testBlobX1 SET c = ? WHERE a = 1");
    stmt4.setClob(1,  clob);
    stmt4.executeUpdate();
    stmt4.close();

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

    clob = rs3.getClob(1);
    long new_length = clob.length();
    assertEquals("FAIL -- wrong clob length", streamSize[1], new_length);

    // Check contents ...
    InputStream fStream = new LoopingAlphabetStream(streamSize[1]);
    InputStream lStream = clob.getAsciiStream();
    assertTrue("FAIL - Clob and file contents do not match", compareLob2File(fStream, lStream));
    fStream.close();
    lStream.close();
    rs3.close();
    stmt3.close();
}
 

public static List<String> sampleData(final IDbFactory factory, final ColumnMetaData metaData) throws IOException, DataDefenderException {
    final ISqlBuilder sqlBuilder  = factory.createSQLBuilder();
    String            querySample = "";
    String            select      = "SELECT ";
    
    
    
    if (!metaData.getColumnType().equals("CLOB") && !factory.getVendorName().equals("mssql")) {
        select = select + "DISTINCT ";
    }
    
    querySample = sqlBuilder.buildSelectWithLimit(
        select + metaData.getColumnName() + " FROM "
            + sqlBuilder.prefixSchema(metaData.getTable().getTableName())
            + " WHERE " + metaData.getColumnName() + " IS NOT NULL",
        5
    );
    log.debug("Executing query against database: " + querySample);

    final List<String> sampleDataList = new ArrayList<>();

    try (Statement stmt = factory.getConnection().createStatement();
        ResultSet resultSet = stmt.executeQuery(querySample);) {
        while (resultSet.next()) {
            String tmp;
            if (metaData.getColumnType().equals("CLOB")) {
                Clob clob = resultSet.getClob(1);
                InputStream is = clob.getAsciiStream();
                tmp = IOUtils.toString(is, StandardCharsets.UTF_8.name());
            } else {
                tmp = resultSet.getString(1);
            }
            
            if (StringUtils.isNotBlank(tmp)) {
                sampleDataList.add(tmp);
                tmp = null;
            }
        }
    } catch (SQLException sqle) {
        log.error(sqle.toString());
    }

    // Removing duplicates
    List<String> sampleDataListWithoutDuplicates = 
            new ArrayList<>(new HashSet<>(sampleDataList));
    
    return sampleDataListWithoutDuplicates;
}
 

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

    Connection conn = getConnection();
    int b, c;
    Clob clob;

    Statement s = createStatement();

    PreparedStatement ps =
            prepareStatement("insert into clobtable2 (n, clobcol)" + " values(?,?)");
    ps.setInt(1,1000);
    clob = conn.createClob();

    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 = clob.setAsciiStream(1);
    ArrayList beforeUpdateList = new ArrayList();

    c = is.read();
    while(c>0) {
        os.write(c);
        beforeUpdateList.add(c);
        c = is.read();
    }
    ps.setClob(2, clob);
    ps.executeUpdate();

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

    clob = rs.getClob(1);
    assertEquals(beforeUpdateList.size(), clob.length());

    //Get the InputStream from this Clob.
    InputStream in = clob.getAsciiStream();
    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 < clob.length(); i++) {
        assertEquals(beforeUpdateList.get(i), afterUpdateList.get(i));
    }

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

}
 

/**
 * Tests that the Clob can handle multiple streams and the length call
 * multiplexed.
 * <p>
 * This test was written after bug DERBY-2806 was reported, where getting
 * the length of the Clob after fetching a stream from it would exhaust
 * the stream and cause the next read to return -1.
 * <p>
 * The test is written to work on a Clob that operates on streams from
 * the store, which currently means that it must be over a certain size
 * and that no modifying methods can be called on it.
 */
public void testMultiplexedOperationProblem()
        throws IOException, SQLException {
    getConnection().setAutoCommit(false);
    int length = 266000;
    PreparedStatement ps = prepareStatement(
            "insert into updateClob (id, data) values (?,?)");
    ps.setInt(1, length);
    ps.setCharacterStream(2, new LoopingAlphabetReader(length), length);
    assertEquals(1, ps.executeUpdate());
    ps.close();
    PreparedStatement psFetchClob = prepareStatement(
            "select data from updateClob where id = ?");
    psFetchClob.setInt(1, length);
    ResultSet rs = psFetchClob.executeQuery();
    assertTrue("No Clob of length " + length + " in database", rs.next());
    Clob clob = rs.getClob(1);
    assertEquals(length, clob.length());
    Reader r = clob.getCharacterStream();
    int lastReadChar = r.read();
    lastReadChar = assertCorrectChar(lastReadChar, r.read());
    lastReadChar = assertCorrectChar(lastReadChar, r.read());
    assertEquals(length, clob.length());
    // Must be bigger than internal buffers might be.
    int nextChar;
    for (int i = 2; i < 160000; i++) {
        nextChar = r.read();
        // Check manually to report position where it fails.
        if (nextChar == -1) {
            fail("Failed at position " + i + ", stream should not be" +
                    " exhausted now");
        }
        lastReadChar = assertCorrectChar(lastReadChar, nextChar);
    }
    lastReadChar = assertCorrectChar(lastReadChar, r.read());
    lastReadChar = assertCorrectChar(lastReadChar, r.read());
    InputStream ra = clob.getAsciiStream();
    assertEquals(length, clob.length());
    int lastReadAscii = ra.read();
    lastReadAscii = assertCorrectChar(lastReadAscii, ra.read());
    lastReadAscii = assertCorrectChar(lastReadAscii, ra.read());
    assertEquals(length, clob.length());
    lastReadAscii = assertCorrectChar(lastReadAscii, ra.read());
    lastReadChar = assertCorrectChar(lastReadChar, r.read());
    // Close resources.
    r.close();
    ra.close();
    rs.close();
    psFetchClob.close();
}
 

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

    Connection conn = getConnection();
    int b, c;
    Clob clob;

    Statement s = createStatement();

    PreparedStatement ps =
            prepareStatement("insert into clobtable2 (n, clobcol)" + " values(?,?)");
    ps.setInt(1,1000);
    clob = conn.createClob();

    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 = clob.setAsciiStream(1);
    ArrayList beforeUpdateList = new ArrayList();

    c = is.read();
    while(c>0) {
        os.write(c);
        beforeUpdateList.add(c);
        c = is.read();
    }
    ps.setClob(2, clob);
    ps.executeUpdate();

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

    clob = rs.getClob(1);
    assertEquals(beforeUpdateList.size(), clob.length());

    //Get the InputStream from this Clob.
    InputStream in = clob.getAsciiStream();
    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 < clob.length(); i++) {
        assertEquals(beforeUpdateList.get(i), afterUpdateList.get(i));
    }

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

}
 

/**
 * Tests that the Clob can handle multiple streams and the length call
 * multiplexed.
 * <p>
 * This test was written after bug DERBY-2806 was reported, where getting
 * the length of the Clob after fetching a stream from it would exhaust
 * the stream and cause the next read to return -1.
 * <p>
 * The test is written to work on a Clob that operates on streams from
 * the store, which currently means that it must be over a certain size
 * and that no modifying methods can be called on it.
 */
public void testMultiplexedOperationProblem()
        throws IOException, SQLException {
    getConnection().setAutoCommit(false);
    int length = 266000;
    PreparedStatement ps = prepareStatement(
            "insert into updateClob (id, data) values (?,?)");
    ps.setInt(1, length);
    ps.setCharacterStream(2, new LoopingAlphabetReader(length), length);
    assertEquals(1, ps.executeUpdate());
    ps.close();
    PreparedStatement psFetchClob = prepareStatement(
            "select data from updateClob where id = ?");
    psFetchClob.setInt(1, length);
    ResultSet rs = psFetchClob.executeQuery();
    assertTrue("No Clob of length " + length + " in database", rs.next());
    Clob clob = rs.getClob(1);
    assertEquals(length, clob.length());
    Reader r = clob.getCharacterStream();
    int lastReadChar = r.read();
    lastReadChar = assertCorrectChar(lastReadChar, r.read());
    lastReadChar = assertCorrectChar(lastReadChar, r.read());
    assertEquals(length, clob.length());
    // Must be bigger than internal buffers might be.
    int nextChar;
    for (int i = 2; i < 160000; i++) {
        nextChar = r.read();
        // Check manually to report position where it fails.
        if (nextChar == -1) {
            fail("Failed at position " + i + ", stream should not be" +
                    " exhausted now");
        }
        lastReadChar = assertCorrectChar(lastReadChar, nextChar);
    }
    lastReadChar = assertCorrectChar(lastReadChar, r.read());
    lastReadChar = assertCorrectChar(lastReadChar, r.read());
    InputStream ra = clob.getAsciiStream();
    assertEquals(length, clob.length());
    int lastReadAscii = ra.read();
    lastReadAscii = assertCorrectChar(lastReadAscii, ra.read());
    lastReadAscii = assertCorrectChar(lastReadAscii, ra.read());
    assertEquals(length, clob.length());
    lastReadAscii = assertCorrectChar(lastReadAscii, ra.read());
    lastReadChar = assertCorrectChar(lastReadChar, r.read());
    // Close resources.
    r.close();
    ra.close();
    rs.close();
    psFetchClob.close();
}
 

public static IRecord toRecord(Object o, IMetaData dataStoreMeta) {
	Object[] row;
	if (!(o instanceof Object[])) {
		row = new Object[1];
		row[0] = o == null ? "" : o;
	} else {
		row = (Object[]) o;
	}
	String rowS = "";
	for (int i = 0; i < row.length; i++) {
		rowS = rowS + " [" + row[i] + "]";
	}
	IRecord record = new Record();
	for (int i = 0, j = 0; i < dataStoreMeta.getFieldCount(); i++) {
		IFieldMetaData fieldMeta = dataStoreMeta.getFieldMeta(i);
		Boolean calculated = (Boolean) fieldMeta.getProperty("calculated");
		if (calculated.booleanValue() == false) {
			Assert.assertTrue(j < row.length, "Impossible to read field [" + fieldMeta.getName() + "] from resultset");

			if (row[j] instanceof java.sql.Clob) {
				Clob clob = (Clob) row[j];
				InputStream in;
				try {
					in = clob.getAsciiStream();
				} catch (SQLException e) {
					logger.error("Error in reading clob");
					throw new RuntimeException(e);
				}
				try (Scanner s = new Scanner(in)) {
					s.useDelimiter("\\A");

					String clobAsString = s.hasNext() ? s.next() : "";
					record.appendField(new Field(clobAsString));
				}
				if (row[j] != null)
					fieldMeta.setType(row[j].getClass());
			} else {

				record.appendField(new Field(row[j]));
				if (row[j] != null)
					fieldMeta.setType(row[j].getClass());
			}
			j++;
		} else {
			DataSetVariable variable = (DataSetVariable) fieldMeta.getProperty("variable");
			if (variable.getResetType() == DataSetVariable.RESET_TYPE_RECORD) {
				variable.reset();
			}

			record.appendField(new Field(variable.getValue()));
			if (variable.getValue() != null)
				fieldMeta.setType(variable.getValue().getClass());
		}
	}
	return record;
}
 

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

    Connection conn = getConnection();
    int b, c;
    Clob clob;

    Statement s = createStatement();

    PreparedStatement ps =
            prepareStatement("insert into clobtable2 (n, clobcol)" + " values(?,?)");
    ps.setInt(1,1000);
    clob = conn.createClob();

    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 = clob.setAsciiStream(1);
    ArrayList<Integer> beforeUpdateList = new ArrayList<Integer>();

    c = is.read();
    while(c>0) {
        os.write(c);
        beforeUpdateList.add(c);
        c = is.read();
    }
    ps.setClob(2, clob);
    ps.executeUpdate();

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

    clob = rs.getClob(1);
    assertEquals(beforeUpdateList.size(), clob.length());

    //Get the InputStream from this Clob.
    InputStream in = clob.getAsciiStream();
    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 < clob.length(); i++) {
        assertEquals(beforeUpdateList.get(i), afterUpdateList.get(i));
    }

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

}