Looks like this was fixed in trunk for OPENJPA-774. Dinkar
On Sun, Feb 28, 2010 at 10:06 PM, Dinkar Rao <[email protected]> wrote: > 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. >> >
