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

public static String clobToDescription(Clob clobValue) {
    StringBuilder clobDescription = new StringBuilder("<CLOB ");    //NOI18N

    try {
        long size = clobValue.length();
        if (size < 1000) {
            clobDescription.append(String.format("%1$d Chars", size)); //NOI18N
        } else if (size < 1000000) {
            clobDescription.append(String.format("%1$d kChars", size / 1000)); //NOI18N
        } else {
            clobDescription.append(String.format("%1$d MChars", size
                    / 1000000)); //NOI18N
        }
    } catch (SQLException ex) {
        clobDescription.append("of unknown size");                  //NOI18N
    }
    clobDescription.append(">");

    return clobDescription.toString();
}
 

protected String getStringFromClob(Clob profile) throws SQLException{
  String clob = null;
  if (profile != null) {
    if (profile.length() == 0) clob = "empty";
    else {
      BufferedReader reader = new BufferedReader(profile.getCharacterStream());
      clob = ResultSetHelper.convertCharArrayToString(
          reader, (int)profile.length());
      try {
        reader.close();
      } catch (IOException e) {
        throw new TestException("could not close the BufferedReader" + 
            TestHelper.getStackTrace(e));
      }
    }          
  }
  return clob;
}
 

/**
 * @see java.sql.PreparedStatement#setClob(int, java.sql.Clob)
 */
@Override
public void setClob(int parameterIndex, Clob x) throws SQLException {
    synchronized (checkClosed().getConnectionMutex()) {

        if (x == null) {
            setNull(parameterIndex, java.sql.Types.BINARY);
        } else {
            BindValue binding = getBinding(parameterIndex, true);
            resetToType(binding, MysqlDefs.FIELD_TYPE_BLOB);

            binding.value = x.getCharacterStream();
            binding.isLongData = true;

            if (this.connection.getUseStreamLengthsInPrepStmts()) {
                binding.bindLength = x.length();
            } else {
                binding.bindLength = -1;
            }
        }
    }
}
 

protected String getStringFromClob(Clob profile) throws SQLException{
  String clob = null;
  if (profile != null) {
    if (profile.length() == 0) clob = "empty";
    else {
      BufferedReader reader = new BufferedReader(profile.getCharacterStream());
      clob = ResultSetHelper.convertCharArrayToString(
          reader, (int)profile.length());
      try {
        reader.close();
      } catch (IOException e) {
        throw new TestException("could not close the BufferedReader" + 
            TestHelper.getStackTrace(e));
      }
    }          
  }
  return clob;
}
 

/**
 * Tests that the LOB objects are not closed when closing the result set.
 *
 * @throws SQLException if something causes the test to fail
 */
public void testBlobClobStateAfterCloseOnScrollable()
        throws SQLException {
    getConnection().setAutoCommit(false);
    Statement stmt = createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
        ResultSet.CONCUR_READ_ONLY);
    ResultSet rs = stmt.executeQuery(
            "select dBlob, dClob from LOBLOC_NO_NULLS");
    rs.next();
    rs.relative(5);
    Blob b = rs.getBlob(1);
    final long blobLength = b.length();
    rs.next();
    Clob c = rs.getClob(2);
    final long clobLength = c.length();
    rs.first();
    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);
    }
}
 

private String extractData(Clob clob) throws Throwable {
	if ( getDialect() instanceof H2Dialect ) {
		return clob.getSubString( 1, ( int ) clob.length() );
	}
	else {
		char[] data = new char[ (int) clob.length() ];
		clob.getCharacterStream().read( data );
		return new String( data );
	}
}
 

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

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

public void setClob(OptimizedElementArray row, int columnIndex, Clob x)
    throws SQLException {
  long len = x.length();
  if (len <= Integer.MAX_VALUE) {
    setString(row, columnIndex, x.getSubString(1, (int)len));
  }
  else {
    throw ThriftExceptionUtil.newSQLException(
        SQLState.BLOB_TOO_LARGE_FOR_CLIENT, null, len, Integer.MAX_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 String getNullableResult(ResultSet rs, String columnName)
    throws SQLException {
  String value = "";
  Clob clob = rs.getClob(columnName);
  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 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;
}
 

private ClobChunk handleClob(Clob clob, ConnectionHolder connHolder,
    StatementAttrs attrs) throws SQLException {
  final long length = clob.length();
  if (length > Integer.MAX_VALUE) {
    throw Util.generateCsSQLException(SQLState.BLOB_TOO_LARGE_FOR_CLIENT,
        Long.toString(length), Long.toString(Integer.MAX_VALUE));
  }
  ClobChunk chunk = new ClobChunk().setOffset(0)
      .setTotalLength(length);
  final int chunkSize;
  if (attrs != null && attrs.isSetLobChunkSize()) {
    chunkSize = attrs.lobChunkSize;
  }
  else {
    chunkSize = gfxdConstants.DEFAULT_LOB_CHUNKSIZE;
  }
  if (chunkSize > 0 && chunkSize < length) {
    chunk.setChunk(clob.getSubString(1, chunkSize)).setLast(false);
    // need to add explicit mapping for the LOB in this case
    int lobId;
    if (clob instanceof EngineLOB) {
      lobId = ((EngineLOB)clob).getLocator();
    }
    else {
      lobId = connHolder.getConnection().addLOBMapping(clob);
    }
    chunk.setLobId(lobId);
  }
  else {
    chunk.setChunk(clob.getSubString(1, (int)length)).setLast(true);
  }
  return chunk;
}
 

/**
    * JDBC 2.0
    *
    * Set a CLOB parameter.
    *
    * @param i the first parameter is 1, the second is 2, ...
    * @param x an object representing a CLOB
    */
   public void setClob (int i, Clob x)
       throws SQLException
   {
       checkClobConditions(i);
	if (x == null)
		setNull(i, Types.CLOB);
	else
       {
           // 1. max number of characters that can be inserted into a clob column
           // is 2Gb-1 which is Integer.MAX_INT.
           // This means that we do not allow any inserts of clobs where
           // clob.length() > Integer.MAX_INT. For now, we cast the x.length()
           // to int as a result. This will work ok for valid clob values that
           // derby supports. If we ever decide to increase these limits for clobs, in that
           // case the cast of x.Length() to int would not be appropriate.
           // 2. Note, x.length() needs to be called before retrieving the
           // stream using x.getCharacterStream() because EmbedClob.length()
           // will read from the stream and drain the stream. 
           // Hence the need to declare this local variable - streamLength
           long streamLength = x.length();

           setCharacterStreamInternal(i, x.getCharacterStream(),
                                      false, streamLength);
       }
       
}
 

/**
 * Generic setObject method.
 *
 * @param parameterIndex Parameter index 1 to n.
 * @param x The value to set.
 * @param targetSqlType The java.sql.Types constant describing the data.
 * @param scale The decimal scale -1 if not set.
 */
public void setObjectBase(int parameterIndex, Object x, int targetSqlType, int scale)
        throws SQLException {
    checkOpen();

    int length = 0;

    if (targetSqlType == java.sql.Types.CLOB) {
        targetSqlType = java.sql.Types.LONGVARCHAR;
    } else if (targetSqlType == java.sql.Types.BLOB) {
        targetSqlType = java.sql.Types.LONGVARBINARY;
    }

    if (x != null) {
        x = Support.convert(this, x, targetSqlType, connection.getCharset());

        if (scale >= 0) {
            if (x instanceof BigDecimal) {
                x = ((BigDecimal) x).setScale(scale, BigDecimal.ROUND_HALF_UP);
            } else if (x instanceof Number) {
                synchronized (f) {
                    f.setGroupingUsed(false);
                    f.setMaximumFractionDigits(scale);
                    x = Support.convert(this, f.format(x), targetSqlType,
                            connection.getCharset());
                }
            }
        }

        if (x instanceof Blob) {
            Blob blob = (Blob) x;
            length = (int) blob.length();
            x = blob.getBinaryStream();
        } else if (x instanceof Clob) {
            Clob clob = (Clob) x;
            length = (int) clob.length();
            x = clob.getCharacterStream();
        }
    }

    setParameter(parameterIndex, x, targetSqlType, scale, length);
}
 

private void selectUpdateClob(String testId,
        PreparedStatement ps, int cloblen, int id, int updateId) throws Exception {
    println("========================================");
    println("START " + testId + " - select and then update clob of size= "
            + cloblen + " - Uses setClob api");

    ps.setInt(1, id);
    ResultSet rs = ps.executeQuery();
    rs.next();
    Clob value = rs.getClob(1);
    long l = value.length();
    long dlen = rs.getLong(2);
    assertEquals("FAIL - MISMATCH LENGTHS GOT " + l + " expected "
            + dlen + " for row in CLOBTBL with ID=" + id, dlen, l);
    // DERBY-5317 cannot use setCharacterStream with value from
    // Clob.getCharacterStream because server will try to stream
    // lob to and from server at the same time. setClob can be
    // used as a work around.
    if (!usingDerbyNetClient()) {
        PreparedStatement psUpd =
                prepareStatement("update CLOBTBL set content=?, " +
                        "dlen =? where id = ?");
        psUpd.setCharacterStream(1, value.getCharacterStream(), (int) l);
        psUpd.setLong(2, l);
        psUpd.setInt(3, updateId);

        assertUpdateCount(psUpd, 1);
    }
    commit();

    // now select and verify that update went through ok.
    ps.setInt(1, updateId);
    ResultSet rs2 = ps.executeQuery();
    rs2.next();
    Clob updatedValue = rs2.getClob(1);
    assertEquals(
            "FAIL - Retrieving the updated clob length does not match " +
                    "expected length = " + l + " found = "
                    + updatedValue.length(), l,
            updatedValue.length());

    commit();

    // close resultsets
    rs.close();
    rs2.close();
    println("========================================");
}
 

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

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

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

    PreparedStatement psUpd =
           prepareStatement("update CLOBTBL set content=?,dlen =? " +
                            "where id = ?");
    psUpd.setClob(1, value);
    psUpd.setLong(2, l);
    psUpd.setInt(3, updateId);

    assertUpdateCount(psUpd, 1);
    commit();

    // now select and verify that update went through ok.
    sel.setInt(1, updateId);
    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);
    commit();

    // close resultsets
    rs.close();
    rs2.close();
    println("========================================");

}
 

/**
 * Converts a CLOB to a string.
 *
 * @param clob the CLOB to be converted
 * @return the extracted string value
 * @throws SQLException if an error occurs
 */
private static Object convertClob(final Clob clob) throws SQLException
{
    final int len = (int) clob.length();
    return len > 0 ? clob.getSubString(1, len) : StringUtils.EMPTY;
}