jpa/eclipselink.jpa.test/src/it/java/org/eclipse/persistence/testing/tests/jpa21/advanced/NamedStoredProcedureQueryTestSuite.java - eclipselink - Git at Google

 /*
  * Copyright (c) 2012, 2020 Oracle and/or its affiliates. All rights reserved.
  *
  * This program and the accompanying materials are made available under the
  * terms of the Eclipse Public License v. 2.0 which is available at
  * http://www.eclipse.org/legal/epl-2.0,
  * or the Eclipse Distribution License v. 1.0 which is available at
  * http://www.eclipse.org/org/documents/edl-v10.php.
  *
  * SPDX-License-Identifier: EPL-2.0 OR BSD-3-Clause
  */

 // Contributors:
 //     02/08/2012-2.4 Guy Pelletier
 //       - 350487: JPA 2.1 Specification defined support for Stored Procedure Calls
 //     06/20/2012-2.5 Guy Pelletier
 //       - 350487: JPA 2.1 Specification defined support for Stored Procedure Calls
 //     07/13/2012-2.5 Guy Pelletier
 //       - 350487: JPA 2.1 Specification defined support for Stored Procedure Calls
 //     08/24/2012-2.5 Guy Pelletier
 //       - 350487: JPA 2.1 Specification defined support for Stored Procedure Calls
 //     09/13/2013-2.5 Guy Pelletier
 //       - 350487: JPA 2.1 Specification defined support for Stored Procedure Calls
 //     09/27/2012-2.5 Guy Pelletier
 //       - 350487: JPA 2.1 Specification defined support for Stored Procedure Calls
 //     11/05/2012-2.5 Guy Pelletier
 //       - 350487: JPA 2.1 Specification defined support for Stored Procedure Calls
 //     01/23/2013-2.5 Guy Pelletier
 //       - 350487: JPA 2.1 Specification defined support for Stored Procedure Calls
 package org.eclipse.persistence.testing.tests.jpa21.advanced;

 import java.util.List;

 import jakarta.persistence.EntityManager;
 import jakarta.persistence.ParameterMode;
 import jakarta.persistence.StoredProcedureQuery;

 import junit.framework.TestSuite;
 import junit.framework.Test;

 import org.eclipse.persistence.internal.jpa.StoredProcedureQueryImpl;
 import org.eclipse.persistence.testing.framework.junit.JUnitTestCase;

 import org.eclipse.persistence.testing.models.jpa21.advanced.Address;
 import org.eclipse.persistence.testing.models.jpa21.advanced.LargeProject;
 import org.eclipse.persistence.testing.models.jpa21.advanced.Project;
 import org.eclipse.persistence.testing.models.jpa21.advanced.SmallProject;
 import org.eclipse.persistence.testing.models.jpa21.advanced.Employee;

 public class NamedStoredProcedureQueryTestSuite extends JUnitTestCase {
     public NamedStoredProcedureQueryTestSuite() {}

     public NamedStoredProcedureQueryTestSuite(String name) {
         super(name);
         setPuName("MulitPU-1");
     }

     public static Test suite() {
         TestSuite suite = new TestSuite();
         suite.setName("NamedStoredProcedureQueryTestSuite");

         suite.addTest(new NamedStoredProcedureQueryTestSuite("testQueryExecuteUpdateOnSelectQueryWithNoResultClass"));
         suite.addTest(new NamedStoredProcedureQueryTestSuite("testQueryExecuteUpdateOnSelectQueryWithResultClass"));
         suite.addTest(new NamedStoredProcedureQueryTestSuite("testQueryExecuteWithNamedCursors"));
         suite.addTest(new NamedStoredProcedureQueryTestSuite("testQueryExecuteWithUnNamedCursor"));
         suite.addTest(new NamedStoredProcedureQueryTestSuite("testQueryGetResultListWithNamedCursors"));
         suite.addTest(new NamedStoredProcedureQueryTestSuite("testQueryWithMultipleResults"));
         suite.addTest(new NamedStoredProcedureQueryTestSuite("testQueryWithNamedColumnResult"));
         suite.addTest(new NamedStoredProcedureQueryTestSuite("testQueryWithNamedFieldResult"));
         suite.addTest(new NamedStoredProcedureQueryTestSuite("testQueryWithNumberedFieldResult"));
         suite.addTest(new NamedStoredProcedureQueryTestSuite("testQueryWithResultClass"));
         suite.addTest(new NamedStoredProcedureQueryTestSuite("testQueryExecuteOnStoredProcQueryBuiltFromJPAThatDoesNothing"));

         return suite;
     }

     /**
      * Tests an execute update on a named stored procedure that does a select.
      * NamedStoredProcedure defines a result class.
      */
     public void testQueryExecuteOnStoredProcQueryBuiltFromJPAThatDoesNothing() {
         if (supportsStoredProcedures() && getPlatform().isMySQL()) {
             EntityManager em = createEntityManager();

             try {
                 getServerSession(getPersistenceUnitName()).executeQuery(em.createNamedStoredProcedureQuery("ReadNoAddresses").unwrap(StoredProcedureQueryImpl.class).getDatabaseQuery());
             } catch (Exception e) {
                 if (isTransactionActive(em)){
                     rollbackTransaction(em);
                 }

                 fail("Exception was caught: " + e);
             } finally {
                 closeEntityManager(em);
             }
         }
     }

     /**
      * Tests an execute update on a named stored procedure that does a select.
      * NamedStoredProcedure defines a result class.
      */
     public void testQueryExecuteUpdateOnSelectQueryWithNoResultClass() {
         if (supportsStoredProcedures() && getPlatform().isMySQL()) {
             EntityManager em = createEntityManager();

             try {
                 beginTransaction(em);
                 em.createNamedStoredProcedureQuery("ReadAllAddressesWithNoResultClass").executeUpdate();
                 commitTransaction(em);
             } catch (IllegalStateException e) {
                 if (isTransactionActive(em)){
                     rollbackTransaction(em);
                 }

                 return;
             } finally {
                 closeEntityManager(em);
             }

             fail("Expected Illegal state exception was not thrown.");
         }
     }

     /**
      * Tests an execute update on a named stored procedure that does a select.
      * NamedStoredProcedure defines a result class.
      */
     public void testQueryExecuteUpdateOnSelectQueryWithResultClass() {
         if (supportsStoredProcedures() && getPlatform().isMySQL()) {
             EntityManager em = createEntityManager();

             try {
                 beginTransaction(em);
                 em.createNamedStoredProcedureQuery("ReadAddressWithResultClass").setParameter("address_id_v", 1).executeUpdate();
                 commitTransaction(em);
             } catch (IllegalStateException e) {
                 if (isTransactionActive(em)){
                     rollbackTransaction(em);
                 }

                 return;
             } finally {
                 closeEntityManager(em);
             }

             fail("Expected Illegal state exception was not thrown.");
         }
     }

     /**
      * Tests a StoredProcedureQuery using multiple names cursors.
      */
     public void testQueryExecuteWithNamedCursors() {
         if (supportsStoredProcedures() && getPlatform().isOracle() ) {
             EntityManager em = createEntityManager();

             try {
                 StoredProcedureQuery query = em.createNamedStoredProcedureQuery("ReadUsingNamedRefCursors");

                 boolean returnVal = query.execute();

                 List<Employee> employees = (List<Employee>) query.getOutputParameterValue("CUR1");
                 assertFalse("No employees were returned", employees.isEmpty());
                 List<Address> addresses = (List<Address>) query.getOutputParameterValue("CUR2");
                 assertFalse("No addresses were returned", addresses.isEmpty());
             } catch (RuntimeException e) {
                 if (isTransactionActive(em)){
                     rollbackTransaction(em);
                 }

                 throw e;
             } finally {
                 closeEntityManager(em);
             }
         }
     }

     /**
      * Tests a StoredProcedureQuery with an unnamed cursor.
      */
     public void testQueryExecuteWithUnNamedCursor() {
         if (supportsStoredProcedures() && getPlatform().isOracle() ) {
             EntityManager em = createEntityManager();

             try {
                 StoredProcedureQuery query = em.createNamedStoredProcedureQuery("ReadUsingUnNamedRefCursor");

                 boolean returnVal = query.execute();

                 List<Employee> employees = (List<Employee>) query.getOutputParameterValue(1);
                 assertFalse("No employees were returned", employees.isEmpty());
             } catch (RuntimeException e) {
                 if (isTransactionActive(em)){
                     rollbackTransaction(em);
                 }

                 throw e;
             } finally {
                 closeEntityManager(em);
             }
         }
     }

     /**
      * Tests a StoredProcedureQuery on Oracle using named ref cursors.
      */
     public void testQueryGetResultListWithNamedCursors() {
         if (supportsStoredProcedures() && getPlatform().isOracle() ) {
             EntityManager em = createEntityManager();

             try {
                 StoredProcedureQuery query = em.createNamedStoredProcedureQuery("ReadUsingNamedRefCursors");

                 List<Employee> employees = (List<Employee>) query.getResultList();
                 assertFalse("No employees were returned", employees.isEmpty());

                 List<Address> addresses = (List<Address>) query.getResultList();
                 assertFalse("No addresses were returned", addresses.isEmpty());
             } catch (RuntimeException e) {
                 if (isTransactionActive(em)){
                     rollbackTransaction(em);
                 }

                 throw e;
             } finally {
                 closeEntityManager(em);
             }
         }
     }

     /**
      * Test multiple result sets by setting the SQL results set mapping from annotation.
      */
     public void testQueryWithMultipleResults() throws Exception {
         if (supportsStoredProcedures() && getPlatform().isMySQL()) {
             StoredProcedureQuery multipleResultSetQuery = createEntityManager().createNamedStoredProcedureQuery("ReadUsingMultipleResultSetMappings");

             // Verify first result set mapping --> Employee
             List results = multipleResultSetQuery.getResultList();
             assertNotNull("No Employee results returned", results);
             assertTrue("Empty Employee results returned", results.size() > 0);

             // Verify second result set mapping --> Address
             assertTrue("Address results not available", multipleResultSetQuery.hasMoreResults());
             results = multipleResultSetQuery.getResultList();
             assertNotNull("No Address results returned", results);
             assertTrue("Empty Address results returned", results.size() > 0);

             // Verify third result set mapping --> Project
             assertTrue("Projects results not available", multipleResultSetQuery.hasMoreResults());
             results = multipleResultSetQuery.getResultList();
             assertNotNull("No Project results returned", results);
             assertTrue("Empty Project results returned", results.size() > 0);

             for (Object result : results) {
                 Object[] resultElement = (Object[]) result;
                 assertTrue("Failed to Return 3 items", (resultElement.length == 3));
                 // Using Number as Different db/drivers  can return different types
                 // e.g. Oracle with ijdbc14.jar returns BigDecimal where as Derby
                 // with derbyclient.jar returns Double. NOTE: the order of checking
                 // here is valid and as defined by the spec.
                 assertTrue("Failed to return LargeProject", (resultElement[0] instanceof LargeProject) );
                 assertTrue("Failed To Return SmallProject", (resultElement[1] instanceof SmallProject) );
                 assertTrue("Failed to return column",(resultElement[2] instanceof Number) );
                 assertFalse("Returned same data in both result elements",((SmallProject)resultElement[1]).getName().equals(((LargeProject)resultElement[0]).getName()));
             }

             // Verify fourth result set mapping --> Employee Constructor Result
             assertTrue("Employee constructor results not available", multipleResultSetQuery.hasMoreResults());
             results = multipleResultSetQuery.getResultList();
             assertNotNull("No Employee constructor results returned", results);
             assertTrue("Empty Employee constructor results returned", results.size() > 0);

             // Verify there as no more results available
             assertFalse("More results available", multipleResultSetQuery.hasMoreResults());
         }
     }

     /**
      * Tests a NamedStoredProcedureQuery annotation using a result-set mapping.
      */
     public void testQueryWithNamedColumnResult() {
         if (supportsStoredProcedures() && getPlatform().isMySQL()) {
             EntityManager em = createEntityManager();

             try {
                 beginTransaction(em);

                 Address address = new Address();
                 address.setCity("Ottawa");
                 address.setPostalCode("K1G 6P3");
                 address.setProvince("ON");
                 address.setStreet("123 Street");
                 address.setCountry("Canada");
                 em.persist(address);
                 commitTransaction(em);

                 // Clear the cache
                 em.clear();
                 clearCache();

                 Object[] values = (Object[]) em.createNamedStoredProcedureQuery("ReadAddressMappedNamedColumnResult").setParameter("address_id_v", address.getId()).getSingleResult();
                 assertTrue("Address data not found or returned using stored procedure", ((values != null) && (values.length == 6)));
                 assertNotNull("No results returned from store procedure call", values[1]);
                 assertTrue("Address not found using stored procedure", address.getStreet().equals(values[1]));
             } catch (RuntimeException e) {
                 if (isTransactionActive(em)){
                     rollbackTransaction(em);
                 }

                 throw e;
             } finally {
                 closeEntityManager(em);
             }
         }
     }

     /**
      * Tests a NamedStoredProcedureQuery using a result-set mapping.
      */
     public void testQueryWithNamedFieldResult() {
         if (supportsStoredProcedures() && getPlatform().isMySQL()) {
             EntityManager em = createEntityManager();

             try {
                 beginTransaction(em);

                 Address address1 = new Address();
                 address1.setCity("Ottawa");
                 address1.setPostalCode("K1G 6P3");
                 address1.setProvince("ON");
                 address1.setStreet("123 Street");
                 address1.setCountry("Canada");

                 em.persist(address1);
                 commitTransaction(em);

                 // Clear the cache
                 em.clear();
                 clearCache();

                 Address address2 = (Address) em.createNamedStoredProcedureQuery("ReadAddressMappedNamedFieldResult").setParameter("address_id_v", address1.getId()).getSingleResult();
                 assertNotNull("Address returned from stored procedure is null", address2);
                 assertTrue("Address not found using stored procedure", (address1.getId() == address2.getId()));
             } catch (RuntimeException e) {
                 if (isTransactionActive(em)){
                     rollbackTransaction(em);
                 }

                 throw e;
             } finally {
                 closeEntityManager(em);
             }
         }
     }

     /**
      * Tests a NamedStoredProcedureQuery using positional paramters.
      */
     public void testQueryWithNumberedFieldResult() {
         if (supportsStoredProcedures() && getPlatform().isMySQL()) {
             EntityManager em = createEntityManager();

             try {
                 beginTransaction(em);

                 Address address1 = new Address();
                 address1.setCity("Ottawa");
                 address1.setPostalCode("K1G 6P3");
                 address1.setProvince("ON");
                 address1.setStreet("123 Street");
                 address1.setCountry("Canada");
                 em.persist(address1);
                 commitTransaction(em);

                 // Clear the cache
                 em.clear();
                 clearCache();

                 Address address2 = (Address) em.createNamedStoredProcedureQuery("ReadAddressMappedNumberedFieldResult").setParameter(1, address1.getId()).getSingleResult();
                 assertNotNull("Address returned from stored procedure is null", address2);
                 assertTrue("Address didn't build correctly using stored procedure", (address1.getId() == address2.getId()));
                 assertTrue("Address didn't build correctly using stored procedure", (address1.getStreet().equals(address2.getStreet())));
                 assertTrue("Address didn't build correctly using stored procedure", (address1.getCountry().equals(address2.getCountry())));
                 assertTrue("Address didn't build correctly using stored procedure", (address1.getProvince().equals(address2.getProvince())));
                 assertTrue("Address didn't build correctly using stored procedure", (address1.getPostalCode().equals(address2.getPostalCode())));
             } catch (RuntimeException e) {
                 if (isTransactionActive(em)){
                     rollbackTransaction(em);
                 }

                 throw e;
             } finally {
                 closeEntityManager(em);
             }
         }
     }

     /**
      * Tests a NamedStoredProcedureQuery using a result-class.
      */
     public void testQueryWithResultClass() {
         if (supportsStoredProcedures() && getPlatform().isMySQL()) {
             EntityManager em = createEntityManager();

             try {
                 beginTransaction(em);

                 Address address1 = new Address();
                 address1.setCity("Ottawa");
                 address1.setPostalCode("K1G 6P3");
                 address1.setProvince("ON");
                 address1.setStreet("123 Street");
                 address1.setCountry("Canada");
                 em.persist(address1);
                 commitTransaction(em);

                 // Clear the cache
                 em.clear();
                 clearCache();

                 Address address2 = (Address) em.createNamedStoredProcedureQuery("ReadAddressWithResultClass").setParameter("address_id_v", address1.getId()).getSingleResult();
                 assertNotNull("Address returned from stored procedure is null", address2);
                 assertTrue("Address didn't build correctly using stored procedure", (address1.getId() == address2.getId()));
                 assertTrue("Address didn't build correctly using stored procedure", (address1.getStreet().equals(address2.getStreet())));
                 assertTrue("Address didn't build correctly using stored procedure", (address1.getCountry().equals(address2.getCountry())));
                 assertTrue("Address didn't build correctly using stored procedure", (address1.getProvince().equals(address2.getProvince())));
                 assertTrue("Address didn't build correctly using stored procedure", (address1.getPostalCode().equals(address2.getPostalCode())));
             } catch (RuntimeException e) {
                 if (isTransactionActive(em)){
                     rollbackTransaction(em);
                 }

                 throw e;
             } finally {
                 closeEntityManager(em);
             }
         }
     }
     @Override
     public String getPersistenceUnitName() {
        return "MulitPU-1";
     }
 }
	/*
	* Copyright (c) 2012, 2020 Oracle and/or its affiliates. All rights reserved.
	*
	* This program and the accompanying materials are made available under the
	* terms of the Eclipse Public License v. 2.0 which is available at
	* http://www.eclipse.org/legal/epl-2.0,
	* or the Eclipse Distribution License v. 1.0 which is available at
	* http://www.eclipse.org/org/documents/edl-v10.php.
	*
	* SPDX-License-Identifier: EPL-2.0 OR BSD-3-Clause
	*/

	// Contributors:
	// 02/08/2012-2.4 Guy Pelletier
	// - 350487: JPA 2.1 Specification defined support for Stored Procedure Calls
	// 06/20/2012-2.5 Guy Pelletier
	// - 350487: JPA 2.1 Specification defined support for Stored Procedure Calls
	// 07/13/2012-2.5 Guy Pelletier
	// - 350487: JPA 2.1 Specification defined support for Stored Procedure Calls
	// 08/24/2012-2.5 Guy Pelletier
	// - 350487: JPA 2.1 Specification defined support for Stored Procedure Calls
	// 09/13/2013-2.5 Guy Pelletier
	// - 350487: JPA 2.1 Specification defined support for Stored Procedure Calls
	// 09/27/2012-2.5 Guy Pelletier
	// - 350487: JPA 2.1 Specification defined support for Stored Procedure Calls
	// 11/05/2012-2.5 Guy Pelletier
	// - 350487: JPA 2.1 Specification defined support for Stored Procedure Calls
	// 01/23/2013-2.5 Guy Pelletier
	// - 350487: JPA 2.1 Specification defined support for Stored Procedure Calls
	package org.eclipse.persistence.testing.tests.jpa21.advanced;

	import java.util.List;

	import jakarta.persistence.EntityManager;
	import jakarta.persistence.ParameterMode;
	import jakarta.persistence.StoredProcedureQuery;

	import junit.framework.TestSuite;
	import junit.framework.Test;

	import org.eclipse.persistence.internal.jpa.StoredProcedureQueryImpl;
	import org.eclipse.persistence.testing.framework.junit.JUnitTestCase;

	import org.eclipse.persistence.testing.models.jpa21.advanced.Address;
	import org.eclipse.persistence.testing.models.jpa21.advanced.LargeProject;
	import org.eclipse.persistence.testing.models.jpa21.advanced.Project;
	import org.eclipse.persistence.testing.models.jpa21.advanced.SmallProject;
	import org.eclipse.persistence.testing.models.jpa21.advanced.Employee;

	public class NamedStoredProcedureQueryTestSuite extends JUnitTestCase {
	public NamedStoredProcedureQueryTestSuite() {}

	public NamedStoredProcedureQueryTestSuite(String name) {
	super(name);
	setPuName("MulitPU-1");
	}

	public static Test suite() {
	TestSuite suite = new TestSuite();
	suite.setName("NamedStoredProcedureQueryTestSuite");

	suite.addTest(new NamedStoredProcedureQueryTestSuite("testQueryExecuteUpdateOnSelectQueryWithNoResultClass"));
	suite.addTest(new NamedStoredProcedureQueryTestSuite("testQueryExecuteUpdateOnSelectQueryWithResultClass"));
	suite.addTest(new NamedStoredProcedureQueryTestSuite("testQueryExecuteWithNamedCursors"));
	suite.addTest(new NamedStoredProcedureQueryTestSuite("testQueryExecuteWithUnNamedCursor"));
	suite.addTest(new NamedStoredProcedureQueryTestSuite("testQueryGetResultListWithNamedCursors"));
	suite.addTest(new NamedStoredProcedureQueryTestSuite("testQueryWithMultipleResults"));
	suite.addTest(new NamedStoredProcedureQueryTestSuite("testQueryWithNamedColumnResult"));
	suite.addTest(new NamedStoredProcedureQueryTestSuite("testQueryWithNamedFieldResult"));
	suite.addTest(new NamedStoredProcedureQueryTestSuite("testQueryWithNumberedFieldResult"));
	suite.addTest(new NamedStoredProcedureQueryTestSuite("testQueryWithResultClass"));
	suite.addTest(new NamedStoredProcedureQueryTestSuite("testQueryExecuteOnStoredProcQueryBuiltFromJPAThatDoesNothing"));

	return suite;
	}

	/**
	* Tests an execute update on a named stored procedure that does a select.
	* NamedStoredProcedure defines a result class.
	*/
	public void testQueryExecuteOnStoredProcQueryBuiltFromJPAThatDoesNothing() {
	if (supportsStoredProcedures() && getPlatform().isMySQL()) {
	EntityManager em = createEntityManager();

	try {
	getServerSession(getPersistenceUnitName()).executeQuery(em.createNamedStoredProcedureQuery("ReadNoAddresses").unwrap(StoredProcedureQueryImpl.class).getDatabaseQuery());
	} catch (Exception e) {
	if (isTransactionActive(em)){
	rollbackTransaction(em);
	}

	fail("Exception was caught: " + e);
	} finally {
	closeEntityManager(em);
	}
	}
	}

	/**
	* Tests an execute update on a named stored procedure that does a select.
	* NamedStoredProcedure defines a result class.
	*/
	public void testQueryExecuteUpdateOnSelectQueryWithNoResultClass() {
	if (supportsStoredProcedures() && getPlatform().isMySQL()) {
	EntityManager em = createEntityManager();

	try {
	beginTransaction(em);
	em.createNamedStoredProcedureQuery("ReadAllAddressesWithNoResultClass").executeUpdate();
	commitTransaction(em);
	} catch (IllegalStateException e) {
	if (isTransactionActive(em)){
	rollbackTransaction(em);
	}

	return;
	} finally {
	closeEntityManager(em);
	}

	fail("Expected Illegal state exception was not thrown.");
	}
	}

	/**
	* Tests an execute update on a named stored procedure that does a select.
	* NamedStoredProcedure defines a result class.
	*/
	public void testQueryExecuteUpdateOnSelectQueryWithResultClass() {
	if (supportsStoredProcedures() && getPlatform().isMySQL()) {
	EntityManager em = createEntityManager();

	try {
	beginTransaction(em);
	em.createNamedStoredProcedureQuery("ReadAddressWithResultClass").setParameter("address_id_v", 1).executeUpdate();
	commitTransaction(em);
	} catch (IllegalStateException e) {
	if (isTransactionActive(em)){
	rollbackTransaction(em);
	}

	return;
	} finally {
	closeEntityManager(em);
	}

	fail("Expected Illegal state exception was not thrown.");
	}
	}

	/**
	* Tests a StoredProcedureQuery using multiple names cursors.
	*/
	public void testQueryExecuteWithNamedCursors() {
	if (supportsStoredProcedures() && getPlatform().isOracle() ) {
	EntityManager em = createEntityManager();

	try {
	StoredProcedureQuery query = em.createNamedStoredProcedureQuery("ReadUsingNamedRefCursors");

	boolean returnVal = query.execute();

	List<Employee> employees = (List<Employee>) query.getOutputParameterValue("CUR1");
	assertFalse("No employees were returned", employees.isEmpty());
	List<Address> addresses = (List<Address>) query.getOutputParameterValue("CUR2");
	assertFalse("No addresses were returned", addresses.isEmpty());
	} catch (RuntimeException e) {
	if (isTransactionActive(em)){
	rollbackTransaction(em);
	}

	throw e;
	} finally {
	closeEntityManager(em);
	}
	}
	}

	/**
	* Tests a StoredProcedureQuery with an unnamed cursor.
	*/
	public void testQueryExecuteWithUnNamedCursor() {
	if (supportsStoredProcedures() && getPlatform().isOracle() ) {
	EntityManager em = createEntityManager();

	try {
	StoredProcedureQuery query = em.createNamedStoredProcedureQuery("ReadUsingUnNamedRefCursor");

	boolean returnVal = query.execute();

	List<Employee> employees = (List<Employee>) query.getOutputParameterValue(1);
	assertFalse("No employees were returned", employees.isEmpty());
	} catch (RuntimeException e) {
	if (isTransactionActive(em)){
	rollbackTransaction(em);
	}

	throw e;
	} finally {
	closeEntityManager(em);
	}
	}
	}

	/**
	* Tests a StoredProcedureQuery on Oracle using named ref cursors.
	*/
	public void testQueryGetResultListWithNamedCursors() {
	if (supportsStoredProcedures() && getPlatform().isOracle() ) {
	EntityManager em = createEntityManager();

	try {
	StoredProcedureQuery query = em.createNamedStoredProcedureQuery("ReadUsingNamedRefCursors");

	List<Employee> employees = (List<Employee>) query.getResultList();
	assertFalse("No employees were returned", employees.isEmpty());

	List<Address> addresses = (List<Address>) query.getResultList();
	assertFalse("No addresses were returned", addresses.isEmpty());
	} catch (RuntimeException e) {
	if (isTransactionActive(em)){
	rollbackTransaction(em);
	}

	throw e;
	} finally {
	closeEntityManager(em);
	}
	}
	}

	/**
	* Test multiple result sets by setting the SQL results set mapping from annotation.
	*/
	public void testQueryWithMultipleResults() throws Exception {
	if (supportsStoredProcedures() && getPlatform().isMySQL()) {
	StoredProcedureQuery multipleResultSetQuery = createEntityManager().createNamedStoredProcedureQuery("ReadUsingMultipleResultSetMappings");

	// Verify first result set mapping --> Employee
	List results = multipleResultSetQuery.getResultList();
	assertNotNull("No Employee results returned", results);
	assertTrue("Empty Employee results returned", results.size() > 0);

	// Verify second result set mapping --> Address
	assertTrue("Address results not available", multipleResultSetQuery.hasMoreResults());
	results = multipleResultSetQuery.getResultList();
	assertNotNull("No Address results returned", results);
	assertTrue("Empty Address results returned", results.size() > 0);

	// Verify third result set mapping --> Project
	assertTrue("Projects results not available", multipleResultSetQuery.hasMoreResults());
	results = multipleResultSetQuery.getResultList();
	assertNotNull("No Project results returned", results);
	assertTrue("Empty Project results returned", results.size() > 0);

	for (Object result : results) {
	Object[] resultElement = (Object[]) result;
	assertTrue("Failed to Return 3 items", (resultElement.length == 3));
	// Using Number as Different db/drivers can return different types
	// e.g. Oracle with ijdbc14.jar returns BigDecimal where as Derby
	// with derbyclient.jar returns Double. NOTE: the order of checking
	// here is valid and as defined by the spec.
	assertTrue("Failed to return LargeProject", (resultElement[0] instanceof LargeProject) );
	assertTrue("Failed To Return SmallProject", (resultElement[1] instanceof SmallProject) );
	assertTrue("Failed to return column",(resultElement[2] instanceof Number) );
	assertFalse("Returned same data in both result elements",((SmallProject)resultElement[1]).getName().equals(((LargeProject)resultElement[0]).getName()));
	}

	// Verify fourth result set mapping --> Employee Constructor Result
	assertTrue("Employee constructor results not available", multipleResultSetQuery.hasMoreResults());
	results = multipleResultSetQuery.getResultList();
	assertNotNull("No Employee constructor results returned", results);
	assertTrue("Empty Employee constructor results returned", results.size() > 0);

	// Verify there as no more results available
	assertFalse("More results available", multipleResultSetQuery.hasMoreResults());
	}
	}

	/**
	* Tests a NamedStoredProcedureQuery annotation using a result-set mapping.
	*/
	public void testQueryWithNamedColumnResult() {
	if (supportsStoredProcedures() && getPlatform().isMySQL()) {
	EntityManager em = createEntityManager();

	try {
	beginTransaction(em);

	Address address = new Address();
	address.setCity("Ottawa");
	address.setPostalCode("K1G 6P3");
	address.setProvince("ON");
	address.setStreet("123 Street");
	address.setCountry("Canada");
	em.persist(address);
	commitTransaction(em);

	// Clear the cache
	em.clear();
	clearCache();

	Object[] values = (Object[]) em.createNamedStoredProcedureQuery("ReadAddressMappedNamedColumnResult").setParameter("address_id_v", address.getId()).getSingleResult();
	assertTrue("Address data not found or returned using stored procedure", ((values != null) && (values.length == 6)));
	assertNotNull("No results returned from store procedure call", values[1]);
	assertTrue("Address not found using stored procedure", address.getStreet().equals(values[1]));
	} catch (RuntimeException e) {
	if (isTransactionActive(em)){
	rollbackTransaction(em);
	}

	throw e;
	} finally {
	closeEntityManager(em);
	}
	}
	}

	/**
	* Tests a NamedStoredProcedureQuery using a result-set mapping.
	*/
	public void testQueryWithNamedFieldResult() {
	if (supportsStoredProcedures() && getPlatform().isMySQL()) {
	EntityManager em = createEntityManager();

	try {
	beginTransaction(em);

	Address address1 = new Address();
	address1.setCity("Ottawa");
	address1.setPostalCode("K1G 6P3");
	address1.setProvince("ON");
	address1.setStreet("123 Street");
	address1.setCountry("Canada");

	em.persist(address1);
	commitTransaction(em);

	// Clear the cache
	em.clear();
	clearCache();

	Address address2 = (Address) em.createNamedStoredProcedureQuery("ReadAddressMappedNamedFieldResult").setParameter("address_id_v", address1.getId()).getSingleResult();
	assertNotNull("Address returned from stored procedure is null", address2);
	assertTrue("Address not found using stored procedure", (address1.getId() == address2.getId()));
	} catch (RuntimeException e) {
	if (isTransactionActive(em)){
	rollbackTransaction(em);
	}

	throw e;
	} finally {
	closeEntityManager(em);
	}
	}
	}

	/**
	* Tests a NamedStoredProcedureQuery using positional paramters.
	*/
	public void testQueryWithNumberedFieldResult() {
	if (supportsStoredProcedures() && getPlatform().isMySQL()) {
	EntityManager em = createEntityManager();

	try {
	beginTransaction(em);

	Address address1 = new Address();
	address1.setCity("Ottawa");
	address1.setPostalCode("K1G 6P3");
	address1.setProvince("ON");
	address1.setStreet("123 Street");
	address1.setCountry("Canada");
	em.persist(address1);
	commitTransaction(em);

	// Clear the cache
	em.clear();
	clearCache();

	Address address2 = (Address) em.createNamedStoredProcedureQuery("ReadAddressMappedNumberedFieldResult").setParameter(1, address1.getId()).getSingleResult();
	assertNotNull("Address returned from stored procedure is null", address2);
	assertTrue("Address didn't build correctly using stored procedure", (address1.getId() == address2.getId()));
	assertTrue("Address didn't build correctly using stored procedure", (address1.getStreet().equals(address2.getStreet())));
	assertTrue("Address didn't build correctly using stored procedure", (address1.getCountry().equals(address2.getCountry())));
	assertTrue("Address didn't build correctly using stored procedure", (address1.getProvince().equals(address2.getProvince())));
	assertTrue("Address didn't build correctly using stored procedure", (address1.getPostalCode().equals(address2.getPostalCode())));
	} catch (RuntimeException e) {
	if (isTransactionActive(em)){
	rollbackTransaction(em);
	}

	throw e;
	} finally {
	closeEntityManager(em);
	}
	}
	}

	/**
	* Tests a NamedStoredProcedureQuery using a result-class.
	*/
	public void testQueryWithResultClass() {
	if (supportsStoredProcedures() && getPlatform().isMySQL()) {
	EntityManager em = createEntityManager();

	try {
	beginTransaction(em);

	Address address1 = new Address();
	address1.setCity("Ottawa");
	address1.setPostalCode("K1G 6P3");
	address1.setProvince("ON");
	address1.setStreet("123 Street");
	address1.setCountry("Canada");
	em.persist(address1);
	commitTransaction(em);

	// Clear the cache
	em.clear();
	clearCache();

	Address address2 = (Address) em.createNamedStoredProcedureQuery("ReadAddressWithResultClass").setParameter("address_id_v", address1.getId()).getSingleResult();
	assertNotNull("Address returned from stored procedure is null", address2);
	assertTrue("Address didn't build correctly using stored procedure", (address1.getId() == address2.getId()));
	assertTrue("Address didn't build correctly using stored procedure", (address1.getStreet().equals(address2.getStreet())));
	assertTrue("Address didn't build correctly using stored procedure", (address1.getCountry().equals(address2.getCountry())));
	assertTrue("Address didn't build correctly using stored procedure", (address1.getProvince().equals(address2.getProvince())));
	assertTrue("Address didn't build correctly using stored procedure", (address1.getPostalCode().equals(address2.getPostalCode())));
	} catch (RuntimeException e) {
	if (isTransactionActive(em)){
	rollbackTransaction(em);
	}

	throw e;
	} finally {
	closeEntityManager(em);
	}
	}
	}
	@Override
	public String getPersistenceUnitName() {
	return "MulitPU-1";
	}
	}