New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Fix mssql limit template #405

Closed
zzhu8192 opened this Issue Apr 28, 2013 · 2 comments

Comments

Projects
None yet
2 participants
@zzhu8192

When using limit() on query expressions, you get the following error:

Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near '@p0'.

In looking at SQLServerTemplates.java, looks like doing something like the following addresses the issue. I'm not familiar with the querydsl-sql code base, but it looks like the runtime isn't aware of the extra binding that's introduced in the original commented code here:

            //metadata.addFlag(new QueryFlag(QueryFlag.Position.AFTER_SELECT,
            //        Expressions.template(Integer.class, topTemplate, mod.getLimit())));
          metadata.addFlag(new QueryFlag(Position.AFTER_SELECT, " top ("+mod.getLimit()+") "));

Sample stacktrace:

com.mysema.query.QueryException: Caught SQLServerException for select top ? upc.subcategory_id, upc.start_date, upc.upc_id, upc.upc_type_id, upc.manufacturer_id, upc.category_id, upc.last_screening_date, upc.upc_length, upc.end_date, upc.operation_status_id, upc.upc_number, upc.version, upc.screening_status_id, upc.image_list, upc.default_filtered, upc.upc_number_short, upc.upc_nutritional_info, ua_national.start_date, ua_national.agency_short_product_name, ua_national.uom_quantity, ua_national.agency_custom_attributes, ua_national.upc_id, ua_national.agency_subcategory_id, ua_national.agency_broadband_flag, ua_national.agency_container_size, ua_national.end_date, ua_national.agency_rebate_flag, ua_national.agency_id, ua_national.agency_container_type, ua_national.agency_effective_date, ua_national.upc_unit_of_measure_id, ua_national.version, ua_national.agency_filtered, ua_national.upc_agency_id, ua_national.agency_category_id, ua_national.agency_operation_status_id, ua_national.product_name, ua_national.agency_voucher_flag, ua_national.agency_authorization_status_id
from upc upc
inner join upc_agency ua_national
on ua_national.upc_id = upc.upc_id and ua_national.agency_id = ?
inner join category category
on category.category_id = upc.category_id
inner join subcategory subcategory
on subcategory.subcategory_id = upc.subcategory_id
left join upc_agency ua_search
on ua_search.upc_id = upc.upc_id and ua_search.agency_id = ?
where upc.end_date = ? and upc.screening_status_id = ? and upc.default_filtered = ? and (ua_search.agency_filtered = ? or ua_search.agency_filtered is null)
order by upc.upc_number asc

at com.mysema.query.sql.AbstractSQLQuery.list(AbstractSQLQuery.java:480)
at com.ndg.nupc.services.search.UpcSearchService.searchUpcHelper(UpcSearchService.java:213)
at com.ndg.nupc.services.search.UpcSearchService.searchUpc(UpcSearchService.java:69)
at com.ndg.nupc.services.search.UpcSearchService.searchUpc(UpcSearchService.java:63)
at com.ndg.nupc.services.search.UpcSearchExecutorIntegrationTest.testSearchUpc_ScreeningStatus(UpcSearchExecutorIntegrationTest.java:195)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:601)
at org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:47)
at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12)
at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:44)
at org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:17)
at org.junit.internal.runners.statements.RunBefores.evaluate(RunBefores.java:26)
at org.junit.internal.runners.statements.RunAfters.evaluate(RunAfters.java:27)
at org.junit.runners.ParentRunner.runLeaf(ParentRunner.java:271)
at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:70)
at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:50)
at org.junit.runners.ParentRunner$3.run(ParentRunner.java:238)
at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:63)
at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:236)
at org.junit.runners.ParentRunner.access$000(ParentRunner.java:53)
at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:229)
at org.junit.runners.ParentRunner.run(ParentRunner.java:309)
at org.ops4j.pax.exam.junit.PaxExam.run(PaxExam.java:111)
at org.eclipse.jdt.internal.junit4.runner.JUnit4TestReference.run(JUnit4TestReference.java:50)
at org.eclipse.jdt.internal.junit.runner.TestExecution.run(TestExecution.java:38)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:467)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:683)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:390)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:197)

Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near '@p0'.

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.mysema.query.sql.AbstractSQLQuery.list(AbstractSQLQuery.java:442)
... 30 more

timowest added a commit that referenced this issue Apr 29, 2013

@timowest

This comment has been minimized.

Show comment
Hide comment
@timowest

timowest Apr 29, 2013

Member

Yes, you were right http://stackoverflow.com/questions/175962/dynamic-select-top-var-in-sql-server

Now the top argument is serialized as a string.

Member

timowest commented Apr 29, 2013

Yes, you were right http://stackoverflow.com/questions/175962/dynamic-select-top-var-in-sql-server

Now the top argument is serialized as a string.

@timowest

This comment has been minimized.

Show comment
Hide comment
@timowest

timowest May 27, 2013

Member

Released in 3.2.0

Member

timowest commented May 27, 2013

Released in 3.2.0

@timowest timowest closed this May 27, 2013

@timowest timowest added this to the 3.2.0 milestone Apr 14, 2014

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment