glassfish
  1. glassfish
  2. GLASSFISH-20596

Failure to invoke JPA 2.1 Stored Procedure with Derby / EclipseLink

    Details

    • Type: Bug Bug
    • Status: Open
    • Priority: Critical Critical
    • Resolution: Unresolved
    • Affects Version/s: 4.0_b86_RC2
    • Fix Version/s: 4.1
    • Component/s: None
    • Labels:
      None
    • Environment:

      Java SE 7 / Gradle / GlassFish 4.0 embedded

      Description

      I have spent several hours attempting to get the following JPA 2.1 code to work.

      The error is in JavaDB or EclipseLink

      javax.persistence.PersistenceException: Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.5.0.v20130417-5763b06): org.eclipse.persistence.exceptions.DatabaseException
      Internal Exception: java.sql.SQLSyntaxErrorException: Column name 'TAX_CODE_ID' appears in a statement without a FROM list.
      Error Code: 20000
      Call: CALL READ_TAX_SP(TAX_CODE_ID = ?)
      bind => [101]
      Query: ResultSetMappingQuery()
      at org.eclipse.persistence.internal.jpa.QueryImpl.getDetailedException(QueryImpl.java:377)
      at org.eclipse.persistence.internal.jpa.QueryImpl.executeReadQuery(QueryImpl.java:260)
      at org.eclipse.persistence.internal.jpa.StoredProcedureQueryImpl.execute(StoredProcedureQueryImpl.java:316)
      at je7hb.jpa.advanced.storedproc1.StoredProcedureJPATest.shouldInvokeStoredProcedure(StoredProcedureJPATest.java:74)
      at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
      at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
      at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
      at org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:47)
      at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12)
      at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:44)
      at org.jboss.arquillian.junit.Arquillian$6$1.invoke(Arquillian.java:270)
      at org.jboss.arquillian.container.test.impl.execution.LocalTestExecuter.execute(LocalTestExecuter.java:60)
      at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
      at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
      at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
      at org.jboss.arquillian.core.impl.ObserverImpl.invoke(ObserverImpl.java:94)
      at org.jboss.arquillian.core.impl.EventContextImpl.invokeObservers(EventContextImpl.java:99)
      at org.jboss.arquillian.core.impl.EventContextImpl.proceed(EventContextImpl.java:81)
      at org.jboss.arquillian.core.impl.ManagerImpl.fire(ManagerImpl.java:135)
      at org.jboss.arquillian.core.impl.ManagerImpl.fire(ManagerImpl.java:115)
      at org.jboss.arquillian.core.impl.EventImpl.fire(EventImpl.java:67)
      at org.jboss.arquillian.container.test.impl.execution.ContainerTestExecuter.execute(ContainerTestExecuter.java:38)
      at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
      at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
      at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
      at org.jboss.arquillian.core.impl.ObserverImpl.invoke(ObserverImpl.java:94)
      at org.jboss.arquillian.core.impl.EventContextImpl.invokeObservers(EventContextImpl.java:99)
      at org.jboss.arquillian.core.impl.EventContextImpl.proceed(EventContextImpl.java:81)
      at org.jboss.arquillian.test.impl.TestContextHandler.createTestContext(TestContextHandler.java:89)
      at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
      at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
      at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
      at org.jboss.arquillian.core.impl.ObserverImpl.invoke(ObserverImpl.java:94)
      at org.jboss.arquillian.core.impl.EventContextImpl.proceed(EventContextImpl.java:88)
      at org.jboss.arquillian.test.impl.TestContextHandler.createClassContext(TestContextHandler.java:75)
      at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
      at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
      at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
      at org.jboss.arquillian.core.impl.ObserverImpl.invoke(ObserverImpl.java:94)
      at org.jboss.arquillian.core.impl.EventContextImpl.proceed(EventContextImpl.java:88)
      at org.jboss.arquillian.test.impl.TestContextHandler.createSuiteContext(TestContextHandler.java:60)
      at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
      at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
      at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
      at org.jboss.arquillian.core.impl.ObserverImpl.invoke(ObserverImpl.java:94)
      at org.jboss.arquillian.core.impl.EventContextImpl.proceed(EventContextImpl.java:88)
      at org.jboss.arquillian.core.impl.ManagerImpl.fire(ManagerImpl.java:135)
      at org.jboss.arquillian.test.impl.EventTestRunnerAdaptor.test(EventTestRunnerAdaptor.java:111)
      at org.jboss.arquillian.junit.Arquillian$6.evaluate(Arquillian.java:263)
      at org.jboss.arquillian.junit.Arquillian$4.evaluate(Arquillian.java:226)
      at org.jboss.arquillian.junit.Arquillian.multiExecute(Arquillian.java:314)
      at org.jboss.arquillian.junit.Arquillian.access$100(Arquillian.java:46)
      at org.jboss.arquillian.junit.Arquillian$5.evaluate(Arquillian.java:240)
      at org.junit.runners.ParentRunner.runLeaf(ParentRunner.java:271)
      at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:70)
      at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:50)
      at org.junit.runners.ParentRunner$3.run(ParentRunner.java:238)
      at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:63)
      at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:236)
      at org.junit.runners.ParentRunner.access$000(ParentRunner.java:53)
      at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:229)
      at org.jboss.arquillian.junit.Arquillian$2.evaluate(Arquillian.java:185)
      at org.jboss.arquillian.junit.Arquillian.multiExecute(Arquillian.java:314)
      at org.jboss.arquillian.junit.Arquillian.access$100(Arquillian.java:46)
      at org.jboss.arquillian.junit.Arquillian$3.evaluate(Arquillian.java:199)
      at org.junit.runners.ParentRunner.run(ParentRunner.java:309)
      at org.jboss.arquillian.junit.Arquillian.run(Arquillian.java:147)
      at org.junit.runner.JUnitCore.run(JUnitCore.java:160)
      at org.junit.runner.JUnitCore.run(JUnitCore.java:138)
      at org.jboss.arquillian.junit.container.JUnitTestRunner.execute(JUnitTestRunner.java:65)
      at org.jboss.arquillian.protocol.servlet.runner.ServletTestRunner.executeTest(ServletTestRunner.java:160)
      at org.jboss.arquillian.protocol.servlet.runner.ServletTestRunner.execute(ServletTestRunner.java:126)
      at org.jboss.arquillian.protocol.servlet.runner.ServletTestRunner.doGet(ServletTestRunner.java:90)
      at javax.servlet.http.HttpServlet.service(HttpServlet.java:687)
      at javax.servlet.http.HttpServlet.service(HttpServlet.java:790)
      at org.apache.catalina.core.StandardWrapper.service(StandardWrapper.java:1682)
      at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:318)
      at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:160)
      at org.apache.catalina.core.StandardPipeline.doInvoke(StandardPipeline.java:734)
      at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:673)
      at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:174)
      at org.apache.catalina.connector.CoyoteAdapter.doService(CoyoteAdapter.java:357)
      at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:260)
      at com.sun.enterprise.v3.services.impl.ContainerMapper.service(ContainerMapper.java:246)
      at org.glassfish.grizzly.http.server.HttpHandler.runService(HttpHandler.java:191)
      at org.glassfish.grizzly.http.server.HttpHandler.doHandle(HttpHandler.java:168)
      at org.glassfish.grizzly.http.server.HttpServerFilter.handleRead(HttpServerFilter.java:189)
      at org.glassfish.grizzly.filterchain.ExecutorResolver$9.execute(ExecutorResolver.java:119)
      at org.glassfish.grizzly.filterchain.DefaultFilterChain.executeFilter(DefaultFilterChain.java:288)
      at org.glassfish.grizzly.filterchain.DefaultFilterChain.executeChainPart(DefaultFilterChain.java:206)
      at org.glassfish.grizzly.filterchain.DefaultFilterChain.execute(DefaultFilterChain.java:136)
      at org.glassfish.grizzly.filterchain.DefaultFilterChain.process(DefaultFilterChain.java:114)
      at org.glassfish.grizzly.ProcessorExecutor.execute(ProcessorExecutor.java:77)
      at org.glassfish.grizzly.nio.transport.TCPNIOTransport.fireIOEvent(TCPNIOTransport.java:838)
      at org.glassfish.grizzly.strategies.AbstractIOStrategy.fireIOEvent(AbstractIOStrategy.java:113)
      at org.glassfish.grizzly.strategies.WorkerThreadIOStrategy.run0(WorkerThreadIOStrategy.java:115)
      at org.glassfish.grizzly.strategies.WorkerThreadIOStrategy.access$100(WorkerThreadIOStrategy.java:55)
      at org.glassfish.grizzly.strategies.WorkerThreadIOStrategy$WorkerThreadRunnable.run(WorkerThreadIOStrategy.java:135)
      at org.glassfish.grizzly.threadpool.AbstractThreadPool$Worker.doWork(AbstractThreadPool.java:564)
      at org.glassfish.grizzly.threadpool.AbstractThreadPool$Worker.run(AbstractThreadPool.java:544)
      at java.lang.Thread.run(Thread.java:722)
      Caused by: Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.5.0.v20130417-5763b06): org.eclipse.persistence.exceptions.DatabaseException
      Internal Exception: java.sql.SQLSyntaxErrorException: Column name 'TAX_CODE_ID' appears in a statement without a FROM list.
      Error Code: 20000
      at org.eclipse.persistence.exceptions.DatabaseException.sqlException(DatabaseException.java:340)
      at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.basicExecuteCall(DatabaseAccessor.java:679)
      at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeCall(DatabaseAccessor.java:558)
      at org.eclipse.persistence.internal.sessions.AbstractSession.basicExecuteCall(AbstractSession.java:1995)
      at org.eclipse.persistence.sessions.server.ClientSession.executeCall(ClientSession.java:296)
      at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeCall(DatasourceCallQueryMechanism.java:242)
      at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeCall(DatasourceCallQueryMechanism.java:228)
      at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.execute(DatasourceCallQueryMechanism.java:220)
      at org.eclipse.persistence.queries.ResultSetMappingQuery.executeDatabaseQuery(ResultSetMappingQuery.java:291)
      at org.eclipse.persistence.queries.DatabaseQuery.execute(DatabaseQuery.java:899)
      at org.eclipse.persistence.queries.DatabaseQuery.executeInUnitOfWork(DatabaseQuery.java:798)
      at org.eclipse.persistence.internal.sessions.UnitOfWorkImpl.internalExecuteQuery(UnitOfWorkImpl.java:2894)
      at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1797)
      at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1779)
      at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1744)
      at org.eclipse.persistence.internal.jpa.QueryImpl.executeReadQuery(QueryImpl.java:258)
      ... 105 more
      Caused by: java.sql.SQLSyntaxErrorException: Column name 'TAX_CODE_ID' appears in a statement without a FROM list.
      at org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(Unknown Source)
      at org.apache.derby.impl.jdbc.Util.generateCsSQLException(Unknown Source)
      at org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(Unknown Source)
      at org.apache.derby.impl.jdbc.TransactionResourceImpl.handleException(Unknown Source)
      at org.apache.derby.impl.jdbc.EmbedConnection.handleException(Unknown Source)
      at org.apache.derby.impl.jdbc.ConnectionChild.handleException(Unknown Source)
      at org.apache.derby.impl.jdbc.EmbedPreparedStatement.<init>(Unknown Source)
      at org.apache.derby.impl.jdbc.EmbedPreparedStatement20.<init>(Unknown Source)
      at org.apache.derby.impl.jdbc.EmbedPreparedStatement30.<init>(Unknown Source)
      at org.apache.derby.impl.jdbc.EmbedPreparedStatement40.<init>(Unknown Source)
      at org.apache.derby.jdbc.Driver40.newEmbedPreparedStatement(Unknown Source)
      at org.apache.derby.impl.jdbc.EmbedConnection.prepareStatement(Unknown Source)
      at org.apache.derby.impl.jdbc.EmbedConnection.prepareStatement(Unknown Source)
      at com.sun.gjc.spi.base.ConnectionHolder.prepareStatement(ConnectionHolder.java:586)
      at com.sun.gjc.spi.jdbc40.ConnectionWrapper40.prepareCachedStatement(ConnectionWrapper40.java:255)
      at com.sun.gjc.spi.jdbc40.ConnectionWrapper40.prepareCachedStatement(ConnectionWrapper40.java:52)
      at com.sun.gjc.spi.ManagedConnectionImpl.prepareCachedStatement(ManagedConnectionImpl.java:992)
      at com.sun.gjc.spi.jdbc40.ConnectionWrapper40.prepareStatement(ConnectionWrapper40.java:173)
      at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.prepareStatement(DatabaseAccessor.java:1551)
      at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.prepareStatement(DatabaseAccessor.java:1500)
      at org.eclipse.persistence.internal.databaseaccess.DatabaseCall.prepareStatement(DatabaseCall.java:778)
      at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.basicExecuteCall(DatabaseAccessor.java:619)
      ... 119 more
      Caused by: java.sql.SQLException: Column name 'TAX_CODE_ID' appears in a statement without a FROM list.
      at org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(Unknown Source)
      at org.apache.derby.impl.jdbc.SQLExceptionFactory40.wrapArgsForTransportAcrossDRDA(Unknown Source)
      ... 141 more
      Caused by: ERROR 42X15: Column name 'TAX_CODE_ID' appears in a statement without a FROM list.
      at org.apache.derby.iapi.error.StandardException.newException(Unknown Source)
      at org.apache.derby.impl.sql.compile.ColumnReference.bindExpression(Unknown Source)
      at org.apache.derby.impl.sql.compile.BinaryOperatorNode.bindExpression(Unknown Source)
      at org.apache.derby.impl.sql.compile.BinaryComparisonOperatorNode.bindExpression(Unknown Source)
      at org.apache.derby.impl.sql.compile.SQLToJavaValueNode.bindExpression(Unknown Source)
      at org.apache.derby.impl.sql.compile.MethodCallNode.bindParameters(Unknown Source)
      at org.apache.derby.impl.sql.compile.StaticMethodCallNode.bindExpression(Unknown Source)
      at org.apache.derby.impl.sql.compile.JavaToSQLValueNode.bindExpression(Unknown Source)
      at org.apache.derby.impl.sql.compile.CallStatementNode.bindStatement(Unknown Source)
      at org.apache.derby.impl.sql.GenericStatement.prepMinion(Unknown Source)
      at org.apache.derby.impl.sql.GenericStatement.prepare(Unknown Source)
      at org.apache.derby.impl.sql.conn.GenericLanguageConnectionContext.prepareInternalStatement(Unknown Source)
      ... 135 more

      [EL Config]: connection: Connection(2016291100)--disconnect
      [EL Info]: connection: file:/C:/Users/peter/AppData/Local/Temp/gfembed6127754577011602453tmp/applications/test/WEB-INF/lib/278086af-fcae-4066-a179-a2aa1b679e23.jar_testDatabase logout successful
      PlainTextActionReporterSUCCESSNo monitoring data to report.
      JdbcRuntimeExtension, getAllSystemRAResourcesAndPools = [GlassFishConfigBean.org.glassfish.jdbc.config.JdbcResource, GlassFishConfigBean.org.glassfish.jdbc.config.JdbcResource, GlassFishConfigBean.org.glassfish.jdbc.config.JdbcConnectionPool, GlassFishConfigBean.org.glassfish.jdbc.config.JdbcConnectionPool, GlassFishConfigBean.org.glassfish.jdbc.config.JdbcConnectionPool, GlassFishConfigBean.org.glassfish.jdbc.config.JdbcResource]
      May 31, 2013 6:02:07 PM org.glassfish.admin.mbeanserver.JMXStartupService shutdown
      INFO: JMXStartupService and JMXConnectors have been shut down.
      May 31, 2013 6:02:07 PM com.sun.enterprise.connectors.service.ResourceAdapterAdminServiceImpl sendStopToResourceAdapter
      INFO: RAR7094: __ds_jdbc_ra shutdown successful.
      May 31, 2013 6:02:07 PM com.sun.enterprise.v3.server.AppServerStartup stop
      INFO: Shutdown procedure finished

      The SQL script file is `src/test/resources-glassfish-embedded/scripts/create-schema.sql':
      CREATE TABLE TAX_CODE ( TAX_CODE_ID BIGINT, NAME VARCHAR(16), PRIMARY KEY(TAX_CODE_ID));

      CREATE TABLE REGION ( REGION_ID BIGINT, NAME VARCHAR(16), PRIMARY KEY(REGION_ID));

      INSERT INTO TAX_CODE VALUES ( 101, 'FULL_TIME');
      INSERT INTO TAX_CODE VALUES ( 102, 'CHARITY');
      INSERT INTO TAX_CODE VALUES ( 103, 'TEMPORARY');
      INSERT INTO TAX_CODE VALUES ( 104, 'EMPLOYED');
      INSERT INTO TAX_CODE VALUES ( 105, 'EMPLOYED');

      CREATE PROCEDURE READ_TAX_SP( TAX_CODE_ID INTEGER )
      PARAMETER STYLE JAVA
      LANGUAGE JAVA
      READS SQL DATA
      DYNAMIC RESULT SETS 1
      EXTERNAL NAME 'je7hb.jpa.advanced.storedproc1.CustomStoreProcedure.selectTaxCode';

      The junit test is:
      package je7hb.jpa.advanced.storedproc1;

      import je7hb.jpa.Utils;
      import org.jboss.arquillian.container.test.api.Deployment;
      import org.jboss.arquillian.junit.Arquillian;
      import org.jboss.shrinkwrap.api.ArchivePaths;
      import org.jboss.shrinkwrap.api.ShrinkWrap;
      import org.jboss.shrinkwrap.api.asset.EmptyAsset;
      import org.jboss.shrinkwrap.api.spec.JavaArchive;
      import org.junit.Before;
      import org.junit.Test;
      import org.junit.runner.RunWith;

      import javax.annotation.Resource;
      import javax.persistence.EntityManager;
      import javax.persistence.ParameterMode;
      import javax.persistence.PersistenceContext;
      import javax.persistence.StoredProcedureQuery;
      import javax.sql.DataSource;
      import javax.transaction.UserTransaction;
      import java.io.InputStream;
      import java.util.List;

      /**

      • A unit test CustomStoredProcedureJDBCTest to verify the operation of CustomStoredProcedureJDBCTest
        *
      • @author Peter Pilgrim
        */
        @RunWith(Arquillian.class)
        // Does not work properly @CreateSchema( {"scripts/create-schema.sql"}

        )
        public class StoredProcedureJPATest {

      @Deployment
      public static JavaArchive createDeployment()

      { JavaArchive jar = ShrinkWrap.create(JavaArchive.class) .addClasses(TaxCode.class) .addAsResource( "test-persistence.xml", "META-INF/persistence.xml") .addAsManifestResource( EmptyAsset.INSTANCE, ArchivePaths.create("beans.xml")); return jar; }

      @PersistenceContext
      EntityManager em;

      @Resource
      UserTransaction utx;

      @Resource(name="jdbc/arquillian")
      DataSource dataSource;

      static boolean initialised = false;

      @Before
      public void createTablesAndCustomStoredProcedure() throws Exception {
      if ( !initialised)

      { InputStream is = this.getClass().getResourceAsStream("/scripts/create-schema.sql"); Utils.executeScript(dataSource, is); initialised = true; }

      }

      @Test
      public void shouldInvokeStoredProcedure() throws Exception

      { utx.begin(); StoredProcedureQuery query = em.createStoredProcedureQuery("READ_TAX_SP"); query.registerStoredProcedureParameter( "TAX_CODE_ID", Integer.class, ParameterMode.IN); query.setParameter("TAX_CODE_ID", 101); boolean status = query.execute(); System.out.printf("status=%s\n", status); List rs = query.getResultList(); System.out.printf("rs=%s\n", rs ); utx.commit(); }

      }

      The implementation of the Java DB stored procedure is:
      package je7hb.jpa.advanced.storedproc1;

      import java.sql.*;

      public class CustomStoreProcedure {

      public static void selectTaxCode(int p1, ResultSet[] data1 )
      throws SQLException

      { Connection conn = DriverManager.getConnection("jdbc:default:connection"); PreparedStatement ps1 = conn.prepareStatement("select TAX_CODE_ID, NAME from TAX_CODE where TAX_CODE_ID = ?"); ps1.setInt(1, p1); data1[0] = ps1.executeQuery(); conn.close(); }

      }

      The utility class is:
      package je7hb.jpa;

      import javax.persistence.EntityManager;
      import javax.sql.DataSource;
      import javax.transaction.UserTransaction;
      import java.io.*;
      import java.sql.*;
      import java.util.Map;
      import java.util.StringTokenizer;

      import static org.junit.Assert.fail;

      public final class Utils {

      public static void executeScript( DataSource dataSource, InputStream is )
      throws Exception
      {
      System.out.printf("executeScript(%s, %s)\n", dataSource, is );
      StringBuilder script = new StringBuilder();
      LineNumberReader lnreader = new LineNumberReader( new InputStreamReader(is));
      String line = null;
      Connection conx = dataSource.getConnection();
      while ( (line = lnreader.readLine()) != null ) {
      line = line.trim();
      if ( line.length() != 0 && !line.startsWith("--"))

      { script.append(line+" "); }

      }

      StringTokenizer stk = new StringTokenizer(script.toString(),";");
      while ( stk.hasMoreTokens()) {
      String sql = stk.nextToken();
      if ( sql.trim().length() > 0 )

      { Statement stmt = conx.createStatement(); System.out.printf("executing sql -- %s", sql); boolean done = stmt.execute(sql); System.out.printf(" - %s\n", (done?"Result set":"No result set")); }

      }
      conx.commit();
      conx.close();
      }

      public static void dumpResultSet(ResultSet rs, PrintStream ps) throws SQLException {
      ResultSetMetaData rsmd = rs.getMetaData();
      int count = 1;
      while (rs.next()) {
      ps.printf(" [%d] ", count);
      for ( int col = 1; col <= rsmd.getColumnCount(); ++col )

      { if ( col == 1) System.out.print("|"); ps.printf("%12s|", rs.getObject(col)); }

      ps.println();
      ++count;
      }
      }
      }

      The build file is `build.gradle':
      apply plugin: 'java'
      apply plugin: 'maven'
      apply plugin: 'eclipse'
      apply plugin: 'idea'

      // Define equivalent Maven GAV coordinates.
      group = 'je7hb.jpa.advanced'
      archivesBaseName = 'ch11-jpa-maps1'
      version = '1.0'

      repositories {
      mavenCentral()
      maven

      { url 'https://maven.java.net/content/groups/promoted' }

      maven

      { url 'http://repository.jboss.org/nexus/content/groups/public' }

      }

      dependencies {
      compile 'javax.enterprise:cdi-api:1.1-PFD'
      compile 'com.sun.faces:jsf-api:2.0.3-b05'
      compile 'javax.validation:validation-api:1.1.0.CR3'
      compile 'org.hibernate:hibernate-validator:5.0.0.CR4'
      compile 'javax:javaee-api:7.0'
      compile 'joda-time:joda-time:2.2'
      runtime 'org.glassfish.main.extras:glassfish-embedded-all:4.0-b86'

      testCompile 'org.slf4j:slf4j-simple:1.6.4'
      testCompile 'org.jboss.arquillian.junit:arquillian-junit-container:1.0.3.Final'
      testCompile 'org.jboss.arquillian.extension:arquillian-persistence-api:1.0.0.Alpha6'
      testCompile 'org.jboss.arquillian.extension:arquillian-persistence-impl:1.0.0.Alpha6'
      testCompile 'org.jboss.arquillian.container:arquillian-glassfish-embedded-3.1:1.0.0.Final-SNAPSHOT'
      testCompile 'junit:junit:4.11'
      }

      task wrapper(type: Wrapper) {
      gradleVersion = '1.6'
      }

      // Override Gradle defaults - a force an exploded JAR view
      sourceSets {
      main

      { output.resourcesDir = 'build/classes/main' output.classesDir = 'build/classes/main' }

      test {
      resources

      { srcDir 'src/test/resources' }

      resources

      { srcDir 'src/test/resources-glassfish-embedded' }

      output.resourcesDir = 'build/classes/test'
      output.classesDir = 'build/classes/test'
      }
      }

      You will also need the following files `src/test/resources-glassfish-embedded/test-persistence.xml':

      <persistence version="1.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_1_0.xsd">

      <persistence-unit name="testDatabase" transaction-type="JTA">
      <provider>org.eclipse.persistence.jpa.PersistenceProvider</provider>
      <jta-data-source>jdbc/arquillian</jta-data-source>
      <properties>
      <property name="eclipselink.target-database" value="JavaDB"/>
      <property name="eclipselink.ddl-generation" value="drop-and-create-tables" />
      <property name="eclipselink.ddl-generation.output-mode" value="both"/>
      <!-- Arquillian EclipseLink is broken; Fix from Jason Drake 14/Dec/2012
      http://syrupsucker.blogspot.co.uk/2012/11/eclipselink-logging-with-arquillian-in.html

      Also see EclipseLink logging JPA page
      http://wiki.eclipse.org/EclipseLink/Examples/JPA/Logging
      -->
      <property name="eclipselink.logging.logger" value="org.eclipse.persistence.logging.DefaultSessionLog"/>
      <!-- Print Timestamp to determine when a log output was printed.
      (default value is "true") -->
      <property name="eclipselink.logging.timestamp" value="false"/>
      <!-- Print Session — to determine on which underlying session (if any) the message was sent.
      (default value is "true") -->
      <property name="eclipselink.logging.session" value="false"/>
      <!-- Print Thread - when you are running multithreaded applications in order
      to print the hashcode of the thread, which wrote the message.
      (default value is "false") -->
      <property name="eclipselink.logging.thread" value="false"/>
      <!-- Print Exceptions to enable logging of the exceptions's messages
      at the time when these exceptions are thrown.
      (default value is "true") -->
      <property name="eclipselink.logging.exceptions" value="true"/>

      <property name="eclipselink.logging.level" value="FINE"/>
      <property name="eclipselink.logging.level.sql" value="FINE"/>
      <property name="eclipselink.logging.parameters" value="true"/>
      <property name="eclipselink.create-ddl-jdbc-file-name" value="createDDL.jdbc"/>
      </properties>
      </persistence-unit>

      </persistence>

      And

      You will also need the following files `src/test/resources-glassfish-embedded/glassfish-resources.xml':
      <?xml version="1.0" encoding="UTF-8"?>
      <!DOCTYPE resources PUBLIC
      "-//GlassFish.org//DTD GlassFish Application Server 3.1 Resource Definitions//EN"
      "http://glassfish.org/dtds/glassfish-resources_1_5.dtd">
      <resources>
      <jdbc-resource pool-name="ArquillianEmbeddedDerbyPool"
      jndi-name="jdbc/arquillian"/>
      <jdbc-connection-pool name="ArquillianEmbeddedDerbyPool"
      res-type="javax.sql.DataSource"
      datasource-classname="org.apache.derby.jdbc.EmbeddedDataSource"
      is-isolation-level-guaranteed="false">
      <property name="databaseName" value="build/databases/derby"/>
      <property name="createDatabase" value="create"/>
      </jdbc-connection-pool>
      </resources>

      And finally you need `src/test/resources/arquillian.xml':
      <?xml version="1.0" encoding="UTF-8"?>
      <arquillian xmlns="http://jboss.org/schema/arquillian"
      xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
      xsi:schemaLocation="
      http://jboss.org/schema/arquillian
      http://jboss.org/schema/arquillian/arquillian_1_0.xsd">
      <container qualifier="glassfish-embedded" default="true">
      <configuration>
      <property name="resourcesXml">
      src/test/resources-glassfish-embedded/glassfish-resources.xml
      </property>
      </configuration>
      </container>
      </arquillian>

      I tested the code with a normal JDBC CallableStatement and yet the JPA 2.1 side fails with a weird issue:

      Caused by: ERROR 42X15: Column name 'TAX_CODE_ID' appears in a statement without a FROM list.

      The end.

        Activity

        Hide
        peterkrogh added a comment -

        This error is a catch all error of sorts from Derby. I have hit it
        before when executing with Named Parameters on CallableStatements.

        This appears to be because Derby doesn't support Named Parameters on
        CallableStatements. (Or at least didn't back when this page was created
        (2011).
        http://wiki.apache.org/db-derby/JDBCSupport

        I suspect that in the JDBC test program that works, it is being executed using positional parameters.

        Change:
        query.registerStoredProcedureParameter( "TAX_CODE_ID", Integer.class,
        ParameterMode.IN);
        query.setParameter("TAX_CODE_ID", 101)

        to this:

        query.registerStoredProcedureParameter( 1, Integer.class,
        ParameterMode.IN);
        query.setParameter(1, 101);

        Show
        peterkrogh added a comment - This error is a catch all error of sorts from Derby. I have hit it before when executing with Named Parameters on CallableStatements. This appears to be because Derby doesn't support Named Parameters on CallableStatements. (Or at least didn't back when this page was created (2011). http://wiki.apache.org/db-derby/JDBCSupport I suspect that in the JDBC test program that works, it is being executed using positional parameters. Change: query.registerStoredProcedureParameter( "TAX_CODE_ID", Integer.class, ParameterMode.IN); query.setParameter("TAX_CODE_ID", 101) to this: query.registerStoredProcedureParameter( 1, Integer.class, ParameterMode.IN); query.setParameter(1, 101);
        Hide
        Mitesh Meswani added a comment -

        peter_pilgrim, can you please try out perterkrogh's above?

        Based on perterkrogh's comment above, this may be just due to a limitation on Derby and should be closed. Keeping it open till the filer get a chance to try out the workaround. Targeting this for 4.0.1 for now.

        Show
        Mitesh Meswani added a comment - peter_pilgrim, can you please try out perterkrogh's above? Based on perterkrogh's comment above, this may be just due to a limitation on Derby and should be closed. Keeping it open till the filer get a chance to try out the workaround. Targeting this for 4.0.1 for now.

          People

          • Assignee:
            Mitesh Meswani
            Reporter:
            peter_pilgrim
          • Votes:
            0 Vote for this issue
            Watchers:
            0 Start watching this issue

            Dates

            • Created:
              Updated: