Java Code Examples for java.sql.Connection#createClob()

public static final void setStatementParameters(Connection conn, PreparedStatement ps,
    UUID stmt_id, 
    StringBuilder xmlFragment) throws SQLException {
  ps.setString(1, (stmt_id != null ? stmt_id.toString() : null));
  final Clob c = conn.createClob();
  try {
    c.setCharacterStream(1).write(xmlFragment.toString());
  } catch (IOException e) {
    if (GemFireXDUtils.TracePlanAssertion) {
      SanityManager.DEBUG_PRINT(GfxdConstants.TRACE_PLAN_ASSERTION,
          "couldn't set clob stream.", e);
    }
    else if (GemFireXDUtils.TracePlanGeneration) {
      SanityManager.DEBUG_PRINT(GfxdConstants.TRACE_PLAN_GENERATION,
          "couldn't set clob stream.");
    }
  }
  ps.setClob(2, c);
}
 

public int insertBORawData(Connection conn, String boTxnId, String rawData, String msgId, String incomingQueueName)
throws SQLException {
  if (INSERT_RAW_DATA_PS == null) {
    INSERT_RAW_DATA_PS = conn.prepareStatement(INSERT_RAW_DATA_SQL);
  }
  INSERT_RAW_DATA_PS.setString(1, boTxnId);
  final Clob clob = conn.createClob();
  clob.setString(1, rawData);
  INSERT_RAW_DATA_PS.setClob(2, clob);
  INSERT_RAW_DATA_PS.setString(3, msgId);
  INSERT_RAW_DATA_PS.setString(4, incomingQueueName);

  if (Log.getLogWriter().fineEnabled()) {
    Log.getLogWriter().fine("INSERT into SECL_BO_RAW_DATA BO_TXN_ID=" + boTxnId + " BO_RAW_DATA=" + rawData + " MESSAGE_ID=" + msgId + " INCOMING_QUEUE=" + incomingQueueName);
  }
  return INSERT_RAW_DATA_PS.executeUpdate();
}
 

public int insertBORawData(Connection conn, String boTxnId, String rawData, String msgId, String incomingQueueName)
throws SQLException {
  if (INSERT_RAW_DATA_PS == null) {
    INSERT_RAW_DATA_PS = conn.prepareStatement(INSERT_RAW_DATA_SQL);
  }
  INSERT_RAW_DATA_PS.setString(1, boTxnId);
  final Clob clob = conn.createClob();
  clob.setString(1, rawData);
  INSERT_RAW_DATA_PS.setClob(2, clob);
  INSERT_RAW_DATA_PS.setString(3, msgId);
  INSERT_RAW_DATA_PS.setString(4, incomingQueueName);

  if (Log.getLogWriter().fineEnabled()) {
    Log.getLogWriter().fine("INSERT into SECL_BO_RAW_DATA BO_TXN_ID=" + boTxnId + " BO_RAW_DATA=" + rawData + " MESSAGE_ID=" + msgId + " INCOMING_QUEUE=" + incomingQueueName);
  }
  return INSERT_RAW_DATA_PS.executeUpdate();
}
 

public static final void setStatementParameters(Connection conn, PreparedStatement ps,
    UUID stmt_id, 
    StringBuilder xmlFragment) throws SQLException {
  ps.setString(1, (stmt_id != null ? stmt_id.toString() : null));
  final Clob c = conn.createClob();
  try {
    c.setCharacterStream(1).write(xmlFragment.toString());
  } catch (IOException e) {
    if (GemFireXDUtils.TracePlanAssertion) {
      SanityManager.DEBUG_PRINT(GfxdConstants.TRACE_PLAN_ASSERTION,
          "couldn't set clob stream.", e);
    }
    else if (GemFireXDUtils.TracePlanGeneration) {
      SanityManager.DEBUG_PRINT(GfxdConstants.TRACE_PLAN_GENERATION,
          "couldn't set clob stream.");
    }
  }
  ps.setClob(2, c);
}
 

/**
 * Tests fix for Bug#56122 - JDBC4 functionality failure when using replication connections.
 */
public void testBug56122() throws Exception {
    for (final Connection testConn : new Connection[] { this.conn, getFailoverConnection(), getLoadBalancedConnection(),
            getMasterSlaveReplicationConnection() }) {
        testConn.createClob();
        testConn.createBlob();
        testConn.createNClob();
        testConn.createSQLXML();
        testConn.isValid(12345);
        testConn.setClientInfo(new Properties());
        testConn.setClientInfo("NAME", "VALUE");
        testConn.getClientInfo();
        testConn.getClientInfo("CLIENT");
        assertThrows(SQLFeatureNotSupportedException.class, new Callable<Void>() {
            public Void call() throws Exception {
                testConn.createArrayOf("A_TYPE", null);
                return null;
            }
        });
        assertThrows(SQLFeatureNotSupportedException.class, new Callable<Void>() {
            public Void call() throws Exception {
                testConn.createStruct("A_TYPE", null);
                return 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;

}
 

/**
 * Converts the given string to a clob object
 *
 * @param stringName string name to clob
 * @param sqlConnection Connection object
 * @return Clob object or NULL
 */

public Clob toClob(String stringName, Connection sqlConnection) {
    Clob clobName = null;
    try {
        clobName = sqlConnection.createClob();
        clobName.setString(1, stringName);
    } catch (SQLException e) {
        // TODO Auto-generated catch block
        logger.info("Unable to create clob object");
        e.printStackTrace();
    }
    return clobName;
}
 

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

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

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

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

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 fix for Bug#56122 - JDBC4 functionality failure when using replication connections.
 */
public void testBug56122() throws Exception {
    for (final Connection testConn : new Connection[] { this.conn, getFailoverConnection(), getLoadBalancedConnection(),
            getMasterSlaveReplicationConnection() }) {
        testConn.createClob();
        testConn.createBlob();
        testConn.createNClob();
        testConn.createSQLXML();
        testConn.isValid(12345);
        testConn.setClientInfo(new Properties());
        testConn.setClientInfo("NAME", "VALUE");
        testConn.getClientInfo();
        testConn.getClientInfo("CLIENT");
        assertThrows(SQLFeatureNotSupportedException.class, new Callable<Void>() {
            public Void call() throws Exception {
                testConn.createArrayOf("A_TYPE", null);
                return null;
            }
        });
        assertThrows(SQLFeatureNotSupportedException.class, new Callable<Void>() {
            public Void call() throws Exception {
                testConn.createStruct("A_TYPE", null);
                return null;
            }
        });
    }
}
 

/**
 * Test the createClob method implementation in the Connection interface
 *
 * @exception SQLException, FileNotFoundException, Exception if error occurs
 */
public void testCreateClob() throws   SQLException,
        FileNotFoundException, IOException,
        Exception{

    Connection conn = getConnection();
    int b, c;
    Clob clob;

    Statement s = createStatement();

    PreparedStatement ps =
            prepareStatement("insert into clobtable2 (n, clobcol)" + " values(?,?)");
    ps.setInt(1,1000);
    clob = conn.createClob();

    try {
        is = (FileInputStream) AccessController.doPrivileged(
                new PrivilegedExceptionAction() {
            public Object run() throws FileNotFoundException {
                return new FileInputStream("extin/short.txt");
            }
        });
    } catch (PrivilegedActionException e) {
        // e.getException() should be an instance of FileNotFoundException,
        // as only "checked" exceptions will be "wrapped" in a
        // PrivilegedActionException.
        throw (FileNotFoundException) e.getException();
    }
    OutputStream os = clob.setAsciiStream(1);
    ArrayList beforeUpdateList = new ArrayList();

    c = is.read();
    while(c>0) {
        os.write(c);
        beforeUpdateList.add(c);
        c = is.read();
    }
    ps.setClob(2, clob);
    ps.executeUpdate();

    Statement stmt = createStatement();
    ResultSet rs =
            stmt.executeQuery("select clobcol from clobtable2 where n = 1000");
    assertTrue(rs.next());

    clob = rs.getClob(1);
    assertEquals(beforeUpdateList.size(), clob.length());

    //Get the InputStream from this Clob.
    InputStream in = clob.getAsciiStream();
    ArrayList afterUpdateList = new ArrayList();

    b = in.read();

    while (b > -1) {
        afterUpdateList.add(b);
        b = in.read();
    }

    assertEquals(beforeUpdateList.size(), afterUpdateList.size());

    //Now check if the two InputStreams
    //match
    for (int i = 0; i < clob.length(); i++) {
        assertEquals(beforeUpdateList.get(i), afterUpdateList.get(i));
    }

    os.close();
    is.close();

}
 

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

private void fillSectChannelDataStmt(Connection conn, PreparedStatement stmt)
throws SQLException {
  UUID txid = UUID.randomUUID();
  stmt.setString(1, txid.toString()); // CHANNEL_TX_ID

  Timestamp valueDate = generateTimestamp();
  stmt.setTimestamp(2, valueDate); // VALUE_DATE

  String companyId = generateNumericString(7);
  stmt.setString(3, companyId); // COMPANY_ID

  String clientAccount = generateNumericString(8);
  stmt.setString(4, clientAccount); // CLIENT_ACCOUNT

  Double amount = generateDecimal(5,2);
  stmt.setDouble(5, amount); // AMOUNT

  String currency = "GBP";
  stmt.setString(6, currency); // CURRENCY

  String clientRefNo = generateString(13);
  stmt.setString(7, clientRefNo); // CLIENT_REF_NO

  String origBankId = generateString(10,15);
  stmt.setString(8, origBankId); // ORIG_BANK_ID

  String beneAccntNo = generateString(10);
  stmt.setString(9, beneAccntNo); // BENE_ACCNT_NO

  String beneBankId = generateString(10);
  stmt.setString(10, beneBankId); // BENE_BANK_ID

  String beneBankAddr = generateString(20);
  stmt.setString(11, beneBankAddr); // BENE_BANK_ADDR

  String beneName = generateString(10);
  stmt.setString(12, beneName); //  BENE_NAME

  String beneAddr = generateString(20);
  stmt.setString(13, beneAddr); // BENE_ADDR

  String backofficeCode = IPAY;
  stmt.setString(14, backofficeCode); // BACKOFFICE_CODE (should come from SECM_BACKOFFICE_ONBOARDED.BACKOFFICE_CODE)

  String txnType = "DDI";
  stmt.setString(15, txnType); // TXN_TYPE

  stmt.setInt(16, 0); // DATA_LIFE_STATUS

  stmt.setInt(17, 1); // MATCH_STATUS

  stmt.setInt(18, -1); // MATCH_CATEG_ID // TBD: this int should be null

  stmt.setLong(19, -1); // ETL_TIME

  String channelName = CHANNEL_NAMES[this.rng.nextInt(CHANNEL_NAMES.length - 1)]; // perhaps default to "UNKNOWN" sometimes
  stmt.setString(20, channelName); // CHANNEL_NAME

  final Clob clob = conn.createClob();
  clob.setString(1, SECT_CHANNEL_RAW_DATA);
  stmt.setClob(21, clob); // RAW_DATA

  String fileTypeId = UUID.randomUUID().toString();
  stmt.setString(22, fileTypeId); // FILE_TYPE_ID

  if (Log.getLogWriter().fineEnabled()) {
    Log.getLogWriter().fine("INSERT into SECT_CHANNEL_DATA with" +
      " CURRENCY=" + currency +
      " AMOUNT=" + amount +
      " CLIENT_REF_NO=" + clientRefNo +
      " VALUE_DATE=" + valueDate +
      " CLIENT_ACCOUNT=" + clientAccount +
      " COMPANY_ID=" + companyId);
  }
}
 

private void fillSectChannelDataStmt(Connection conn, PreparedStatement stmt)
throws SQLException {
  UUID txid = UUID.randomUUID();
  stmt.setString(1, txid.toString()); // CHANNEL_TX_ID

  Timestamp valueDate = generateTimestamp();
  stmt.setTimestamp(2, valueDate); // VALUE_DATE

  String companyId = generateNumericString(7);
  stmt.setString(3, companyId); // COMPANY_ID

  String clientAccount = generateNumericString(8);
  stmt.setString(4, clientAccount); // CLIENT_ACCOUNT

  Double amount = generateDecimal(5,2);
  stmt.setDouble(5, amount); // AMOUNT

  String currency = "GBP";
  stmt.setString(6, currency); // CURRENCY

  String clientRefNo = generateString(13);
  stmt.setString(7, clientRefNo); // CLIENT_REF_NO

  String origBankId = generateString(10,15);
  stmt.setString(8, origBankId); // ORIG_BANK_ID

  String beneAccntNo = generateString(10);
  stmt.setString(9, beneAccntNo); // BENE_ACCNT_NO

  String beneBankId = generateString(10);
  stmt.setString(10, beneBankId); // BENE_BANK_ID

  String beneBankAddr = generateString(20);
  stmt.setString(11, beneBankAddr); // BENE_BANK_ADDR

  String beneName = generateString(10);
  stmt.setString(12, beneName); //  BENE_NAME

  String beneAddr = generateString(20);
  stmt.setString(13, beneAddr); // BENE_ADDR

  String backofficeCode = IPAY;
  stmt.setString(14, backofficeCode); // BACKOFFICE_CODE (should come from SECM_BACKOFFICE_ONBOARDED.BACKOFFICE_CODE)

  String txnType = "DDI";
  stmt.setString(15, txnType); // TXN_TYPE

  stmt.setInt(16, 0); // DATA_LIFE_STATUS

  stmt.setInt(17, 1); // MATCH_STATUS

  stmt.setInt(18, -1); // MATCH_CATEG_ID // TBD: this int should be null

  stmt.setLong(19, -1); // ETL_TIME

  String channelName = CHANNEL_NAMES[this.rng.nextInt(CHANNEL_NAMES.length - 1)]; // perhaps default to "UNKNOWN" sometimes
  stmt.setString(20, channelName); // CHANNEL_NAME

  final Clob clob = conn.createClob();
  clob.setString(1, SECT_CHANNEL_RAW_DATA);
  stmt.setClob(21, clob); // RAW_DATA

  String fileTypeId = UUID.randomUUID().toString();
  stmt.setString(22, fileTypeId); // FILE_TYPE_ID

  if (Log.getLogWriter().fineEnabled()) {
    Log.getLogWriter().fine("INSERT into SECT_CHANNEL_DATA with" +
      " CURRENCY=" + currency +
      " AMOUNT=" + amount +
      " CLIENT_REF_NO=" + clientRefNo +
      " VALUE_DATE=" + valueDate +
      " CLIENT_ACCOUNT=" + clientAccount +
      " COMPANY_ID=" + companyId);
  }
}
 

/**
 * Test the createClob method implementation in the Connection interface
 *
 * @exception SQLException, FileNotFoundException, Exception if error occurs
 */
public void testCreateClob() throws   SQLException,
        FileNotFoundException, IOException,
        Exception{

    Connection conn = getConnection();
    int b, c;
    Clob clob;

    Statement s = createStatement();

    PreparedStatement ps =
            prepareStatement("insert into clobtable2 (n, clobcol)" + " values(?,?)");
    ps.setInt(1,1000);
    clob = conn.createClob();

    try {
        is = (FileInputStream) AccessController.doPrivileged(
                new PrivilegedExceptionAction() {
            public Object run() throws FileNotFoundException {
                return new FileInputStream("extin/short.txt");
            }
        });
    } catch (PrivilegedActionException e) {
        // e.getException() should be an instance of FileNotFoundException,
        // as only "checked" exceptions will be "wrapped" in a
        // PrivilegedActionException.
        throw (FileNotFoundException) e.getException();
    }
    OutputStream os = clob.setAsciiStream(1);
    ArrayList beforeUpdateList = new ArrayList();

    c = is.read();
    while(c>0) {
        os.write(c);
        beforeUpdateList.add(c);
        c = is.read();
    }
    ps.setClob(2, clob);
    ps.executeUpdate();

    Statement stmt = createStatement();
    ResultSet rs =
            stmt.executeQuery("select clobcol from clobtable2 where n = 1000");
    assertTrue(rs.next());

    clob = rs.getClob(1);
    assertEquals(beforeUpdateList.size(), clob.length());

    //Get the InputStream from this Clob.
    InputStream in = clob.getAsciiStream();
    ArrayList afterUpdateList = new ArrayList();

    b = in.read();

    while (b > -1) {
        afterUpdateList.add(b);
        b = in.read();
    }

    assertEquals(beforeUpdateList.size(), afterUpdateList.size());

    //Now check if the two InputStreams
    //match
    for (int i = 0; i < clob.length(); i++) {
        assertEquals(beforeUpdateList.get(i), afterUpdateList.get(i));
    }

    os.close();
    is.close();

}
 

@Override
public Clob create(Connection connection) throws SQLException {
  return connection.createClob();
}
 

/**
 * Test the createClob method implementation in the Connection interface
 *
 * @exception SQLException, FileNotFoundException, Exception if error occurs
 */
public void testCreateClob() throws   SQLException,
        FileNotFoundException, IOException,
        Exception{

    Connection conn = getConnection();
    int b, c;
    Clob clob;

    Statement s = createStatement();

    PreparedStatement ps =
            prepareStatement("insert into clobtable2 (n, clobcol)" + " values(?,?)");
    ps.setInt(1,1000);
    clob = conn.createClob();

    try {
        is = AccessController.doPrivileged(
                new PrivilegedExceptionAction<FileInputStream>() {
            public FileInputStream run() throws FileNotFoundException {
                return new FileInputStream("extin/short.txt");
            }
        });
    } catch (PrivilegedActionException e) {
        // e.getException() should be an instance of FileNotFoundException,
        // as only "checked" exceptions will be "wrapped" in a
        // PrivilegedActionException.
        throw (FileNotFoundException) e.getException();
    }
    OutputStream os = clob.setAsciiStream(1);
    ArrayList<Integer> beforeUpdateList = new ArrayList<Integer>();

    c = is.read();
    while(c>0) {
        os.write(c);
        beforeUpdateList.add(c);
        c = is.read();
    }
    ps.setClob(2, clob);
    ps.executeUpdate();

    Statement stmt = createStatement();
    ResultSet rs =
            stmt.executeQuery("select clobcol from clobtable2 where n = 1000");
    assertTrue(rs.next());

    clob = rs.getClob(1);
    assertEquals(beforeUpdateList.size(), clob.length());

    //Get the InputStream from this Clob.
    InputStream in = clob.getAsciiStream();
    ArrayList<Integer> afterUpdateList = new ArrayList<Integer>();

    b = in.read();

    while (b > -1) {
        afterUpdateList.add(b);
        b = in.read();
    }

    assertEquals(beforeUpdateList.size(), afterUpdateList.size());

    //Now check if the two InputStreams
    //match
    for (int i = 0; i < clob.length(); i++) {
        assertEquals(beforeUpdateList.get(i), afterUpdateList.get(i));
    }

    os.close();
    is.close();

}