I am trying to create a function that calculates the distance between 2 points (longitude and latitude wise) using an SQL script while running the application by specifying in the persistence.xml
<property name="javax.persistence.schema-generation.create-script-source"
value="calculate_distance_function.sql" />
But I am having a CommandAcceptanceException and cannot figure out why. I even tried to use
<property name="javax.persistence.sql-load-script-source"
value="calculate_distance_function.sql" />
but still does not work and it gives the following error.
00:06:21,512 INFO [stdout] (ServerService Thread Pool -- 72) Hibernate: CREATE FUNCTION CalcDistance( Lat1 DOUBLE, Long1 DOUBLE, Lat2 DOUBLE, Long2 DOUBLE ) RETURNS double
00:06:21,513 WARN [org.hibernate.tool.schema.internal.ExceptionHandlerLoggedImpl] (ServerService Thread Pool -- 72) GenerationTarget encountered exception accepting command : Error executing DDL "CREATE FUNCTION CalcDistance( Lat1 DOUBLE, Long1 DOUBLE, Lat2 DOUBLE, Long2 DOUBLE ) RETURNS double" via JDBC Statement: org.hibernate.tool.schema.spi.CommandAcceptanceException: Error executing DDL "CREATE FUNCTION CalcDistance( Lat1 DOUBLE, Long1 DOUBLE, Lat2 DOUBLE, Long2 DOUBLE ) RETURNS double" via JDBC Statement at org.hibernate.tool.schema.internal.exec.GenerationTargetToDatabase.accept(GenerationTargetToDatabase.java:67) at org.hibernate.tool.schema.internal.SchemaCreatorImpl.applySqlString(SchemaCreatorImpl.java:440) at org.hibernate.tool.schema.internal.SchemaCreatorImpl.createFromScript(SchemaCreatorImpl.java:192) at org.hibernate.tool.schema.internal.SchemaCreatorImpl.performCreation(SchemaCreatorImpl.java:162) at org.hibernate.tool.schema.internal.SchemaCreatorImpl.doCreation(SchemaCreatorImpl.java:135) at org.hibernate.tool.schema.internal.SchemaCreatorImpl.doCreation(SchemaCreatorImpl.java:121) at org.hibernate.tool.schema.spi.SchemaManagementToolCoordinator.performDatabaseAction(SchemaManagementToolCoordinator.java:129) at org.hibernate.tool.schema.spi.SchemaManagementToolCoordinator.process(SchemaManagementToolCoordinator.java:72) at org.hibernate.internal.SessionFactoryImpl.(SessionFactoryImpl.java:310) at org.hibernate.boot.internal.SessionFactoryBuilderImpl.build(SessionFactoryBuilderImpl.java:467) at org.hibernate.jpa.boot.internal.EntityManagerFactoryBuilderImpl.build(EntityManagerFactoryBuilderImpl.java:939) at org.jboss.as.jpa.hibernate5.TwoPhaseBootstrapImpl.build(TwoPhaseBootstrapImpl.java:44) at org.jboss.as.jpa.service.PersistenceUnitServiceImpl$1$1.run(PersistenceUnitServiceImpl.java:167) at org.jboss.as.jpa.service.PersistenceUnitServiceImpl$1$1.run(PersistenceUnitServiceImpl.java:125) at org.wildfly.security.manager.WildFlySecurityManager.doChecked(WildFlySecurityManager.java:650) at org.jboss.as.jpa.service.PersistenceUnitServiceImpl$1.run(PersistenceUnitServiceImpl.java:209) at org.jboss.threads.ContextClassLoaderSavingRunnable.run(ContextClassLoaderSavingRunnable.java:35) at org.jboss.threads.EnhancedQueueExecutor.safeRun(EnhancedQueueExecutor.java:1985) at org.jboss.threads.EnhancedQueueExecutor$ThreadBody.doRunTask(EnhancedQueueExecutor.java:1487) at org.jboss.threads.EnhancedQueueExecutor$ThreadBody.run(EnhancedQueueExecutor.java:1378) at java.lang.Thread.run(Thread.java:748) at org.jboss.threads.JBossThread.run(JBossThread.java:485) Caused by: java.sql.SQLSyntaxErrorException: 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 '' at line 1 at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:118) at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:95) at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122) at com.mysql.cj.jdbc.StatementImpl.executeInternal(StatementImpl.java:790) at com.mysql.cj.jdbc.StatementImpl.execute(StatementImpl.java:675) at org.jboss.jca.adapters.jdbc.WrappedStatement.execute(WrappedStatement.java:198) at org.hibernate.tool.schema.internal.exec.GenerationTargetToDatabase.accept(GenerationTargetToDatabase.java:54) ... 21 more
This is my persistence.xml
<?xml version="1.0" encoding="UTF-8"?>
<persistence xmlns="http://xmlns.jcp.org/xml/ns/persistence"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/persistence
http://xmlns.jcp.org/xml/ns/persistence/persistence_2_1.xsd"
version="2.1">
<persistence-unit name="Persistence"
transaction-type="JTA">
<provider>org.hibernate.jpa.HibernatePersistenceProvider</provider>
<jta-data-source>java:jboss/datasources/tontapatt_db</jta-data-source>
<class>fr.eql.ai109.tontapatt.entity.ZipCodeCity</class>
<properties>
<property
name="javax.persistence.schema-generation.database.action"
value="create" />
<property name="hibernate.show_sql" value="true" />
<property name="hibernate.dialect"
value="org.hibernate.dialect.MySQL8Dialect" />
<property name="javax.persistence.schema-generation.create-script-source"
value="calculate_distance_function.sql" />
<!-- <property name="javax.persistence.sql-load-script-source"
value="calculate_distance_function.sql" /> -->
</properties>
</persistence-unit>
</persistence>
Changing the dialect of MySQL did not help either. If I try to open the script in MySQL workbench and run it works fine and I can see the function added. This is my SQL function script
CREATE DEFINER=`root`@`localhost` FUNCTION `CalcDistance`( Lat1 DOUBLE, Long1 DOUBLE, Lat2 DOUBLE, Long2 DOUBLE ) RETURNS double
BEGIN
DECLARE r INT;
DECLARE phi1 DOUBLE;
DECLARE phi2 DOUBLE;
DECLARE dphi DOUBLE;
DECLARE dlam DOUBLE;
DECLARE a DOUBLE;
DECLARE c DOUBLE;
DECLARE d DOUBLE;
SET r = 6371000;
SET phi1 = Lat1 * PI() / 180;
SET phi2 = Lat2 * PI() / 180;
SET dphi = ( Lat2 - Lat1 ) * PI() / 180;
SET dlam = ( Long2 - Long1 ) * PI() / 180;
SET a = SIN( dphi / 2 ) * SIN( dphi / 2 ) + COS( phi1 ) * COS( phi2 ) * SIN( dlam / 2 ) * SIN( dlam / 2 );
SET c = 2 * ATAN2( SQRT( a ), SQRT( 1 - a ) );
SET d = r * c / 1000;
RETURN d;
END
I even tried to add a delimiter before and after it.
Can someone help me out with this one?
Can you try single quotes instead of backticks in first line.
Change
DEFINER=`root`@`localhost` FUNCTION `CalcDistance`
To
DEFINER='root'@'localhost' FUNCTION 'CalcDistance' (Note single quotes)
Here is a sample from MySQL official documentation which works perfectly fine.
CREATE DEFINER = 'admin'@'localhost' PROCEDURE account_count()
BEGIN
SELECT 'Number of accounts:', COUNT(*) FROM mysql.user;
END;