I’ ve little experience with using the SQLContainer, but am giving it a try!
I’m able to read data from my Oracle database. However updating fields in a record of the database is not successful!
I’ve the following very basic code (conpl is a JDBCConnectionPool initialized elsewhere):
OracleGenerator og = new OracleGenerator();
TableQuery tq = new TableQuery("mytable",conpl, og);
tq.setVersionColumn("OPTLOCK");
for (int i = 0; i < sqlCont.size(); i++) {
Object id = sqlCont.getIdByIndex(i);
RowItem item = (RowItem) sqlCont.getItem(id);
item.getItemProperty("MYFIELD").setValue(new BigDecimal(2015));
item.commit();
}
Besides that I’ve an Oracle database with one table called “mytable” with the field “MYFIELD” that I want to update. The table has additional fields like ID (primary key) and OPTLOCK!
The statement:
System.out.println("Demo: " + item.getItemProperty("MYFIELD").getValue());
yields the initial value of that field!
I understand from all the documentation that the commit should do the trick. But when viewing the database from within SQLDeveloper and doing a refresh, the field MYFIELD doesn’ t show the value 2015?
I can see at least one issue: You’re not calling commit() on the SQLContainer. SQLContainer works by default in buffered mode, so you need to call commit() in order to write the changes to the database.
sorry for not being that clear. What I meant is that you don’t need the item.commit() at all. SQLContainer should keep track of changes to the items. After your for-loop you should call sqlCont.commit() once.
If this doesn’t work there’s some bug somewhere. You might want to set the logging level to finest and look at the log for any SQL statements the SQLContainer is doing when you call commit.
If you’re using e.g. Tomcat it should be enough to create a logging.properties file to the root package of your web application and insert “.level=FINEST” into the file. After this the logging output from SQLContainer (including DB statements) will go to the server log (log target depends on server configuration).
You should look at the Tomcat console. If you start it from Eclipse this should be in the Console view (be sure to select the correct console if there’s more than one).