c++ - 过程在 mysql workbench 中有效，但不适用于 C++ 连接器

Question

我有一个在 MySQL Workbench 中执行并且没有给出错误的过程:

DELIMITER //
CREATE PROCEDURE test()
BEGIN
    DECLARE maxwf_fir INT DEFAULT 0;
    SET maxwf_fir = (SELECT MAX(wf_fir) FROM establishment);
    WHILE maxwf_fir > 0 DO
        UPDATE individual AS i LEFT JOIN establishment AS e USING (establishment_id) SET 
        wf_fir = wf_fir-1,
        e.wf_est = e.wf_est-1,
        i.labor = 2,
        i.labor_periods = 0,
        i.establishment_id = 0
        WHERE (e.wf_hir <= e.wf_fir)
        AND (wf_fir > 0);

        UPDATE individual AS i LEFT JOIN establishment AS e USING (establishment_id) SET 
        e.counter1 = e.counter1+1
        WHERE (e.wf_hir <= e.wf_fir)
        AND (wf_fir > 0);

        UPDATE individual AS i LEFT JOIN establishment AS e USING (establishment_id) SET 
        e.counter1 = 0,
        e.counter2 = e.counter2+1
        WHERE (e.wf_hir <= e.wf_fir)
        AND (wf_fir > 0)
        AND (e.counter1 = e.wf_est);

        SET maxwf_fir = maxwf_fir-1;
    END WHILE;
END//

DELIMITER ;
CALL test;

DROP PROCEDURE test;

然而，当我将某些代码与 C++ 连接器一起使用时，我被告知存在语法错误，但代码完全相同(我从工作台复制并粘贴到 Geany 中):

std::stringstream query4;
//query4 << "drop procedure if exists loop;\n";
query4 << "DELIMITER //\n";
query4 << "CREATE PROCEDURE loop()\n";
query4 << "BEGIN\n";
query4 << "\tDECLARE maxwf_fir INT DEFAULT 0;\n";
query4 << "\tSET maxwf_fir = (SELECT MAX(wf_fir) FROM establishment);\n";
query4 << "\tWHILE maxwf_fir > 0 DO\n";
query4 << "\t\tUPDATE individual AS i LEFT JOIN establishment AS e USING (establishment_id) SET\n";
query4 << "\t\te.wf_fir = e.wf_fir-1,\n";
query4 << "\t\te.wf_est = e.wf_est-1,\n";
query4 << "\t\ti.labor = 2,\n";
query4 << "\t\ti.labor_periods = 0,\n";
query4 << "\t\ti.establishment_id = 0\n";
query4 << "\t\tWHERE (e.wf_hir <= e.wf_fir)\n";
query4 << "\t\tAND (e.wf_fir > 0)\n";
query4 << "\t\tAND e.temp < POW("<< Coeffs::gamma67 << ", e.counter2)*" << random << ";\n";

query4 << "\t\tUPDATE individual AS i LEFT JOIN establishment AS e USING (establishment_id) SET\n";
query4 << "\t\te.counter1 = e.counter1+1\n";
query4 << "\t\tWHERE (e.wf_hir <= e.wf_fir)\n";
query4 << "\t\tAND NOT (e.temp < POW("<< Coeffs::gamma67 << ", e.counter2)*" << random << ")\n";
query4 << "\t\tAND (wf_fir > 0);\n";

query4 << "\t\tUPDATE individual AS i LEFT JOIN establishment AS e USING (establishment_id) SET\n";
query4 << "\t\te.counter1 = 0\n";
query4 << "\t\te.counter2 = e.counter2+1\n";
query4 << "\t\tWHERE (e.wf_hir <= e.wf_fir)\n";
query4 << "\t\tAND NOT (e.temp < POW("<< Coeffs::gamma67 << ", e.counter2)*" << random << ")\n";
query4 << "\t\tAND e.counter1 = e.wf_est\n";
query4 << "\t\tAND (wf_fir > 0);\n";

query4 << "\t\tSET maxwf_fir = maxwf_fir-1;\n";
query4 << "\tEND WHILE;\n";
query4 << "END//\n";
query4 << "DELIMITER ;\n";
query4 << "CALL loop;\n";
query4 << "DROP PROCEDURE loop;\n";
stmt->execute(query4.str());

这给了我错误:

terminate called after throwing an instance of 'sql::SQLException'
  what():  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 loop()
BEGIN
    DECLARE maxwf_fir INT DEFAULT 0;
    SE' at line 1
Aborted (core dumped)

谁能看出我在导致此错误的 C++ 代码中做错了什么？提前致谢!

Answer 1

从 SQL 引擎以外的接口(interface)创建存储过程时，delimiter 是可选的。示例 @ SQL FIddle

变化 1:

删除语句

query4 << "DELIMITER //\n";

变化 2:

loop 是一个 Reserved Word .如果您仍想为过程使用相同的名称，请用反引号将其括起来，如

`loop()`

变化 3:

query4 << "END//\n";

收件人:

query4 << "END;\n"; // semi-colon is optional, if it is last statement in the sp

变化4:

删除语句

query4 << "DELIMITER ;\n";

变化5:

query4 << "CALL loop;\n";
query4 << "DROP PROCEDURE loop;\n";

收件人:

query4 << "CALL `loop`();\n";
query4 << "DROP PROCEDURE `loop`;\n";