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

@Override
public String getNullableResult(CallableStatement cs, int columnIndex)
    throws SQLException {
  String value = "";
  Clob clob = cs.getClob(columnIndex);
  if (clob != null) {
    int size = (int) clob.length();
    value = clob.getSubString(1, size);
  }
  return value;
}
 

private void compareClobs( Clob left, Clob right ) throws Exception
{
    long leftLength = left.length();
    long rightLength = right.length();

    println( "Left clob has " + leftLength + " characters and right clob has " + rightLength + " characters." );

    assertEquals( leftLength, rightLength );

    if ( leftLength == rightLength );
    {
        String leftString = left.getSubString( 1L, (int) leftLength );
        String rightString = right.getSubString( 1L, (int) rightLength );

        for ( int i = 0; i < leftLength; i++ )
        {
            int leftIdx = (int) i;
            int rightIdx = (int) ((leftLength - leftIdx) - 1);
            char leftC = leftString.charAt( leftIdx );
            char rightC = rightString.charAt( rightIdx );

            if ( leftC != rightC )
            {
                println( "left[ " + leftIdx+ " ] = " + leftC + " but right[ " + rightIdx + " ] = " + rightC );
                return;
            }

            assertEquals( leftC, rightC );
        }
    }

}
 

@Override
public String getNullableResult(CallableStatement cs, int columnIndex) throws SQLException {
	String value = "";
	Clob clob = cs.getClob(columnIndex);
	if (clob != null) {
		int size = (int) clob.length();
		value = clob.getSubString(1, size);
	}
	return value;
}
 

@Override
public String getNullableResult(ResultSet rs, int columnIndex)
    throws SQLException {
  String value = "";
  Clob clob = rs.getClob(columnIndex);
  if (clob != null) {
    int size = (int) clob.length();
    value = clob.getSubString(1, size);
  }
  return value;
}
 

public Object getSource(String id, ResultSet rs) throws SQLException
{
	ResultSetMetaData metadata = rs.getMetaData();
	String rv = null;
	switch(metadata.getColumnType(1))
	{
	case Types.BLOB:
		Blob blob = rs.getBlob(1);
		if(blob != null)
		{
			rv = new String(blob.getBytes(1L, (int) blob.length()));
		}
		break;
	case Types.CLOB:
		Clob clob = rs.getClob(1);
		if(clob != null)
		{
			rv = clob.getSubString(1L, (int) clob.length());
		}
		break;
	case Types.CHAR:
	case Types.LONGVARCHAR:
	case Types.VARCHAR:
	case Types.BINARY:
	case Types.VARBINARY:
	case Types.LONGVARBINARY:
		byte[] bytes = rs.getBytes(1);
		if(bytes != null)
		{
			rv = new String(bytes);
		}
		break;
	}
	return rv;
}
 

private void compareClobs( Clob left, Clob right ) throws Exception
{
    long leftLength = left.length();
    long rightLength = right.length();

    println( "Left clob has " + leftLength + " characters and right clob has " + rightLength + " characters." );

    assertEquals( leftLength, rightLength );

    if ( leftLength == rightLength );
    {
        String leftString = left.getSubString( 1L, (int) leftLength );
        String rightString = right.getSubString( 1L, (int) rightLength );

        for ( int i = 0; i < leftLength; i++ )
        {
            int leftIdx = (int) i;
            int rightIdx = (int) ((leftLength - leftIdx) - 1);
            char leftC = leftString.charAt( leftIdx );
            char rightC = rightString.charAt( rightIdx );

            if ( leftC != rightC )
            {
                println( "left[ " + leftIdx+ " ] = " + leftC + " but right[ " + rightIdx + " ] = " + rightC );
                return;
            }

            assertEquals( leftC, rightC );
        }
    }

}
 

public Object getSource(String id, ResultSet rs) throws SQLException
{
	ResultSetMetaData metadata = rs.getMetaData();
	String rv = null;
	switch(metadata.getColumnType(1))
	{
	case Types.BLOB:
		Blob blob = rs.getBlob(1);
		if(blob != null)
		{
			rv = new String(blob.getBytes(1L, (int) blob.length()));
		}
		break;
	case Types.CLOB:
		Clob clob = rs.getClob(1);
		if(clob != null)
		{
			rv = clob.getSubString(1L, (int) clob.length());
		}
		break;
	case Types.CHAR:
	case Types.LONGVARCHAR:
	case Types.VARCHAR:
	case Types.BINARY:
	case Types.VARBINARY:
	case Types.LONGVARBINARY:
		byte[] bytes = rs.getBytes(1);
		if(bytes != null)
		{
			rv = new String(bytes);
		}
		break;
	}
	return rv;
}
 

@Override
public String getNullableResult(ResultSet rs, int columnIndex)
    throws SQLException {
  String value = "";
  Clob clob = rs.getClob(columnIndex);
  if (clob != null) {
    int size = (int) clob.length();
    value = clob.getSubString(1, size);
  }
  return value;
}
 

/**
 * Retrieve a JDBC column value from a ResultSet, using the most appropriate
 * value type. The returned value should be a detached value object, not having
 * any ties to the active ResultSet: in particular, it should not be a Blob or
 * Clob object but rather a byte array or String representation, respectively.
 * <p>Uses the {@code getObject(index)} method, but includes additional "hacks"
 * to get around Oracle 10g returning a non-standard object for its TIMESTAMP
 * datatype and a {@code java.sql.Date} for DATE columns leaving out the
 * time portion: These columns will explicitly be extracted as standard
 * {@code java.sql.Timestamp} object.
 * @param rs is the ResultSet holding the data
 * @param index is the column index
 * @return the value object
 * @throws SQLException if thrown by the JDBC API
 * @see java.sql.Blob
 * @see java.sql.Clob
 * @see java.sql.Timestamp
 */
public static Object getResultSetValue(ResultSet rs, int index) throws SQLException {
	Object obj = rs.getObject(index);
	String className = null;
	if (obj != null) {
		className = obj.getClass().getName();
	}
	if (obj instanceof Blob) {
		Blob blob = (Blob) obj;
		obj = blob.getBytes(1, (int) blob.length());
	}
	else if (obj instanceof Clob) {
		Clob clob = (Clob) obj;
		obj = clob.getSubString(1, (int) clob.length());
	}
	else if ("oracle.sql.TIMESTAMP".equals(className) || "oracle.sql.TIMESTAMPTZ".equals(className)) {
		obj = rs.getTimestamp(index);
	}
	else if (className != null && className.startsWith("oracle.sql.DATE")) {
		String metaDataClassName = rs.getMetaData().getColumnClassName(index);
		if ("java.sql.Timestamp".equals(metaDataClassName) || "oracle.sql.TIMESTAMP".equals(metaDataClassName)) {
			obj = rs.getTimestamp(index);
		}
		else {
			obj = rs.getDate(index);
		}
	}
	else if (obj instanceof java.sql.Date) {
		if ("java.sql.Timestamp".equals(rs.getMetaData().getColumnClassName(index))) {
			obj = rs.getTimestamp(index);
		}
	}
	return obj;
}
 

@Override
public String getNullableResult(ResultSet rs, int columnIndex)
    throws SQLException {
  String value = "";
  Clob clob = rs.getClob(columnIndex);
  if (clob != null) {
    int size = (int) clob.length();
    value = clob.getSubString(1, size);
  }
  return value;
}
 

@Override
public String getNullableResult(CallableStatement cs, int columnIndex)
    throws SQLException {
  String value = "";
  Clob clob = cs.getClob(columnIndex);
  if (clob != null) {
    int size = (int) clob.length();
    value = clob.getSubString(1, size);
  }
  return value;
}
 

@Override
public Object preChange(Object val) throws SQLException {
    if (val instanceof Clob) {
        Clob clob = ((Clob) val);
        return clob.getSubString(1, (int) clob.length());
    } else if (val instanceof Byte) {
        return ((Byte) val).byteValue() > 0;
    } else {
        return val;
    }
}
 

@Override
public String getClobAsString(ResultSet rs, int columnIndex) throws SQLException {
	logger.debug("Returning CLOB as string");
	if (this.wrapAsLob) {
		Clob clob = rs.getClob(columnIndex);
		return clob.getSubString(1, (int) clob.length());
	}
	else {
		return rs.getString(columnIndex);
	}
}
 

/**
 * Tests that the LOB objects are not closed when closing the result set.
 *
 * @throws SQLException if something causes the test to fail
 */
public void testBlobClobStateForwardOnlyWithNoNulls()
        throws SQLException {
    getConnection().setAutoCommit(false);
    Statement stmt = createStatement();
    ResultSet rs = stmt.executeQuery(
            "select dBlob, dClob from LOBLOC_NO_NULLS");
    rs.next();
    Blob b = rs.getBlob(1);
    final long blobLength = b.length();
    rs.next();
    Clob c = rs.getClob(2);
    final long clobLength = c.length();
    rs.next();
    rs.close();
    // The LOB objects should still be usable.
    assertEquals(blobLength, b.length());
    assertEquals(clobLength, c.length());
    commit();
    try {
        // This should fail because the locator has been released.
        c.getSubString(1, 9);
        fail("Locator should have been released, causing the call to fail");
    } catch (SQLException sqle) {
        assertSQLState("XJ215", sqle);
    }
}
 

@Override
public Object preChange(Object val) throws SQLException {
    if(val instanceof Clob){
        Clob clob = ((Clob) val);
        return clob.getSubString(1,(int)clob.length());
    } else{
        return val;
    }
}
 

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

public List<ErrorReport> listReports(
    boolean withText,
    @Nullable final Date since,
    @Nullable final Date to,
    final Set<String> ids
)
{
    ArrayList<ErrorReport> errors = new ArrayList<>();
    String where = "1=1";

    if (!ids.isEmpty()) {
        where += " AND ERROR_ID IN ('" + String.join("','", ids) + "')";
    }

    if (since != null)
    {
        where += " AND DATETIME >= '" + since.toString() + "'";
    }

    if (to != null)
    {
        where += " AND DATETIME <= '" + to.toString() + "'";
    }

    final String stmtStr = "SELECT" +
        " INSTANCE_EPOCH, ERROR_NR, NODE, MODULE, ERROR_ID, DATETIME, VERSION, PEER," +
        " EXCEPTION, EXCEPTION_MESSAGE, ORIGIN_FILE, ORIGIN_METHOD, ORIGIN_LINE, TEXT" +
        " FROM ERRORS" +
        " WHERE " + where +
        " ORDER BY DATETIME";
    try (Connection con = dataSource.getConnection();
         Statement stmt = con.createStatement();
         ResultSet rslt = stmt.executeQuery(stmtStr)) {
        while(rslt.next()) {
            String text = null;
            if (withText) {
                Clob clob = rslt.getClob("TEXT");
                // this is how you get the whole string back from a CLOB
                text = clob.getSubString(1, (int)clob.length());
            }
            errors.add(new ErrorReport(
                rslt.getString("NODE"),
                Node.Type.getByValue(rslt.getInt("MODULE")),
                "ErrorReport-" + rslt.getString("ERROR_ID") + ".log",
                rslt.getString("VERSION"),
                rslt.getString("PEER"),
                rslt.getString("EXCEPTION"),
                rslt.getString("EXCEPTION_MESSAGE"),
                rslt.getString("ORIGIN_FILE"),
                rslt.getString("ORIGIN_METHOD"),
                rslt.getInt("ORIGIN_LINE"),
                rslt.getTimestamp("DATETIME"),
                text
            ));
        }
    } catch(SQLException sqlExc) {
        errorReporter.logError("Unable to operate on error-reports database: " + sqlExc.getMessage());
    }

    return errors;
}
 

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

/**
 * Converts a DB clob into a string
 *
 * @param clob the source
 * @return     the extracted string
 */
public static String clob2string(Clob clob) throws SQLException {
	return clob == null ? "" : clob.getSubString(1, (int) clob.length());
}
 

public  static  String  varchar_Clob_Clob( Clob a ) throws Exception { return a.getSubString( 1L, (int) a.length() ); }