[rules-users] Drools Ruleflow persistence on MySQL - invalid SQL query generated

[victorC]

Hi,

After few days of struggling on Drools Ruleflow persistence setup on MySQL I
finally came to the point when I feel it almost works but I am getting an
MySQL error.

I see the following SQL query in the log /it does not look correct/ which 
is followed by the MySQL error message.

select processins0_.InstanceId as col_0_0_ from ProcessInstanceInfo
processins0_, EventTypes eventtypes1_ where
processins0_.InstanceId=eventtypes1_.InstanceId and (? in (.))


WARN  [JDBCExceptionReporter] SQL Error: 1064, SQLState: 42000
ERROR [JDBCExceptionReporter] You have an error in your SQL syntax; check
the manual that corresponds to your MySQL server version for the right
syntax to use near '))' at line 1

I googled for similar symptoms but with no success.

Will be very grateful for any support, hint, etc!

My setup is:
	JBoss 4.2.3.GA
	Hibernate 3.2.4.sp1
	JBoss Seam 2.2.0.GA
	Drools 5.1.1
	MySQL 5.1.41 / mysql-connector 5.0.7

persistence.xml
...
<persistence-unit name="org.drools.persistence.jpa" transaction-type="JTA">
    <provider>org.hibernate.ejb.HibernatePersistence</provider>
    <jta-data-source>java:/edento-MySQL-ds-XA</jta-data-source>
    
    <mapping-file>META-INF/orm.xml</mapping-file>
    
    <class>org.drools.persistence.session.SessionInfo</class>
   
<class>org.drools.persistence.processinstance.ProcessInstanceInfo</class>
    <class>org.drools.persistence.processinstance.WorkItemInfo</class>
   
<class>org.drools.persistence.processinstance.variabletypes.VariableInstanceInfo</class>
   
<class>org.drools.persistence.processinstance.variabletypes.JPAPersistedVariable</class>
   
<class>org.drools.persistence.processinstance.variabletypes.SerializablePersistedVariable</class>

    <properties>
      <property name="hibernate.dialect"
value="org.hibernate.dialect.MySQL5Dialect"/>
      <property name="hibernate.max_fetch_depth" value="3"/>
      <property name="hibernate.hbm2ddl.auto" value="update"/>
      <property name="hibernate.show_sql" value="true"/>
      <property name="hibernate.transaction.manager_lookup_class"
value="org.hibernate.transaction.JBossTransactionManagerLookup"/>
    </properties>
  </persistence-unit>
...

datasources-ds.xml
…
 <xa-datasource>
	<jndi-name>MySQL-ds-XA</jndi-name>
     <xa-datasource-property
name="URL">jdbc:mysql://localhost:3306/db</xa-datasource-property>
    
<xa-datasource-class>com.mysql.jdbc.jdbc2.optional.MysqlXADataSource</xa-datasource-class>

	<user-name>root</user-name>
	<password></password>

     <track-connection-by-tx>true</track-connection-by-tx>
    
<exception-sorter-class-name>org.jboss.resource.adapter.jdbc.vendor.MySQLExceptionSorter</exception-sorter-class-name>
     
     <min-pool-size>1</min-pool-size>
     <max-pool-size>10</max-pool-size>
     <idle-timeout-minutes>10</idle-timeout-minutes>
     <metadata>
         <type-mapping>mySQL</type-mapping>
     </metadata>
 </xa-datasource>
...


orm.xml
<?xml version="1.0" encoding="UTF-8"?>
<entity-mappings xmlns="http://java.sun.com/xml/ns/persistence/orm"
                 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
                
xsi:schemaLocation="http://java.sun.com/xml/ns/persistence/orm orm_1_0.xsd"
                 version="1.0">  
      <named-query name="ProcessInstancesWaitingForEvent">
          <query>
select 
    processInstanceInfo.processInstanceId
from 
    ProcessInstanceInfo processInstanceInfo
where
    :type in (processInstanceInfo.eventTypes)
          </query>
      </named-query>
</entity-mappings>

The bean java code – just starts the process and then immediately attempts
to load it from the database.

		EntityManagerFactory emf =
Persistence.createEntityManagerFactory("org.drools.persistence.jpa");
        Environment env = KnowledgeBaseFactory.newEnvironment();
        env.set(EnvironmentName.ENTITY_MANAGER_FACTORY, emf);
		env.set(EnvironmentName.GLOBALS, new MapGlobalResolver());
        StatefulKnowledgeSession ksession =
JPAKnowledgeService.newStatefulKnowledgeSession(kbase, null, env);

        WorkflowProcessInstance processInstance =
(WorkflowProcessInstance)ksession.startProcess("TestProcess");

        //retrieving...
        ksession =
JPAKnowledgeService.loadStatefulKnowledgeSession(ksession.getId(), kbase,
null, env);
		processInstance =
(WorkflowProcessInstance)ksession.getProcessInstance(processInstance.getId());


--
View this message in context: http://drools.46999.n3.nabble.com/Drools-Ruleflow-persistence-on-MySQL-invalid-SQL-query-generated-tp3548289p3548289.html
Sent from the Drools: User forum mailing list archive at Nabble.com.