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

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, 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;
}
 

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

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

@Override
public InputStream getClobAsAsciiStream(ResultSet rs, int columnIndex) throws SQLException {
	logger.debug("Returning Oracle CLOB as ASCII stream");
	Clob clob = rs.getClob(columnIndex);
	initializeResourcesBeforeRead(rs.getStatement().getConnection(), clob);
	InputStream retVal = (clob != null ? clob.getAsciiStream() : null);
	releaseResourcesAfterRead(rs.getStatement().getConnection(), clob);
	return retVal;
}
 

/**
 * Tests the ClobWriter.write(int c) method
 **/
public void testClobCharacterWrite1Char() throws Exception
{
    char testchar = 'a';

    PreparedStatement stmt3 = prepareStatement(
        "SELECT c FROM testBlobX1 WHERE a = 1");
    ResultSet rs3 = stmt3.executeQuery();
    rs3.next();
    Clob clob = rs3.getClob(1);

    assertTrue ("FAIL -- clob is NULL", clob != null);
    Writer clobWriter = clob.setCharacterStream(1L);
    clobWriter.write(testchar);
    clobWriter.close();

    PreparedStatement stmt4 = prepareStatement(
        "UPDATE testBlobX1 SET c = ? WHERE a = 1");
    stmt4.setClob(1,  clob);
    stmt4.executeUpdate();
    stmt4.close();

    rs3.close();
    rs3 = stmt3.executeQuery();
    assertTrue("FAIL -- clob not found", rs3.next());

    clob = rs3.getClob(1);
    long new_length = clob.length();
    assertEquals("FAIL -- wrong clob length", 1, new_length);
    // Check contents ...
    Reader lStream = clob.getCharacterStream();
    char clobchar = (char) lStream.read();
    assertEquals("FAIL - fetched Clob and original contents do " +
            "not match", testchar, clobchar);

    lStream.close();
    rs3.close();
    stmt3.close();
}
 

public static InputStream getClobInputStream(ResultSet rs, int columnIndex) throws SQLException, JdbcException {
	Clob clob = rs.getClob(columnIndex);
	if (clob==null) {
		throw new JdbcException("no clob found in column ["+columnIndex+"]");
	}
	return getClobInputStream(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);
    }
}
 

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

/**
 * Obtain a Clob containing the empty string.
 */
protected void setUp()
        throws Exception {
    // Obtain a Clob containing the empty string ("").
    Statement stmt = createStatement();
    // Keep reference to the result set to be able to close it.
    ResultSet rs = stmt.executeQuery(
            "select dClob from ClobTestData where id = 1");
    assertTrue(rs.next());
    this.clob = rs.getClob(1);
    // Leave the result set open to keep the Clob alive.
}
 

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

	//return rs.getBytes(1);
}
 

@Override
public Clob fromResult(ResultSet results, int column) throws SQLException {
    return results.getClob(column);
}
 

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

}
 

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("========================================");

}
 

/**
 * This methods tests the ResultSet interface method
 * updateClob
 *
 * @throws SQLException if some error occurs while calling the method
 */
public void testUpdateClobStringParameterName()
throws Exception {
    // Life span of Clob objects are limited by the transaction.  Need
    // autocommit off so Clob objects survive execution of next statement.
    getConnection().setAutoCommit(false);

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

    //1 Input Stream for insertion
    InputStream is1 = new java.io.ByteArrayInputStream(BYTES1);

    //2 Input Stream for insertion
    InputStream is2 = new java.io.ByteArrayInputStream(BYTES2);

    //Prepared Statement used to insert the data
    PreparedStatement ps_sb = prep("dClob");

    //first insert
    ps_sb.setInt(1, key);
    ps_sb.setAsciiStream(2,is1,BYTES1.length);
    ps_sb.executeUpdate();

    //second insert
    int key2 = requestKey();
    ps_sb.setInt(1, key2);
    ps_sb.setAsciiStream(2,is2,BYTES2.length);
    ps_sb.executeUpdate();

    ps_sb.close();

    //Update operation
    //use a different ResultSet variable so that the
    //other tests can go on unimpacted
    //we do not have set methods on Clob and Blob implemented
    //So query the first Clob from the database
    //update the second result set with this
    //Clob value

    ResultSet rs1 = fetch("dClob", key);
    rs1.next();
    Clob clob = rs1.getClob(1);
    rs1.close();

    rs1 = fetchUpd("dClob", key2);
    rs1.next();
    rs1.updateClob("dClob",clob);
    rs1.updateRow();
    rs1.close();

    //Query to see whether the data that has been updated
    //using the updateClob method is the same
    //data that we expected

    rs1 = fetch("dClob", key2);
    rs1.next();
    assertEquals(clob, rs1.getClob(1));
    rs1.close();
}
 

/**
 * Delegates to {@link ResultSet#getObject(int, Class)} without throwing an {@link AbstractMethodError}.
 * <p>
 * If the JDBC driver does not implement {@link ResultSet#getObject(int, Class)}, then return 0.
 * </p>
 *
 * @param <T>
 *            See {@link ResultSet#getObject(int, Class)}
 * @param resultSet
 *            See {@link ResultSet#getObject(int, Class)}
 * @param columnIndex
 *            See {@link ResultSet#getObject(int, Class)}
 * @param type
 *            See {@link ResultSet#getObject(int, Class)}
 * @return See {@link ResultSet#getObject(int, Class)}
 * @throws SQLException
 *             See {@link ResultSet#getObject(int, Class)}
 * @see ResultSet#getObject(int, Class)
 */
@SuppressWarnings("unchecked")
public static <T> T getObject(final ResultSet resultSet, final int columnIndex, final Class<T> type)
        throws SQLException {
    try {
        return resultSet.getObject(columnIndex, type);
    } catch (final AbstractMethodError e) {
        if (type == String.class) {
            return (T) resultSet.getString(columnIndex);
        }
        // Numbers
        if (type == Integer.class) {
            return (T) Integer.valueOf(resultSet.getInt(columnIndex));
        }
        if (type == Long.class) {
            return (T) Long.valueOf(resultSet.getLong(columnIndex));
        }
        if (type == Double.class) {
            return (T) Double.valueOf(resultSet.getDouble(columnIndex));
        }
        if (type == Float.class) {
            return (T) Float.valueOf(resultSet.getFloat(columnIndex));
        }
        if (type == Short.class) {
            return (T) Short.valueOf(resultSet.getShort(columnIndex));
        }
        if (type == BigDecimal.class) {
            return (T) resultSet.getBigDecimal(columnIndex);
        }
        if (type == Byte.class) {
            return (T) Byte.valueOf(resultSet.getByte(columnIndex));
        }
        // Dates
        if (type == Date.class) {
            return (T) resultSet.getDate(columnIndex);
        }
        if (type == Time.class) {
            return (T) resultSet.getTime(columnIndex);
        }
        if (type == Timestamp.class) {
            return (T) resultSet.getTimestamp(columnIndex);
        }
        // Streams
        if (type == InputStream.class) {
            return (T) resultSet.getBinaryStream(columnIndex);
        }
        if (type == Reader.class) {
            return (T) resultSet.getCharacterStream(columnIndex);
        }
        // Other
        if (type == Object.class) {
            return (T) resultSet.getObject(columnIndex);
        }
        if (type == Boolean.class) {
            return (T) Boolean.valueOf(resultSet.getBoolean(columnIndex));
        }
        if (type == Array.class) {
            return (T) resultSet.getArray(columnIndex);
        }
        if (type == Blob.class) {
            return (T) resultSet.getBlob(columnIndex);
        }
        if (type == Clob.class) {
            return (T) resultSet.getClob(columnIndex);
        }
        if (type == Ref.class) {
            return (T) resultSet.getRef(columnIndex);
        }
        if (type == RowId.class) {
            return (T) resultSet.getRowId(columnIndex);
        }
        if (type == SQLXML.class) {
            return (T) resultSet.getSQLXML(columnIndex);
        }
        if (type == URL.class) {
            return (T) resultSet.getURL(columnIndex);
        }
        throw new SQLFeatureNotSupportedException(
                String.format("resultSet=%s, columnIndex=%,d, type=%s", resultSet, columnIndex, type));
    }
}
 

public void tmp_SB_test44550() throws Exception {
  
  Properties p = new Properties();

  // p.setProperty(Attribute.ENABLE_STATS, "true");
  // p.setProperty(Attribute.ENABLE_TIMESTATS, "true");
  
  Connection systemconn = TestUtil.getConnection();
  try {
    CallableStatement cusr = systemconn
        .prepareCall("call SYSCS_UTIL.SET_DATABASE_PROPERTY(?,?)");
    cusr.setString(1, "gemfirexd.enable-getall-local-index-embed-gfe");
    cusr.setString(2, "true");
    cusr.execute();
    cusr.close();
    
    // start a network server
    int netPort = startNetworkServer(1, null, p);
    startNetworkServer(2, null, p);
    checkLoadLib(getTestName());

    final Connection conn = TestUtil.getNetConnection(netPort, null, null);

    Statement s = conn.createStatement();
    s.execute("Create Table TEST_TABLE(idx numeric(12),"
        + "AccountID varchar(10)," + "OrderNo varchar(20),"
        + "primary key(idx)" + ")" + "PARTITION BY COLUMN ( AccountID )");

    s.execute("CREATE INDEX idx_AccountID ON test_Table (AccountID ASC)");
    PreparedStatement insps = conn
        .prepareStatement("insert into test_table values(?,?,?)");
    int base = 1;
    for (int i = 0; i < 1000; i++) {
      insps.setInt(1, base + i);
      insps.setString(2, String.valueOf(i % 9));
      insps.setString(3, String.valueOf(i));
      insps.executeUpdate();
    }

    {
      ResultSet r = conn.createStatement().executeQuery(
          "explain select * from test_table where accountid = '" + 8
              + "' order by idx desc ");

      assertTrue(r.next());
      java.sql.Clob c = r.getClob(1);
      BufferedReader reader = new BufferedReader(c.getCharacterStream());
      int sz = (int)c.length();
      char[] charArray = new char[sz];
      reader.read(charArray, 0, sz);
      final String pl = new String(charArray);
      getLogWriter().info("Plan: " + pl);

      String currentPlanLine = null;

      currentPlanLine = localPlan(pl);
      currentPlanLine = planMember(currentPlanLine);
      currentPlanLine = sort(currentPlanLine, "111", 1);
      currentPlanLine = localindexgetall(currentPlanLine, "111", 1);
      r.close();
    }
  } finally {
    if (systemconn != null) {
      systemconn.close();
    }
    
    stopNetworkServer(2);
    stopNetworkServer(1);
  }
}