Hi everyone!
I am having trouble to add a new item to a vaadin table and commit it to our database. I am getting exception ORA-22922: nonexistent LOB value.
I am using sqlContainer and a FreeformQuery to display the content of an Oracle 11g database in my vaadin table. In order to display CLOB datatype, I implemented a
Converter<String, oracle.sql.CLOB>. At the moment I am able read, update and delete items from my table. In order to update a CLOB, I had to used a trick in order to keep my connection
to the CLOB alive. However I am unable to commit a new item to my database.
I would be very thankfull for any help. 
John
source code of my connection, my sqlContainer and my table
JDBCConnectionPool connectionPool = new SimpleJDBCConnectionPool("oracle.jdbc.OracleDriver", "jdbc:oracle:thin:@oracleserver:port:dbName", user, password, 2, 5);
FreeformQuery query = new FreeformQuery("SELECT * FROM table", connectionPool, primaryKey);
FreeformDelegateFailureClassImplementation delegate = new FreeformDelegateFailureClassImplementation();
query.setDelegate(delegate);
try {
sqlContainer = new SQLContainer(query);
} catch (SQLException e) {
e.printStackTrace();
}
Table table = table = new Table();
table.setContainerDataSource(sqlContainer);
I bound the selected item of my table to several TextFields, which i use to update my table.
TextField id = new TextField("ID", sqlContainer.getContainerProperty("ID", table.getValue()));
id.setConverter(new MyNumberToStringConverter());
TextField description = new TextArea("DESCRIPTION", sqlContainer.getContainerProperty("DESCRIPTION", table.getValue()));
description.setConverter(new OracleClobToStringConverter());
my Converter
import java.io.BufferedReader;
import java.io.IOException;
import java.io.Reader;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.Locale;
import oracle.jdbc.OracleConnection;
import oracle.sql.CLOB;
import com.vaadin.data.util.converter.Converter;
public class OracleClobToStringConverter implements Converter<String, oracle.sql.CLOB> {
private OracleConnection oracleConnection;
public OracleClobToStringConverter() {
}
public OracleClobToStringConverter(Connection connection) {
oracleConnection = (OracleConnection) connection;
}
@Override
public CLOB convertToModel(String value, Class<? extends oracle.sql.CLOB> targetType, Locale locale)
throws com.vaadin.data.util.converter.Converter.ConversionException {
if (targetType != getModelType()) {
throw new ConversionException("Converter only supports " + getModelType().getName() + " (targetType was " + targetType.getName() + ")");
}
oracle.sql.CLOB clob = null;
StringBuilder sb = null;
try {
clob = oracle.sql.CLOB.createTemporary(oracleConnection, true, oracle.sql.CLOB.DURATION_SESSION);
if(clob!=null)
clob.putString(1, value);
} catch (SQLException e) {
System.err.println(e);
}
return clob;
}
@Override
public String convertToPresentation(CLOB value, Class<? extends String> targetType, Locale locale)
throws com.vaadin.data.util.converter.Converter.ConversionException {
//clob hack to keep connection :)
initConnection(value);
if (targetType != getPresentationType()) {
throw new ConversionException("Converter only supports " + getPresentationType().getName() + " (targetType was " + targetType.getName() + ")");
}
StringBuilder sb = new StringBuilder();
try {
Reader reader = value.getCharacterStream();
BufferedReader br = new BufferedReader(reader);
String line;
while(null != (line = br.readLine())) {
sb.append(line);
}
br.close();
} catch (SQLException e) {
System.err.println(e);
} catch (IOException e) {
System.err.println(e);
}
return sb.toString();
}
@Override
public Class<CLOB> getModelType() {
return CLOB.class;
}
@Override
public Class<String> getPresentationType() {
return String.class;
}
private void initConnection(CLOB value) {
try {
oracleConnection = value.getConnection();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
In order to add an item, i tryed to use the same TextFields, which I already used to display and update an item. This is where I can’t get it to work. This what I tryed so far.
private class AddListener implements Button.ClickListener {
@Override
public void buttonClick(ClickEvent event) {
Object itemId = table.addItem();
table.setValue(id);
table.select(itemId);
oracle.sql.CLOB clob = null;
OracleConnection oracleConnection = (OracleConnection) connection;
try {
clob = oracle.sql.CLOB.createTemporary(oracleConnection, true, oracle.sql.CLOB.DURATION_SESSION);
} catch (SQLException e) {
e.printStackTrace();
}
}
}