Hi Russell,
Thanks for the tip. I tried all versions of the MySQL driver from
3.0.4 to 5.1.12, but it's a no go, at least for native queries.
At this point, I'm working around this by getting the JDBC connection
from OpenJPA and executing a new statement in it:
em.getTransaction().begin();
OpenJPAEntityManager ojem = (OpenJPAEntityManager) em;
Connection conn = (Connection) ojem.getConnection();
Statement s1 = conn.createStatement();
s1.executeUpdate("create table DB2ADMIN.PARENT (a int)");
s1.close();
em.getTransaction().commit();
Not neat, but...
Thanks,
Dinkar
On Sun, Feb 28, 2010 at 12:42 PM, Russell Collins
<[email protected]> wrote:
> Have you tried using a different MySQL driver? I ran into general database
> issues when trying to use MySQL 4.X with the latest greatest MySQL Driver. I
> now use MySQL connector 3.0.17 and have not had database issues and that
> includes working with OpenJPA 1.2.1
>
> -----Original Message-----
> From: Dinkar Rao [mailto:[email protected]]
> Sent: Saturday, February 27, 2010 9:04 PM
> To: [email protected]
> Subject: MySQL 4.1.22 + Native Query = Callable statements not supported
>
>
> Hi Folks,
>
> I have the following setup:
>
> MySQL 4.1.22 with database/schema DB2ADMIN (don't ask!)
> MySQL JDBC connector 5.1.12
> OpenJPA-1.2.1
>
> I have a simple native query:
>
> EntityManager em = emf.createEntityManager();
> em.getTransaction().begin();
> Query create = em.createNativeQuery("create table DB2ADMIN.PARENT (a
> int)");
> create.executeUpdate();
> em.getTransaction().commit();
>
> The exception (callable statements not supported) is pasted below. This
> happens for any native query, not just one with a delete statement.
>
> The nativequery works fine with OpenJPA-2.0.0-beta with the same MySQL db
> and the same MySQL driver. Additionally, DB2 + OpenJPA-1.2.1 also works
> fine. So it's a combination of MySQL JDBC connector 5.1.12 + OpenJPA 1.2.1
> that is behaving strangely.
>
> If I run a separate Java program that creates its own JDBC connection using
> the same MySQL driver, the CREATE TABLE and subsequent DELETE FROM statement
> work fine. So it's not a driver problem.
>
> Any suggestions on how to get the native query working ?
>
> (Aside: I saw a similar nativequery+delete issue [1] that resulted in
> OPENJPA-459. It has been fixed in trunk, and I tested that it works fine
> with the MySQL 5.1.12 driver. Unfortunately, I am in a production
> environment and cannot move to that release. I backported OPENJPA-459
> (commit 835257) from trunk to OpenJPA-1.2.1 in my local workspace, but that
> did not fix the nativequery problem, so something else fixed it in trunk.
> OPENJPA-459 is probably not even related to this problem.)
>
> The testcase is pasted below.
>
> Thanks,
> Dinkar
>
> [1] http://n2.nabble.com/Native-DELETE-on-MySQL-td217583.html#a217583
>
> Exception with OpenJPA-1.2.1:
>
> <openjpa-1.2.1-r752877:753278 nonfatal general error>
> org.apache.openjpa.persistence.PersistenceException: Callable statements not
> supported.
> at
> org.apache.openjpa.jdbc.sql.DBDictionary.narrow(DBDictionary.java:4232)
> at
> org.apache.openjpa.jdbc.sql.DBDictionary.newStoreException(DBDictionary.java:4197)
> at
> org.apache.openjpa.jdbc.sql.SQLExceptions.getStore(SQLExceptions.java:102)
> at
> org.apache.openjpa.jdbc.sql.SQLExceptions.getStore(SQLExceptions.java:88)
> at
> org.apache.openjpa.jdbc.sql.SQLExceptions.getStore(SQLExceptions.java:64)
> at
> org.apache.openjpa.jdbc.kernel.SQLStoreQuery$SQLExecutor.executeUpdate(SQLStoreQuery.java:237)
> at org.apache.openjpa.kernel.QueryImpl.update(QueryImpl.java:1039)
> at org.apache.openjpa.kernel.QueryImpl.execute(QueryImpl.java:809)
> at org.apache.openjpa.kernel.QueryImpl.updateAll(QueryImpl.java:884)
> at org.apache.openjpa.kernel.QueryImpl.updateAll(QueryImpl.java:880)
> at
> org.apache.openjpa.kernel.DelegatingQuery.updateAll(DelegatingQuery.java:565)
> at
> org.apache.openjpa.persistence.QueryImpl.executeUpdate(QueryImpl.java:339)
> at
> org.apache.openjpa.persistence.MySQLNativeTest.testNativeCreateTable(MySQLNativeTest.java:28)
> at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
> at
> sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
> at
> sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
> at java.lang.reflect.Method.invoke(Method.java:592)
> at junit.framework.TestCase.runTest(TestCase.java:154)
> at junit.framework.TestCase.runBare(TestCase.java:127)
> at junit.framework.TestResult$1.protect(TestResult.java:106)
> at junit.framework.TestResult.runProtected(TestResult.java:124)
> at junit.framework.TestResult.run(TestResult.java:109)
> at junit.framework.TestCase.run(TestCase.java:118)
> at
> org.apache.openjpa.persistence.test.PersistenceTestCase.run(PersistenceTestCase.java:132)
> at junit.framework.TestSuite.runTest(TestSuite.java:208)
> at junit.framework.TestSuite.run(TestSuite.java:203)
> at
> org.eclipse.jdt.internal.junit.runner.junit3.JUnit3TestReference.run(JUnit3TestReference.java:130)
> at
> org.eclipse.jdt.internal.junit.runner.TestExecution.run(TestExecution.java:38)
> at
> org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:467)
> at
> org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:683)
> at
> org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:390)
> at
> org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:197)
> Caused by: java.sql.SQLException: Callable statements not supported.
> at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1075)
> at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:989)
> at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:984)
> at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:929)
> at com.mysql.jdbc.ConnectionImpl.prepareCall(ConnectionImpl.java:4191)
> at com.mysql.jdbc.ConnectionImpl.prepareCall(ConnectionImpl.java:4139)
> at
> org.apache.openjpa.lib.jdbc.DelegatingConnection.prepareCall(DelegatingConnection.java:187)
> at
> org.apache.openjpa.lib.jdbc.DelegatingConnection.prepareCall(DelegatingConnection.java:185)
> at
> org.apache.openjpa.lib.jdbc.DelegatingConnection.prepareCall(DelegatingConnection.java:185)
> at
> org.apache.openjpa.lib.jdbc.DelegatingConnection.prepareCall(DelegatingConnection.java:174)
> at
> org.apache.openjpa.jdbc.sql.SQLBuffer.prepareCall(SQLBuffer.java:570)
> at
> org.apache.openjpa.jdbc.sql.SQLBuffer.prepareCall(SQLBuffer.java:550)
> at
> org.apache.openjpa.jdbc.sql.SQLBuffer.prepareCall(SQLBuffer.java:539)
> at
> org.apache.openjpa.jdbc.kernel.SQLStoreQuery$SQLExecutor.prepareCall(SQLStoreQuery.java:335)
> at
> org.apache.openjpa.jdbc.kernel.SQLStoreQuery$SQLExecutor.executeUpdate(SQLStoreQuery.java:227)
> ... 26 more
>
> Test Program:
>
> package org.apache.openjpa.persistence;
> import javax.persistence.EntityManager;
> import javax.persistence.Query;
> import org.apache.openjpa.persistence.test.SingleEMFTestCase;
> public class MySQLNativeTest extends SingleEMFTestCase {
> public void setUp() throws Exception {
> super.setUp();
> }
> public void tearDown() throws Exception {
> super.tearDown();
> }
> public void testNativeDelete() {
> EntityManager em = emf.createEntityManager();
> em.getTransaction().begin();
> Query create = em.createNativeQuery("delete from DB2ADMIN.PARENT)");
> create.executeUpdate();
> em.getTransaction().commit();
> }
> public void testNativeCreateTable() {
> EntityManager em = emf.createEntityManager();
> em.getTransaction().begin();
> Query create = em.createNativeQuery("create table DB2ADMIN.PARENT (a
> int)");
> create.executeUpdate();
> em.getTransaction().commit();
> }
> }
> --
> View this message in context:
> http://n2.nabble.com/MySQL-4-1-22-Native-Query-Callable-statements-not-supported-tp4647741p4647741.html
> Sent from the OpenJPA Users mailing list archive at Nabble.com.
>
> CONFIDENTIALITY NOTICE: The information contained in this electronic mail
> (email) transmission (including attachments), is intended by MCLANE ADVANCED
> TECHNOLOGIES for the use of the named individual or entity to which it is
> addressed and may contain information that is privileged, confidential and/or
> protected as a trade secret. It is not intended for transmission to, or
> receipt by, any individual or entity other than the named addressee(s). If
> you have received this email in error, please delete it (including
> attachments) and any copies thereof without printing, copying or forwarding
> it, and notify the sender of the error by email reply immediately.
>
