sql server and oracle problems with paging page number when not initted

Finally, I think I have figured out the issue. It is only happening in sql server. With MySql, it works fine. I modified some code in the ByHql.java class file in the attachQueryInfo(Session session) method to make it work.

I have added if else condition like below:

if(queryPaging.getFirstIndexOnPage() < 0)

{ query.setFirstResult(0); }

else

{ query.setFirstResult(queryPaging.getFirstIndexOnPage()); }

Thanks,
Vivek

From: vivek sachdeva
Date: Thu, 14 Aug 2014 08:39:28 -0700
To: Chris Hyzer
Cc: "grouper-users"
Subject: Re: [grouper-users] Re: Exception

Attached is the screenshot of the variables I replaced with non –1 . They are actually under QueryPaging.

Thanks,
Vivek

From: vivek sachdeva
Date: Thu, 14 Aug 2014 15:17:33 +0000
To: Chris Hyzer
Cc: "grouper-users
Subject: Re: [grouper-users] Re: Exception

The problem might be with the query options. While debugging, I noticed that some variables in the queryoptions were set to –1. I changed the values at runtime and it worked fine. Now I am trying to see why query options variables are set to –1.

Thanks,
Vivek

From: vivek sachdeva
Date: Thu, 14 Aug 2014 15:03:25 +0000
To: Chris Hyzer
Cc: "grouper-users"
Subject: [grouper-users] Re: Exception

Hi,

When I debug I see the query which is :
select distinct group0_.id as id20_, group0_.hibernate_version_number as hibernate2_20_, group0_.last_membership_change as last3_20_, group0_.last_imm_membership_change as last4_20_, group0_.parent_stem as parent5_20_, group0_.creator_id as creator6_20_, group0_.create_time as create7_20_, group0_.modifier_id as modifier8_20_, group0_.modify_time as modify9_20_, group0_.name as name20_, group0_.display_name as display11_20_, group0_.extension as extension20_, group0_.display_extension as display13_20_, group0_.description as descrip14_20_, group0_.context_id as context15_20_, group0_.alternate_name as alternate16_20_, group0_.type_of_group as type17_20_, group0_.id_index as id18_20_ from grouper_groups group0_ where group0_.parent_stem=? and (group0_.type_of_group in (? , ?)) order by group0_.display_extension asc

When I run the same query in the db visualizer tool directly, it runs fine and returns 530 rows.

Can it be due to the fact that I had replaced the sqljdbc4.jar ?

Below is the full stack trace.

edu.internet2.middleware.grouper.internal.dao.GrouperDAOException: Problem in HibernateSession: HibernateSession (58f3dd70): new, readonly, READONLY_NEW, notActiveTransaction, session (1b375ce6),
Exception in list: (class edu.internet2.middleware.grouper.Group), ByHqlStatic, query: 'select distinct theGroup from Group theGroup where theGroup.parentUuid = :theStemId and theGroup.typeOfGroupDb in ( :R84GNLMU0, :R84GNLMU1 ) ', cacheable: false, cacheRegion: edu.internet2.middleware.grouper.internal.dao.hib3.Hib3GroupDAO.GetAllGroupsSecure, tx type: null, options: QueryOptions: queryPaging: pageSize: 50, pageNumberOnIndexed: 1, querySort: theGroup.displayExtensionDb asc, , tx type: nullBind var[0]: 'Param (class java.lang.String): 'theStemId'>'a6f9b4bb14a64be1b98739d2e3b54362', Bind var[1]: 'Param (class java.lang.String): 'R84GNLMU0'>'role'Bind var[2]: 'Param (class java.lang.String): 'R84GNLMU1'>'group',
at edu.internet2.middleware.grouper.hibernate.HibernateSession._internal_hibernateSessionCatch(HibernateSession.java:542)
at edu.internet2.middleware.grouper.hibernate.HibernateSession.callbackHibernateSession(HibernateSession.java:664)
at edu.internet2.middleware.grouper.hibernate.ByHqlStatic.list(ByHqlStatic.java:372)
at edu.internet2.middleware.grouper.hibernate.ByHqlStatic.listSet(ByHqlStatic.java:421)
at edu.internet2.middleware.grouper.internal.dao.hib3.Hib3GroupDAO.findAllGroupsSecureHelper(Hib3GroupDAO.java:2701)
at edu.internet2.middleware.grouper.internal.dao.hib3.Hib3GroupDAO.getAllGroupsSecure(Hib3GroupDAO.java:3101)
at edu.internet2.middleware.grouper.GroupFinder.findGroups(GroupFinder.java:683)
at edu.internet2.middleware.grouper.misc.GrouperObjectFinder.findGrouperObjects(GrouperObjectFinder.java:676)
at edu.internet2.middleware.grouper.grouperUi.serviceLogic.UiV2Stem.filterHelper(UiV2Stem.java:873)
at edu.internet2.middleware.grouper.grouperUi.serviceLogic.UiV2Stem.viewStem(UiV2Stem.java:913)
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:606)
at edu.internet2.middleware.grouper.util.GrouperUtil.invokeMethod(GrouperUtil.java:3951)
at edu.internet2.middleware.grouper.util.GrouperUtil.callMethod(GrouperUtil.java:3902)
at edu.internet2.middleware.grouper.j2ee.GrouperUiRestServlet.doGet(GrouperUiRestServlet.java:277)
at edu.internet2.middleware.grouper.j2ee.GrouperUiRestServlet.doPost(GrouperUiRestServlet.java:160)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:647)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:728)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:305)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)
at org.owasp.csrfguard.CsrfGuardFilter.doFilter(CsrfGuardFilter.java:110)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:243)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)
at edu.internet2.middleware.grouper.ui.GrouperUiFilter.doFilter(GrouperUiFilter.java:1076)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:243)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:222)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:123)
at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:611)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:171)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:99)
at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:953)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:118)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:408)
at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1023)
at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:589)
at org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:310)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)
at java.lang.Thread.run(Thread.java:724)
Caused by: org.hibernate.exception.SQLGrammarException: could not execute query
at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:92)
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:66)
at org.hibernate.loader.Loader.doList(Loader.java:2536)
at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2276)
at org.hibernate.loader.Loader.list(Loader.java:2271)
at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:452)
at org.hibernate.hql.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:363)
at org.hibernate.engine.query.HQLQueryPlan.performList(HQLQueryPlan.java:196)
at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1268)
at org.hibernate.impl.QueryImpl.list(QueryImpl.java:102)
at edu.internet2.middleware.grouper.hibernate.ByHql.list(ByHql.java:349)
at edu.internet2.middleware.grouper.hibernate.ByHqlStatic$2.callback(ByHqlStatic.java:382)
at edu.internet2.middleware.grouper.hibernate.HibernateSession.callbackHibernateSession(HibernateSession.java:654)
... 40 more
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near '-'.
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.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeQuery(NewProxyPreparedStatement.java:76)
at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:208)
at org.hibernate.loader.Loader.getResultSet(Loader.java:1953)
at org.hibernate.loader.Loader.doQuery(Loader.java:802)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:274)
at org.hibernate.loader.Loader.doList(Loader.java:2533)
... 50 more

From: Chris Hyzer
Date: Thu, 14 Aug 2014 08:41:10 +0000
To: vivek sachdeva, "grouper-users"
Subject: RE: Exception

Im having trouble reproducing this one…

It is supposed to show max 30 roles (and max 30 folders, attribute defs and names for a total of one hundred and some objects max). The browser scrollbar will get you to the bottom of the list. Is there more to your stacktrace? Can you send me the whole thing (and any logs above and below it) so I can see what the problem is?

I added 530 roles to a folder and displayed it… here is the menu on the left and the parent folder screen which both show the roles.

/**

• @author mchyzer
• $Id$
  */
  package edu.internet2.middleware.grouper.ui.util;

import edu.internet2.middleware.grouper.GroupSave;
import edu.internet2.middleware.grouper.GrouperSession;
import edu.internet2.middleware.grouper.group.TypeOfGroup;

/**
*
*/
publicclass LoadSomeGroups {

/**

• */
  public LoadSomeGroups() {
  }

/**

• @param args
  */
  publicstaticvoid main(String[] args) {

GrouperSession grouperSession = GrouperSession.startRootSession();

for (int i=0;i<540;i++)

{ new GroupSave(grouperSession) .assignTypeOfGroup(TypeOfGroup.role) .assignCreateParentStemsIfNotExist(true) .assignName("someStem3:role_" + i).save(); }

}

}

From:grouper-users-request] On Behalf Of Sachdeva, Vivek
Sent: Thursday, August 14, 2014 1:38 AM
To: grouper-users
Subject: [grouper-users] Exception

Hi,

I am seeing the following exception when I click on one of the folders in the Browse Folder section on the left. Folder has about 530 roles. Also when I click on the + icon to see the roles in the tree structure, it shows only up to 30 roles and there is no scrollbar. Can someone help?

Thanks,
Vivek

Problem in HibernateSession: HibernateSession (2573b49): new, readonly, READONLY_NEW, notActiveTransaction, session (6e98f967),
Exception in list: (class edu.internet2.middleware.grouper.Group), ByHqlStatic, query: 'select distinct theGroup from Group theGroup where theGroup.parentUuid = :theStemId and theGroup.typeOfGroupDb in ( :R83WNZ490, :R83WNZ491 ) ', cacheable: false, cacheRegion: edu.internet2.middleware.grouper.internal.dao.hib3.Hib3GroupDAO.GetAllGroupsSecure, tx type: null, options: QueryOptions: queryPaging: pageSize: 50, pageNumberOnIndexed: 1, querySort: theGroup.displayExtensionDb asc, , tx type: nullBind var[0]: 'Param (class java.lang.String): 'theStemId'>'a6f9b4bb14a64be1b98739d2e3b54362', Bind var[1]: 'Param (class java.lang.String): 'R83WNZ490'>'role'Bind var[2]: 'Param (class java.lang.String): 'R83WNZ491'>'group',
at edu.internet2.middleware.grouper.hibernate.HibernateSession._internal_hibernateSessionCatch(HibernateSession.java:542)
at edu.internet2.middleware.grouper.hibernate.HibernateSession.callbackHibernateSession(HibernateSession.java:664)
at edu.internet2.middleware.grouper.hibernate.ByHqlStatic.list(ByHqlStatic.java:372)
at edu.internet2.middleware.grouper.hibernate.ByHqlStatic.listSet(ByHqlStatic.java:421)
at edu.internet2.middleware.grouper.internal.dao.hib3.Hib3GroupDAO.findAllGroupsSecureHelper(Hib3GroupDAO.java:2701)
at edu.internet2.middleware.grouper.internal.dao.hib3.Hib3GroupDAO.getAllGroupsSecure(Hib3GroupDAO.java:3101)
at edu.internet2.middleware.grouper.GroupFinder.findGroups(GroupFinder.java:683)
at edu.internet2.middleware.grouper.misc.GrouperObjectFinder.findGrouperObjects(GrouperObjectFinder.java:676)
at edu.internet2.middleware.grouper.grouperUi.serviceLogic.UiV2Stem.filterHelper(UiV2Stem.java:873)
at edu.internet2.middleware.grouper.grouperUi.serviceLogic.UiV2Stem.viewStem(UiV2Stem.java:913)