aspose file tools*
The moose likes Object Relational Mapping and the fly likes call a stored procedure using eclipselink Problem... Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Soft Skills this week in the Jobs Discussion forum!
JavaRanch » Java Forums » Databases » Object Relational Mapping
Bookmark "call a stored procedure using eclipselink Problem..." Watch "call a stored procedure using eclipselink Problem..." New topic
Author

call a stored procedure using eclipselink Problem...

kin lau
Greenhorn

Joined: May 25, 2006
Posts: 11
Hi,

I try to write a simple java program to call a stored procedure using eclipselink. However, I hit the following problem...:

My codes are as follow:













Table : customer
fields: ID int , firstname varchar(255), lastname varchar(255)


When i execute the java program : BankClient1.class, it throws exception like this:

Exception in thread "main" Local Exception Stack:
Exception [EclipseLink-6044] (Eclipse Persistence Services - 2.0.0.v20091127-r5931): org.eclipse.persistence.exceptions.QueryException
Exception Description: The primary key read from the row [DatabaseRecord(
id => 3
firstname = KIN
lastname = CAA )] during the execution of the query was detected to be null. Primary keys must not contain null.
Query: ReadAllQuery(name="cus.GenFn" referenceClass=Customer1 )
at org.eclipse.persistence.exceptions.QueryException.nullPrimaryKeyInBuildingObject(QueryException.java:863)
at org.eclipse.persistence.internal.descriptors.ObjectBuilder.buildObject(ObjectBuilder.java:468)
at org.eclipse.persistence.internal.descriptors.ObjectBuilder.buildObject(ObjectBuilder.java:441)
at org.eclipse.persistence.queries.ObjectLevelReadQuery.buildObject(ObjectLevelReadQuery.java:635)
at org.eclipse.persistence.queries.ReadAllQuery.registerResultInUnitOfWork(ReadAllQuery.java:838)
at org.eclipse.persistence.queries.ReadAllQuery.executeObjectLevelReadQuery(ReadAllQuery.java:464)
at org.eclipse.persistence.queries.ObjectLevelReadQuery.executeDatabaseQuery(ObjectLevelReadQuery.java:997)
at org.eclipse.persistence.queries.DatabaseQuery.execute(DatabaseQuery.java:675)


Anybody can help ?
James Sutherland
Ranch Hand

Joined: Oct 01, 2007
Posts: 553
The issue is the case of the parameter names.

Your attributes have not set the @Column so they will default to the attribute name as upper case, ID, FIRSTNAME, LASTNAME. But you have configured your stored procedure to use lower case.

Change the query def to,




You could also use an output cursor on Oracle to return a result set.

TopLink : EclipseLink : Book:Java Persistence : Blog:Java Persistence Performance
kin lau
Greenhorn

Joined: May 25, 2006
Posts: 11
James Sutherland wrote:The issue is the case of the parameter names.

Change the query def to,



I have changed and run it again...however, it still have this exception...

With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 - Production
Driver: Oracle JDBC driver Version: 10.2.0.4.0
[EL Info]: 2010-01-26 14:30:30.586--ServerSession(26194423)--Thread(Thread[main,5,main])--file:/D:/kin/workspace/EclipseLinkTest/build/classes/_BankService login successful
[EL Fine]: 2010-01-26 14:34:16.052--ClientSession(25068634)--Connection(4779445)--Thread(Thread[main,5,main])--BEGIN NA_CUSTOMER(id=>?, firstname=>?, lastname=>?); END;
bind => [=> ID, => FIRSTNAME, => LASTNAME]
[EL Fine]: 2010-01-26 14:34:16.161--ClientSession(25068634)--Thread(Thread[main,5,main])--SELECT 1 FROM DUAL
[EL Warning]: 2010-01-26 14:34:16.166--UnitOfWork(4889213)--Thread(Thread[main,5,main])--Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.0.0.v20091127-r5931): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: java.sql.SQLException: ORA-00900: invalid SQL statement

Error Code: 900
Call: BEGIN NA_CUSTOMER(id=>?, firstname=>?, lastname=>?); END;
bind => [=> ID, => FIRSTNAME, => LASTNAME]
Query: ReadAllQuery(name="cus.GenFn" referenceClass=Customer1 )
Exception in thread "main" Local Exception Stack:
Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.0.0.v20091127-r5931): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: java.sql.SQLException: ORA-00900: invalid SQL statement

Error Code: 900
Call: BEGIN NA_CUSTOMER(id=>?, firstname=>?, lastname=>?); END;
bind => [=> ID, => FIRSTNAME, => LASTNAME]

[Query: ReadAllQuery(name="cus.GenFn" referenceClass=Customer1 )
at org.eclipse.persistence.exceptions.DatabaseException.sqlException(DatabaseException.java:324)
at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.buildSortedFields(DatabaseAccessor.java:283)
at org.eclipse.persistence.internal.databaseaccess.DatabaseCall.matchFieldOrder(DatabaseCall.java:526)
at org.eclipse.persistence.internal.databaseaccess.DatabasePlatform.executeStoredProcedure(DatabasePlatform.java:1951)
at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.basicExecuteCall(DatabaseAccessor.java:596)
at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeCall(DatabaseAccessor.java:529)
at org.eclipse.persistence.internal.sessions.AbstractSession.executeCall(AbstractSession.java:914)
at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeCall(DatasourceCallQueryMechanism.java:205)
at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeCall(DatasourceCallQueryMechanism.java:191)
at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeSelectCall(DatasourceCallQueryMechanism.java:262)
at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.selectAllRows(DatasourceCallQueryMechanism.java:618)
at org.eclipse.persistence.queries.ReadAllQuery.executeObjectLevelReadQuery(ReadAllQuery.java:455)
at org.eclipse.persistence.queries.ObjectLevelReadQuery.executeDatabaseQuery(ObjectLevelReadQuery.java:997)
at org.eclipse.persistence.queries.DatabaseQuery.execute(DatabaseQuery.java:675)
at org.eclipse.persistence.queries.ObjectLevelReadQuery.execute(ObjectLevelReadQuery.java:958)
at org.eclipse.persistence.queries.ReadAllQuery.execute(ReadAllQuery.java:432)
at org.eclipse.persistence.queries.ObjectLevelReadQuery.executeInUnitOfWork(ObjectLevelReadQuery.java:1021)
at org.eclipse.persistence.internal.sessions.UnitOfWorkImpl.internalExecuteQuery(UnitOfWorkImpl.java:2863)
at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1225)
at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1207)
at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1181)
at org.eclipse.persistence.internal.jpa.EJBQueryImpl.executeReadQuery(EJBQueryImpl.java:453)
at org.eclipse.persistence.internal.jpa.EJBQueryImpl.getSingleResult(EJBQueryImpl.java:702)
James Sutherland
Ranch Hand

Joined: Oct 01, 2007
Posts: 553
This error indicates you are trying to build a ResultSet from the query, but your stored procedure does not return a ResultSet.

Seems like you removed the option,

returnsResultSet=false,

as the execute was working before, you should not remove this.
kin lau
Greenhorn

Joined: May 25, 2006
Posts: 11
Thanks James Sutherland ..... I have find a way to solve the problem...

here is my code:
Stored Proc


Java Main


Java Entity


Result:

[EL Config]: 2010-01-27 10:42:04.051--ServerSession(11197591)--Connection(23293518)--Thread(Thread[main,5,main])--Connected: Database: Oracle Version: Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 - Production
Driver: Oracle JDBC driver Version: 10.2.0.4.0
[EL Info]: 2010-01-27 10:42:04.276--ServerSession(11197591)--Thread(Thread[main,5,main])--file:/D:/kin/workspace/EclipseLinkTest/build/classes/-sas login successful
[EL Fine]: 2010-01-27 10:42:04.302--ServerSession(11197591)--Connection(7907968)--Thread(Thread[main,5,main])--SELECT ID, LASTNAME, FIRSTNAME FROM CUSTOMER WHERE (ID = ?)
bind => [3]
[EL Fine]: 2010-01-27 10:42:04.408--ClientSession(21279119)--Connection(25865024)--Thread(Thread[main,5,main])--BEGIN NA_CUSTOMER(IPIID=>?, OPIID=>?, OPSFIRSTNAME=>?, OPSLASTNAME=>?); END;
bind => [4, => ID, => FIRSTNAME, => LASTNAME]
Jan 27, 2010 10:42:04 AM hk.client.CustomerTest main
INFO: Customer[3]: name = KIN CAA
Jan 27, 2010 10:42:04 AM hk.client.CustomerTest main
INFO: Result from SP: Customer[4: name = KIN CAA
Jan 27, 2010 10:42:04 AM hk.client.CustomerTest main
INFO: Result from SP, approach 2: Customer[6]: name = TANNA TCAR
Jan 27, 2010 10:42:04 AM hk.client.CustomerTest tearDown
INFO: closing EntityManagerFactory
[EL Config]: 2010-01-27 10:42:04.427--ServerSession(11197591)--Connection(32308743)--Thread(Thread[main,5,main])--disconnect
[EL Info]: 2010-01-27 10:42:04.43--ServerSession(11197591)--Thread(Thread[main,5,main])--file:/D:/kin/workspace/EclipseLinkTest/build/classes/-sas logout successful

Juan Ramirez
Greenhorn

Joined: Feb 01, 2010
Posts: 7
Hey guys, I need your help!

I just found your post, it's actually one of the few, if not the only one on the web showing how to do stored procedures with JPA and annotations. I'm using Netbeans 6.8 and MySQL. I have written a simple stored procedure that only inserts values like this:



This one is working perfectly fine. I also have my entity bean and some JPA controllers created by netbeans. I created under the bean the stored procedure annotation:



and the calling code, where it should do all the magic is:



This should be inserting into the table, buy it doesn't do anything. It compiles and runs without errors, but the database is not being populated. The procedure works fine by itself... do you know what might be happening???!
James Sutherland
Ranch Hand

Joined: Oct 01, 2007
Posts: 553
You did not actually execute the Query. You need to call q.executeUpdate();

It is also possible in EclipseLink to hook up an insert stored procedure to handle the insert of an Entity, if desired. (you can use a DescriptorCustomizer for this).


Juan Ramirez
Greenhorn

Joined: Feb 01, 2010
Posts: 7
Hi James, thank you for your help.

I did that yesterday and put the q.executeUpdate(); as follows:




and I got this error:



So I really don't know how to call it. I'm actually checking that the annotations are OK because I can print their binded values...

Thanks!
James Sutherland
Ranch Hand

Joined: Oct 01, 2007
Posts: 553
It seems that your procedure name may not match the procedure you defined.

You defined it as `insert`, perhaps try including the quotes in your procedureName annotation, or using a better procedure name that is not a reserved word.

You could also try calling the procedure directly through JDBC or SQL to ensure you have the SQL syntax correct.
Juan Ramirez
Greenhorn

Joined: Feb 01, 2010
Posts: 7
Thank you James, you got it. It wasn't a wise thing to name the procedure "insert". I changed the name and immediatly worked. Thanks for your help.
One last question, in case I had a more elaborate stored procedure that does several inserts in different tables, where do you think the annotations for this stored procedure should go? In which class?

Juan
James Sutherland
Ranch Hand

Joined: Oct 01, 2007
Posts: 553
Named queries can be put on any annotated class, it doesn't really matter for any named query.

You can also defined named queries in the orm.xml.
 
 
subject: call a stored procedure using eclipselink Problem...