UI History with MSSQL SQLServerException: Incorrect syntax near 'limit'.

[sterlp]

com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near 'limit'.
	at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:259) ~[mssql-jdbc-12.4.2.jre11.jar:na]
	at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1695) ~[mssql-jdbc-12.4.2.jre11.jar:na]
	at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:648) ~[mssql-jdbc-12.4.2.jre11.jar:na]
	at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:567) ~[mssql-jdbc-12.4.2.jre11.jar:na]
	at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7675) ~[mssql-jdbc-12.4.2.jre11.jar:na]
	at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:4137) ~[mssql-jdbc-12.4.2.jre11.jar:na]
	at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:272) ~[mssql-jdbc-12.4.2.jre11.jar:na]
	at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:246) ~[mssql-jdbc-12.4.2.jre11.jar:na]
	at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeQuery(SQLServerPreparedStatement.java:485) ~[mssql-jdbc-12.4.2.jre11.jar:na]
	at com.zaxxer.hikari.pool.ProxyPreparedStatement.executeQuery(ProxyPreparedStatement.java:52) ~[HikariCP-5.0.1.jar:na]
	at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeQuery(HikariProxyPreparedStatement.java) ~[HikariCP-5.0.1.jar:na]
	at org.springframework.jdbc.core.JdbcTemplate$1.doInPreparedStatement(JdbcTemplate.java:732) ~[spring-jdbc-6.1.6.jar:6.1.6]
	at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:658) ~[spring-jdbc-6.1.6.jar:6.1.6]
	at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:723) ~[spring-jdbc-6.1.6.jar:6.1.6]
	at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:748) ~[spring-jdbc-6.1.6.jar:6.1.6]
	at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:804) ~[spring-jdbc-6.1.6.jar:6.1.6]
	at org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate.query(NamedParameterJdbcTemplate.java:218) ~[spring-jdbc-6.1.6.jar:6.1.6]
	at no.bekk.dbscheduler.ui.service.LogLogic.getLogsDirectlyFromDB(LogLogic.java:114) ~[db-scheduler-ui-1.1.2.jar:na]
	at no.bekk.dbscheduler.ui.service.LogLogic.pollLogs(LogLogic.java:57) ~[db-scheduler-ui-1.1.2.jar:na]
	at no.bekk.dbscheduler.ui.controller.LogController.pollLogs(LogController.java:40) ~[db-scheduler-ui-1.1.2.jar:na]
	at java.base/jdk.internal.reflect.DirectMethodHandleAccessor.invoke(DirectMethodHandleAccessor.java:103) ~[na:na]
	at java.base/java.lang.reflect.Method.invoke(Method.java:580) ~[na:na]

[sterlp]

I had a very brief look into the code, just a question: Why don't you use JPA here? I assume most projects would be fine with that?

Alternatively you may use the jdbcTemplate.setMaxRows(1);

[sterlp]

Servlet.service() for servlet [dispatcherServlet] in context with path [] threw exception [Request processing failed: org.springframework.jdbc.UncategorizedSQLException: PreparedStatementCallback; uncategorized SQLException for SQL [select * from scheduled_execution_logs where time_started >= ? and time_finished <= ? order by time_finished desc limit 500]; SQL state [S0001]; error code [102]; Incorrect syntax near 'limit'.] with root cause

this would be correct:

select TOP 500 * from scheduled_execution_logs 
order by time_finished desc

[sterlp]

here is my current fix for this issue:

    // queryBuilder.limit(DEFAULT_LIMIT);
    final NamedParameterJdbcTemplate namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(dataSource);
    namedParameterJdbcTemplate.getJdbcTemplate().setMaxRows(500);
    return namedParameterJdbcTemplate.query(
        queryBuilder.getQuery(), queryBuilder.getParameters(), new LogModelRowMapper(showData, objectMapper));

[geirsagberg]

I agree, JPA or some other abstraction would be preferable to support more SQL engines.
In lieu of this, your fix seems nice, would you mind opening a PR?

[sterlp]

thanks works like a charm.