Vaadin SQLSyntaxErrorException using JavaDB/Derby: Encountered "LIMIT" at line 1, column 41

Question

Following this tutorial about Vaadin 7 and SQLContainer I encountered the following problem. I am coding inside Netbeans 8.0.2 and would like to use the embedded JavaDB/Derby database over JDBC in this early development stage.
I am trying to databind a table in my Vaadin application to show data. Inside init(VaadinRequest vaadinRequest) I call my database() method.

import com.vaadin.data.util.sqlcontainer.SQLContainer;
import com.vaadin.data.util.sqlcontainer.connection.JDBCConnectionPool;
import com.vaadin.data.util.sqlcontainer.connection.SimpleJDBCConnectionPool;
import com.vaadin.data.util.sqlcontainer.query.TableQuery;

private void database() {
    System.out.println("database()");
    JDBCConnectionPool pool = null;
    try {
        pool = new SimpleJDBCConnectionPool("org.apache.derby.jdbc.ClientDriver", 
                "jdbc:derby://localhost:1527/gr", "a", "a", 2, 5);
        TableQuery tq = new TableQuery("GROCERY", pool);
        tq.setVersionColumn("OPTLOCK");
        SQLContainer container = new SQLContainer(tq);  // <-- raises exception
        //container.setAutoCommit(true);
        Item i = container.addItem(1);
        Object props = i.getItemPropertyIds();
    } catch (SQLException ex) {
        Logger.getLogger(MyUI.class.getName()).log(Level.SEVERE, null, ex);
    }
}

Instead of the data I get this exception. What am I doing wrong or missing?

java.sql.SQLSyntaxErrorException: Syntax error: Encountered "LIMIT" at line 1, column 41.
at org.apache.derby.client.am.SQLExceptionFactory40.getSQLException(Unknown Source)
at org.apache.derby.client.am.SqlException.getSQLException(Unknown Source)
at org.apache.derby.client.am.Connection.prepareStatement(Unknown Source)
at com.vaadin.data.util.sqlcontainer.query.TableQuery.executeQuery(TableQuery.java:526)
at com.vaadin.data.util.sqlcontainer.query.TableQuery.getResults(TableQuery.java:252)
at com.vaadin.data.util.sqlcontainer.SQLContainer.getPropertyIds(SQLContainer.java:1200)
at com.vaadin.data.util.sqlcontainer.SQLContainer.<init>(SQLContainer.java:134)
at org.darugna.MyUI.database(MyUI.java:294)
at org.darugna.MyUI.init(MyUI.java:194)
at com.vaadin.ui.UI.doInit(UI.java:675)
at com.vaadin.server.communication.UIInitHandler.getBrowserDetailsUI(UIInitHandler.java:214)
at com.vaadin.server.communication.UIInitHandler.synchronizedHandleRequest(UIInitHandler.java:74)
at com.vaadin.server.SynchronizedRequestHandler.handleRequest(SynchronizedRequestHandler.java:41)
at com.vaadin.server.VaadinService.handleRequest(VaadinService.java:1408)
at com.vaadin.server.VaadinServlet.service(VaadinServlet.java:351)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:790)
at org.apache.catalina.core.StandardWrapper.service(StandardWrapper.java:1682)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:344)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:214)
at org.glassfish.tyrus.servlet.TyrusServletFilter.doFilter(TyrusServletFilter.java:295)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:256)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:214)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:316)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:160)
at org.apache.catalina.core.StandardPipeline.doInvoke(StandardPipeline.java:734)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:673)
at com.sun.enterprise.web.WebPipeline.invoke(WebPipeline.java:99)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:174)
at org.apache.catalina.connector.CoyoteAdapter.doService(CoyoteAdapter.java:415)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:282)
at com.sun.enterprise.v3.services.impl.ContainerMapper$HttpHandlerCallable.call(ContainerMapper.java:459)
at com.sun.enterprise.v3.services.impl.ContainerMapper.service(ContainerMapper.java:167)
at org.glassfish.grizzly.http.server.HttpHandler.runService(HttpHandler.java:201)
at org.glassfish.grizzly.http.server.HttpHandler.doHandle(HttpHandler.java:175)
at org.glassfish.grizzly.http.server.HttpServerFilter.handleRead(HttpServerFilter.java:235)
at org.glassfish.grizzly.filterchain.ExecutorResolver$9.execute(ExecutorResolver.java:119)
at org.glassfish.grizzly.filterchain.DefaultFilterChain.executeFilter(DefaultFilterChain.java:284)
at org.glassfish.grizzly.filterchain.DefaultFilterChain.executeChainPart(DefaultFilterChain.java:201)
at org.glassfish.grizzly.filterchain.DefaultFilterChain.execute(DefaultFilterChain.java:133)
at org.glassfish.grizzly.filterchain.DefaultFilterChain.process(DefaultFilterChain.java:112)
at org.glassfish.grizzly.ProcessorExecutor.execute(ProcessorExecutor.java:77)
at org.glassfish.grizzly.nio.transport.TCPNIOTransport.fireIOEvent(TCPNIOTransport.java:561)
at org.glassfish.grizzly.strategies.AbstractIOStrategy.fireIOEvent(AbstractIOStrategy.java:112)
at org.glassfish.grizzly.strategies.WorkerThreadIOStrategy.run0(WorkerThreadIOStrategy.java:117)
at org.glassfish.grizzly.strategies.WorkerThreadIOStrategy.access$100(WorkerThreadIOStrategy.java:56)
at org.glassfish.grizzly.strategies.WorkerThreadIOStrategy$WorkerThreadRunnable.run(WorkerThreadIOStrategy.java:137)
at org.glassfish.grizzly.threadpool.AbstractThreadPool$Worker.doWork(AbstractThreadPool.java:565)
at org.glassfish.grizzly.threadpool.AbstractThreadPool$Worker.run(AbstractThreadPool.java:545)
at java.lang.Thread.run(Thread.java:745)
Caused by: org.apache.derby.client.am.SqlException: Syntax error: Encountered "LIMIT" at line 1, column 41.
at org.apache.derby.client.am.Statement.completeSqlca(Unknown Source)
at org.apache.derby.client.net.NetStatementReply.parsePrepareError(Unknown Source)
at org.apache.derby.client.net.NetStatementReply.parsePRPSQLSTTreply(Unknown Source)
at org.apache.derby.client.net.NetStatementReply.readPrepareDescribeOutput(Unknown Source)
at org.apache.derby.client.net.StatementReply.readPrepareDescribeOutput(Unknown Source)
at org.apache.derby.client.net.NetStatement.readPrepareDescribeOutput_(Unknown Source)
at org.apache.derby.client.am.Statement.readPrepareDescribeOutput(Unknown Source)
at org.apache.derby.client.am.PreparedStatement.readPrepareDescribeInputOutput(Unknown Source)
at org.apache.derby.client.am.PreparedStatement.flowPrepareDescribeInputOutput(Unknown Source)
at org.apache.derby.client.am.PreparedStatement.prepare(Unknown Source)
at org.apache.derby.client.am.Connection.prepareStatementX(Unknown Source)
... 47 more

This is my table's schema

CREATE TABLE grocery (
    id INTEGER PRIMARY KEY,
    OPTLOCK INTEGER,
    name TEXT,
    category TEXT,
    price INTEGER
);

Patroclus

what is this DerbySQLGenerator()? – Elusive

@Elusive I was experimenting to solve the issue. Thanks for noticing, as I have pasted here the modifed code and not the exact one I run. Now I changed as it was originally. – Patroclus

i may have never used derby, but the error would indicate, that derby does not support limit or has some other syntax for it. see db.apache.org/derby/faq.html#limit. if one searches for DerbySQLGenerator on github github.com/…, that would indicate, that you wre already on the right track. – Elusive

Answer 1

The problem lies in the fact that Derby does not support SQL LIMIT.

One must supply its own SQL generator that creates the query in some other way.

I'm posting here the solution by Janko Dimitroff that I found in Vaadin forums.

import com.vaadin.data.util.sqlcontainer.query.generator.DefaultSQLGenerator;

/**  
 * @author Janko Dimitroff
 */
@SuppressWarnings("serial")
public class DerbySQLGenerator extends DefaultSQLGenerator {

    public DerbySQLGenerator() {
    }

    /** Construct a DerbySQLGenerator with the specified identifiers for start and end of quoted strings. The identifiers
     * may be different depending on the database engine and it's settings.
     * 
     * @param quoteStart the identifier (character) denoting the start of a quoted string
     * @param quoteEnd the identifier (character) denoting the end of a quoted string */
    public DerbySQLGenerator(String quoteStart, String quoteEnd) {
        super(quoteStart, quoteEnd);
    }

    /** Generates the LIMIT and OFFSET clause.
     * 
     * @param sb StringBuffer to which the clause is appended.
     * @param offset Value for offset.
     * @param pagelength Value for pagelength.
     * @return StringBuffer with LIMIT and OFFSET clause added. */
    protected StringBuffer generateLimits(StringBuffer sb, int offset, int pagelength) {
        sb.append(" OFFSET ").append(offset).append(" ROWS").append(" FETCH NEXT ").append(pagelength).append(" ROWS ONLY");
        return sb;
    }
}

Patroclus

Answer 2

For anybody looking for code to replace the missing LIMIT in Apache Derby, use FIRST instead, as referenced here and provided as an example in the Derby docs

SELECT * FROM myLargeTable OFFSET 0 ROWS FETCH NEXT 100 ROWS ONLY

Another more messy method is given here in the Derby FAQ:

SELECT * FROM (SELECT ROW_NUMBER() OVER() AS rownum, myLargeTable.* FROM myLargeTable) AS tmp WHERE rownum <= 100;

This code is equivalent to using LIMIT (impossible in Derby) as so:

SELECT * FROM myLargeTable LIMIT 100;

Source: https://db.apache.org/derby/faq.html#limit

For those wanting to do ordered sorts in Derby, here's an example:

SELECT * FROM myLargeTable ORDER BY someColumn DESC OFFSET 0 ROWS FETCH NEXT 100 ROWS ONLY

Schnell