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

@Test
public void testClob() throws Exception {
    String clobString = "A TEST OF CLOB INSERT...";
    PreparedStatement ps = conn.prepareStatement("INSERT INTO documents VALUES (?, ?)");
    Clob clob = conn.createClob();
    clob.setString(1,clobString);
    ps.setInt(1, 100);
    ps.setClob(2, clob);
    ps.execute();

    ps = conn.prepareStatement("SELECT c FROM documents where id=100");
    ResultSet rs = ps.executeQuery();
    while (rs.next()) {
        String s = rs.getString(1);
        Assert.assertEquals(s, clobString, s);
    }
}
 

/**
 * Tests fix for BUG#20453712 - CLOB.SETSTRING() WITH VALID INPUT RETURNS EXCEPTION
 * server-side prepared statements and streaming BINARY data.
 * 
 * @throws Exception
 *             if the test fails.
 */
public void testBug20453712() throws Exception {
    final String s1 = "NewClobData";
    this.rs = this.stmt.executeQuery("select 'a'");
    this.rs.next();
    final Clob c1 = this.rs.getClob(1);

    // check with wrong position
    assertThrows(SQLException.class, "Starting position can not be < 1", new Callable<Void>() {
        public Void call() throws Exception {
            c1.setString(0, s1, 7, 4);
            return null;
        }
    });

    // check with wrong substring index
    assertThrows(SQLException.class, "String index out of range: 12", new Callable<Void>() {
        public Void call() throws Exception {
            c1.setString(1, s1, 8, 4);
            return null;
        }
    });

    // full replace
    c1.setString(1, s1, 3, 4);
    assertEquals("Clob", c1.getSubString(1L, (int) c1.length()));

    // add
    c1.setString(5, s1, 7, 4);
    assertEquals("ClobData", c1.getSubString(1L, (int) c1.length()));

    // replace middle chars
    c1.setString(2, s1, 7, 4);
    assertEquals("CDataata", c1.getSubString(1L, (int) c1.length()));
}
 

@Override
public Clob createClob(String string) {
	try {
		final Clob clob = createClob();
		clob.setString( 1, string );
		return clob;
	}
	catch ( SQLException e ) {
		throw new JDBCException( "Unable to set CLOB string after creation", e );
	}
}
 

/**
 * Use this to generate a Clob object populated with random data.
 *
 * @param conn The thin client driver Connection to GemFireXD, used to create the clob.
 * @param columnLength A String representing the length of the column. Format: nK, nM, or nG.
 * @param dataPrefix A String representing what to prefix the Clob field's value with.
 *
 * @return The newly created Clob Object.
 */
private static Clob buildRandomClobObject(Connection conn, String columnLength, String dataPrefix) {
  // Setup all the randomness
  long colLenBytes = getNbrBytes(columnLength);
  int firstThird = (int) (colLenBytes * 0.33);
  int secondThird = (int) (colLenBytes * 0.66);
  GsRandom rand = TestConfig.tab().getRandGen();
  long desiredDataLen;
  int randInt = rand.nextInt(1, 100);
  if (randInt <= 25) { // use a value from the first third
    desiredDataLen = rand.nextLong(1, firstThird);
  } else if (randInt <= 50) { // use a value from the second third
    desiredDataLen = rand.nextLong(firstThird + 1, secondThird);
  } else if (randInt <= 75) { // use a value from the last third
    desiredDataLen = rand.nextLong(secondThird + 1, colLenBytes);
  } else { // use the full size
    desiredDataLen = colLenBytes;
  }

  // Now fill the clob with random data
  Clob clobObj;
  try {
    RandomValues rv = new RandomValues();
    RandomValues.setPrintableChars(true);
    clobObj = conn.createClob();
    String clobValue = dataPrefix + rv.getRandom_String('\'', (desiredDataLen - dataPrefix.length()));
    clobObj.setString(1, clobValue);
    logWriter.fine("BackupRestoreBigDataTest.buildRandomClobObject-Created CLOB of size " + desiredDataLen +
                   " for a CLOB field of size " + columnLength);
  } catch (SQLException e) {
    throw new TestException(TestHelper.getStackTrace(e));
  }
  return clobObj;
}
 

protected void addBatchInsert(PreparedStatement stmt, int oid, int cid, int sid, int qty,
    String status, Timestamp time, BigDecimal bid, int tid, boolean isPut) throws SQLException {
  
  JSONObject json = new JSONObject();
  String jsonLog ="";
  
  if (SQLTest.hasJSON &&  ! SQLHelper.isDerbyConn(stmt.getConnection()) ) {
         json = getJSONObject(oid,cid,sid,qty,status,time,bid,tid);
         jsonLog = ",JSON_DETAILS: " +json.toJSONString();
  }
  
  Log.getLogWriter().info( (SQLHelper.isDerbyConn(stmt.getConnection())? "Derby - " :"gemfirexd - "  ) +  (isPut ? "putting " : "inserting ") + " into trade.buyorders with data OID:" + oid +
      ",CID:"+ cid + ",SID:" + sid + ",QTY:" + qty + ",STATUS:" + status +
      ",TIME:"+ time + ",BID:" + bid + ",TID:" + tid + jsonLog);
  
  stmt.setInt(1, oid);
  stmt.setInt(2, cid);
  stmt.setInt(3, sid);
  stmt.setInt(4, qty);
  stmt.setBigDecimal(5, bid);
  stmt.setTimestamp(6, time);
  stmt.setString(7, status);       
  stmt.setInt(8, tid);
  if (SQLTest.hasJSON &&  ! SQLHelper.isDerbyConn(stmt.getConnection()) ) {  Clob jsonClob = stmt.getConnection().createClob();
  jsonClob.setString(1, json.toJSONString());
  stmt.setClob(9, jsonClob); }
  stmt.addBatch();
}
 

/**
 * Tests fix for BUG#20453712 - CLOB.SETSTRING() WITH VALID INPUT RETURNS EXCEPTION
 * server-side prepared statements and streaming BINARY data.
 * 
 * @throws Exception
 *             if the test fails.
 */
public void testBug20453712() throws Exception {
    final String s1 = "NewClobData";
    this.rs = this.stmt.executeQuery("select 'a'");
    this.rs.next();
    final Clob c1 = this.rs.getClob(1);

    // check with wrong position
    assertThrows(SQLException.class, "Starting position can not be < 1", new Callable<Void>() {
        public Void call() throws Exception {
            c1.setString(0, s1, 7, 4);
            return null;
        }
    });

    // check with wrong substring index
    assertThrows(SQLException.class, "String index out of range: 12", new Callable<Void>() {
        public Void call() throws Exception {
            c1.setString(1, s1, 8, 4);
            return null;
        }
    });

    // full replace
    c1.setString(1, s1, 3, 4);
    assertEquals("Clob", c1.getSubString(1L, (int) c1.length()));

    // add
    c1.setString(5, s1, 7, 4);
    assertEquals("ClobData", c1.getSubString(1L, (int) c1.length()));

    // replace middle chars
    c1.setString(2, s1, 7, 4);
    assertEquals("CDataata", c1.getSubString(1L, (int) c1.length()));
}
 

@Override
public void setClobAsString(PreparedStatement ps, int paramIndex, String content)
		throws SQLException {

	Clob clob = ps.getConnection().createClob();
	clob.setString(1, content);

	this.temporaryClobs.add(clob);
	ps.setClob(paramIndex, clob);

	if (logger.isDebugEnabled()) {
		logger.debug(content != null ? "Copied string into temporary CLOB with length " + content.length() :
				"Set CLOB to null");
	}
}
 

protected void addBatchInsert(PreparedStatement stmt, int oid, int cid, int sid, int qty,
    String status, Timestamp time, BigDecimal bid, int tid, boolean isPut) throws SQLException {
  
  JSONObject json = new JSONObject();
  String jsonLog ="";
  
  if (SQLTest.hasJSON &&  ! SQLHelper.isDerbyConn(stmt.getConnection()) ) {
         json = getJSONObject(oid,cid,sid,qty,status,time,bid,tid);
         jsonLog = ",JSON_DETAILS: " +json.toJSONString();
  }
  
  Log.getLogWriter().info( (SQLHelper.isDerbyConn(stmt.getConnection())? "Derby - " :"gemfirexd - "  ) +  (isPut ? "putting " : "inserting ") + " into trade.buyorders with data OID:" + oid +
      ",CID:"+ cid + ",SID:" + sid + ",QTY:" + qty + ",STATUS:" + status +
      ",TIME:"+ time + ",BID:" + bid + ",TID:" + tid + jsonLog);
  
  stmt.setInt(1, oid);
  stmt.setInt(2, cid);
  stmt.setInt(3, sid);
  stmt.setInt(4, qty);
  stmt.setBigDecimal(5, bid);
  stmt.setTimestamp(6, time);
  stmt.setString(7, status);       
  stmt.setInt(8, tid);
  if (SQLTest.hasJSON &&  ! SQLHelper.isDerbyConn(stmt.getConnection()) ) {  Clob jsonClob = stmt.getConnection().createClob();
  jsonClob.setString(1, json.toJSONString());
  stmt.setClob(9, jsonClob); }
  stmt.addBatch();
}
 

@Override
public void setClobAsString(PreparedStatement ps, int paramIndex, String content)
		throws SQLException {

	Clob clob = ps.getConnection().createClob();
	clob.setString(1, content);

	this.temporaryClobs.add(clob);
	ps.setClob(paramIndex, clob);

	if (logger.isDebugEnabled()) {
		logger.debug(content != null ? "Copied string into temporary CLOB with length " + content.length() :
				"Set CLOB to null");
	}
}
 

public PreparedStatement createInsertStatement(Connection conn, String tableName,
    AbstractEntity entity) throws Exception {
  if (!insertSqlMap.containsKey(tableName)) {
    String insertSql = generateInsertSql(tableName,
        entityMappingHolder.columnInfoPerTable.get(tableName.toLowerCase()));
    insertSqlMap.put(tableName, insertSql);
    LOG.debug(insertSql);
  }

  String sql = insertSqlMap.get(tableName);
  PreparedStatement preparedStatement =
      conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
  LinkedHashMap<String, ColumnInfo> columnInfoMap =
      entityMappingHolder.columnInfoPerTable.get(tableName);
  int parameterIndex = 1;
  for (ColumnInfo columnInfo : columnInfoMap.values()) {
    if (columnInfo.field != null
        && !AUTO_UPDATE_COLUMN_SET.contains(columnInfo.columnNameInDB.toLowerCase())) {
      Object val = columnInfo.field.get(entity);
      LOG.debug("Setting value: {} for:{} sqlType:{}", val, columnInfo.columnNameInDB,
          columnInfo.sqlType);
      if (val != null) {
        if (columnInfo.sqlType == Types.CLOB) {
          Clob clob = conn.createClob();
          clob.setString(1, val.toString());
          preparedStatement.setClob(parameterIndex++, clob);
        } else if (columnInfo.sqlType == Types.TIMESTAMP) {
          preparedStatement.setObject(parameterIndex++, val, columnInfo.sqlType);
        } else {
          preparedStatement.setObject(parameterIndex++, val.toString(), columnInfo.sqlType);
        }

      } else {
        preparedStatement.setNull(parameterIndex++, columnInfo.sqlType);
      }
    }
  }
  return preparedStatement;

}
 

/**
 * Use this to generate a Clob object populated with random data.
 *
 * @param conn The thin client driver Connection to GemFireXD, used to create the clob.
 * @param columnLength A String representing the length of the column. Format: nK, nM, or nG.
 * @param dataPrefix A String representing what to prefix the Clob field's value with.
 *
 * @return The newly created Clob Object.
 */
private static Clob buildRandomClobObject(Connection conn, String columnLength, String dataPrefix) {
  // Setup all the randomness
  long colLenBytes = getNbrBytes(columnLength);
  int firstThird = (int) (colLenBytes * 0.33);
  int secondThird = (int) (colLenBytes * 0.66);
  GsRandom rand = TestConfig.tab().getRandGen();
  long desiredDataLen;
  int randInt = rand.nextInt(1, 100);
  if (randInt <= 25) { // use a value from the first third
    desiredDataLen = rand.nextLong(1, firstThird);
  } else if (randInt <= 50) { // use a value from the second third
    desiredDataLen = rand.nextLong(firstThird + 1, secondThird);
  } else if (randInt <= 75) { // use a value from the last third
    desiredDataLen = rand.nextLong(secondThird + 1, colLenBytes);
  } else { // use the full size
    desiredDataLen = colLenBytes;
  }

  // Now fill the clob with random data
  Clob clobObj;
  try {
    RandomValues rv = new RandomValues();
    RandomValues.setPrintableChars(true);
    clobObj = conn.createClob();
    String clobValue = dataPrefix + rv.getRandom_String('\'', (desiredDataLen - dataPrefix.length()));
    clobObj.setString(1, clobValue);
    logWriter.fine("BackupRestoreBigDataTest.buildRandomClobObject-Created CLOB of size " + desiredDataLen +
                   " for a CLOB field of size " + columnLength);
  } catch (SQLException e) {
    throw new TestException(TestHelper.getStackTrace(e));
  }
  return clobObj;
}
 

/**
 * Load only one record
 */
public static void loadAllTypesData(Connection conn, String tablename) throws SQLException {
    try (PreparedStatement statement = conn
            .prepareStatement("insert into " + tablename + " values(?,?,?,?,?,?,?,?,?,?,?,?,?,?)")) {
        statement.setShort(1, (short) 32767);
        statement.setInt(2, 2147483647);
        statement.setLong(3, 9223372036854775807l);
        statement.setFloat(4, 1.11111111f);
        statement.setDouble(5, 2.222222222);
        statement.setBigDecimal(6, new BigDecimal("1234567890.1234567890"));
        statement.setString(7, "abcd");
        statement.setString(8, "abcdefg");

        Blob blob = conn.createBlob();
        byte[] bytes = { 0, 1, 2, 3, 4, 5, 6, 7, 8, 9 };
        blob.setBytes(1, bytes);
        statement.setBlob(9, blob);

        Clob clob = conn.createClob();
        clob.setString(1, "abcdefg");
        statement.setClob(10, clob);

        statement.setDate(11, Date.valueOf("2016-12-28"));
        statement.setTime(12, Time.valueOf("14:30:33"));
        statement.setTimestamp(13, Timestamp.valueOf("2016-12-28 14:31:56.12345"));
        statement.setBoolean(14, true);

        statement.executeUpdate();
    }

    if (!conn.getAutoCommit()) {
        conn.commit();
    }
}
 

public int persistChunkedMessages2(Connection conn, FsChunkedMessage fsChunkedMessage)
throws SQLException {
  if (INSERT_LOG_OFAC_CHUNKED_2_PS == null) {
    INSERT_LOG_OFAC_CHUNKED_2_PS = conn.prepareStatement(INSERT_LOG_OFAC_CHUNKED_2_SQL);
  }
  PreparedStatement ps = INSERT_LOG_OFAC_CHUNKED_2_PS;
  ps.setString(1, fsChunkedMessage.getChunkId());
  ps.setInt(2, fsChunkedMessage.getChunkSequence());
  FsDataLifeStatus fsdatalife = FsDataLifeStatus.SENT_SS;
  ps.setObject(3, FsDataLifeStatus.getFsDataLifeStatus(fsdatalife));
  ps.setString(4, fsChunkedMessage.getBoTranId());
  ps.setString(5, fsChunkedMessage.getOfacComment());
  ps.setString(6, "checksum_here");
  ps.setString(7, fsChunkedMessage.getChnTxnId());
  ps.setString(8, String.valueOf(fsChunkedMessage.getChunkedMessage()));
  final Clob clob = conn.createClob();
  clob.setString(1, "THIS IS THE FIRCOSOFT HEADER DATA");
  ps.setClob(9, clob);
  ps.setTimestamp(10, fsChunkedMessage.getSentDate());
  ps.setTimestamp(11, fsChunkedMessage.getReceivedDate());
  FsAckStatus fsack = FsAckStatus.ACK_HIT;
  ps.setObject(12, FsAckStatus.getFsAckStatusCode(fsack));
  FsOutStatus fsout = FsOutStatus.OUT_PASSED;
  ps.setObject(13, FsOutStatus.getFsOutStatusCode(fsout));
  ps.setString(14, fsChunkedMessage.getFsMessageId());

  return INSERT_LOG_OFAC_CHUNKED_2_PS.executeUpdate();
}
 

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

@Test
public void testGetMethod() throws Throwable
{
  String prepInsertString = "insert into test_get values(?, ?, ?, ?, ?, ?, ?, ?)";
  int bigInt = Integer.MAX_VALUE;
  long bigLong = Long.MAX_VALUE;
  short bigShort = Short.MAX_VALUE;
  String str = "hello";
  double bigDouble = Double.MAX_VALUE;
  float bigFloat = Float.MAX_VALUE;

  Connection connection = getConnection();
  Clob clob = connection.createClob();
  clob.setString(1, "hello world");
  Statement statement = connection.createStatement();
  statement.execute("create or replace table test_get(colA integer, colB number, colC number, "
                    + "colD string, colE double, colF float, colG boolean, colH text)");

  PreparedStatement prepStatement = connection.prepareStatement(prepInsertString);
  prepStatement.setInt(1, bigInt);
  prepStatement.setLong(2, bigLong);
  prepStatement.setLong(3, bigShort);
  prepStatement.setString(4, str);
  prepStatement.setDouble(5, bigDouble);
  prepStatement.setFloat(6, bigFloat);
  prepStatement.setBoolean(7, true);
  prepStatement.setClob(8, clob);
  prepStatement.execute();

  statement.execute("select * from test_get");
  ResultSet resultSet = statement.getResultSet();
  resultSet.next();
  assertEquals(bigInt, resultSet.getInt(1));
  assertEquals(bigInt, resultSet.getInt("COLA"));
  assertEquals(bigLong, resultSet.getLong(2));
  assertEquals(bigLong, resultSet.getLong("COLB"));
  assertEquals(bigShort, resultSet.getShort(3));
  assertEquals(bigShort, resultSet.getShort("COLC"));
  assertEquals(str, resultSet.getString(4));
  assertEquals(str, resultSet.getString("COLD"));
  Reader reader = resultSet.getCharacterStream("COLD");
  char[] sample = new char[str.length()];

  assertEquals(str.length(), reader.read(sample));
  assertEquals(str.charAt(0), sample[0]);
  assertEquals(str, new String(sample));

  //assertEquals(bigDouble, resultSet.getDouble(5), 0);
  //assertEquals(bigDouble, resultSet.getDouble("COLE"), 0);
  assertEquals(bigFloat, resultSet.getFloat(6), 0);
  assertEquals(bigFloat, resultSet.getFloat("COLF"), 0);
  assertTrue(resultSet.getBoolean(7));
  assertTrue(resultSet.getBoolean("COLG"));
  assertEquals("hello world", resultSet.getClob("COLH").toString());

  //test getStatement method
  assertEquals(statement, resultSet.getStatement());

  prepStatement.close();
  statement.execute("drop table if exists table_get");
  statement.close();
  resultSet.close();
  connection.close();
}
 

protected int insertToTable(PreparedStatement stmt, int oid, int cid, int sid, int qty,
     String status, Timestamp time, BigDecimal bid, int tid, boolean isPut) throws SQLException {
       
   
   String database = SQLHelper.isDerbyConn(stmt.getConnection())?"Derby - " :"gemfirexd - " +  " ";
   
   
   JSONObject json = new JSONObject();
   String  jsonLog ="";
   if (!SQLHelper.isDerbyConn(stmt.getConnection())) { 
      json = getJSONObject(oid,cid,sid,qty,status,time,bid,tid);
      jsonLog = ",JSON_DETAILS: " +json.toJSONString();
   }
   
   Log.getLogWriter().info( database + (isPut ? "putting" : "inserting" ) + " into trade.buyorders with OID:" + oid +
       ",CID:"+ cid + ",SID:" + sid + ",QTY:" + qty + ",STATUS:" + status +
       ",TIME:"+ time + ",BID:" + bid + ",TID:" + tid + jsonLog);
   
   stmt.setInt(1, oid);
   stmt.setInt(2, cid);
   stmt.setInt(3, sid);
   stmt.setInt(4, qty);
   stmt.setBigDecimal(5, bid);
   stmt.setTimestamp(6, time);
   stmt.setString(7, status);       
   stmt.setInt(8, tid);
   if (!SQLHelper.isDerbyConn(stmt.getConnection())) { 
   Clob jsonClob = stmt.getConnection().createClob();
   jsonClob.setString(1, json.toJSONString());
   stmt.setClob(9, jsonClob);
   }
   
   int rowCount = stmt.executeUpdate();

   Log.getLogWriter().info( database + (isPut ? "put " : "inserted " ) + rowCount + " rows in trade.buyorders with OID:" + oid +
       ",CID:"+ cid + ",SID:" + sid + ",QTY:" + qty + ",STATUS:" + status +
       ",TIME:"+ time + ",BID:" + bid + ",TID:" + tid + jsonLog);
   
   if (SQLTest.hasJSON &&  !SQLHelper.isDerbyConn(stmt.getConnection()) && !SQLTest.hasTx && !setCriticalHeap)  insertUpdateCustomerJson(stmt.getConnection(), cid, json);
   
   SQLWarning warning = stmt.getWarnings(); //test to see there is a warning
   if (warning != null) {
     SQLHelper.printSQLWarning(warning);
   } 

   if ( database.contains("gemfirexd") && isPut) {
     if (! SQLTest.ticket49794fixed) {
       insertToBuyordersFulldataset(stmt.getConnection() , oid, cid, sid, qty, bid, time, status, tid);
       }      
     Log.getLogWriter().info( database +  (isPut ? "putting" : "inserting" ) + " into trade.buyorders with OID:" + oid +
         ",CID:"+ cid + ",SID:" + sid + ",QTY:" + qty + ",STATUS:" + status +
         ",TIME:"+ time + ",BID:" + bid + ",TID:" + tid + jsonLog);    
     
    rowCount = stmt.executeUpdate();
    
    Log.getLogWriter().info( database + (isPut ? "put" : "inserted" ) + rowCount + " rows in trade.buyorders with OID:" + oid +
        ",CID:"+ cid + ",SID:" + sid + ",QTY:" + qty + ",STATUS:" + status +
        ",TIME:"+ time + ",BID:" + bid + ",TID:" + tid + jsonLog);   
    warning = stmt.getWarnings(); //test to see there is a warning   
    if (warning != null) {
      SQLHelper.printSQLWarning(warning);
    } 
}    
   return rowCount;
 }
 

/**
 * Test updating a large clob
 */
public void testUpdateableStoreReader () throws Exception {
    getConnection().setAutoCommit (false);
    PreparedStatement ps = prepareStatement ("insert into updateClob " +
            "(id , data) values (? ,?)");
    ps.setInt (1, 2);
    StringBuilder sb = new StringBuilder ();
    String base = "SampleSampleSample";
    for (int i = 0; i < 100000; i++) {
        sb.append (base);
    }
    //insert a large enough data to ensure stream is created in dvd
    ps.setCharacterStream (2, new StringReader (sb.toString()),
                                        sb.length());
    ps.execute();
    ps.close();
    Statement stmt = createStatement ();
    ResultSet rs = stmt.executeQuery("select data from " +
            "updateClob where id = 2");
    rs.next();
    Clob clob = rs.getClob (1);
    rs.close();
    stmt.close();
    assertEquals (sb.length(), clob.length());
    Reader r = clob.getCharacterStream();
    String newString = "this is a new string";
    //access reader before modifying the clob
    long l = r.skip (100);
    clob.setString (1001, newString);
    //l chars are already skipped
    long toSkip = 1000 - l;
    while (toSkip > 0) {
        long skipped = r.skip (toSkip);
        toSkip -= skipped;
    }
    char [] newdata = new char [newString.length()];
    int len = r.read(newdata);
    assertEquals ("updated not reflected", newString,
                            new String (newdata, 0, len));
    r.close();
}
 

/**
 * Tests fix for BUG#11614 - StringUtils.getBytes() doesn't work when using
 * multibyte character encodings and a length in _characters_ is specified.
 * 
 * @throws Exception
 *             if the test fails.
 */
public void testBug11614() throws Exception {
    if (versionMeetsMinimum(4, 1)) {
        createTable("testBug11614",
                "(`id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT, `text` TEXT NOT NULL," + "PRIMARY KEY(`id`)) CHARACTER SET utf8 COLLATE utf8_general_ci");

        Properties props = new Properties();
        props.setProperty("characterEncoding", "utf8");

        Connection utf8Conn = null;

        try {
            utf8Conn = getConnectionWithProps(props);

            utf8Conn.createStatement().executeUpdate("INSERT INTO testBug11614  (`id`,`text`) values (1,'')");
            this.rs = utf8Conn.createStatement().executeQuery("SELECT `text` FROM testBug11614 WHERE id=1");
            assertTrue(this.rs.next());

            Clob c = this.rs.getClob(1);
            c.truncate(0);
            int blockSize = 8192;
            int sizeToTest = blockSize + 100;

            StringBuilder blockBuf = new StringBuilder(sizeToTest);

            for (int i = 0; i < sizeToTest; i++) {
                blockBuf.append('\u00f6');
            }

            String valueToTest = blockBuf.toString();

            c.setString(1, valueToTest);
            this.pstmt = utf8Conn.prepareStatement("UPDATE testBug11614 SET `text` = ? WHERE id=1");
            this.pstmt.setClob(1, c);
            this.pstmt.executeUpdate();
            this.pstmt.close();

            String fromDatabase = getSingleIndexedValueWithQuery(utf8Conn, 1, "SELECT `text` FROM testBug11614").toString();
            assertEquals(valueToTest, fromDatabase);
        } finally {
            if (utf8Conn != null) {
                utf8Conn.close();
            }

        }
    }
}
 

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

/**
 * Tests updates on reader.
 */
public void testUpdateableReader () throws Exception {
    getConnection().setAutoCommit (false);
    PreparedStatement ps = prepareStatement ("insert into updateClob " +
            "(id , data) values (? ,?)");
    ps.setInt (1, 1);
    StringBuilder sb = new StringBuilder ();
    String base = "SampleSampleSample";
    for (int i = 0; i < 100; i++) {
        sb.append (base);
    }
    ps.setCharacterStream (2, new StringReader (sb.toString()),
                                        sb.length());
    ps.execute();
    ps.close();
    Statement stmt = createStatement ();
    ResultSet rs = stmt.executeQuery("select data from " +
            "updateClob where id = 1");
    rs.next();
    Clob clob = rs.getClob (1);
    rs.close();
    stmt.close();
    assertEquals (sb.length(), clob.length());
    Reader r = clob.getCharacterStream();
    char [] clobData = new char [sb.length()];
    r.read (clobData);
    assertEquals ("mismatch from inserted string",
                        String.valueOf (clobData), sb.toString());
    r.close();
    //update before gettting the reader
    clob.setString (50, dummy);
    r = clob.getCharacterStream();
    r.skip (49);
    char [] newChars = new char [dummy.length()];
    r.read (newChars);
    assertEquals ("update not reflected", dummy,
                                String.valueOf (newChars));
    //update again and see if stream is refreshed
    clob.setString (75, dummy);
    r.skip (75 - 50 - dummy.length());
    char [] testChars = new char [dummy.length()];
    r.read (testChars);
    assertEquals ("update not reflected", dummy,
                                String.valueOf (newChars));
    r.close();
    //try inserting some unicode string
    String unicodeStr = getUnicodeString();
    clob.setString (50, unicodeStr);
    char [] utf16Chars = new char [unicodeStr.length()];
    r = clob.getCharacterStream();
    r.skip(49);
    r.read(utf16Chars);
    assertEquals ("update not reflected",  unicodeStr,
                                String.valueOf (utf16Chars));
    r.close();
    Writer w = clob.setCharacterStream (1);
    //write enough data to switch the data to file
    r = clob.getCharacterStream ();
    for (int i = 0; i < 10000; i++) {
        w.write (dummy);
    }
    w.close();
    clob.setString (500, unicodeStr);
    r.skip (499);
    char [] unicodeChars = new char [unicodeStr.length()];
    r.read (unicodeChars);
    assertEquals ("update not reflected",  unicodeStr,
                                String.valueOf (unicodeChars));
}