Convert Java String to CLOB

Question

I have the following table: enter image description here

The problem is that when I try to use JDBC to insert a String to this field I always get the following error:

java.sql.SQLException: Data size bigger than max size for this type: 6019

what should I do now?

The java code to generate the INSERT is the following:

Connection conn2 = null;
   if(connection==null||connection.isClosed())
   {
    System.out.println("Connection is null");
    conn2 = connectDB();
   }
   //REPLACE is non-standard SQL from MySQL, it's counter part of INSERT IGNORE
   String sql = ""; 
   sql = "INSERT INTO TEST."+tablename+" ("+fields+") VALUES ("+fields.replaceAll("[^,]+", "?")+")";

   System.out.println("SQL: "+sql);
   PreparedStatement pstmt =  conn2.prepareStatement(sql);

   // start parsing
   int event = r.getEventType();
   while (true) {
    // for each wanted element 
    if (event == XMLStreamConstants.START_ELEMENT
      && r.getName().toString().equalsIgnoreCase("row")) {
     System.out.println("Table : "+tablename+" / Row : "+rowCount );
     if (attributeCount == 0)
      attributeCount = r.getAttributeCount();


     //put each parameter to SQL
     int f=1;
     for (String field : fieldsArray){
    String value = r.getAttributeValue("",field);
    if("body".equalsIgnoreCase(field) && value != null) {
         pstmt.setCharacterStream(f++, new CharArrayReader(value.toCharArray()), value.length());
    } else {
         pstmt.setString(f++, value);
    }
 }

     pstmt.addBatch();
     rowCount++;

     if(rowCount%rowspercommit==0){
      System.out.println("Importing at row "+rowCount+" ... ");
      pstmt.executeBatch();
      conn2.commit();
     }
    } // end for each row.

Not sure how the CREATE TABLE is, as someone did that for me

With the current code above, I am now getting this:

Exception in thread "main" java.lang.NullPointerException
    at oracle.jdbc.dbaccess.DBData.clearItem(DBData.java:431)
    at oracle.jdbc.dbaccess.DBDataSetImpl.clearItem(DBDataSetImpl.java:3528)
    at oracle.jdbc.driver.OraclePreparedStatement.checkBindTypes(OraclePreparedStatement.java:3271)
    at oracle.jdbc.driver.OraclePreparedStatement.setStreamItem(OraclePreparedStatement.java:1178)
    at oracle.jdbc.driver.OraclePreparedStatement.setCharacterStream(OraclePreparedStatement.java:3539)
    at XMLDumpImporter.importXMLFile(XMLDumpImporter.java:179)
    at XMLDumpImporter.importXMLFolder(XMLDumpImporter.java:117)
    at XMLDumpImporter.main(XMLDumpImporter.java:48)

at line:

if("body".equalsIgnoreCase(field) && value != null) {
             pstmt.setCharacterStream(f++, new CharArrayReader(value.toCharArray()), value.length());

Which made no total sense as what could possibly be null

Can you show the insert statement and perhaps the create table statement? And the Java code you use to set the value of the clob? — Aleksi Yrttiaho
– Aleksi Yrttiaho, Commented Mar 28, 2011 at 4:57

Aleksi Yrttiaho · Accepted Answer · 2011-03-28 07:38:57Z

6

It seems that you are using setString instead of setCharacterStream to set value of the clob. If you are using setString then the driver probably converts the value to varchar2 which has size limit of 4000 characters.

Hackety hack:

int fieldIndex = 0;
for(String fieldName : fieldsArray) {
   String value = r.getAttributeValue("",field);
   if(value != null && "body".equalsIgnoreCase(fieldName)) {
     pstmt.setCharacterStream(++fieldIndex, new StringReader(value), value.length());
   } else {
     pstmt.setString(++fieldIndex, value);
   }
}

Relevant documentation:

edited Mar 28, 2011 at 7:38

answered Mar 28, 2011 at 5:10

Aleksi Yrttiaho

8,52632 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

13 Comments

aherlambang Over a year ago

well could you use setCharacterStream when you have a String? I modified my code above, how should I use setCharacterStream?

Aleksi Yrttiaho Over a year ago

@EquinoX - if you have the content in a String already you can wrap the String into a CharArrayReader or a ByteArrayInputStream

aherlambang Over a year ago

sometimes it gives me the error Exception in thread "main" java.lang.NullPointerException: if("body".equalsIgnoreCase(fieldName)) { pstmt.setCharacterStream(fieldName, new CharArrayReader(value.toCharArray()), value.length()); }

Aleksi Yrttiaho Over a year ago

check for nullity of the value and proceed as fit

aherlambang Over a year ago

I actually did check if value == null and it never hit there... weird.. I can't figure out where this null is from

|

Collectives™ on Stack Overflow

Convert Java String to CLOB

1 Answer 1

13 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

13 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related