Hmmm - perhaps I’m doing something wrong… My delete code now looks like this:
==============
public boolean delMaintenanceRecord(Object oid){
boolean retval = false;
try{
Logger.info("Delete Maintenance Record - OID = " + oid);
sqc_MaintRec.setDebugMode(true);
Logger.info(“Debug set on Maintenance Container…”);
retval = sqc_MaintRec.removeItem(oid);
sqc_MaintRec.commit();
sqc_MaintRec.setDebugMode(false);
retval = true;
} catch(Exception e){
Logger.error("Exception: " + e);
e.printStackTrace();
retval = false;
}
return(retval);
}
==============
And the output in the Tomcat catalina.out file is shown below - you can see my own log statement that the debug() call was made. There are a couple of additional log statements (Updated row count, fetched 200 rows etc.) but I don’t see the SQL statement in it’s entirety. Do I have debug set properly? Is there someplace besides catalina.out that I should be looking for output??
Thanks,
=========== catalina.out ======
…
…
Delete Selected: 100
Delete Maintenance Record - OID = 100
Debug set on Maintenance Container…
Updated row count. New count is: 100
Fetched 200 rows starting from 0
Commiting changes through delegate…
Exception: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘AND “startTime” = ‘1/2/3333 1:23’ AND “stopTime” = ‘1/2/3334 1:23’’ at line 1
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘AND “startTime” = ‘1/2/3333 1:23’ AND “stopTime” = ‘1/2/3334 1:23’’ at line 1
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:39)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)
at java.lang.reflect.Constructor.newInstance(Constructor.java:513)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:406)
at com.mysql.jdbc.Util.getInstance(Util.java:381)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1030)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:956)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3558)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3490)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1959)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2109)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2648)
at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2077)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2362)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2280)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2265)
at com.vaadin.addon.sqlcontainer.query.TableQuery.executeUpdate(TableQuery.java:424)
at com.vaadin.addon.sqlcontainer.query.TableQuery.removeRow(TableQuery.java:594)
at com.vaadin.addon.sqlcontainer.SQLContainer.commit(SQLContainer.java:859)
at com.verisign.montools.netcool.ncadmin.db.dbManager.delMaintenanceRecord(dbManager.java:180)
at com.verisign.montools.netcool.ncadmin.EditImpactMaint$5.buttonClick(EditImpactMaint.java:254)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:597)
at com.vaadin.event.ListenerMethod.receiveEvent(ListenerMethod.java:490)
at com.vaadin.event.EventRouter.fireEvent(EventRouter.java:162)
at com.vaadin.ui.AbstractComponent.fireEvent(AbstractComponent.java:1166)
at com.vaadin.ui.Button.fireClick(Button.java:380)
at com.vaadin.ui.Button.changeVariables(Button.java:196)
at com.vaadin.terminal.gwt.server.AbstractCommunicationManager.handleVariableBurst(AbstractCommunicationManager.java:1297)
at com.vaadin.terminal.gwt.server.AbstractCommunicationManager.handleVariables(AbstractCommunicationManager.java:1217)
at com.vaadin.terminal.gwt.server.AbstractCommunicationManager.doHandleUidlRequest(AbstractCommunicationManager.java:733)
at com.vaadin.terminal.gwt.server.CommunicationManager.handleUidlRequest(CommunicationManager.java:296)
at com.vaadin.terminal.gwt.server.AbstractApplicationServlet.service(AbstractApplicationServlet.java:483)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:233)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:191)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:128)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:293)
at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:849)
at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:583)
at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:454)
at java.lang.Thread.run(Thread.java:662)
I am using SQLContainer as Container in ComboBox. I updated Vaadin 6.6.0.pre1. Before update everything seemed to work fine, but after update, my ComboBox filtering is not working properly. I get the next error message (filtering char is “V”):
3.5.2011 14:53:07 com.vaadin.Application terminalError
SEVERE: Terminal error:
java.lang.AbstractMethodError: com.vaadin.addon.sqlcontainer.SQLContainer.addContainerFilter(Lcom/vaadin/data/Container$Filter;)V
at com.vaadin.ui.Select.getOptionsWithFilter(Select.java:336)
at com.vaadin.ui.Select.paintContent(Select.java:212)
at com.vaadin.ui.ComboBox.paintContent(ComboBox.java:98)
…
Line 336 in Select.class seems to be the crash point. Before that line, buildFilter() method is building filter correctly and it is not NULL. I tried to debug the code but it does not lead anywhere, it just crashes in line 336
I have my own FreeformStatementDelegate as delegate in SQLContainer and I am using MySQL procedure in getCountStatement() and getQueryStatement() methods. I do not add any filters in my FreeformStatementDelegate. I repeat that everything was fine until Vaadin 6.6.0.pre1 update (i was using 6.5.7). Anyone has any ideas whats going on?.
The version of SQLContainer in the directory does not support the new container filtering features in Vaadin 6.6. There is a branch of SQLContainer that does, but it is not yet in the directory and is missing one little feature.
A new version of SQLContainer should be available next week.
The version of SQLContainer in the directory does not support the new container filtering features in Vaadin 6.6. There is a branch of SQLContainer that does, but it is not yet in the directory and is missing one little feature.
A new version of SQLContainer should be available next week.
[/quote]
Thanks for quick answer. OK good, so its not something that I’ve done in my code.
We’re pretty busy with Vaadin 6.6.0 at the moment, so I don’t think we’ll be able to make a release of SQLContainer before next week. I’d also rather check into Neil’s problem (above) before releasing the next version.
Anyway, a custom build of SQLContainer should tide you over until the official release is made.
I just released SQLContainer 1.1.0, which has a completely rewritten filtering system based on the new filtering API introduced in Vaadin 6.6.0. The new filtering system allows for much more complex filtering than what was previously possible.
If you’re still on an older version of Vaadin, you should stick to SQLContainer 1.0.1, since 1.1.0 will not work with anything older than Vaadin 6.6.0.
Just stumbled upon the SQLContainer and I like it.
However I have one question about the optimistic locking:
The manual states:
If another user has changed the row and the version number in the
database does not match the version number in memory, an OptimisticLockException is
thrown and you can recover by refreshing the container and allow the user to merge the data.
Caused by: java.lang.IllegalStateException
at com.vaadin.addon.sqlcontainer.query.TableQuery.beginTransaction(TableQuery.java:285)
at com.vaadin.addon.sqlcontainer.SQLContainer.getPage(SQLContainer.java:1104)
at com.vaadin.addon.sqlcontainer.SQLContainer.updateOffsetAndCache(SQLContainer.java:961)
at com.vaadin.addon.sqlcontainer.SQLContainer.getIdByIndex(SQLContainer.java:589)
at com.vaadin.ui.Table.getIdByIndex(Table.java:937)
at com.vaadin.ui.Table.setCurrentPageFirstItemIndex(Table.java:1282)
at com.vaadin.ui.Table.containerItemSetChange(Table.java:3203)
at com.vaadin.addon.sqlcontainer.SQLContainer.fireContentsChange(SQLContainer.java:1362)
at com.vaadin.addon.sqlcontainer.SQLContainer.refresh(SQLContainer.java:745)
That exception is thrown if the SQLContainer is in an illegal state internally. It seems like a transaction is not closed properly or something like that. You may have found a bug in SQLContainer. Could you please try to reproduce the problem in a small test-application attach the code to a new ticket at
dev.vaadin.com ?
try the address book example select a row and then modify it. save. Redit the same row, it will launch an optimistic lock exception. Then trying to do other operations it will launch the illegal state exception…
setting the form’s itemdatasource to null after a commit request to the user to re-select the same row if he wants to re-edit that so optimistic lock and illegal state exception is avoided.
in this case filtering with FILTERINGMODE_CONTAINS not work!
If I’m comment line cbKredName.setItemCaptionPropertyId(“Name”); - filtering will work,
but Combobox display split values Code and Name…
Is this SQLContainer issue?
update:
as it turned out, in this example NOT working event
cbKredName.addListener(new Property.ValueChangeListener() {
public void valueChange(ValueChangeEvent event) {}});
and cbKredName.getValue() is always null, even I’m select any item!!!
The reason was in line “order by name” in SQL select.
Why ordering in sql affect on selecting items in Combobox?
I have a question about the SQLContainer. I build the SQL query from various parameters, and then execute it with my function “execSQL()”.
Building the query
String sql = "SELECT NOLIAISON.NOLIAISON AS \"N° fiscal\", CONCAT(CONCAT(BENEFICIARY.BENEFICIARYLNAME, ' '), BENEFICIARY.BENEFICIARYFNAME) AS \"Nom, prénom\", BENEFICIARY.BIRTHDAYDATE AS \"Date de naissance\" FROM NOLIAISON INNER JOIN BENEFICIARY ON NOLIAISON.IDFAMILY = BENEFICIARY.IDFAMILY WHERE "+where+";";
Executing it
public SQLContainer execSQL(String query) {
// Variables
SimpleJDBCConnectionPool cp = null;
SQLContainer container = null;
try {
// Connexion à la bdd
cp = new SimpleJDBCConnectionPool("COM.ibm.db2.jdbc.app.DB2Driver",
"jdbc:db2:TEST_CM1", "user", "password");
// exécution de la requête
container = new SQLContainer(new FreeformQuery(query, cp));
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return container;
}
Then I set the DataSource of my Table from the returned Container.
SQLContainer sqlc = new Fonctions().execSQL(sql);
tblSearchResults.setContainerDataSource(sqlc);
It works great, data are displayed in my table as attended.
Now here is my question : Is it possible to fetch data directly from the SQLContainer ? Like
where 1 is the row number and myColumn the name of the SQL column. When I try to do something like that, mySqlContainer.getItem(1) is null, and I can’t figure how to get the datas.
in SQLContainer, the itemId is based on the primary keys of the table: it is an Object where the primary key column values are in order given when creating the query (or returned from DB in case of a TableQuery.
I cannot get the filtering up and running with a J2EEConnectionPool. I am using
Vaadin 6.6.4, SQLContainer 1.1.0
Changing the implementation to use the SimpleJDBCConnectionPool works fine but since
have have already a configuration for my Datasource in my Spring configuration my intention is to use
JDBCConnectionPool pool = new J2EEConnectionPool(my configured Datasource);
In case of
JDBCConnectionPool connectionPool = new SimpleJDBCConnectionPool(“…”,“…”, “…”, “”, 1, 1);
(ok, this is not the kind of pool we want to setup) it also fails …
Are there any restriction/requirements for the SQLContainer regarding DB connections ?
Hello, I just started using SQL Container toghether with Vaadin and I really like the framework
However there is one thing I am breaking my head over:
I am using a MySQL database.
when wanting to execute a complex query with a JOIN, you should use the FreeformQueryDelegate,
at this moment I succeeded in implementing my own delegate and enabling sorting.
But the filtering will only work in specific conditions.
After a join statement, ambiguous columns will probably have been fetched from the database, normally you could choose whichever you want by prefixing the column with ‘tablename.’
But when using this syntax I get the following error:
[b]
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column 'i.id' in 'where clause'
[/b]
Strangely the exact same syntax will work when ordering columns, but not when filtering.
Filtering works when just giving the columnname, but for the ambiguous columns I of course get the following error:
[b]
com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Column 'id' in where clause is ambiguous
[/b]
So how should I try to use filtering on such columns?
I tried to trace the function creating the WHERE string: Query.getWhereStringForFilters(arguments), but I can’t seem to figure out a workaround
Does anybody else have experience with this?
It’s hard to say what could be the problem here. Would it be possible for you to create a simplified failing JUnit test so that I can investigate the issue further?
thanks for your fast response,
I haven’t used JUnit before but when trying to create a failing test, I didn’t succeed.
The JUnit test returns no errors or failures…
However I have attached a simple test project and a SQL database snippet to enable you to reproduce the problem.
In the DatabaseHelper class you will probably have to make some changes.
So in the TestApplication class I try to create a FreeformQuery with a SQL Join statement between 2 tables.
Everything works, but after adding the filter I get the error:
in both of my tables the column ‘id’ exists and to select one of them I prefix it with the table name.
When using a non ambiguous column name ( like ‘levnr’ ) there isn’t a problem, the filter works.
But for example when using ‘fact_in.levnr’, i get the same error.
