Skip to Content

EntityManager injection fails with custom datasource

Hi,

Iu2019m trying to work with JPA and a custom datasource in CE 7.1.1. It works fine with the system datasource, but when I use the custom datasource, injection of the entity manager doesnu2019t work.

My setup is an EJB 3.0 Stateless Session Bean exposed as a web service that performs some basic operations on a simple JPA entity: insert two rows or get all.

Hereu2019s how I configured the custom datasource:

  • Itu2019s a Hypersonic SQL DB 1.8.

  • I changed the password of user SA to u201CSAu201D because NWA wonu2019t accept an empty password.

  • The database is up and running. I can connect it using the hsql database manager and I have an active connection from NWDS.

  • The EJB DC has a connection to the database. Generating DDL from the JPA entities works fine. I can also browse and edit tables and their contents from NWDS.

  • I uploaded the JDBC driver file hsqldb.jar for this DBMS to the CE server using NWA > Application Resources under the name u201Chsqlu201D. In the Resource List, the state of the driver is given as u201Cunknownu201D.

  • I created a User-Defined JDBC DataSource named u201CHSQLu201D parametrized as follows:

- Driver Name: hsql

- SQL Engine: Vendor SQL

- Isolation Level: Default

- Driver Class: org.hsqldb.jdbcDriver

- Database URL: jdbc:hsqldb:hsql://<hostname of the DBMS>/PUBLIC/

- Username: sa, Password: sa

- Description: hsql

- I didnu2019t touch anything on the other tabs and saved it.

  • After saving it, the datasource shows up as available (green).

In my EAR DC META-INF/data-source-aliases.xml, Iu2019m referring to it as follows:

<?xml version="1.0" encoding="UTF-8"?>
<data-source-aliases xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="data-source-aliases.xsd">
  <application-name>LocalDevelopment~0~mysql_ear~aoksystems.de</application-name>
  <aliases>
    <data-source-name>HSQL</data-source-name>
    <alias>HSQLALIAS</alias>
  </aliases>
</data-source-aliases>

In my EJB DC, Iu2019m using the alias defined above. Here is the content of META-INF/persistence.xml:

<?xml version="1.0" encoding="UTF-8"?>
<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="mysql_pu">
		<jta-data-source>HSQLALIAS</jta-data-source>
		<properties>
			<property name="com.sap.jpa.versioning.generator.tablename"
				value="MYSQL_VERSION_GEN" />
		</properties>
	</persistence-unit>
</persistence>

Hereu2019s the code of the entity with its two named queries:

@Entity
@Table(name = "MYSQL_EMPLOYEE")
@NamedQueries( {
		@NamedQuery(name = "Employee.getAll", query = "SELECT e FROM Employee e"),
		@NamedQuery(name = "Employee.getById", query = "SELECT e FROM Employee e WHERE e.id = :id") })
public class Employee implements Serializable {
	@Id
	@Column(name = "ID")
	@GeneratedValue(strategy = GenerationType.TABLE, generator = "mysqlGen")
	@TableGenerator(name = "mysqlGen", table = "MYSQL_VERSION_GEN")
	private int id;
	@Column(name = "FIRST_NAME")
	private String firstName;
	@Column(name = "LAST_NAME")
	private String lastName;
	@Version
	@Column(name = "VERSION")
	private int version;
	private static final long serialVersionUID = 1L;

	public Employee() {
		super();
	}

  // Setters and getters
  // ...
}

Hereu2019s the code of the Stateless Session Bean:

@WebService(name="NewEmployeeFacade", portName="NewEmployeeFacadeBeanPort", serviceName="NewEmployeeFacadeService", targetNamespace="http://mysql.aoksystems.de/")
@Stateless
public class EmployeeFacadeBean implements EmployeeFacadeLocal {
	@PersistenceContext(unitName = "mysql_pu")
	protected EntityManager em;
 // methods 
}

Again, everything works fine as soon as I change META-INF/data-source-alias.xml in the EAR DC and set the datasource to u201CSAPCE1DBu201D, thus using the system database.

But when I use datasource HSQL, I get this exception:

javax.ejb.EJBException: Exception in getMethodReady() for stateless bean aoksystems.de/mysql_earannotation|aoksystems.de~mysql_ejb.jar*annotation|EmployeeFacadeBean; nested exception is: com.sap.engine.services.ejb3.util.pool.PoolException: javax.ejb.EJBException: Cannot perform injection over bean instance de.aoksystems.mysql.EmployeeFacadeBean@14ca646 for bean

aoksystems.de/mysql_earannotation|aoksystems.de~mysql_ejb.jarannotation|EmployeeFacadeBean; nested exception is: com.sap.engine.lib.injection.InjectionException: Injection on field em of instance de.aoksystems.mysql.EmployeeFacadeBean@14ca646 failed. Could not get a value to be injected from the factory.*

I get the same error when I remove the named queries from the entity and the calls from the session bean. Exactly the same happens when I use MySQL 5.1.7 instead of HSQL.

Can anyone help? Thanks,

Thorsten

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

4 Answers

  • Best Answer
    Apr 08, 2009 at 11:08 AM

    Hi Vladimir,

    Thanks for this hint. The trace file contains a lot of valuable information.

    At first I realized that I had accidentally set the SQL Engine to "Native SQL" instead of "Vendor SQL".

    Then I got an error reporting trouble with the ID Generator:

    Caused by: javax.persistence.PersistenceException: IdGenerator >>mysqlGen<< could not be initialized

    at com.sap.engine.services.orpersistence.generator.GeneratorFactory.<init>(GeneratorFactory.java:176)

    at com.sap.engine.services.orpersistence.entitymanager.EntityManagerFactoryImpl$MetaDataImpl.<init>(EntityManagerFactoryImpl.java:285)

    ...

    at com.sap.engine.lib.injection.ReferenceObjectFactory.getObject(ReferenceObjectFactory.java:67)

    at com.sap.engine.lib.injection.FieldInjector.inject(FieldInjector.java:113)

    ... 73 more

    Caused by: java.sql.SQLException: Unexpected token: FOR in statement [SELECT "GEN_VALUE" FROM "MYSQL_VERSION_GEN" WHERE "GEN_KEY" = ? FOR UPDATE]

    at org.hsqldb.jdbc.Util.throwError(Unknown Source)

    at org.hsqldb.jdbc.jdbcPreparedStatement.<init>(Unknown Source)

    at org.hsqldb.jdbc.jdbcConnection.prepareStatement(Unknown Source)

    at com.sap.sql.jdbc.vendor.VendorConnectionHandle.prepareStatement(VendorConnectionHandle.java:98)

    at com.sap.engine.services.dbpool.cci.ConnectionHandle.prepareStatement(ConnectionHandle.java:79)

    at com.sap.engine.services.orpersistence.generator.TableIdGenerator.selectForUpdate(TableIdGenerator.java:144)

    at com.sap.engine.services.orpersistence.generator.TableIdGenerator.initialize(TableIdGenerator.java:181)

    at com.sap.engine.services.orpersistence.generator.GeneratorFactory.<init>(GeneratorFactory.java:173)

    ... 83 more

    The ID generator is configured as follows:

    persistence.xml:

    <?xml version="1.0" encoding="UTF-8"?> <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="mysql_pu">
        <jta-data-source>HSQLALIAS3</jta-data-source>
          <properties>
            <property name="com.sap.jpa.versioning.generator.tablename" value="MYSQL_VERSION_GEN" />
        </properties>
      </persistence-unit>
    </persistence>

    In the entity:

    public class Employee implements Serializable {
    	@Id
    	@Column(name = "ID")
    	@GeneratedValue(strategy = GenerationType.TABLE, generator = "mysqlGen")
    	@TableGenerator(name = "mysqlGen", table = "MYSQL_VERSION_GEN")
    	private int id;
    	...
    }

    When I comment out every reference to the ID generator and set hard-coded ids, I can create and retrieve some records. That's already a great progress!

    But what can I do about ID generation? Is the SAP ID generator I'm trying to use only supposed to work with the system database? What else can I use?

    Also, my attempts to use the MySQL database as a datasource still fail. The tracefile says:

    Caused by: java.lang.RuntimeException: The persistence unit is inconsistent with the database schema:

    Error on entity >>de.aoksystems.mysql.Employee<<: The table >>MYSQL_EMPLOYEE<< does not exist.

    at com.sap.engine.services.orpersistence.entitymanager.EntityManagerFactoryImpl.validateSchemaAndFillInJdbcTypes(EntityManagerFactoryImpl.java:191)

    at com.sap.engine.services.orpersistence.entitymanager.EntityManagerFactoryImpl.<init>(EntityManagerFactoryImpl.java:104)

    at com.sap.engine.services.orpersistence.entitymanager.JtaEntityManagerFactoryImpl.<init>(JtaEntityManagerFactoryImpl.java:33)

    at com.sap.engine.services.orpersistence.provider.PersistenceProviderImpl.createJtaEntityManagerFactory(PersistenceProviderImpl.java:126)

    ...

    Yet clearly, the table exists. I'm using database URL jdbc:mysql://<db host name>/mysql and and driver class com.mysql.jdbc.Driver to connect to MySQL. These exact same settings work fine in NWDS (database connection) and in the HSQL Database Manager, which I can also use to connect to the MySQL database.

    By the way, I've tried different spellings in the @Table(name="...") annotation: MYSQL_EMPLOYEE, mysql_employee, MYSQL.MYSQL_EMPLOYEE, mysql.mysql_employee. The result was the same in each case.

    Thanks a lot,

    Thorsten

    Edited by: Thorsten Franz on Apr 8, 2009 1:08 PM

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member

      Hi Thorsten,

      neither HSQL nor MySQL are officially supported by NetWeaver and SAP JPA. We basically have no experience with HSQL. However, some users have sucessfully used SAP JPA with MySQL. In general, I'd reccomend you to use one of the fully supported databases (Oracle, SQL Server, SAP MaxDB, DB2 for Unix/NT, OS390 or iSeries).

      With respect to MySQL, you might want to try forcing MySQL to understand "ANSI Quotes" as described [in this thread|EntityManager injection fails with custom datasource;.

      I hope this helps,

      Adrian

  • Apr 08, 2009 at 09:15 AM

    Hi Thorsten,

    Please have a look in the defaultTrace.trc file - there should be more details about the error, e.g. nested exceptions.

    -- Vladimir

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Apr 08, 2009 at 12:24 PM

    Hi

    Your data Sourse Alias xml file has many missing parameter

    /*****************************************************************************************************************/

    <?xml version="1.0" encoding="UTF-8"?>

    http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="data-source-aliases.xsd">

    <application-name>LocalDevelopment0mysql_ear~aoksystems.de

    <aliases>

    <data-source-name>HSQL</data-source-name>

    <alias>HSQLALIAS</alias>

    </aliases>

    </data-source-aliases>

    /****************************************************************************************************************/

    No Database and no userID / Password is define

    Please look into it.

    Thank You

    Syed Saifuddin

    Add comment
    10|10000 characters needed characters exceeded

  • Apr 08, 2009 at 06:08 PM

    Hi Adrian and Vladimir,

    thank you very much for your help. My problem with using MySQL is solved.

    Following your advice, I changed a setting in configuration file my.ini located in the server directory.

    I just added ",ANSI":

    [mysqld]
    ...
    # Set the SQL mode to strict
    sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,ANSI"
    

    After restarting the server, it works properly - including ID generation with GenerationType.TABLE. By the way, I shortened my table names so that none is longer than 18 characters.

    As far as ID generation not working with HSQL is concerned, I've understood the limitations of the SAP JPA implementation to supported database systems. Alternatives I would explore if necessary:

    1. work with a different JPA implementation

    2. work with plain Hibernate

    3. use JDBC directly

    I have also understood that it is wise to create an extensive test suite to verify the correct behaviour of the database, for example when switching from using the system datasource to using a custom datasource, to find freak errors resulting from the use and non-use of OpenSQL. 😊

    Thanks a lot,

    Thorsten

    Add comment
    10|10000 characters needed characters exceeded