Win a copy of Design for the Mind this week in the Design forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

call a stored procedure using eclipselink Problem...

 
kin lau
Greenhorn
Posts: 11
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 553
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
kin lau
Greenhorn
Posts: 11
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 553
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 11
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 7
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 553
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 7
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 553
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 7
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 553
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic