Description Yiping Zhao 2010-06-22 11:18:07 EDT

We build stored function as following in testSetup() of org.eclipse.persistence.testing.tests.jpa.jpql.JUnitJPQLComplexTestSuite, it worked with Oracle database, but failed with MySQL 5.1.10. The stored function generated by EclipseLink is:

CREATE FUNCTION StoredFunction_In (P_IN BIGINT) RETURN BIGINT
BEGIN 
RETURN P_IN * 1000; 
END

The reason it failed on MySQL, because the return syntax for create function in MySQL is RETURNS, not RETURN. So the correct statement on MySQL will be:

CREATE FUNCTION StoredFunction_In (P_IN BIGINT) RETURNS BIGINT
BEGIN 
RETURN P_IN * 1000; 
END

The error message for the failure on MySQL is:

all: CREATE FUNCTION StoredFunction_In (
	P_IN BIGINT)
	 RETURN  BIGINT

BEGIN 
RETURN P_IN * 1000; 
END
Query: DataModifyQuery(sql="CREATE FUNCTION StoredFunction_In (
	P_IN BIGINT)
	 RETURN  BIGINT

BEGIN 
RETURN P_IN * 1000; 
END")" type="org.eclipse.persistence.exceptions.DatabaseException">Local Exception Stack: 
Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.2.0.qualifier): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'RETURN  BIGINT

BEGIN 
RETURN P_IN * 1000; 
END' at line 3
Error Code: 1064
Call: CREATE FUNCTION StoredFunction_In (
	P_IN BIGINT)
	 RETURN  BIGINT

BEGIN 
RETURN P_IN * 1000; 
END