How to get a Clob using java

Question

I'm having some problem to get a Clob field using java. So this is what I've done:

    String 
    sql = ("SELECT DBMS_LOB.substr(LDTEXT, 1000) AS TEXT "
        +  "FROM LONGDESCRIPTION ");

    try (Connection connection = BDOracle.abreConexaoDB2();
            Statement statement = connection.createStatement();
                ResultSet resultSet = statement.executeQuery(sql);
            ) {

        while (resultSet.next()) {

It breaks here:

            Clob clob = (Clob) resultSet.getClob("TEXT");

            long length = clob.length();
            String out = clob.getSubString(1, (int) length);
        }           

    } catch (SQLException err) {
        System.out.println(err.getMessage());
    }

Exception returns:

ERRORCODE=-4461, SQLSTATE=42815 [jcc][t4][1092][11644][3.53.70] invalid data conversion wrong result column type for requested conversion.

The query works fine on the database, I'm using DB2.

mustaccio mustaccio · Accepted Answer · 2016-08-16T13:52:03

Assuming LDTEXT is a CLOB, DBMS_LOB.SUBSTR() will return a VARCHAR value, as indicated in the manual. Subsequently, you should use getString(), not getClob() to retrieve it (and use the correct column name -- you have TEXT0 while the query defines it as TEXT).

How to get a Clob using java

1 Answers