"Incorrect syntax near 'LIMIT'"

Vaadin 6 / 7 / 8 Framework Data Binding
1

My research indicates that, whenever SQLException spits out this message while working with Microsoft SQL Server, a MSSQLGenerator must be used instead of DefaultSQLGenerator. That’s fine, although I have yet to see an example of it used. Anyway, even when I switch to using the MSSQLGenerator, I am still getting that message, along with “Failed to fetch property ids, rolling back”. However, there’s something very strange going on. Here is a paste of the relevant class and the stack trace:

[code]
package info.chrismcgee.sky.vaadinsqltest.dbutil;

import java.sql.SQLException;
import java.util.logging.Logger;

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.FreeformQuery;
import com.vaadin.data.util.sqlcontainer.query.TableQuery;
import com.vaadin.data.util.sqlcontainer.query.generator.MSSQLGenerator;

public class VaadinConnManager {

Logger logger = Logger.getLogger(ConnectionManager.class.getName());

private static final String USERNAME = "web";
private static final String PASSWORD = "web";
private static final String CONN_STRING = "jdbc:sqlserver://192.168.0.248;databaseName=job_orders_2014";

public VaadinConnManager() {
    }

public static SQLContainer getJobs() {
    
    JDBCConnectionPool pool = null;
    SQLContainer container;
    
    try {
        pool = new SimpleJDBCConnectionPool("com.microsoft.sqlserver.jdbc.SQLServerDriver", CONN_STRING, USERNAME, PASSWORD, 2, 5);
        System.out.println("Connected!");
    } catch (SQLException e) {
        System.err.println(e);
    }

// MSSQLGenerator generator = new MSSQLGenerator();
// TableQuery tq = new TableQuery(“Job”, pool, generator);
// tq.setVersionColumn(“proof_spec_date”);

// FreeformQuery query = new FreeformQuery(“SELECT * FROM Job AS j JOIN OrderDetail AS o ON j.job_id = o.order_id”, pool, “job_id”);
FreeformQuery query = new FreeformQuery(“SELECT * FROM Job”, pool, “job_id”);

    try {
        container = new SQLContainer(query);
        return container;
    } catch (SQLException e) {
        System.err.println(e);
    }
    
    return null;
}

}
[/code]I have commented out previous attempts at using TableQuery, which generated the same error messages, figuring that FreeformQuery would work. I was wrong. Here’s the stack trace:

[code]
Apr 03, 2015 2:12:49 PM org.atmosphere.cpr.AtmosphereFramework info
INFO: Atmosphere is using async support: org.atmosphere.container.JSR356AsyncSupport running under container: Apache Tomcat/8.0.18 using javax.servlet/3.0 and jsr356/WebSocket API
Apr 03, 2015 2:12:49 PM org.atmosphere.cpr.AtmosphereFramework info
INFO: Atmosphere Framework 2.2.4.vaadin5 started.
Apr 03, 2015 2:12:49 PM org.atmosphere.cpr.AtmosphereFramework interceptor
INFO: Installed AtmosphereInterceptor Track Message Size Interceptor using | with priority BEFORE_DEFAULT
Connected!
Apr 03, 2015 2:13:01 PM com.vaadin.data.util.sqlcontainer.SQLContainer getPropertyIds

WARNING: Failed to fetch property ids, rolling back

com.microsoft.sqlserver.jdbc.SQLServerException:
Incorrect syntax near ‘LIMIT’.

at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:216)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1515)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:404)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:350)
at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:5696)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:1715)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:180)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:155)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeQuery(SQLServerPreparedStatement.java:285)
at com.vaadin.data.util.sqlcontainer.query.TableQuery.executeQuery(TableQuery.java:529)
at com.vaadin.data.util.sqlcontainer.query.TableQuery.getResults(TableQuery.java:252)
at com.vaadin.data.util.sqlcontainer.SQLContainer.getPropertyIds(SQLContainer.java:1194)
at com.vaadin.data.util.sqlcontainer.SQLContainer.(SQLContainer.java:134)

at info.chrismcgee.sky.vaadinsqltest.dbutil.VaadinConnManager.getJobs(VaadinConnManager.java:38)

at info.chrismcgee.sky.vaadinsqltest.VaadinsqltestUI$1.buttonClick(VaadinsqltestUI.java:41)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:483)
at com.vaadin.event.ListenerMethod.receiveEvent(ListenerMethod.java:508)
at com.vaadin.event.EventRouter.fireEvent(EventRouter.java:198)
at com.vaadin.event.EventRouter.fireEvent(EventRouter.java:161)
at com.vaadin.server.AbstractClientConnector.fireEvent(AbstractClientConnector.java:977)
at com.vaadin.ui.Button.fireClick(Button.java:393)
at com.vaadin.ui.Button$1.click(Button.java:61)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:483)
at com.vaadin.server.ServerRpcManager.applyInvocation(ServerRpcManager.java:168)
at com.vaadin.server.ServerRpcManager.applyInvocation(ServerRpcManager.java:118)
at com.vaadin.server.communication.ServerRpcHandler.handleInvocations(ServerRpcHandler.java:291)
at com.vaadin.server.communication.ServerRpcHandler.handleRpc(ServerRpcHandler.java:184)
at com.vaadin.server.communication.UidlRequestHandler.synchronizedHandleRequest(UidlRequestHandler.java:92)
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:350)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:725)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:291)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:239)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:219)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:106)
at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:501)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:142)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:79)
at org.apache.catalina.valves.AbstractAccessLogValve.invoke(AbstractAccessLogValve.java:610)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:88)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:516)
at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1086)
at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:659)
at org.apache.coyote.http11.Http11NioProtocol$Http11ConnectionHandler.process(Http11NioProtocol.java:223)
at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1558)
at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.run(NioEndpoint.java:1515)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
at java.lang.Thread.run(Thread.java:745)

com.microsoft.sqlserver.jdbc.SQLServerException:
Incorrect syntax near ‘LIMIT’.

[/code]Line 9 shows the “property ids” warning, and the following line is the exception itself. Further down, on line 24, you’ll see where in my class that the error occurred. Notice the line number: 38. If you’ll see in my source code above, line 38 contains a commented-out line. And if I add more blank lines or move things around a bit,
the error stays on line number 38
.

This makes no sense, unless perhaps a part of this program is “stuck” and not seeing any changes that I make to it. Can anyone
please
 help me to understand what’s happening here?

2

Well for one, I know that Sql Server doesn’t support the LIMIT syntax to get a subset of the results. They instead use TOP as the keyword (and the position is also different). You need to be using the MSSQLGenerator or you will continue to get that issue.

3

Right, and if you’ll see in my code, I did, indeed, use the MSSQLGenerator for that TableQuery. However, all of the references to TableQuery are commented out, anyway, as I switched to using FreeformQuery. But when the code is run, errors occur referencing line number 38, which is commented out by now.

I also made another project, duplicating this one to the last detail, and it works just fine. Makes no sense. It must have something to do with Eclipse.

4

Thanks for this - I was seeing the same error when trying to follow the example on SQLContainer, there is no mention of having to use MSSQLGenerator for MSQ SQL.

Anyway using it works as you suggest. I didn’t need to use the freeform option.