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

private static void streamTestDataVerification(ResultSet rs, int maxValueAllowed)
throws Exception{
	ResultSetMetaData met;

	met = rs.getMetaData();
	byte[] buff = new byte[128];
	// fetch all rows back, get the varchar and/ long varchar columns as streams.
	while (rs.next()) {
		// get the first column as an int
		int a = rs.getInt("a");
		// get the second column as a stream
		InputStream fin = rs.getAsciiStream(2);
		int columnSize = 0;
		for (;;) {
			int size = fin.read(buff);
				if (size == -1)
				break;
				columnSize += size;
		}
		if((a>=1 && a <= 5) && columnSize == maxValueAllowed)
			System.out.println("===> verified length " + maxValueAllowed);
		else
			System.out.println("test failed, columnSize should be " + maxValueAllowed + " but it is" + columnSize);
	}
}
 

private static void streamTestDataVerification(ResultSet rs,
        int maxValueAllowed) throws Exception {

    rs.getMetaData();
    byte[] buff = new byte[128];
    // fetch all rows back, get the varchar and/ long varchar columns as
    // streams.
    while (rs.next()) {
        // get the first column as an int
        int a = rs.getInt("a");
        // get the second column as a stream
        InputStream fin = rs.getAsciiStream(2);
        int columnSize = 0;
        for (;;) {
            int size = fin.read(buff);
            if (size == -1)
                break;
            columnSize += size;
        }
        if ((a >= 1 && a <= 5) && columnSize == maxValueAllowed)
            println("===> verified length " + maxValueAllowed);
        else
            println("test failed, columnSize should be " + maxValueAllowed
                    + " but it is" + columnSize);
    }
}
 

private static void streamTestDataVerification(ResultSet rs, int maxValueAllowed)
throws Exception{
	ResultSetMetaData met;

	met = rs.getMetaData();
	byte[] buff = new byte[128];
	// fetch all rows back, get the varchar and/ long varchar columns as streams.
	while (rs.next()) {
		// get the first column as an int
		int a = rs.getInt("a");
		// get the second column as a stream
		InputStream fin = rs.getAsciiStream(2);
		int columnSize = 0;
		for (;;) {
			int size = fin.read(buff);
				if (size == -1)
				break;
				columnSize += size;
		}
		if((a>=1 && a <= 5) && columnSize == maxValueAllowed)
			System.out.println("===> verified length " + maxValueAllowed);
		else
			System.out.println("test failed, columnSize should be " + maxValueAllowed + " but it is" + columnSize);
	}
}
 

@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);
	}
}
 

public void testUpdateAsciiStreamLengthlessParameterName()
        throws IOException, SQLException {
    // Array to keep updated data fetched from the database.
    byte[] bytesRet = new byte[10];

    // Input Stream inserted initially.
    InputStream is = new java.io.ByteArrayInputStream(BYTES1);

    // InputStream that is used for update.
    InputStream isForUpdate = new
            java.io.ByteArrayInputStream(BYTES2);

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

    // Update the data.
    ResultSet rs1 = fetchUpd("dLongVarchar", key);
    rs1.next();
    rs1.updateAsciiStream("dLongVarchar", isForUpdate);
    rs1.updateRow();
    rs1.close();

    // Query to see whether the data that has been updated.
    rs1 = fetch("dLongVarchar", key);
    rs1.next();
    InputStream isRet = rs1.getAsciiStream(1);
    isRet.read(bytesRet);
    isRet.close();

    for (int i=0; i < BYTES2.length; i++) {
        assertEquals("Error in updateAsciiStream", BYTES2[i], bytesRet[i]);
    }
    rs1.close();
}
 

public void testGetAsciiStream() 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()){
        InputStream is = rs.getAsciiStream(1);
        int i = 0;
        for(int c = is.read(); c > -1; c = is.read()){
            int exp = (int) TEST_STRING_DATA.charAt(i++);
            if (exp > 255)
                exp  = (int) 0x3f;
            assertEquals(exp,c);

        }
        Statement s = createStatement();
        s.executeUpdate("delete from t3");
    }

    
}
 

public void testUpdateAsciiStreamLengthless()
        throws IOException, SQLException {
    // Array to keep updated data fetched from the database.
    byte[] bytesRet = new byte[10];

    // Input Stream inserted initially.
    InputStream is = new java.io.ByteArrayInputStream(BYTES1);

    // InputStream that is used for update.
    InputStream isForUpdate = new
            java.io.ByteArrayInputStream(BYTES2);

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

    // Update the data.
    ResultSet rs1 = fetchUpd("dLongVarchar", key);
    rs1.next();
    rs1.updateAsciiStream(1, isForUpdate);
    rs1.updateRow();
    rs1.close();

    // Query to see whether the data that has been updated.
    rs1 = fetch("dLongVarchar", key);
    rs1.next();
    InputStream isRet = rs1.getAsciiStream(1);
    isRet.read(bytesRet);
    isRet.close();

    for (int i=0; i < BYTES2.length; i++) {
        assertEquals("Error in updateAsciiStream", BYTES2[i], bytesRet[i]);
    }
    rs1.close();
}
 

private void verifyResultsUsingAsciiStream(ResultSet rs, int col)
        throws Exception
{
    InputStream valueStream;
    String value;

    // First row
    assertTrue("FAIL - row not found", rs.next());
    valueStream = rs.getAsciiStream(col + 1);
    assertFalse("FAIL - value should not be null", rs.wasNull());

    byte[] valueBytes = new byte[LEN_ASCII_VALUE];
    assertEquals("FAIL - wrong length read from stream", LEN_ASCII_VALUE,
            valueStream.read(valueBytes));
    assertEquals("FAIL - wrong value on column " + col,
            ASCII_VALUE, new String(valueBytes, "US-ASCII"));

    // null row
    assertTrue("FAIL - row not found", rs.next());
    value = rs.getString(col + 1);
    assertTrue("FAIL - value should be null", rs.wasNull());

    assertEquals("FAIL - wrong value on column " + col, null, value);

    assertFalse("FAIL - more rows than expected", rs.next());
    rs.close();
}
 

public void testUpdateAsciiStreamLengthlessParameterName()
        throws IOException, SQLException {
    // Array to keep updated data fetched from the database.
    byte[] bytesRet = new byte[10];

    // Input Stream inserted initially.
    InputStream is = new java.io.ByteArrayInputStream(BYTES1);

    // InputStream that is used for update.
    InputStream isForUpdate = new
            java.io.ByteArrayInputStream(BYTES2);

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

    // Update the data.
    ResultSet rs1 = fetchUpd("dLongVarchar", key);
    rs1.next();
    rs1.updateAsciiStream("dLongVarchar", isForUpdate);
    rs1.updateRow();
    rs1.close();

    // Query to see whether the data that has been updated.
    rs1 = fetch("dLongVarchar", key);
    rs1.next();
    InputStream isRet = rs1.getAsciiStream(1);
    isRet.read(bytesRet);
    isRet.close();

    for (int i=0; i < BYTES2.length; i++) {
        assertEquals("Error in updateAsciiStream", BYTES2[i], bytesRet[i]);
    }
    rs1.close();
}
 

public void testUpdateAsciiStreamLengthless()
        throws IOException, SQLException {
    // Array to keep updated data fetched from the database.
    byte[] bytesRet = new byte[10];

    // Input Stream inserted initially.
    InputStream is = new java.io.ByteArrayInputStream(BYTES1);

    // InputStream that is used for update.
    InputStream isForUpdate = new
            java.io.ByteArrayInputStream(BYTES2);

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

    // Update the data.
    ResultSet rs1 = fetchUpd("dLongVarchar", key);
    rs1.next();
    rs1.updateAsciiStream(1, isForUpdate);
    rs1.updateRow();
    rs1.close();

    // Query to see whether the data that has been updated.
    rs1 = fetch("dLongVarchar", key);
    rs1.next();
    InputStream isRet = rs1.getAsciiStream(1);
    isRet.read(bytesRet);
    isRet.close();

    for (int i=0; i < BYTES2.length; i++) {
        assertEquals("Error in updateAsciiStream", BYTES2[i], bytesRet[i]);
    }
    rs1.close();
}
 

public void testGetAsciiStream() 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()){
        InputStream is = rs.getAsciiStream(1);
        int i = 0;
        for(int c = is.read(); c > -1; c = is.read()){
            int exp = (int) TEST_STRING_DATA.charAt(i++);
            if (exp > 255)
                exp  = (int) 0x3f;
            assertEquals(exp,c);

        }
        Statement s = createStatement();
        s.executeUpdate("delete from t3");
    }

    
}
 

static void get_using_ascii_stream(ResultSet rs, int col_no) throws Exception{
	System.out.println("getAsciiStream(" + col_no + ")");
int no_bytes_read = 0;
      InputStream rsbin = rs.getAsciiStream(col_no);
      byte [] bytearray = new byte[200];
int count = 0;
      while((no_bytes_read=rsbin.read(bytearray)) != -1)
      {
	count = printbytearray(bytearray, no_bytes_read, count);
      }
System.out.println("");

  }
 

public void testUpdateAsciiStreamLengthless()
        throws IOException, SQLException {
    // Array to keep updated data fetched from the database.
    byte[] bytesRet = new byte[10];

    // Input Stream inserted initially.
    InputStream is = new java.io.ByteArrayInputStream(BYTES1);

    // InputStream that is used for update.
    InputStream isForUpdate = new
            java.io.ByteArrayInputStream(BYTES2);

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

    // Update the data.
    ResultSet rs1 = fetchUpd("dLongVarchar", key);
    rs1.next();
    rs1.updateAsciiStream(1, isForUpdate);
    rs1.updateRow();
    rs1.close();

    // Query to see whether the data that has been updated.
    rs1 = fetch("dLongVarchar", key);
    rs1.next();
    InputStream isRet = rs1.getAsciiStream(1);
    isRet.read(bytesRet);
    isRet.close();

    for (int i=0; i < BYTES2.length; i++) {
        assertEquals("Error in updateAsciiStream", BYTES2[i], bytesRet[i]);
    }
    rs1.close();
}
 

@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);
	}
}
 

/** Invokes the specified getter on the given result set 1-based index. */
private void invokeGetter(int column, ResultSet rs, int typeIdx, int getter)
        throws SQLException {
    println("invoking " + debugInfo(column, rs, typeIdx, getter));
    Object ret;
    switch (getter) {
        case GET_BYTES:
            ret = rs.getBytes(column);
            break;
        case GET_STRING:
            ret = rs.getString(column);
            break;
        case GET_ASCII_STREAM:
            ret = rs.getAsciiStream(column);
            break;
        case GET_BINARY_STREAM:
            ret = rs.getBinaryStream(column);
            break;
        case GET_CHARACTER_STREAM:
            ret = rs.getCharacterStream(column);
            break;
        case GET_CLOB:
            ret = rs.getClob(column);
            break;
        case GET_BLOB:
            ret = rs.getBlob(column);
            break;
        case GET_OBJECT:
            ret = rs.getObject(column);
            break;
        default:
            fail("unsupported getter index: " + getter);
            // Help the compiler a little.
            throw new IllegalStateException();
    }
    if (rs.wasNull()) {
        assertNull(ret);
    } else {
        assertNotNull(ret);
    }
}
 

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

public void testUpdateAsciiStreamStringParameterName()
throws Exception {
    //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];

    //Input Stream inserted initially
    InputStream is = new java.io.ByteArrayInputStream(BYTES1);

    //InputStream that is used for update
    InputStream is_for_update = new
            java.io.ByteArrayInputStream(BYTES2);

    //Prepared Statement used to insert the data
    PreparedStatement ps_sb = prep("dLongVarchar");
    ps_sb.setInt(1, key);
    ps_sb.setAsciiStream(2,is,BYTES1.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.updateAsciiStream("dLongVarchar",is_for_update,(int)BYTES2.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();
    InputStream is_ret = rs1.getAsciiStream(1);

    is_ret.read(bytes_ret);
    is_ret.close();

    for(int i=0;i<BYTES2.length;i++) {
        assertEquals("Error in updateAsciiStream",BYTES2[i],bytes_ret[i]);
    }
    rs1.close();
}
 

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

public void testUpdateAsciiStream()
throws Exception {
    //create the table
    stmt.execute("create table UpdateTestTable_ResultSet (sno int, " +
            "datacol LONG VARCHAR)");

    //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];

    //Input Stream inserted initially
    InputStream is = new java.io.ByteArrayInputStream(BYTES1);

    //InputStream that is used for update
    InputStream is_for_update = new
            java.io.ByteArrayInputStream(BYTES2);

    //Prepared Statement used to insert the data
    PreparedStatement ps_sb = prepareStatement
            ("insert into UpdateTestTable_ResultSet values(?,?)");
    ps_sb.setInt(1,1);
    ps_sb.setAsciiStream(2,is,BYTES1.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 = stmt.executeQuery
            ("select * from UpdateTestTable_ResultSet for update");
    rs1.next();
    rs1.updateAsciiStream(2,is_for_update,(int)BYTES2.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 = stmt.executeQuery
            ("select * from UpdateTestTable_ResultSet");
    rs1.next();
    InputStream is_ret = rs1.getAsciiStream(2);

    is_ret.read(bytes_ret);
    is_ret.close();

    for(int i=0;i<BYTES2.length;i++) {
        assertEquals("Error in updateAsciiStream",BYTES2[i],bytes_ret[i]);
    }
    rs1.close();
    //delete the table
    stmt .execute("drop table UpdateTestTable_ResultSet");
}
 

public InputStream getAsciiStream(ResultSet r) throws SQLException {
  return r.getAsciiStream(label);
}
 

public InputStream getAsciiStream(ResultSet r) throws SQLException {
  return r.getAsciiStream(ordinal);
}
 

@Override
protected String getResultAsEscapedString( ResultSet resultSet, int index,
                                           String columnTypeName ) throws SQLException, IOException {

    String value;
    Object valueAsObject = resultSet.getObject(index);
    if (valueAsObject == null) {
        return null;
    }
    if (valueAsObject != null && valueAsObject.getClass().isArray()) {
        if (! (valueAsObject instanceof byte[])) {
            // FIXME other array types might be needed to be tracked in a different way
            log.warn("Array type that needs attention");
        }
        // we have an array of primitive data type
        InputStream is = null;
        try {
            is = resultSet.getAsciiStream(index);
            value = IoUtils.streamToString(is);
        } finally {
            IoUtils.closeStream(is);
        }
    } else if (valueAsObject instanceof Blob) {
        // we have a blob
        log.debug("Blob detected. Will try to dump as hex");
        Blob blobValue = (Blob) valueAsObject;
        InputStream blobInputStream = blobValue.getBinaryStream();
        StringBuilder hexString = new StringBuilder();
        // Read the binary data from the stream and convert it to hex according to the sample from
        // '\x123ABC', according to https://www.postgresql.org/docs/current/datatype-binary.html,
        // Section 8.4.1. bytea Hex Format
        hexString.append("\\x");
        hexString = addBinDataAsHexAndCloseStream(hexString, blobInputStream);
        value = hexString.toString();
    } else {
        // treat as a string
        value = resultSet.getString(index);
        logDebugInfoForDBValue(value, index, resultSet);
    }

    return value;
}