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

private void    vetClob( int streamLength, String tableName ) throws Exception
{
    PreparedStatement   select = prepareStatement( "select myclob from " + tableName );
    ResultSet               rs = select.executeQuery();
    rs.next();
    Reader      actualReader = rs.getCharacterStream( 1 );
    Reader      expectedReader = new DummyReader( streamLength );

    for ( int i = 0; i < streamLength; i++ )
    {
        int actual = actualReader.read();
        if ( actual < 0 )
        {
            fail( "    Read stream was only " + i + " characters long." );
        }
        
        int    expected = expectedReader.read();
        
        assertEquals( expected, actual );
    }
    assertTrue( actualReader.read() < 0 );

    rs.close();
}
 

/**
 * Test that ResultSet.getCharacterStream does not hold locks after the
 * ResultSet is closed
 * 
 * @throws SQLException
 * @throws IOException
 */
public void testGetCharacterStream() throws SQLException, IOException {
    // getCharacterStream() no locks expected after retrieval
    int numChars = 0;
    Statement stmt = createStatement();
    String sql = "SELECT bc from t1";
    // First with getCharacterStream
    ResultSet rs = stmt.executeQuery(sql);
    rs.next();
    java.io.Reader characterStream = rs.getCharacterStream(1);
    // Extract all the characters
    int read = characterStream.read();
    while (read != -1) {
        read = characterStream.read();
        numChars++;
    }
    assertEquals(38000, numChars);
    rs.close();
    assertEquals(0, countLocks());
    commit();
}
 

/**
 * Test that ResultSet.getCharacterStream does not hold locks after the
 * ResultSet is closed
 * 
 * @throws SQLException
 * @throws IOException
 */
public void testGetCharacterStream() throws SQLException, IOException {
    // getCharacterStream() no locks expected after retrieval
    int numChars = 0;
    Statement stmt = createStatement();
    String sql = "SELECT bc from t1";
    // First with getCharacterStream
    ResultSet rs = stmt.executeQuery(sql);
    rs.next();
    java.io.Reader characterStream = rs.getCharacterStream(1);
    // Extract all the characters
    int read = characterStream.read();
    while (read != -1) {
        read = characterStream.read();
        numChars++;
    }
    assertEquals(38000, numChars);
    rs.close();
    assertLockCount(0);
    commit();
}
 

public void testSmallMultiByteCharLob() throws SQLException, IOException {
    getConnection().setAutoCommit(false);
    Statement s = createStatement();
    
    PreparedStatement ps = prepareStatement("INSERT INTO MB_CLOBTABLE VALUES(?,?)");
    // We allocate 16MB for the test so use something bigger than that.
    ps.setInt(1,1);
    LoopingAlphabetReader reader = new LoopingAlphabetReader(SHORT_CLOB_LENGTH, CharAlphabet.cjkSubset());

    ps.setCharacterStream(2, reader, SHORT_CLOB_LENGTH);
    ps.executeUpdate();

    ResultSet rs = s.executeQuery("SELECT K, LENGTH(C), C FROM MB_CLOBTABLE" +
            "-- GEMFIREXD-PROPERTIES constraint=pk\n ORDER BY K");
    rs.next();
    assertEquals(SHORT_CLOB_LENGTH_STRING, rs.getString(2));
    // make sure we can still access the clob after getting length.
    // It should be ok because we reset the stream
    Reader rsReader = rs.getCharacterStream(3);
    int len= 0;
    char[] buf = new char[32672];
    for (;;)  {
            int size = rsReader.read(buf);
            if (size == -1)
                    break;
            len += size;
            int expectedValue = ((len -1) % 12) + '\u4E00';
            if (size != 0)
                assertEquals(expectedValue,buf[size -1]);
    }
    assertEquals(SHORT_CLOB_LENGTH, len);
    rs.close();
    // Select just length without selecting the clob.
    rs = s.executeQuery("SELECT K, LENGTH(C)  FROM MB_CLOBTABLE " +
            "ORDER BY K");
    JDBC.assertFullResultSet(rs, new String [][] {{"1",SHORT_CLOB_LENGTH_STRING}});
}
 

public void testUpdateCharacterStreamLengthlessParameterName()
        throws IOException, SQLException {
    String str = "This is the (\u0FFF\u1234) test string";
    String strUpdated = "An updated (\u0FEF\u9876) test string";

    // Insert test data
    PreparedStatement psChar = prep("dLongVarchar");
    psChar.setInt(1, key);
    psChar.setCharacterStream(2, new StringReader(str));
    psChar.execute();
    psChar.close();

    // Update test data
    ResultSet rs = fetchUpd("dLongVarchar", key);
    rs.next();
    rs.updateCharacterStream("dLongVarchar", new StringReader(strUpdated));
    rs.updateRow();
    rs.close();

    // Verify that update took place and is correct.
    rs = fetch("dLongVarchar", key);
    rs.next();
    Reader updatedStr = rs.getCharacterStream(1);
    for (int i=0; i < strUpdated.length(); i++) {
        assertEquals("Strings differ at index " + i,
                strUpdated.charAt(i),
                updatedStr.read());
    }
    assertEquals("Too much data in stream", -1, updatedStr.read());
    updatedStr.close();
}
 

public void testUpdateCharacterStreamLengthless()
        throws IOException, SQLException {
    String str = "This is the (\u0FFF\u1234) test string";
    String strUpdated = "An updated (\u0FEF\u9876) test string";

    // Insert test data
    PreparedStatement psChar = prep("dLongVarchar");
    psChar.setInt(1, key);
    psChar.setCharacterStream(2, new StringReader(str));
    psChar.execute();
    psChar.close();

    // Update test data
    ResultSet rs = fetchUpd("dLongVarchar", key);
    rs.next();
    rs.updateCharacterStream(1, new StringReader(strUpdated));
    rs.updateRow();
    rs.close();

    // Verify that update took place and is correct.
    rs = fetch("dLongVarchar", key);
    rs.next();
    Reader updatedStr = rs.getCharacterStream(1);
    for (int i=0; i < strUpdated.length(); i++) {
        assertEquals("Strings differ at index " + i,
                strUpdated.charAt(i),
                updatedStr.read());
    }
    assertEquals("Too much data in stream", -1, updatedStr.read());
    updatedStr.close();
}
 

public Object get(ResultSet rs, String name) throws HibernateException, SQLException {

			// Retrieve the value of the designated column in the current row of this
			// ResultSet object as a java.io.Reader object
			Reader charReader = rs.getCharacterStream(name);

			// if the corresponding SQL value is NULL, the reader we got is NULL as well
			if (charReader==null) return null;

			// Fetch Reader content up to the end - and put characters in a StringBuffer
			StringBuffer sb = new StringBuffer();
			try {
				char[] buffer = new char[2048];
				while (true) {
					int amountRead = charReader.read(buffer, 0, buffer.length);
					if ( amountRead == -1 ) break;
					sb.append(buffer, 0, amountRead);
				}
			}
			catch (IOException ioe) {
				throw new HibernateException( "IOException occurred reading text", ioe );
			}
			finally {
				try {
					charReader.close();
				}
				catch (IOException e) {
					throw new HibernateException( "IOException occurred closing stream", e );
				}
			}

			// Return StringBuffer content as a large String
			return sb.toString();
	}
 

/**
 * test getCharacterStream against inserted data
 * 
 * @throws SQLException
 * @throws IOException
 */
public void testGetCharacterStream() throws SQLException, IOException {
    Connection conn = getConnection();
    PreparedStatement st;
    st = conn.prepareStatement("insert into t3(text_data) values(?)");
    st.setCharacterStream(1,
                          new StringReader(TEST_STRING_DATA),
                          TEST_STRING_DATA.length());
    st.executeUpdate();
    st = conn.prepareStatement("select " + 
               "text_data as text_data_col1," + 
               "text_data as text_data_col2 " + 
               "from " + 
               "t3");
    ResultSet rs = st.executeQuery();

    while(rs.next()){
        Reader r = rs.getCharacterStream(1);
        int i = 0;
        for(int c = r.read(); c > -1; c = r.read()){
            int exp = (int) TEST_STRING_DATA.charAt(i++);
            assertEquals(exp,c);
        }
    }
    Statement s = createStatement();
    s.executeUpdate("delete from t3");
    
}
 

@Override
protected Reader getReader(ResultSet rs, String charset, String streamId, IPipeLineSession session) throws SenderException {
	try {
		return rs.getCharacterStream(1);
	} catch (Exception e) {
		throw new SenderException(e);
	}
}
 

/**
 * test getCharacterStream against inserted data
 * 
 * @throws SQLException
 * @throws IOException
 */
public void testGetCharacterStream() throws SQLException, IOException {
    Connection conn = getConnection();
    PreparedStatement st;
    st = conn.prepareStatement("insert into t3(text_data) values(?)");
    st.setCharacterStream(1,
                          new StringReader(TEST_STRING_DATA),
                          TEST_STRING_DATA.length());
    st.executeUpdate();
    st = conn.prepareStatement("select " + 
               "text_data as text_data_col1," + 
               "text_data as text_data_col2 " + 
               "from " + 
               "t3");
    ResultSet rs = st.executeQuery();

    while(rs.next()){
        Reader r = rs.getCharacterStream(1);
        int i = 0;
        for(int c = r.read(); c > -1; c = r.read()){
            int exp = (int) TEST_STRING_DATA.charAt(i++);
            assertEquals(exp,c);
        }
    }
    Statement s = createStatement();
    s.executeUpdate("delete from t3");
    
}
 

public void testLargeMultiByteCharLob() throws SQLException, IOException {
    getConnection().setAutoCommit(false);
    Statement s = createStatement();

    PreparedStatement ps = prepareStatement("INSERT INTO MB_CLOBTABLE VALUES(?,?)");
    // We allocate 16MB for the test so use something bigger than that.
    ps.setInt(1,1);
    LoopingAlphabetReader reader = new LoopingAlphabetReader(LONG_CLOB_LENGTH, CharAlphabet.cjkSubset());

    ps.setCharacterStream(2, reader, LONG_CLOB_LENGTH);
    ps.executeUpdate();

    ResultSet rs = s.executeQuery("SELECT K, LENGTH(C), C FROM MB_CLOBTABLE" +
            "-- GEMFIREXD-PROPERTIES constraint=pk\n ORDER BY K");
    rs.next();
    assertEquals(LONG_CLOB_LENGTH_STRING, rs.getString(2));
    // make sure we can still access the clob after getting length.
    // It should be ok because we reset the stream
    Reader rsReader = rs.getCharacterStream(3);
    int len= 0;
    char[] buf = new char[32672];
    for (;;)  {
            int size = rsReader.read(buf);
            if (size == -1)
                    break;
            len += size;
            int expectedValue = ((len -1) % 12) + '\u4E00';
            if (size != 0)
                assertEquals(expectedValue,buf[size -1]);
    }
    assertEquals(LONG_CLOB_LENGTH, len);
    rs.close();
    // Select just length without selecting the clob.
    rs = s.executeQuery("SELECT K, LENGTH(C)  FROM MB_CLOBTABLE " +
            "ORDER BY K");
    JDBC.assertFullResultSet(rs, new String [][] {{"1",LONG_CLOB_LENGTH_STRING}});
}
 

/**
 * Retrives a large text column from a result set, automatically performing
 * streaming if the JDBC driver requires it. This is necessary because
 * different JDBC drivers have different capabilities and methods for
 * retrieving large text values.
 *
 * @param rs the ResultSet to retrieve the text field from.
 * @param columnIndex the column in the ResultSet of the text field.
 * @return the String value of the text field.
 * @throws SQLException if an SQL exception occurs.
 */
public static String getLargeTextField(ResultSet rs, int columnIndex) throws SQLException {
    if (isStreamTextRequired()) {
        String value;
        try (Reader bodyReader = rs.getCharacterStream(columnIndex)) {
            if (bodyReader == null) {
                return null;
            }
            char[] buf = new char[256];
            int len;
            StringWriter out = new StringWriter(256);
            while ((len = bodyReader.read(buf)) >= 0) {
                out.write(buf, 0, len);
            }
            value = out.toString();
            out.close();
        }
        catch (Exception e) {
            Log.error(e.getMessage(), e);
            throw new SQLException("Failed to load text field");
        }
        return value;
    }
    else {
        return rs.getString(columnIndex);
    }
}
 

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

public void testUpdateCharacterStreamLengthless()
        throws IOException, SQLException {
    String str = "This is the (\u0FFF\u1234) test string";
    String strUpdated = "An updated (\u0FEF\u9876) test string";

    // Insert test data
    PreparedStatement psChar = prep("dLongVarchar");
    psChar.setInt(1, key);
    psChar.setCharacterStream(2, new StringReader(str));
    psChar.execute();
    psChar.close();

    // Update test data
    ResultSet rs = fetchUpd("dLongVarchar", key);
    rs.next();
    rs.updateCharacterStream(1, new StringReader(strUpdated));
    rs.updateRow();
    rs.close();

    // Verify that update took place and is correct.
    rs = fetch("dLongVarchar", key);
    rs.next();
    Reader updatedStr = rs.getCharacterStream(1);
    for (int i=0; i < strUpdated.length(); i++) {
        assertEquals("Strings differ at index " + i,
                strUpdated.charAt(i),
                updatedStr.read());
    }
    assertEquals("Too much data in stream", -1, updatedStr.read());
    updatedStr.close();
}
 

public Object get(ResultSet rs, String name) throws SQLException {
	Reader stream = rs.getCharacterStream(name);
	if ( stream == null ) return toExternalFormat( null );
	CharArrayWriter writer = new CharArrayWriter();
	for(;;) {
		try {
			int c = stream.read();
			if ( c == -1) return toExternalFormat( writer.toCharArray() );
			writer.write( c );
		}
		catch (IOException e) {
			throw new HibernateException("Unable to read character stream from rs");
		}
	}
}
 

/**
 * Insert a clob and test length.
 *
 * @param lengthless  if true use the lengthless setCharacterStream api
 *
 * @throws SQLException
 * @throws IOException
 * @throws InvocationTargetException
 * @throws IllegalAccessException
 * @throws IllegalArgumentException
 */
private void testClobLength(boolean lengthless) throws SQLException, IOException, IllegalArgumentException,
        IllegalAccessException, InvocationTargetException {
    getConnection().setAutoCommit(false);
    Statement s = createStatement();
    s.executeUpdate("CREATE TABLE CLOBTABLE (K INT CONSTRAINT PK PRIMARY KEY, C CLOB(" + LONG_CLOB_LENGTH + "))");

    PreparedStatement ps = prepareStatement("INSERT INTO CLOBTABLE VALUES(?,?)");
    // We allocate 16MB for the test so use something bigger than that.
    ps.setInt(1,1);
    LoopingAlphabetReader reader = new LoopingAlphabetReader(LONG_CLOB_LENGTH);
    if (lengthless) {
        Method m = null;
        try {
            Class c = ps.getClass();
            m = c.getMethod("setCharacterStream",new Class[] {Integer.TYPE,
                        InputStream.class});
        } catch (NoSuchMethodException e) {
            // ignore method not found as method may not be present for
            // jdk's lower than 1.6.
            println("Skipping lengthless insert because method is not available");
            return;
        }
        m.invoke(ps, new Object[] {new Integer(2), reader});
    }
    else
        ps.setCharacterStream(2, reader, LONG_CLOB_LENGTH);
    ps.executeUpdate();
    // insert a zero length clob.
    ps.setInt(1, 2);
    ps.setString(2, "");
    ps.executeUpdate();
    // insert a null clob.
    ps.setInt(1, 3);
    ps.setString(2,null);
    ps.executeUpdate();
    // insert a short clob
    ps.setInt(1, 4);
    ps.setString(2, new String(SHORT_CLOB_CHARS));
    ps.executeUpdate();
    // Currently need to use optimizer override to force use of the index.
    // Derby should use sort avoidance and do it automatically, but there
    // appears to be a bug.
    ResultSet rs = s.executeQuery("SELECT K, LENGTH(C), C FROM CLOBTABLE" +
            "-- GEMFIREXD-PROPERTIES constraint=pk\n ORDER BY K");
    rs.next();
    assertEquals(LONG_CLOB_LENGTH_STRING,rs.getString(2));
    // make sure we can still access the clob after getting length.
    // It should be ok because we reset the stream
    Reader rsReader = rs.getCharacterStream(3);
    int len= 0;
    char[] buf = new char[32672];
    for (;;)  {
            int size = rsReader.read(buf);
            if (size == -1)
                    break;
            len += size;
            int expectedValue = ((len -1) % 26) + 'a';
            if (size != 0)
                assertEquals(expectedValue,buf[size -1]);
    }

    assertEquals(LONG_CLOB_LENGTH,len);
    // empty clob
    rs.next();
    assertEquals("0",rs.getString(2));
    String chars = rs.getString(3);
    assertEquals(0, chars.length());
    // null clob
    rs.next();
    assertEquals(null, rs.getString(2));
    chars = rs.getString(3);
    assertEquals(null, chars);
    // short clob
    rs.next();
    assertEquals("" + SHORT_CLOB_CHARS.length , rs.getString(2));
    chars = rs.getString(3);
    assertTrue(Arrays.equals(chars.toCharArray(), SHORT_CLOB_CHARS));
    rs.close();

    // Select just length without selecting the clob.
    rs = s.executeQuery("SELECT K, LENGTH(C)  FROM CLOBTABLE " +
            "ORDER BY K");
    JDBC.assertFullResultSet(rs, new String [][] {{"1",LONG_CLOB_LENGTH_STRING},{"2","0"},
            {"3",null},{"4","6"}});
}
 

/**
 * This methods tests the ResultSet interface method
 * updateCharacterStream
 *
 * @throws SQLException if some error occurs while calling the method
 */

public void testUpdateCharacterStreamStringParameterName()
throws Exception {
    String str = "Test data";
    String str_for_update = "Test data used for update";

    StringReader r = new StringReader(str);
    StringReader r_for_update = new StringReader
            (str_for_update);

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

    //Update operation
    //use a different ResultSet variable so that the
    //other tests can go on unimpacted
    ResultSet rs1 = fetchUpd("dLongVarchar", key);
    rs1.next();
    rs1.updateCharacterStream("dLongVarchar", 
                              r_for_update,
                              str_for_update.length());
    rs1.updateRow();
    rs1.close();

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

    rs1 = fetch("dLongVarchar", key);
    rs1.next();

    StringReader r_ret = (StringReader)rs1.getCharacterStream(1);

    char [] c_ret = new char[str_for_update.length()];

    r_ret.read(c_ret);

    String str_ret = new String(c_ret);

    assertEquals("Error in updateCharacterStream" + str_ret,str_for_update,
        str_ret);

    rs1.close();
}
 

/**
 * Test serialization of the XML values inserted as part
 * XBindTestSetup processing.  For the documents that are
 * are larger than 32K, this tests that they can be correctly
 * read from disk as a stream (instead of just as as string).
 */
public void testXMLSerializeBinding() throws Exception
{
    // Array of expected character counts for every row inserted
    // into xTable.t1 as part of XBindTestSetup setup.  A "0"
    // means empty string; a "-1" means we inserted a null.
    int [] expectedCharCounts =
        new int [] { 40228, 38712, 1948, 1942, 1967, 1709, 22, -1, -1 };
    // GemStone changes BEGIN
    // results will be in unpredicatable order, so keep counts in
    // lists for later comparison.
    ArrayList<Integer> expectedCountsList = new ArrayList<Integer>(expectedCharCounts.length);
    ArrayList<Integer> actualCountsList = new ArrayList<Integer>();
    for (int i=0; i<expectedCharCounts.length; i++) {
      expectedCountsList.add(new Integer(expectedCharCounts[i]));
    }
    // GemStone changes END
    int rowCount = 0;
    ResultSet rs = createStatement().executeQuery(
            "select i, XMLSERIALIZE(X AS CLOB) FROM xTable.t1");

    while (rs.next())
    {
        int charCount;
        java.io.Reader xResult = rs.getCharacterStream(2);

        // Count the number of characters we read back.
        if (!rs.wasNull())
        {
            int ch = xResult.read();
            for (charCount = 0; ch != -1; ch = xResult.read())
            {
                /* Xalan serialization produces platform-specific line-
                 * endings (DERBY-2106), which can throw off the character
                 * count on Windows.  So if we see the Windows '\r' char
                 * we do not count it.
                 */
                if ((char)ch != '\r')
                    charCount++;
            }
            xResult.close();
        }
        else
            charCount = -1;

        // GemStone changes BEGIN
        actualCountsList.add(new Integer(charCount));
        //assertEquals("Unexpected serialized character count:",
        //    expectedCharCounts[rowCount], charCount);
        // GemStone changes END

        rowCount++;
    }

    assertEquals("Unexpected row count when serializing:",
        expectedCharCounts.length, rowCount);
    // GemStone changes BEGIN
    actualCountsList.removeAll(expectedCountsList);
    assertEquals(actualCountsList.size(), 0);
    // GemStone changes END

    /* Test binding to the XMLSERIALIZE operand.  Since
     * the operand is an XML value, and since we don't
     * allow binding to an XML value (which is tested in
     * testInvalidXMLBindings()), there's nothing more to
     * to do here.
     */
}
 

/** convert resultset data for the current row to string array. 
    * If large objects are being exported to an external file, 
    * then write the lob  data into the external file and store 
    * the lob data location  in the string array for that column.
    * @param rs   resultset that contains the data to export.
    * @param isLargeBinary  boolean array, whose elements will
    *                      be true, if the column type is blob/or 
    *                      other large binary type, otherwise false. 
    * @param isLargeChar   boolean array, whose elements will
    *                      be true, if the column type is clob/ 
    *                      other large char type, otherwise false. 
    * @return A string array of the row data to write to export file.
    * @exception  Exception  if any errors during conversion. 
    */
   private String[] getOneRowAtATime(ResultSet rs, 
                                     boolean[] isLargeBinary, 
                                     boolean[] isLargeChar) 
       throws Exception 
{
   int columnCount = exportResultSetForObject.getColumnCount();

ResultSetMetaData rsm=rs.getMetaData();
   if (rs.next()){
      String[] rowObjects = new String[columnCount];
      for (int colNum = 0; colNum < columnCount; colNum++) {
          if (lobsInExtFile && 
              (isLargeChar[colNum] || isLargeBinary[colNum])) 
          {	
              String LobExtLocation;
              if (isLargeBinary[colNum]) {

                  // get input stream that has the column value as a 
                  // stream of uninterpreted bytes; if the value is SQL NULL, 
                  // the return value  is null
                  InputStream is = rs.getBinaryStream(colNum + 1);
                  LobExtLocation = 
                      exportWriteData.writeBinaryColumnToExternalFile(is);
              } else {
                  // It is clob data, get character stream that has 
                  // the column value. if the value is SQL NULL, the 
                  // return value  is null
                  Reader ir = rs.getCharacterStream(colNum + 1);
                  LobExtLocation  = 
                      exportWriteData.writeCharColumnToExternalFile(ir);
              }
              rowObjects[colNum]= LobExtLocation;

              // when lob data is written to the main export file, binary 
              // data is written in hex format. getString() call on binary 
              // columns returns the data in hex format, no special handling 
              // required. In case of large char tpe like Clob, data 
              // is written to main export file  similar to other char types. 
              
              // TODO : handling of Nulls. 
          }
	   else {
              String columnValue;
              int jdbcColumnNumber = colNum + 1;
              
              if ( rsm.getColumnType( jdbcColumnNumber ) == java.sql.Types.JAVA_OBJECT )
              { columnValue = stringifyObject( rs.getObject( jdbcColumnNumber ) ); }
              else { columnValue = rs.getString( jdbcColumnNumber ); }
              
		   rowObjects[colNum] = columnValue;
          }
      }
      return rowObjects;
   }
   rs.close();
exportResultSetForObject.close();
   return null;
 }
 

/**
 * Gets a reader from a result set's column
 * @param resultSet
 * @param column
 * @param encoding
 * @return reader
 * @throws SQLException
 */
protected Reader getReader(ResultSet resultSet, String column, String encoding)
    throws SQLException
{
    return resultSet.getCharacterStream(column);
}