无法从 .sql 文件创建存储过程 - JDBC [英] Cannot create stored procedure from .sql file - JDBC

问题描述

我正在尝试使用 jdbc 在我的 Web 项目安装期间执行 .sql 文件.谷歌建议的 2 个选项:手动解析脚本(通过拆分;"字符)或使用 ant.我更喜欢简单的方式，所以蚂蚁是一个不错的选择.这是我用来设置数据库的代码:

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 ;

并给出这个错误:

Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: 你的 SQL 语法有错误；检查与您的 MySQL 服务器版本相对应的手册，以在第 1 行的DELIMITER//CREATE PROCEDURE LOG(IN period INT) BEGIN INSERT INTO log_archi"附近使用正确的语法

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

如果我删除存储过程部分，那么它将运行良好.sql 文件也从 mysql 命令提示符成功执行.你有什么建议来解决这个问题吗?

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 ?

推荐答案

考虑使用像 liquibase.支持mysql，不过有一些小问题.

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

这篇关于无法从 .sql 文件创建存储过程 - JDBC的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！