Sqlcontainer in forced read-only mode, when using DB2

Vaadin 6 / 7 / 8 Framework Data Binding
1

Hello,

Iam having problems setting table in editable mode, when i have sqlcontainer as datasource, and using DB2 database.

This problem seems to be related to DB2 database, because if i change database to MySQL and rewrite queries in QueryDelegate, then eveything works just fine.

private Table contactList = new Table();
     
private SimpleJDBCConnectionPool pool = null;
private SQLContainer sqlCont = null;

try {
    pool = new SimpleJDBCConnectionPool("com.ibm.db2.jcc.DB2Driver",
        "jdbc:db2://testserver:50000/DB:currentSchema=XX;", "test", "test",2,5);
 } catch (SQLException e) {
     showError("Couldn't create the connection pool!");
     e.printStackTrace();
 }
 
try {
     FreeformQuery query = new FreeformQuery("select * from Table1", pool, "ID" );        
     query.setDelegate(new HallintaFreeformQueryDelegate());           
     sqlCont = new SQLContainer(query);

 } catch (SQLException e) {
     showError("Could not create an instance of SQLContainer!");
     e.printStackTrace();
 }  
     
contactList.setContainerDataSource(sqlCont);         
contactList.setSelectable(true);
contactList.setEditable(true);
contactList.setReadOnly(false);
2

Hi
I have the same, or a very similar problem. I am connected to an embedded NetBeans derby database using a derby datasource object to connect.

I did not have the problem when I connected to an external MySQL database. Here are the code extracts (edited slightly for brevity):

The code that sets up the table…
table.setImmediate(true);
table.setSelectable(true);
table.setEditable(true);
table.setWidth(“100.0%”);
table.setNullSelectionAllowed(false);
table.setReadOnly(false);
connectDisplayToSQLTable(table, sqlTableName);
…
The method that sets up the SQLDatasource container for the table:

private int connectDisplayToSQLTable(Table table, String sqlTableName) throws DisplayConnectException{
    try {
        TableQuery q1;
        JDBCConnectionPool connectionPool = DB.getConnectionPool();
        SQLGenerator sqlGenerator = new DB.DerbySQLGenerator();
        q1 = new TableQuery(sqlTableName, connectionPool, sqlGenerator);
        q1.setVersionColumn("VERSION");
        SQLContainer tableContainer = new SQLContainer(q1);
        table.setContainerDataSource(tableContainer);
        table.setPageLength(tableContainer.size());
    } catch (SQLException | UnsupportedFilterException e) {
        throw new DisplayConnectException("Display did not connect ", e);
    }
    return tableContainer.size();
}

Here is the code that gets the Datasource:

public static DataSource getDataSource(String dataBaseName, String userName, String passWord) {
ClientDataSource clientDS;
try {
clientDS = new ClientDataSource();
clientDS.setDatabaseName(dataBaseName);
clientDS.setUser(userName);
clientDS.setPassword(passWord);
} catch (Exception e) {
Log("Datasource creation error: " + e.getMessage());
return null;
}
return clientDS;
}
I get the connectionPool from the datasource using this line in DB.getConnectionPool()

  JDBCConnectionPool pool = new J2EEConnectionPool(ds);

The DerbySQLGenerator is Janko Dimitroff’s excellent simple extension to the DefaultSQLGenerator
(see https://vaadin.com/forum#!/thread/843994) which allows it to work with Derby. Otherwise it is
a standard SQLContainer

I originally tried to setting up a formgroup to edit the values separately, since I wanted full CRUD capability.
In this case, the fields are editable until they are bound to the table with the following:

    Object clickedItemId = table.getValue();
    if (clickedItemId != null) {
       editorFields.setItemDataSource(table.getItem(clickedItemId));
    }

They then become readonly.

I guess it’s something to do with the SQLgenerator but haven’t had time yet to switch back to MySQL to pin down the problem. Before doing that I thought I’d follow up on Joni Lenkkeri’s original query to signal the similarity of the issues and see if anyone else has experienced this (or if Joni has found a solution)
Alan