0

I've recently switched from using a pre-populated DB to allowing Hibernate to create my tables. When trying to create stored procedures in my data.sql, I get the following error:

Exception in thread "task-2" org.springframework.jdbc.datasource.init.ScriptStatementFailedException: Failed to execute SQL script statement #7 of URL [file:/C:/Users/samuel/IdeaProjects/Capripol/target/classes/data.sql]: CREATE PROCEDURE CalculateUserRating( IN baseRating NUMERIC(5,2), IN userID int, IN focusID int, OUT userRating NUMERIC(5,2)) BEGIN SELECT IFNULL(baseRating + SUM(s.sightingValue), baseRating) INTO userRating FROM Sighting s WHERE s.sighteeUserID = userID AND s.sightedFocusID = focusID AND s.sighterUserID != userID; nested exception is 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

I've tried adding ?allowMultiQueries=true to my spring.datasource.url however it made no difference. Similarly I tried this answer which also made no difference.

data.sql:

DROP PROCEDURE IF EXISTS CalculateUserRating;
CREATE PROCEDURE CalculateUserRating(
    IN baseRating NUMERIC(5,2),
    IN userID int,
    IN focusID int,
    OUT userRating NUMERIC(5,2))
BEGIN
    SELECT IFNULL(baseRating + SUM(s.sightingValue), baseRating)
           INTO userRating
    FROM Sighting s
    WHERE
            s.sighteeUserID = userID
      AND
            s.sightedFocusID = focusID
      AND
            s.sighterUserID != userID;

END;

DROP PROCEDURE IF EXISTS CalculateSightingValue;
CREATE PROCEDURE CalculateSightingValue(
    IN baseRating int,
    IN userID int,
    IN focusID int,
    OUT sightingValue NUMERIC(5,2))
BEGIN
    CALL CalculateUserRating(baseRating, userID, focusID, @userRating);
    SELECT (@userRating/100)
           INTO sightingValue;
END;

#If first sighting it will return 1 as user rating.

DROP PROCEDURE IF EXISTS GetSightingSummary;
CREATE PROCEDURE GetSightingSummary(
    IN userID int,
    IN focusID int,
    OUT totalSightings int,
    OUT selfSightings int,
    OUT peerSightings int,
    OUT otherSightings int)
BEGIN
    SELECT count(*) INTO totalSightings
    FROM Sighting s
    WHERE
            s.sighteeUserID = userID;
    SELECT count(*) INTO selfSightings
    FROM Sighting s
    WHERE
            s.sighteeUserID = s.sighterUserID
      AND
            s.sighteeUserID = userID;
    SELECT count(*) INTO peerSightings
    FROM Sighting s
    WHERE
            s.sighteeUserID = userID
      AND
            s.sightedFocusID = focusID
      AND
            s.sighterUserID != userID;

END;

EDIT:

I tried added DELIMITER // to the start and end of the script, as well as trying to put a delimiter between each individual procedure, however no difference was made. According to this the delimiter shouldn't be necessary however.

DELIMITER //
DROP PROCEDURE IF EXISTS CalculateUserRating;
DROP PROCEDURE IF EXISTS CalculateSightingValue;
DROP PROCEDURE IF EXISTS GetSightingSummary;
CREATE PROCEDURE CalculateUserRating(
    IN baseRating NUMERIC(5,2),
    IN userID int,
    IN focusID int,
    OUT userRating NUMERIC(5,2))
BEGIN
    SELECT IFNULL(baseRating + SUM(s.sightingValue), baseRating)
           INTO userRating
    FROM Sighting s
    WHERE
            s.sighteeUserID = userID
      AND
            s.sightedFocusID = focusID
      AND
            s.sighterUserID != userID;

END;

CREATE PROCEDURE CalculateSightingValue(
    IN baseRating int,
    IN userID int,
    IN focusID int,
    OUT sightingValue NUMERIC(5,2))
BEGIN
    CALL CalculateUserRating(baseRating, userID, focusID, @userRating);
    SELECT (@userRating/100)
           INTO sightingValue;
END;

#If first sighting it will return 1 as user rating.


CREATE PROCEDURE GetSightingSummary(
    IN userID int,
    IN focusID int,
    OUT totalSightings int,
    OUT selfSightings int,
    OUT peerSightings int,
    OUT otherSightings int)
BEGIN
    SELECT count(*) INTO totalSightings
    FROM Sighting s
    WHERE
            s.sighteeUserID = userID;
    SELECT count(*) INTO selfSightings
    FROM Sighting s
    WHERE
            s.sighteeUserID = s.sighterUserID
      AND
            s.sighteeUserID = userID;
    SELECT count(*) INTO peerSightings
    FROM Sighting s
    WHERE
            s.sighteeUserID = userID
      AND
            s.sightedFocusID = focusID
      AND
            s.sighterUserID != userID;

END//
DELIMITER ;

Full error stacktrace:

Exception in thread "task-2" org.springframework.jdbc.datasource.init.ScriptStatementFailedException: Failed to execute SQL script statement #10 of URL [file:/C:/Users/samuel/IdeaProjects/Capripol/target/classes/data.sql]: CREATE PROCEDURE CalculateUserRating( IN baseRating NUMERIC(5,2), IN userID int, IN focusID int, OUT userRating NUMERIC(5,2)) BEGIN SELECT IFNULL(baseRating + SUM(s.sightingValue), baseRating) INTO userRating FROM Sighting s WHERE s.sighteeUserID = userID AND s.sightedFocusID = focusID AND s.sighterUserID != userID; nested exception is 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 org.springframework.jdbc.datasource.init.ScriptUtils.executeSqlScript(ScriptUtils.java:622)
    at org.springframework.jdbc.datasource.init.ResourceDatabasePopulator.populate(ResourceDatabasePopulator.java:254)
    at org.springframework.jdbc.datasource.init.DatabasePopulatorUtils.execute(DatabasePopulatorUtils.java:49)
    at org.springframework.boot.autoconfigure.jdbc.DataSourceInitializer.runScripts(DataSourceInitializer.java:202)
    at org.springframework.boot.autoconfigure.jdbc.DataSourceInitializer.initSchema(DataSourceInitializer.java:119)
    at org.springframework.boot.autoconfigure.jdbc.DataSourceInitializerInvoker.onApplicationEvent(DataSourceInitializerInvoker.java:91)
    at org.springframework.boot.autoconfigure.jdbc.DataSourceInitializerInvoker.onApplicationEvent(DataSourceInitializerInvoker.java:38)
    at org.springframework.context.event.SimpleApplicationEventMulticaster.doInvokeListener(SimpleApplicationEventMulticaster.java:172)
    at org.springframework.context.event.SimpleApplicationEventMulticaster.invokeListener(SimpleApplicationEventMulticaster.java:165)
    at org.springframework.context.event.SimpleApplicationEventMulticaster.multicastEvent(SimpleApplicationEventMulticaster.java:139)
    at org.springframework.context.support.AbstractApplicationContext.publishEvent(AbstractApplicationContext.java:404)
    at org.springframework.context.support.AbstractApplicationContext.publishEvent(AbstractApplicationContext.java:361)
    at org.springframework.boot.autoconfigure.orm.jpa.DataSourceInitializedPublisher.publishEventIfRequired(DataSourceInitializedPublisher.java:99)
    at org.springframework.boot.autoconfigure.orm.jpa.DataSourceInitializedPublisher.access$100(DataSourceInitializedPublisher.java:50)
    at org.springframework.boot.autoconfigure.orm.jpa.DataSourceInitializedPublisher$DataSourceSchemaCreatedPublisher.lambda$postProcessEntityManagerFactory$0(DataSourceInitializedPublisher.java:200)
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
    at java.lang.Thread.run(Thread.java:748)
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:120)
    at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97)
    at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
    at com.mysql.cj.jdbc.StatementImpl.executeInternal(StatementImpl.java:764)
    at com.mysql.cj.jdbc.StatementImpl.execute(StatementImpl.java:648)
    at com.zaxxer.hikari.pool.ProxyStatement.execute(ProxyStatement.java:95)
    at com.zaxxer.hikari.pool.HikariProxyStatement.execute(HikariProxyStatement.java)
    at org.springframework.jdbc.datasource.init.ScriptUtils.executeSqlScript(ScriptUtils.java:601)
    ... 17 more
Improve this question

1 Answer 1

Reset to default
0

MySQL needs DELIMITER before and anfter the coammnd because it has to determine Where a stored procedure or function starts and ends.

I also rearranged a bit , because the command din't want to run otherwise

USE testdb;
DELIMITER //
DROP PROCEDURE IF EXISTS CalculateUserRating;
DROP PROCEDURE IF EXISTS CalculateSightingValue;
DROP PROCEDURE IF EXISTS GetSightingSummary;
CREATE PROCEDURE CalculateUserRating(
    IN baseRating NUMERIC(5,2),
    IN userID int,
    IN focusID int,
    OUT userRating NUMERIC(5,2))
BEGIN
    SELECT IFNULL(baseRating + SUM(s.sightingValue), baseRating)
           INTO userRating
    FROM Sighting s
    WHERE
            s.sighteeUserID = userID
      AND
            s.sightedFocusID = focusID
      AND
            s.sighterUserID != userID;

END;

CREATE PROCEDURE CalculateSightingValue(
    IN baseRating int,
    IN userID int,
    IN focusID int,
    OUT sightingValue NUMERIC(5,2))
BEGIN
    CALL CalculateUserRating(baseRating, userID, focusID, @userRating);
    SELECT (@userRating/100)
           INTO sightingValue;
END;

#If first sighting it will return 1 as user rating.


CREATE PROCEDURE GetSightingSummary(
    IN userID int,
    IN focusID int,
    OUT totalSightings int,
    OUT selfSightings int,
    OUT peerSightings int,
    OUT otherSightings int)
BEGIN
    SELECT count(*) INTO totalSightings
    FROM Sighting s
    WHERE
            s.sighteeUserID = userID;
    SELECT count(*) INTO selfSightings
    FROM Sighting s
    WHERE
            s.sighteeUserID = s.sighterUserID
      AND
            s.sighteeUserID = userID;
    SELECT count(*) INTO peerSightings
    FROM Sighting s
    WHERE
            s.sighteeUserID = userID
      AND
            s.sightedFocusID = focusID
      AND
            s.sighterUserID != userID;

END//
DELIMITER ;
Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

Unfortunately this doesn't seem to have made a difference, I still get the same error
really the same? can you please add it to you question
I have added it accordingly

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.