Cannot create stored procedure from .sql file - JD

问题:

I am trying to execute a .sql file during the installation of my web project by using jdbc. 2 options suggested from google: either parsing the script manually (by splitting the ";" character) or using ant. I prefer the simple way so ant is a good choice. This is the code i use to set up the database:

public void executeSql(String sqlFilePath) {
        final class SqlExecuter extends SQLExec {
            public SqlExecuter() {
                Project project = new Project();
                project.init();
                setProject(project);
                setTaskType("sql");
                setTaskName("SQL Init");
            }
        }

        SqlExecuter executer = new SqlExecuter();
        executer.setSrc(new File(sqlFilePath));
        executer.setDriver("com.mysql.jdbc.Driver") ;
        executer.setPassword("123456");
        executer.setUserid("root");
        executer.setUrl("jdbc:mysql://localhost:3306/abc");
        executer.execute();
    }

The code working fine until it meets the part of creating the procedure

DELIMITER //
CREATE PROCEDURE LOG (IN period INT)
BEGIN
    INSERT INTO log_archive
        SELECT * FROM ablog
            WHERE log_date < DATE_SUB(CURRENT_DATE(), INTERVAL period DAY);
    DELETE FROM ablog
        WHERE log_date < DATE_SUB(CURRENT_DATE(), INTERVAL period DAY);
END//
DELIMITER ;

and give this error:

Caused by: 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 'DELIMITER // CREATE PROCEDURE LOG(IN period INT) BEGIN INSERT INTO log_archi' at line 1

if i remove the stored procedure part, then it will run well. The sql file is also executed succesfully from the mysql command prompt. Do you have any suggestion to solve this problem ?

回答1:

Consider using a tool like liquibase. It supports mysql, there are some minor issues though.

回答2:

Try this way http://forums.mysql.com/read.php?46,271411,271411

CREATE PROCEDURE LOG (period INT)
BEGIN
    INSERT INTO log_archive
        SELECT * FROM ablog
            WHERE log_date < CURRENT_DATE- INTERVAL period DAY;
    DELETE FROM ablog
        WHERE log_date < CURRENT_DATE - INTERVAL period DAY;
END

---

you can put delimiters if need be to run it in the commandline mysql>\. yoursqlfile.sql It has just worked from commandline with delimiter // and at END//.

---

commandline :

delimiter //
CREATE PROCEDURE LOG (period INT)
BEGIN
    INSERT INTO log_archive
        SELECT * FROM ablog
            WHERE log_date < CURRENT_DATE- INTERVAL period DAY;
    DELETE FROM ablog
        WHERE log_date < CURRENT_DATE - INTERVAL period DAY;
END//

---

hope it helps abit

回答3:

If you use only one delimiter (eg. \\ or I used $$) and don't change mid script (no DELIMITER XX) then you can run reasonably complex scripts.

dbConnection = DriverManager.getConnection(connectionString, user, password);
statement = dbConnection.createStatement();
for (String sqlpart : sqlFileContents.split("\\$\\$"))
{
    if (!sqlpart.trim().isEmpty())
        statement.addBatch(sqlpart);
}
statement.executeBatch();

For example

DROP PROCEDURE IF EXISTS Log $$
CREATE PROCEDURE Log (period INT)
BEGIN
    INSERT INTO log_archive
        SELECT * FROM ablog
            WHERE log_date < CURRENT_DATE- INTERVAL period DAY;
    DELETE FROM ablog
        WHERE log_date < CURRENT_DATE - INTERVAL period DAY;
END $$