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.email@example.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:
Here is how the JBoss AS 7 managed data sources for both SQLAnywhere and H2 are defined in the JBoss standalone.xml:
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:
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?
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.
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:
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:
First off, I must state many many caveats up front:
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 . . .
answered 29 May '14, 10:38
Nick Elson S...