从文件创建存储过程 [英] Create Stored Procedure from File

问题描述

我试图在Java中执行.SQL个文件的整个目录.

I am trying to execute a whole directory of .SQL files in Java.

我正在努力执行存储过程.到目前为止(最有帮助)，我发现了这可悲的是，一个死链接.我也下载了liquibase，但是我不知道该如何使用它.

I'm struggling to execute a stored procedure. I have found this so far (the most helpful) including a dead link, sadly. I have downloaded liquibase also, but I cannot figure out how I should use it for this purpose.

在当前代码中，我将包括过程在内的文件拆分为不同的语句:

In my current code, I split the files including procedures into different statements:

(语句在Vector [String]中拆分并在for循环中执行)

示例:

//File f;
//Statement st;
Vector<String> vProcedure = getProcedureStatements(f, Charset.defaultCharset(), "//");
for (Iterator<String> itr = vProcedure.iterator(); itr.hasNext();)
    st.execute(itr.next());
System.out.println(f.getName() + " - done executing.");

向量包含四个元素(请参见SQL代码#SPLIT x).

DROP PROCEDURE IF EXISTS `Add_Position`; #SPLIT 1
DELIMITER // #SPLIT 2
CREATE PROCEDURE `Add_Position`
(
    IN iO_ID INT,
    IN iCID INT,
    IN iWID INT,
    IN iA INT
)
BEGIN
    #statements;
END
// #SPLIT  3
DELIMITER ; #SPLIT 4

尝试执行#SPLIT 2时

结果

Result when trying to execute #SPLIT 2:

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 //' at line 1

问:有人可以告诉我是否可以使用一个外部库，或者liquibase如何工作?我无法使用JDBC方式.

推荐答案

语句DELIMITER \\实际上不是SQL，它是mysql命令行工具(可能还有其GUI工具)解释的命令，但是如果将其直接传递给执行引擎，则会出现此语法错误.

The statement DELIMITER \\ is not actually SQL - it is a command that is interpreted by the mysql command-line tool (and possibly also their GUI tool), but if you pass it straight to the execution engine, you will get this syntax error.

不幸的是，Liquibase使用;作为默认的语句分隔符，因此使用SQL文件和Liquibase很难使这种事情正常工作.我提出了拉取请求，以允许从Liquibase命令行设置定界符-请参见 https://github.com/liquibase/liquibase/pull/361 .

Unfortunately, Liquibase uses ; as the default statement separator, so it is difficult to get this sort of thing to work using SQL files and Liquibase. I have put in a pull request to allow setting the delimiter from the Liquibase command line - see https://github.com/liquibase/liquibase/pull/361.

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