The incoming request has too many parameters. The server supports a maximum of 2100 parameters

Copper Contributor

Team , 
I'm facing a strange issue in my project. let me explain the tech ladder that I'm using 

JDBC Version : 7.4.1.jre11

MSSQL Version : Microsoft SQL Server 2016 (SP3-GDR) (KB5021129) - 13.0.6430.49 (X64) - Standard Edition (64-bit) on Windows Server 2016 Datacenter

Framework : hibernate-core 5.4.33

Issue : I have method which takes list of codes and process query in table. I have only 10 items in the list. When the method executes I'm getting the error which I mentioned in the subject , but the same code working fine in my localhost. 

 

Detail Explanation : When we deploy the code its working for a day or two and then we started getting the error. We are not sure why its happening maybe the error message is misleading. can anyone help me to understand what's going on ?

 

Below is the code : 

public List<WorkOrderMaterial> getWorkOrdersMaterialList(Token token, List<String> workOrderCodes,
                                                             Timestamp lastModified) throws DaoException {
        LOGGER.info("Method : getWorkOrdersMaterialList() WO List Size :{}",workOrderCodes.size());
        List<WorkOrderMaterial> workOrderMaterialList = new ArrayList<>();
        Session tenantSession = null;
        Query query = null;
        try {
            tenantSession = openTenantSessionReadUncommitted(token);
            if (lastModified != null) {
                WORKORDERS_MATERIALS = WORKORDERS_MATERIALS
                        + " and st.lastModified >= CONVERT(datetime, :lastModified)";

                query = tenantSession.createNativeQuery(WORKORDERS_MATERIALS, WorkOrderMaterial.class);
                query.setParameterList("workordercodes", workOrderCodes);
                query.setParameter("companyCode", token.getCompanyContext());
                query.setParameter("lastModified", lastModified.toString());
            } else {
                query = tenantSession.createNativeQuery(WORKORDERS_MATERIALS, WorkOrderMaterial.class);
                query.setParameterList("workordercodes", workOrderCodes);
                query.setParameter("companyCode", token.getCompanyContext());

            }
            workOrderMaterialList = query.list();
        } catch (Exception e) {
            LOGGER.error("Exception retrieving Work Order Material method getWorkOrdersMaterialList(): {}", e.getMessage(),e);
            throw new DaoException("Exception retrieving Work Order Material History", e);
        } finally {
            closeTenantSession(tenantSession);
        }
        return workOrderMaterialList;
    }

and below is the full error trace 

Exception retrieving Work Order Material method getWorkOrdersMaterialList(): org.hibernate.exception.SQLGrammarException: could not extract ResultSet
9/15/2023, 12:28:42.000 PM javax.persistence.PersistenceException: org.hibernate.exception.SQLGrammarException: could not extract ResultSet
9/15/2023, 12:28:42.869 PM at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:154)
9/15/2023, 12:28:42.869 PM at org.hibernate.query.internal.AbstractProducedQuery.list(AbstractProducedQuery.java:1613)
9/15/2023, 12:28:42.869 PM at com.dexterchaney.fieldtech.infrastructure.database.WorkOrderMaterialDaoImpl.getWorkOrdersMaterialList(WorkOrderMaterialDaoImpl.java:894)
9/15/2023, 12:28:42.869 PM at com.dexterchaney.fieldtech.core.service.WorkOrderMaterialServiceImpl.getWorkOrdersMaterialList(WorkOrderMaterialServiceImpl.java:1270)
9/15/2023, 12:28:42.869 PM at com.dexterchaney.fieldtech.resources.MaterialsResource.getWorkOrdersMaterial(MaterialsResource.java:792)
9/15/2023, 12:28:42.869 PM at com.dexterchaney.fieldtech.resources.MaterialsResource.getAllWorkOrdersMaterialHistory(MaterialsResource.java:889)
9/15/2023, 12:28:42.869 PM at com.dexterchaney.fieldtech.resources.MaterialsResource.getWorkOrderMaterialHistory(MaterialsResource.java:249)
9/15/2023, 12:28:42.869 PM at jdk.internal.reflect.GeneratedMethodAccessor709.invoke(Unknown Source)
9/15/2023, 12:28:42.869 PM at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
9/15/2023, 12:28:42.869 PM at java.base/java.lang.reflect.Method.invoke(Unknown Source)
9/15/2023, 12:28:42.869 PM at org.glassfish.jersey.server.model.internal.ResourceMethodInvocationHandlerFactory.lambda$static$0(ResourceMethodInvocationHandlerFactory.java:52)
9/15/2023, 12:28:42.869 PM at org.glassfish.jersey.server.model.internal.AbstractJavaResourceMethodDispatcher$1.run(AbstractJavaResourceMethodDispatcher.java:124)
9/15/2023, 12:28:42.869 PM at org.glassfish.jersey.server.model.internal.AbstractJavaResourceMethodDispatcher.invoke(AbstractJavaResourceMethodDispatcher.java:167)
9/15/2023, 12:28:42.869 PM at org.glassfish.jersey.server.model.internal.JavaResourceMethodDispatcherProvider$ResponseOutInvoker.doDispatch(JavaResourceMethodDispatcherProvider.java:176)
9/15/2023, 12:28:42.869 PM at org.glassfish.jersey.server.model.internal.AbstractJavaResourceMethodDispatcher.dispatch(AbstractJavaResourceMethodDispatcher.java:79)
9/15/2023, 12:28:42.869 PM at org.glassfish.jersey.server.model.ResourceMethodInvoker.invoke(ResourceMethodInvoker.java:475)
9/15/2023, 12:28:42.869 PM at org.glassfish.jersey.server.model.ResourceMethodInvoker.apply(ResourceMethodInvoker.java:397)
9/15/2023, 12:28:42.869 PM at org.glassfish.jersey.server.model.ResourceMethodInvoker.apply(ResourceMethodInvoker.java:81)
9/15/2023, 12:28:42.869 PM at org.glassfish.jersey.server.ServerRuntime$1.run(ServerRuntime.java:255)
9/15/2023, 12:28:42.869 PM at org.glassfish.jersey.internal.Errors$1.call(Errors.java:248)
9/15/2023, 12:28:42.869 PM at org.glassfish.jersey.internal.Errors$1.call(Errors.java:244)
9/15/2023, 12:28:42.869 PM at org.glassfish.jersey.internal.Errors.process(Errors.java:292)
9/15/2023, 12:28:42.869 PM at org.glassfish.jersey.internal.Errors.process(Errors.java:274)
9/15/2023, 12:28:42.869 PM at org.glassfish.jersey.internal.Errors.process(Errors.java:244)
9/15/2023, 12:28:42.869 PM at org.glassfish.jersey.process.internal.RequestScope.runInScope(RequestScope.java:265)
9/15/2023, 12:28:42.869 PM at org.glassfish.jersey.server.ServerRuntime.process(ServerRuntime.java:234)
9/15/2023, 12:28:42.869 PM at org.glassfish.jersey.server.ApplicationHandler.handle(ApplicationHandler.java:680)
9/15/2023, 12:28:42.869 PM at org.glassfish.jersey.servlet.WebComponent.serviceImpl(WebComponent.java:394)
9/15/2023, 12:28:42.869 PM at org.glassfish.jersey.servlet.WebComponent.service(WebComponent.java:346)
9/15/2023, 12:28:42.869 PM at org.glassfish.jersey.servlet.ServletContainer.service(ServletContainer.java:366)
9/15/2023, 12:28:42.869 PM at org.glassfish.jersey.servlet.ServletContainer.service(ServletContainer.java:319)
9/15/2023, 12:28:42.869 PM at org.glassfish.jersey.servlet.ServletContainer.service(ServletContainer.java:205)
9/15/2023, 12:28:42.869 PM at org.eclipse.jetty.servlet.ServletHolder.handle(ServletHolder.java:799)
9/15/2023, 12:28:42.869 PM at org.eclipse.jetty.servlet.ServletHandler$ChainEnd.doFilter(ServletHandler.java:1656)
9/15/2023, 12:28:42.869 PM at io.dropwizard.servlets.ThreadNameFilter.doFilter(ThreadNameFilter.java:35)
9/15/2023, 12:28:42.869 PM at org.eclipse.jetty.servlet.FilterHolder.doFilter(FilterHolder.java:193)
9/15/2023, 12:28:42.869 PM at org.eclipse.jetty.servlet.ServletHandler$Chain.doFilter(ServletHandler.java:1626)
9/15/2023, 12:28:42.869 PM at io.dropwizard.jersey.filter.AllowedMethodsFilter.handle(AllowedMethodsFilter.java:47)
9/15/2023, 12:28:42.869 PM at io.dropwizard.jersey.filter.AllowedMethodsFilter.doFilter(AllowedMethodsFilter.java:41)
9/15/2023, 12:28:42.869 PM at org.eclipse.jetty.servlet.FilterHolder.doFilter(FilterHolder.java:193)
9/15/2023, 12:28:42.869 PM at org.eclipse.jetty.servlet.ServletHandler$Chain.doFilter(ServletHandler.java:1626)
9/15/2023, 12:28:42.869 PM at com.dexterchaney.fieldtech.security.SecurityHeaderResponseFilter.doFilter(SecurityHeaderResponseFilter.java:27)
9/15/2023, 12:28:42.869 PM at org.eclipse.jetty.servlet.FilterHolder.doFilter(FilterHolder.java:193)
9/15/2023, 12:28:42.869 PM at org.eclipse.jetty.servlet.ServletHandler$Chain.doFilter(ServletHandler.java:1626)
9/15/2023, 12:28:42.869 PM at org.eclipse.jetty.servlets.CrossOriginFilter.handle(CrossOriginFilter.java:319)
9/15/2023, 12:28:42.869 PM at org.eclipse.jetty.servlets.CrossOriginFilter.doFilter(CrossOriginFilter.java:273)
9/15/2023, 12:28:42.869 PM at org.eclipse.jetty.servlet.FilterHolder.doFilter(FilterHolder.java:193)
9/15/2023, 12:28:42.869 PM at org.eclipse.jetty.servlet.ServletHandler$Chain.doFilter(ServletHandler.java:1626)
9/15/2023, 12:28:42.869 PM at com.google.inject.servlet.FilterChainInvocation.doFilter(FilterChainInvocation.java:89)
9/15/2023, 12:28:42.869 PM at com.google.inject.servlet.ManagedFilterPipeline.dispatch(ManagedFilterPipeline.java:121)
9/15/2023, 12:28:42.869 PM at com.google.inject.servlet.GuiceFilter.doFilter(GuiceFilter.java:133)
9/15/2023, 12:28:42.869 PM at org.eclipse.jetty.servlet.FilterHolder.doFilter(FilterHolder.java:193)
9/15/2023, 12:28:42.869 PM at org.eclipse.jetty.servlet.ServletHandler$Chain.doFilter(ServletHandler.java:1626)
9/15/2023, 12:28:42.869 PM at org.eclipse.jetty.servlet.ServletHandler.doHandle(ServletHandler.java:552)
9/15/2023, 12:28:42.869 PM at org.eclipse.jetty.server.handler.ScopedHandler.nextHandle(ScopedHandler.java:233)
9/15/2023, 12:28:42.869 PM at org.eclipse.jetty.server.handler.ContextHandler.doHandle(ContextHandler.java:1440)
9/15/2023, 12:28:42.869 PM at org.eclipse.jetty.server.handler.ScopedHandler.nextScope(ScopedHandler.java:188)
9/15/2023, 12:28:42.869 PM at org.eclipse.jetty.servlet.ServletHandler.doScope(ServletHandler.java:505)
9/15/2023, 12:28:42.869 PM at org.eclipse.jetty.server.handler.ScopedHandler.nextScope(ScopedHandler.java:186)
9/15/2023, 12:28:42.869 PM at org.eclipse.jetty.server.handler.ContextHandler.doScope(ContextHandler.java:1355)
9/15/2023, 12:28:42.869 PM at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:141)
9/15/2023, 12:28:42.869 PM at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:127)
9/15/2023, 12:28:42.869 PM at com.codahale.metrics.jetty9.InstrumentedHandler.handle(InstrumentedHandler.java:313)
9/15/2023, 12:28:42.869 PM at io.dropwizard.jetty.RoutingHandler.handle(RoutingHandler.java:52)
9/15/2023, 12:28:42.869 PM at org.eclipse.jetty.server.handler.gzip.GzipHandler.handle(GzipHandler.java:772)
9/15/2023, 12:28:42.869 PM at org.eclipse.jetty.server.handler.RequestLogHandler.handle(RequestLogHandler.java:54)
9/15/2023, 12:28:42.869 PM at org.eclipse.jetty.server.handler.StatisticsHandler.handle(StatisticsHandler.java:181)
9/15/2023, 12:28:42.869 PM at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:127)
9/15/2023, 12:28:42.869 PM at org.eclipse.jetty.server.Server.handle(Server.java:516)
9/15/2023, 12:28:42.869 PM at org.eclipse.jetty.server.HttpChannel.lambda$handle$1(HttpChannel.java:487)
9/15/2023, 12:28:42.869 PM at org.eclipse.jetty.server.HttpChannel.dispatch(HttpChannel.java:732)
9/15/2023, 12:28:42.869 PM at org.eclipse.jetty.server.HttpChannel.handle(HttpChannel.java:479)
9/15/2023, 12:28:42.869 PM at org.eclipse.jetty.server.HttpConnection.onFillable(HttpConnection.java:277)
9/15/2023, 12:28:42.869 PM at org.eclipse.jetty.io.AbstractConnection$ReadCallback.succeeded(AbstractConnection.java:311)
9/15/2023, 12:28:42.869 PM at org.eclipse.jetty.io.FillInterest.fillable(FillInterest.java:105)
9/15/2023, 12:28:42.869 PM at org.eclipse.jetty.io.ChannelEndPoint$1.run(ChannelEndPoint.java:104)
9/15/2023, 12:28:42.869 PM at org.eclipse.jetty.util.thread.strategy.EatWhatYouKill.runTask(EatWhatYouKill.java:338)
9/15/2023, 12:28:42.869 PM at org.eclipse.jetty.util.thread.strategy.EatWhatYouKill.doProduce(EatWhatYouKill.java:315)
9/15/2023, 12:28:42.869 PM at org.eclipse.jetty.util.thread.strategy.EatWhatYouKill.tryProduce(EatWhatYouKill.java:173)
9/15/2023, 12:28:42.869 PM at org.eclipse.jetty.util.thread.strategy.EatWhatYouKill.run(EatWhatYouKill.java:131)
9/15/2023, 12:28:42.869 PM at org.eclipse.jetty.util.thread.ReservedThreadExecutor$ReservedThread.run(ReservedThreadExecutor.java:409)
9/15/2023, 12:28:42.869 PM at org.eclipse.jetty.util.thread.QueuedThreadPool.runJob(QueuedThreadPool.java:883)
9/15/2023, 12:28:42.869 PM at org.eclipse.jetty.util.thread.QueuedThreadPool$Runner.run(QueuedThreadPool.java:1034)
9/15/2023, 12:28:42.869 PM at java.base/java.lang.Thread.run(Unknown Source)
9/15/2023, 12:28:42.869 PM Caused by: org.hibernate.exception.SQLGrammarException: could not extract ResultSet
9/15/2023, 12:28:42.869 PM at org.hibernate.exception.internal.SQLStateConversionDelegate.convert(SQLStateConversionDelegate.java:103)
9/15/2023, 12:28:42.869 PM at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:42)
9/15/2023, 12:28:42.870 PM at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:113)
9/15/2023, 12:28:42.870 PM at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:99)
9/15/2023, 12:28:42.870 PM at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:67)
9/15/2023, 12:28:42.870 PM at org.hibernate.loader.Loader.getResultSet(Loader.java:2297)
9/15/2023, 12:28:42.870 PM at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:2050)
9/15/2023, 12:28:42.870 PM at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:2012)
9/15/2023, 12:28:42.870 PM at org.hibernate.loader.Loader.doQuery(Loader.java:948)
9/15/2023, 12:28:42.870 PM at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:349)
9/15/2023, 12:28:42.870 PM at org.hibernate.loader.Loader.doList(Loader.java:2843)
9/15/2023, 12:28:42.870 PM at org.hibernate.loader.Loader.doList(Loader.java:2825)
9/15/2023, 12:28:42.870 PM at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2657)
9/15/2023, 12:28:42.870 PM at org.hibernate.loader.Loader.list(Loader.java:2652)
9/15/2023, 12:28:42.870 PM at org.hibernate.loader.custom.CustomLoader.list(CustomLoader.java:338)
9/15/2023, 12:28:42.870 PM at org.hibernate.internal.SessionImpl.listCustomQuery(SessionImpl.java:2141)
9/15/2023, 12:28:42.870 PM at org.hibernate.internal.AbstractSharedSessionContract.list(AbstractSharedSessionContract.java:1169)
9/15/2023, 12:28:42.000 PM at org.hibernate.query.internal.NativeQueryImpl.doList(NativeQueryImpl.java:176)
9/15/2023, 12:28:42.870 PM at org.hibernate.query.internal.AbstractProducedQuery.list(AbstractProducedQuery.java:1604)
9/15/2023, 12:28:42.870 PM ... 82 common frames omitted
9/15/2023, 12:28:42.870 PM Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: The incoming request has too many parameters. The server supports a maximum of 2100 parameters. Reduce the number of parameters and resend the request.
9/15/2023, 12:28:42.870 PM at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:262)
9/15/2023, 12:28:42.870 PM at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1624)
9/15/2023, 12:28:42.870 PM at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:594)
9/15/2023, 12:28:42.870 PM at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:524)
9/15/2023, 12:28:42.870 PM at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7194)
9/15/2023, 12:28:42.870 PM at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:2979)
9/15/2023, 12:28:42.870 PM at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:248)
9/15/2023, 12:28:42.870 PM at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:223)
9/15/2023, 12:28:42.870 PM at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeQuery(SQLServerPreparedStatement.java:446)
9/15/2023, 12:28:42.870 PM at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeQuery(NewProxyPreparedStatement.java:431)
9/15/2023, 12:28:42.870 PM at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:57)
9/15/2023, 12:28:42.870 PM ... 96 common frames omitted

 

0 Replies