Liferay 6.2+jboss-eap-6.2.3+db2 as400 ERROR!!

[Mario Aballe Rodriguez, modificado 8 Anos atrás.]

I made a portlet-ext for the VerifyDB2 class mdificar the syntax SQL error, that is the solution by the https://www.liferay.com/es/community/forums/-/message_boards/message/34984878, But show new error SQLSTATE Value = 07003 (in DB2), for some querys in some, Why ??? in MySql it works well, because DB2 does not ???

this is ERROR:

verService Thread Pool -- 63) Hibernate:
verService Thread Pool -- 63)     select
verService Thread Pool -- 63)         this_.groupId as groupId322_0_,
verService Thread Pool -- 63)         this_.uuid_ as uuid2_322_0_,
verService Thread Pool -- 63)         this_.companyId as companyId322_0_,
verService Thread Pool -- 63)         this_.creatorUserId as creatorU4_322_0_,
verService Thread Pool -- 63)         this_.classNameId as classNam5_322_0_,
verService Thread Pool -- 63)         this_.classPK as classPK322_0_,
verService Thread Pool -- 63)         this_.parentGroupId as parentGr7_322_0_,
verService Thread Pool -- 63)         this_.liveGroupId as liveGrou8_322_0_,
verService Thread Pool -- 63)         this_.treePath as treePath322_0_,
verService Thread Pool -- 63)         this_.name as name322_0_,
verService Thread Pool -- 63)         this_.description as descrip11_322_0_,
verService Thread Pool -- 63)         this_.type_ as type12_322_0_,
verService Thread Pool -- 63)         this_.typeSettings as typeSet13_322_0_,
verService Thread Pool -- 63)         this_.manualMembership as manualM14_322_0_,
verService Thread Pool -- 63)         this_.membershipRestriction as members15_322_0_,
verService Thread Pool -- 63)         this_.friendlyURL as friendl16_322_0_,
verService Thread Pool -- 63)         this_.site as site322_0_,
verService Thread Pool -- 63)         this_.remoteStagingGroupCount as remoteS18_322_0_,
verService Thread Pool -- 63)         this_.active_ as active19_322_0_
verService Thread Pool -- 63)     from
verService Thread Pool -- 63)         Group_ this_
verService Thread Pool -- 63)     where
verService Thread Pool -- 63)         this_.classNameId=?
verService Thread Pool -- 63)         and this_.site=?
e.util.JDBCExceptionReporter] (ServerService Thread Pool -- 63) SQL Error: -518, SQLState: 07003
e.util.JDBCExceptionReporter] (ServerService Thread Pool -- 63) [SQL0518] Sentencia preparada QZ9BF05BB0104D9001 no encontrada.
verService Thread Pool -- 63) 14:59:32,923 ERROR [ServerService Thread Pool -- 63][BasePersistenceImpl:244] Caught unexpected exception org.hibernate.exception.SQLGrammarException
verService Thread Pool -- 63) 14:59:32,938 ERROR [ServerService Thread Pool -- 63][MainServlet:212] com.liferay.portal.kernel.events.ActionException: com.liferay.portal.verify.VerifyException: com.liferay.portal.verify.VerifyException: com.liferay.portal.kernel.exception.SystemException: org.hibernate.exception.SQLGrammarException: could not execute query
verService Thread Pool -- 63) com.liferay.portal.kernel.events.ActionException: com.liferay.portal.verify.VerifyException: com.liferay.portal.verify.VerifyException: com.liferay.portal.kernel.exception.SystemException: org.hibernate.exception.SQLGrammarException: could not execute query
verService Thread Pool -- 63)   at com.liferay.portal.events.StartupAction.run(StartupAction.java:79)
verService Thread Pool -- 63)   at com.liferay.portal.servlet.MainServlet.processStartupEvents(MainServlet.java:1245)
verService Thread Pool -- 63)   at com.liferay.portal.servlet.MainServlet.init(MainServlet.java:209)
verService Thread Pool -- 63)   at javax.servlet.GenericServlet.init(GenericServlet.java:242)
verService Thread Pool -- 63)   at org.apache.catalina.core.StandardWrapper.loadServlet(StandardWrapper.java:1194)
verService Thread Pool -- 63)   at org.apache.catalina.core.StandardWrapper.load(StandardWrapper.java:1100)
verService Thread Pool -- 63)   at org.apache.catalina.core.StandardContext.loadOnStartup(StandardContext.java:3591)
verService Thread Pool -- 63)   at org.apache.catalina.core.StandardContext.start(StandardContext.java:3798)
verService Thread Pool -- 63)   at org.jboss.as.web.deployment.WebDeploymentService.doStart(WebDeploymentService.java:156)
verService Thread Pool -- 63)   at org.jboss.as.web.deployment.WebDeploymentService.access$000(WebDeploymentService.java:60)
verService Thread Pool -- 63)   at org.jboss.as.web.deployment.WebDeploymentService$1.run(WebDeploymentService.java:93)
verService Thread Pool -- 63)   at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:471)
verService Thread Pool -- 63)   at java.util.concurrent.FutureTask.run(FutureTask.java:262)
verService Thread Pool -- 63)   at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)
verService Thread Pool -- 63)   at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)
verService Thread Pool -- 63)   at java.lang.Thread.run(Thread.java:745)
verService Thread Pool -- 63)   at org.jboss.threads.JBossThread.run(JBossThread.java:122)
verService Thread Pool -- 63) Caused by: com.liferay.portal.verify.VerifyException: com.liferay.portal.verify.VerifyException: com.liferay.portal.kernel.exception.SystemException: org.hibernate.exception.SQLGrammarException: could not execute query
verService Thread Pool -- 63)   at com.liferay.portal.verify.VerifyProcess.verify(VerifyProcess.java:72)
verService Thread Pool -- 63)   at com.liferay.portal.verify.VerifyProcessUtil._verifyProcess(VerifyProcessUtil.java:111)
verService Thread Pool -- 63)   at com.liferay.portal.verify.VerifyProcessUtil._verifyProcess(VerifyProcessUtil.java:76)
verService Thread Pool -- 63)   at com.liferay.portal.verify.VerifyProcessUtil.verifyProcess(VerifyProcessUtil.java:46)
verService Thread Pool -- 63)   at com.liferay.portal.events.StartupHelper.verifyProcess(StartupHelper.java:146)
verService Thread Pool -- 63)   at com.liferay.portal.events.StartupHelperUtil.verifyProcess(StartupHelperUtil.java:67)
verService Thread Pool -- 63)   at com.liferay.portal.tools.DBUpgrader.verify(DBUpgrader.java:240)
verService Thread Pool -- 63)   at com.liferay.portal.events.StartupAction.doRun(StartupAction.java:226)
verService Thread Pool -- 63)   at com.liferay.portal.ee.license.StartupAction.doRun(Unknown Source)
verService Thread Pool -- 63)   at com.liferay.portal.events.StartupAction.run(StartupAction.java:73)
verService Thread Pool -- 63)   ... 16 more

[Neil Griffin, modificado 8 Anos atrás.]

Moved to Liferay Portal - Development category

[Mario Aballe Rodriguez, modificado 8 Anos atrás.]

I can not see the comment By: Neil Griffin, why ??

[Mario Aballe Rodriguez, modificado 8 Anos atrás.]

Hi!!
I think the problem is in jt400.jar + hibernate when querys with HQL or DynamicQuery (Service Builder) run, because change the HQL query or DynamicQuery by the classic procedure in JDBC and it worked!

public class VerifyGroupExt extends VerifyGroup{
	
	@Override
	protected void doVerify()
		    throws Exception
		  {
		    verifyCompanyGroups();
		    verifyNullFriendlyURLGroups();
		    verifyOrganizationNames();
		    verifyRobots();
		    verifySites();
		    verifyStagedGroups();
		    verifyTree();
		  }
	
	@Override
	protected void verifySites() throws Exception {
	       
	    Connection con = null;
	    PreparedStatement ps = null;
	    ResultSet rs = null;
	    try
	    {
	    	long classNameId =PortalUtil.getClassNameId(Organization.class);
	    	
	    	con = DataAccess.getUpgradeOptimizedConnection();
	    	StringBundler sb = new StringBundler();
	    	 sb.append("select groupId from Group_ where classNameId=? and site=?");
	    	 ps = con.prepareStatement(sb.toString());
	    	 ps.setLong(1, classNameId);
	    	 ps.setInt(2, Util.valueBoolean(false));
	    	 
	    	 rs=ps.executeQuery();
	    	while(rs.next()){
	    		long groupId=rs.getLong("groupId");
	    		Group group=GroupLocalServiceUtil.getGroup(groupId);
	    		
	    		if ((group.getPrivateLayoutsPageCount() <= 0) && 
	    		        (group.getPublicLayoutsPageCount() <= 0))
	    		        continue;
	    		      group.setSite(true);
	    	
	    		      GroupLocalServiceUtil.updateGroup(group);
	    		    }
	
	    } finally {
	        DataAccess.cleanUp(con, ps, rs);
	    }
  }

But still the same error in other querys.

e.util.JDBCExceptionReporter] (ServerService Thread Pool -- 63) SQL Error: -518, SQLState: 07003
29e.util.JDBCExceptionReporter] (ServerService Thread Pool -- 63) [SQL0518] Sentencia preparada QZ9BF05BB0104D9001 no encontrada.
30verService Thread Pool -- 63) 14:59:32,923 ERROR [ServerService Thread Pool -- 63][BasePersistenceImpl:244] Caught unexpected exception org.hibernate.exception.SQLGrammarException

So what happens with jt400.jar + hibernate together ???

[Mario Aballe Rodriguez, modificado 8 Anos atrás.]

That is the ERROR:

Caused by: java.sql.SQLException: [SQL0518] Sentencia preparada QZ9BF05BB0104D9001 no encontrada.
at com.ibm.as400.access.JDError.createSQLExceptionSubClass(JDError.java:829)
at com.ibm.as400.access.JDError.throwSQLException(JDError.java:692)
at com.ibm.as400.access.JDError.throwSQLException(JDError.java:662)
at com.ibm.as400.access.AS400JDBCStatement.commonExecute(AS400JDBCStatement.java:1008)
at com.ibm.as400.access.AS400JDBCPreparedStatement.executeQuery(AS400JDBCPreparedStatement.java:1586)
at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeQuery(NewProxyPreparedStatement.java:116)
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:2542)
... 46 more

Why????? thanks!!!

[Jorge Díaz, modificado 8 Anos atrás.]

Hi Mario,

Liferay 6.2 only supports DB2 versions 9.7 and 10.1.

About your DB2 AS400 check the database version fits with 9.7 or 10.1. Also check you are using the correct JDBC driver.

You have more information at following links:
- https://www.liferay.com/es/community/wiki/-/wiki/Main/DB2+Express-C+9.5+on+CentOS+5.2/maximized
- https://www.liferay.com/es/community/wiki/-/wiki/Main/Database+Portal+Properties#section-Database+Portal+Properties-DB2

[Mario Aballe Rodriguez, modificado 8 Anos atrás.]

Jorge Díaz:

Hi Mario,

Liferay 6.2 only supports DB2 versions 9.7 and 10.1.

About your DB2 AS400 check the database version fits with 9.7 or 10.1. Also check you are using the correct JDBC driver.

You have more information at following links:
- https://www.liferay.com/es/community/wiki/-/wiki/Main/DB2+Express-C+9.5+on+CentOS+5.2/maximized
- https://www.liferay.com/es/community/wiki/-/wiki/Main/Database+Portal+Properties#section-Database+Portal+Properties-DB2

thank yuo Jorge. !!!