Hello,

I am having trouble getting SQLAnywhere v9 using jConnect 7 (jconn4.jar) to support the JPA used by the JBoss Drools package under the JBoss AS 7 server. I have a simple test app (generated from the JBoss Tools "Java EE Web Project") that executes some very simple JPA (Hibernate) followed by the JPA operations that are produced by the Drools API (as it persists a business process state).

I believe that the data source definition and simple JPA operations are being executed properly. The JPA operations generated as a part of the template project code simply create a "Member" table in the database and then add rows of "member data" to this table. I do observe that a "member" table is being created in the database and that rows of member data are being added to it. However, when the app then moves forward and executes some code that calls Drools API that "executes a process" and attempts to persist the state of the process I get the following error:

16:50:04,686 ERROR [org.hibernate.engine.jdbc.spi.SqlExceptionHelper] (http-localhost-127.0.0.1-8080-2) ASA Error -807: Host variables may not be used within a batch

I have not attempted to trace into the API called by the Drools test code because that looks to be a huge and difficult undertaking and I am hoping that the error message will be sufficient to see what's wrong here. However, I believe that the Drools test code is operating correctly because if I swap out SQLAnywhere for the H2 in-memory database that JBoss ships with, we have no errors at all. But as soon as I swap SQLAnywhere back in as the pesistence-unit we fail with the above message.

I am under the impression that SQLAnywhere v9 does not support (actually NO VERSION of SQLAnywhere) "XA-style" transactions (see http://sqlanywhere-forum.sap.com/questions/15693/does-sa12-supports-xa-transactions and @sybase.com%3E">http://nntp-archive.sybase.com/nntp-archive/action/article/%3C4a2fad20.3dca.1681692777@sybase.com%3E) but I have no idea if the error message is related to this lack of capability or if there is another problem altogether.

Here is the persistence.xml file:

<?xml version="1.0" encoding="UTF-8"?>
<persistence version="2.0"
   xmlns="http://java.sun.com/xml/ns/persistence" xmlns:xsi="http://www.w3.org/2001/XMLSchema- instance"
   xsi:schemaLocation="
        http://java.sun.com/xml/ns/persistence
        http://java.sun.com/xml/ns/persistence/persistence_2_0.xsd">
   <persistence-unit name="primary" transaction-type="JTA">
      <provider>org.hibernate.ejb.HibernatePersistence</provider>
      <jta-data-source>java:jboss/datasources/SybaseDS</jta-data-source>
      <class>org.drools.persistence.info.SessionInfo</class>
      <class>org.drools.persistence.info.WorkItemInfo</class>
      <class>org.jbpm.persistence.processinstance.ProcessInstanceInfo</class>
      <class>org.jbpm.persistence.correlation.CorrelationKeyInfo</class>
      <class>org.jbpm.persistence.correlation.CorrelationPropertyInfo</class>
      <class>org.jbpm.process.audit.ProcessInstanceLog</class>
      <class>org.jbpm.process.audit.NodeInstanceLog</class>
      <class>org.jbpm.process.audit.VariableInstanceLog</class>
      <properties>
         <property name="hibernate.dialect.Sybase" value="SybaseAnywhereDialect"/>
         <property name="hibernate.hbm2ddl.auto" value="create-drop" />
         <property name="hibernate.show_sql" value="false" />
         <property name="hibernate.jdbc.batch_size" value="0" />
         <property name="hibernate.transaction.jta.platform" value="org.hibernate.service.jta.platform.internal.JBossAppServerJtaPlatform" />
      </properties>
   </persistence-unit>
</persistence>

Here is how the JBoss AS 7 managed data sources for both SQLAnywhere and H2 are defined in the JBoss standalone.xml:

<drivers>
    <driver name="h2" module="com.h2database.h2">
        <xa-datasource-class>org.h2.jdbcx.JdbcDataSource</xa-datasource-class>
    </driver>
    <driver name="Sybase" module="com.mycompany.configuration.Sybase">
        <xa-datasource-class>com.sybase.jdbc4.jdbc.SybXADataSource</xa-datasource-class>
    </driver>
</drivers>

Note that I am guessing about the name of the class (SybXADataSource) to be used here. By looking at the contents of jconn4 I discovered these four driver classes SybXAConnection, SybXADataSource, SybXAResource, SybXAResource11 but I cannot find ANY information about them anywhere. It may turn out that "XA" support is a red-herring here because when I try using the standard (SybDriver) driver in standalone.xml as:

<drivers>
...
    <driver name="Sybase" module="com.mycompany.configuration.Sybase"/>
</drivers>

I still get the same error.

Can someone: 1) Explain how to eliminate the "ASA Error -807: Host variables may not be used within a batch" error? 2) Explain what SybXAConnection, SybXADataSource, SybXAResource, SybXAResource11 are used for?

Thank you.

Al