topic Alfresco Community running on JBOSS and Oracle 11g ORA-01795 in Alfresco Archive

Alfresco Community running on JBOSS and Oracle 11g ORA-01795

fracat71 — Mon, 19 Apr 2010 15:14:54 GMT

Hi all,i'm using Alfresco Community running on JBOSS and Oracle 11g.I have a space with 40000 subspces and wiht i try to access to it using Explorer i have this error:17:01:43,628 ERROR [org.alfresco.web.ui.common.Utils] A system error happened during the operation: could not execute queryorg.hibern

Re: Alfresco Community running on JBOSS and Oracle 11g ORA-01795

fracat71 — Thu, 29 Apr 2010 12:17:24 GMT

Hi all,
nobody can help me.

Regards

Re: Alfresco Community running on JBOSS and Oracle 11g ORA-01795

mrogers — Thu, 29 Apr 2010 20:50:41 GMT

Since the community version does not support Oracle I'm surprised you got that far.

Re: Alfresco Community running on JBOSS and Oracle 11g ORA-01795

fracat71 — Fri, 30 Apr 2010 08:58:24 GMT

Hi mrogers,
so the Enterprise doesn't have this problem (hibernate query written with IN clause with cicle without limitation)?

Regards,
Francesco

Re: Alfresco Community running on JBOSS and Oracle 11g ORA-01795

gyro_gearless — Wed, 05 May 2010 12:15:10 GMT

Hmmm, i suspect this is related to the problem i've outlined in http://forums.alfresco.com/en/viewtopic.php?f=4&t=26267
Point is that Alfresco | Hibernate emits some bogus IN(…) clauses which either are very slow or (in this case) even cause an error.
Hope i'll have some time this week to investigate this a little further, still suspect this is a major bug….

Cheers
Gyro

Re: Alfresco Community running on JBOSS and Oracle 11g ORA-01795

fracat71 — Fri, 07 May 2010 16:53:08 GMT

Hi Gyro.Gearless,
i see this jira http://issues.alfresco.com/jira/browse/ALF-2722?page=com.atlassian.jira.plugin.system.issuetabpanels%3Aall-tabpanel.
So, Enterprise and Community have the same problem. Also previous version?
How can i fix it?

Thanks

Re: Alfresco Community running on JBOSS and Oracle 11g ORA-01795

fracat71 — Fri, 28 May 2010 18:01:18 GMT

Hi all,
i have found this hibernate class:


ppackage org.hibernate.criterion;

import java.util.ArrayList;
import org.hibernate.Criteria;
import org.hibernate.EntityMode;
import org.hibernate.HibernateException;
import org.hibernate.engine.TypedValue;
import org.hibernate.type.AbstractComponentType;
import org.hibernate.type.Type;
import org.hibernate.util.StringHelper;

public class InExpression
  implements Criterion
{
  private final String propertyName;
  private final Object[] values;
   private static final int MAX_PARAMS_PER_INEXPR = 500;

  protected InExpression(String propertyName, Object[] values)
  {
    this.propertyName = propertyName;
    this.values = values;
  }

  public String toSqlString(Criteria criteria, CriteriaQuery criteriaQuery) throws HibernateException
  {
    String[] columns = criteriaQuery.getColumnsUsingProjection(criteria, this.propertyName);
    String singleValueParam = StringHelper.repeat("?, ", columns.length - 1) + "?";
    if (columns.length > 1) singleValueParam = '(' + singleValueParam + ')';
   // String params = (this.values.length > 0) ? 
    //      StringHelper.repeat(new StringBuffer().append(singleValueParam).append(", ").toString(), this.values.length - 1) + singleValueParam : "";

    String cols = StringHelper.join(", ", columns);
    //if (columns.length > 1) cols = '(' + cols + ')';
    //return cols + " in (" + params + ')';
   boolean firstExpr = true;
   String res = "";
   int exprCount = ((values.length - 1) / MAX_PARAMS_PER_INEXPR) + 1;
   for (int i = 0; i < exprCount; i++) {
      if ( firstExpr == false )
         res += " or ";

      int paramsCount;
      if ( i == exprCount - 1 )
         paramsCount = values.length % MAX_PARAMS_PER_INEXPR;
      else
         paramsCount = MAX_PARAMS_PER_INEXPR;

      String params = paramsCount>0 ?
         StringHelper.repeat( singleValueParam + ", ", paramsCount-1 ) + singleValueParam :
         "";
      if ( columns.length>1 ) cols = '(' + cols + ')';
      res += cols + " in (" + params + ')';

      firstExpr = false;
   }

   if ( values.length > MAX_PARAMS_PER_INEXPR ) res = "(" + res + ")";
   System.out.println(res);
   return res; 
  }

  public TypedValue[] getTypedValues(Criteria criteria, CriteriaQuery criteriaQuery) throws HibernateException
  {
    ArrayList list = new ArrayList();
    Type type = criteriaQuery.getTypeUsingProjection(criteria, this.propertyName);
    if (type.isComponentType()) {
      AbstractComponentType actype = (AbstractComponentType)type;
      Type[] types = actype.getSubtypes();
      for (int i = 0; i < types.length; ++i)
        for (int j = 0; j < this.values.length; ++j) {
          Object subval = (this.values[j] == null) ? null : actype.getPropertyValues(this.values[j], EntityMode.POJO)[i];

          list.add(new TypedValue(types[i], subval, EntityMode.POJO));
        }
    }
    else
    {
      for (int j = 0; j < this.values.length; ++j) {
        list.add(new TypedValue(type, this.values[j], EntityMode.POJO));
      }
    }
    return ((TypedValue[])(TypedValue[])list.toArray(new TypedValue[list.size()]));
  }

  public String toString() {
    return this.propertyName + " in (" + StringHelper.toString(this.values) + ')';
  }
}
‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

Can be usefull to solve the problem?. My first tests give positive results.

Rgargds