Re: [eclipselink-users] Error when calling Stored Procedure in Derby

[James Sutherland <jamesssss@xxxxxxxxx>]

The issue seems to be Derby does not support named parameters in procedure
calls.

EclipseLink also supports unnamed parameters, but it seems only from the
StoredProcedureCall API, not from the annotations.  Please log a bug for
this, if you don't set the "name" in the StoredProcedureParameter as you
have done, we should be treating it as a unnamed parameter, not using the
queryParameter as the name.

As a workaround you can use the StoredProcedureCall API, or use a native SQL
query.


Daoqi(Doug) Yang wrote:
> 
> Hi, I have the following error when calling a Derby stored procedure:
>  
> Exception [EclipseLink-4002] (Eclipse Persistence Services -
> 1.1.2.v20090612-r4475):
> org.eclipse.persistence.exceptions.DatabaseException 
> Internal Exception: java.sql.SQLSyntaxErrorException: Syntax error:
> Encountered "=" at line 1, column 37. 
> Error Code: -1 
> Call: CALL getCustomerAndOrders(custID = ?, avgPrice = ?) 
> bind => [2202, => avgPrice] 
> Query: ResultSetMappingQuery(name="GET_CUSTOMER_ORDER" )
>  
> The JPA calling code is:
> Query query = em.createNamedQuery("GET_CUSTOMER_ORDERS");
> query.setParameter("custID", customerId);
> List<Customer> customerList = query.getResultList();
>  
> The stored procedure is annotated as:
> @NamedStoredProcedureQuery( 
>     name="GET_CUSTOMER_ORDERS", 
>     resultSetMapping="customerOrderRSmapping", 
>     procedureName="getCustomerAndOrders", 
>     parameters={ 
>       @StoredProcedureParameter(queryParameter="custID", 
>                                 direction=Direction.IN),
>       @StoredProcedureParameter(queryParameter="avgPrice", 
>                                 direction=Direction.OUT)
> })
>  
> @SqlResultSetMapping(
>     name="customerOrderRSmapping", 
>     entities ={@EntityResult(entityClass=Customer.class, 
>                     discriminatorColumn="CUSTOMER_TYPE"),
>                @EntityResult(entityClass=Order.class)
> })
>  
> The Derby stored procedure is defined as:
> CREATE PROCEDURE getCustomerAndOrders(IN cust_id INTEGER, OUT avgPrice
> DOUBLE) PARAMETER STYLE JAVA LANGUAGE JAVA READS SQL DATA DYNAMIC RESULT
> SETS 2 EXTERNAL NAME 'jpatest.util.CustomerSP.fetchCustomerOrders';
>  
> The Java Stored Procedure method has the signature:
> public static void fetchCustomerOrders(int custmerId, double[] avgPrice,
> ResultSet[] customerRS, ResultSet[] orderRS) throws SQLException;
>  
> It appears to me that EclipseLink does not generate the stored procedure
> call that is acceptable to Derby. To be more specific, the generated
> stored procedure call: 
>  
> getCustomerAndOrders(custID = ?, avgPrice = ?) 
>  
> should not contain the “=” sign, and it should be 
>  
> getCustomerAndOrders(?, ?)
>  
> Any ideas to fix it? Thanks.
>  
> By the way, this stored procedure can be called successfully from JDBC.
> Doug
> 
> 


-----
http://wiki.eclipse.org/User:James.sutherland.oracle.com James Sutherland 
http://www.eclipse.org/eclipselink/
 EclipseLink ,  http://www.oracle.com/technology/products/ias/toplink/
TopLink 
Wiki:  http://wiki.eclipse.org/EclipseLink EclipseLink , 
http://wiki.oracle.com/page/TopLink TopLink 
Forums:  http://forums.oracle.com/forums/forum.jspa?forumID=48 TopLink , 
http://www.nabble.com/EclipseLink-f26430.html EclipseLink 
Book:  http://en.wikibooks.org/wiki/Java_Persistence Java Persistence 
-- 
View this message in context: http://old.nabble.com/Error-when-calling-Stored-Procedure-in-Derby-tp26274744p26286007.html
Sent from the EclipseLink - Users mailing list archive at Nabble.com.