DefaultSQLGenerator behaviour for Oracle database

Vaadin 6 / 7 / 8
1

Hello,

I am using the code below

public void initialize() throws SQLException {
        My2ndTableEditorUI app = (My2ndTableEditorUI) UI.getCurrent();
        QueryDelegate query = new TableQuery(tableName, app.getJdbcPool());
        container = new SQLContainer(query); // <-- this is the important line where error occurs
        table.setContainerDataSource(container);
    }

While running a Vaadin7 application against an Oracle database (tried also Mysql, HSQLDB - both work fine), a problem occurs.
I noticed that the code below …

public class DefaultSQLGenerator 
////
if (pagelength != 0) {
            generateLimits(query, offset, pagelength);
        }
//////

…produces the SQL query below …

SELECT * FROM CUSTOMER ORDER BY "CUST_ID" ASC LIMIT 1 OFFSET 0

This is incorrect syntax for Oracle database, because “LIMIT 1 OFFSET 0” is meaningless for Oracle and cannot recognize.

Therefore Tomcat returns …

root cause java.sql.SQLSyntaxErrorException: ORA-00933: SQL command not properly ended

…whence …
[ORA-00933: SQL command not properly ended ]
is the Oracle error.

So, I am searching for a workaround. Any help?

Environment

Intellij IDEA
Tomcat 7
Oracle 10g, or 11g
JDBC driver: ojdbc6-11.2.0.1.0.jar

java -version
java version “1.7.0”
Java™ SE Runtime Environment (build 1.7.0-b147)
Java HotSpot™ Client VM (build 21.0-b17, mixed mode, sharing)

javac -version
javac 1.6.0_21

Thank you very much.

dim liakop.

2

Hi,

The DefaultSQLGenerator only works for HSQLDB, MySQL and PosttgreSQL. You should give the TableQuery a third parameter in the code block above. Try giving it a new OracleGenerator and it should generate proper queries for your database.

-tepi

3

Indeed! I saw it in book of Vaadin and also in a previous post. Sorry. Tried it and works nice.
Thank you.