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

asked 28 May '14, 23:34

Jim1's gravatar image

Jim1
21114
accept rate: 0%

edited 02 Jun '14, 14:54

Jeff%20Albion's gravatar image

Jeff Albion
10.8k171175


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).

Which version of Hibernate-core is embedded - 3.x or 4.x?

In regards to the Hibernate support for SQL Anywhere, Hibernate is compatible with SQL Anywhere 10 and up, but it does require configuration. There is an updated version of the Hibernate dialect for SQL Anywhere 10 and newer. You can find the information at: https://github.com/sqlanywhere/hibernate. Please see the README.

  1. 'SybaseAnywhereDialect' was not released by Sybase/SAP, thus there are no compatibility guaranteees that this dialect is executing the correct sequence of commands against jConnect for all features.

  2. There was a fix in SQL Anywhere for jConnect 7.x metadata support that I found was required in my Hibernate testing with jConnect - see: CR #680196 (with the latest jConnect 7.0 driver).

  3. Using <property name="hibernate.hbm2ddl.auto" value="create-drop" /> is not recommended in production scenarios for performance reasons.

  4. You're using the following code: <property name="hibernate.jdbc.batch_size" value="0" />. Batch statement support is only available in SQL Anywhere 11 and up, and with the jConnect driver in Hibernate 4.x, only after using the following settings:

    <property name="hibernate.connection.JCONNECT_VERSION">7</property>
    <property name="hibernate.connection.DYNAMIC_PREPARE">true</property>
    <property name="hibernate.jdbc.batch.builder">org.hibernate.dialect.sqlanywhere.JConnBatchBuilderImpl</property>

There is a sample 'JConnBatchBuilderImpl' class with the SQL Anywhere Dialect download for jConnect.

You must use a batch size of "1" if you're going to use SQL Anywhere 9/10 with Hibernate:

<property name="hibernate.jdbc.batch_size" value="1" />

Finally, if you're doing new development against SQL Anywhere with Hibernate, using SQL Anywhere 9.0 as your target version is probably not a great idea for a number of reasons:

  • It is out of engineering support so if you find a bug during development, it won't be fixed.
  • It was never tested with jConnect 7.0, so compatibility may not be ideal.
  • There is no tested/supported Hibernate dialect for it.
permanent link

answered 29 May '14, 17:33

Jeff%20Albion's gravatar image

Jeff Albion
10.8k171175
accept rate: 25%

edited 29 May '14, 17:43

Hi Jeff,

I really appreciate your detailed response! I've tried using the information in the links you provided but nothing has worked (not surprising since you noted that they are for v10 and up). As I told Nick in his post above, we know that version 9 is "way old" but the problem with upgrading to the latest version is that we ship the v9 single user engine embedded/bundled with our application. We purchased a royalty free license for v9 and we understand that Sybase (SAP) no longer offers this. We would now have to purchase a license for each unit of our product that we ship and that would require a significant price increase. (If we can still purchase a royalty free license please correct me on this!). That's why we are doing everything we can to see if there is a fix for this error. Any other ideas?

Thank you, Al

(30 May '14, 00:43) Jim1
Replies hidden

Any other ideas?

Yes - listen to the advice listed at the link listed above that Nick mentioned.

I wasn't aware of what the dialect was doing here until reading that link, but if you're following this code path, it appears that you're saving an entity that is using an operation that's also trying to retreive the primary key in the same operation - that's not going to work and is a bug in the SybaseAnywhereDialect (that's fixed in later versions (?) - which version of hibernate-core are you using with JBoss?).

However, this is also a problem in the current versions posted on Github I pointed you to - I will file a bug report to have it resolved. Unless someone can think of an alternative using SELECT over DML to return generic primary keys, this should be:

SQLAnywhere10Dialect.java:704

    public boolean supportsInsertSelectIdentity() {
        return false;
    }


Which generator strategy are you currently using for your JPA? If you switch to an 'increment' strategy, does this immediately solve the problem? e.g.

@Id
@GenericGenerator(name = "generator1", strategy = "increment")
@GeneratedValue(generator = "generator1")

(02 Jun '14, 10:27) Jeff Albion

As to the "SELECT over DML" - here's at least a link to a similar approach (not Hibernate-bound, apparently):

How do I get SELECT @@IDENTITY FROM ( INSERT ... ) to work properly?

(02 Jun '14, 12:07) Volker Barth
1

Thanks Volker for the reminder of the @@identity behaviour - yes, I agree. :) I did a quick, simple test and couldn't immediately figure out how to make a plain generic insert statement and have it always select 'the primary key' back. @@identity doesn't work here as we can show, and the Hibernate Dialect doesn't know the primary key column of the SELECT statement when it's parsing the statement (it's just the text of the whole INSERT statement) so we also can't just use a column reference like we'd want to here.


I also believe that Hibernate switches its strategy based on the supportsInsertSelectIdentity() check - it can use getIdentitySelectString() and execute it afterwards instead - so disabling the support should be all that's required.

(02 Jun '14, 12:16) Jeff Albion

First off, I must state many many caveats up front:

  1. I do not consider myself to be a Hibernate or JBoss expert. More such beings can be found on other forums.
  2. You are using a version of SQL Anywhere that has be past the end of it's engineering support life for a number of years now; also even the online resources are limited now.
  3. You are using JConnect and JConnect concepts through out, some of the suggestions will be based upon switching to a different JDBC driver that is specific to the SQL Anywhere server.
  4. You question spans multiple technologies and is mixing diverse concepts and will likely need to be broken down into smaller pieces to resolve fully, so take this response as a starting point for other threads to come; quite possibly on other forums.

SybXA* classes are JConnect classes for use with ASE's Distributed Transacton Management and definitely do not work with SQL Anywhwere. So using any of those may indeed be causing many spurious issues with confusing/confounding results.

The SQL Code -807 error is being reported by the server and is likely related to the assumptions Hibernate is using; probably because it was not configured correctly for use with SQL Anywhere. For example see this example "Hibernate and Sybase Anywhere specifications compliance" from the Hibernate community. That example involves the use of some sort of default autoincrement on the table in question, and that is just one way this error could occur; there may be others. If you look into using SQL Anywhere's request logging (just use the Version 9 documents for the specifics for your version) you can see the exact nature of the SQL batch that is causing that error and that may inform what is causeing the error from the SQL Anywhere view of things.

But configuring Hibernate for use with SQL Anywhere is a bit beyound my expertise ... you might want to refere the Hibernate.org and the JBoss forums for ideas from those who use both. You could also try starting with this document "Hibernate Dialect for SQL Anywhere". Caveat #5: even that document starts with version 10 of SQL Anywhere and may/may not work with version 9.

I hope this is of some assistance . . .

permanent link

answered 29 May '14, 10:38

Nick%20Elson%20SAP%20SQL%20Anywhere's gravatar image

Nick Elson S...
7.3k35107
accept rate: 32%

But configuring Hibernate for use with SQL Anywhere is a bit beyound my expertise ... you might want to refere the Hibernate.org and the JBoss forums for ideas from those who use both.

Actually, please don't do this if the issue is only with SQL Anywhere - please continue to post about your Hibernate/hibernate-core issues (used in whatever product) with SQL Anywhere here in this forum.

(29 May '14, 17:35) Jeff Albion

Hi Nick,

Thanks for your detailed response. I've looked at your supplied links and tried some things but I am still getting the error. We know that version 9 is "way old" but the problem with upgrading to the latest version is that we ship the v9 single user engine embedded/bundled with our application. We purchased a royalty free license for v9 and we understand that Sybase (SAP) no longer offers this. We would now have to purchase a license for each unit of our product that we ship and that would require a significant price increase. (If we can still purchase a royalty free license please correct me on this!). That's why we are doing everything we can to see if there is a fix for this error. Any other ideas? Thanks, Al

(30 May '14, 00:39) Jim1

I am afraid to suggest that this may just not be do able. At least not without you doing a lot of customization from the Hibernate side; yourself.

As you already know, your use of the "royalty free" runtime engine comes with severe restrictions in the form of technical feature limitations. Not the least of which is the lack of any intended support for JConnect.

While you may be able get JConnect to connect to the rteng9 process, JConnect itself will know nothing about the capabilities of the product and will not be able to support many of it's own most basic features. Futher there will be no way for you to add that; given other limitations. That alone is very probably the reason you are getting errors concerning parameters.

If others have done this (possibly with either the "iAnywhere JDBC driver" or the JDBC-ODBC bridge), I would defer to their knowledge and experience but I suspect you have a long and uncertain road ahead to even accomplish a partial solution to this.

As such, I suspect it would be better/easier/simpler to forgo the use of Hibernate until you can upgrade to a full and supported product.

(30 May '14, 11:25) Nick Elson S...
Replies hidden

Hi Nick,

In trying to keep things simple I misled you with my comments on the runtime engine. What I said is correct but the problem we are having is when we run the Drools API which uses transactions which apparently trigger the batch operations in Hibernate against the multi-user server. We have a simple/standalone version of our product that uses the single-user runtime engine and a more feature rich version that runs against the multi-user server and that is where we have this problem. We haven't upgraded the multi-user server part of the product line because we discovered several years ago that a v9 database is incompatible with the v10 (and I assume later) server. Up till now it is possible for our customer to start with the standalone app, build a database and then easily upgrade to the multi-user product and continue to use the original database. We could upgrade the multi-user server but then we open up the whole issue of having to port the data out of the v9 database and get it into a format for v10/11/16. But at this point we don't even know that the latest version of the server is compatible with what Hibernate is doing. (I would hope that Sybase has built-in this compatibility but at this time no one knows.) We have found it hard to get info on runtime versions - would you know if we are correct that there is no longer a royalty free single user runtime engine? I hope this clarifies what we are dealing with and any additional information or ideas is greatly appreciated.

Thanks for your interest.

(30 May '14, 12:30) Jim1

We have found it hard to get info on runtime versions - would you know if we are correct that there is no longer a royalty free single user runtime engine?

Yes, there is no currently supported version of the runtime engine available. There was also a misprint in the PowerBuilder 12.5 documentation about the availability of one for SQL Anywhere 12. See: http://search.sybase.com/kbx/changerequests?bug_id=720479

The last version of the runtime engine is 'rteng11.exe' from SQL Anywhere 11 / PowerBuilder 11 - it was removed in SQL Anywhere 12 (and 16).

(30 May '14, 14:17) Jeff Albion
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×108
×39
×7

question asked: 28 May '14, 23:34

question was seen: 13,416 times

last updated: 02 Jun '14, 14:54