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

["Daoqi\(Doug\) Yang" <d_q_yang@xxxxxxxxx>]

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