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:
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? Thank you. Al |
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:
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
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
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.
(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 I also believe that Hibernate switches its strategy based on the
(02 Jun '14, 12:16)
Jeff Albion
|
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 . . .
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
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
|