mysql - 捕获事务中的错误并回滚-在此位置声明无效(Capturing errors in a transaction and rollback - declare not valid at this position)

Question

Trying to craft a transaction from the code to send to MySQL from my code.

(尝试从代码中编写事务以从我的代码发送到MySQL。)

I am not using an ORM just executing the raw query.

(我不使用ORM只是执行原始查询。)

My code is as follows

(我的代码如下)

START TRANSACTION;

INSERT INTO EMPLOYEE (`firstname`,`lastname`)
 VALUES('john','Doe');

SET EMP_ID = last_insert_id();
 //do some inserts here that fail

COMMIT;

Now, when the other inserts into child tables fail I see that just the first insert being committed.

(现在，当其他插入子表失败时，我看到只有第一个插入被提交。)

To avoid that I need to capture error and set rollback on error being triggered.

(为了避免这种情况，我需要捕获错误并在触发错误时设置回滚。)

When I include the declare statements for error it just fails saying

(当我包含对错误的声明语句时，它只会失败)

"Declare is not valid at this position. expecting EOF ;"

(“声明在此位置无效。期望EOF；”)

Any pointers would be greatly appreciated

(任何指针将不胜感激)

START TRANSACTION;

DECLARE error BOOL DEFAULT 0;
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET `_rollback` = 1;

....other code

I stuff the query in to a string and use nodejs mysql library

(我将查询填充到字符串中并使用nodejs mysql库)

dbSource.query(
  query,
  function(err, results, fields) {
    console.log(fields); 
    res.json(results);
    //console.log(results); // results contains rows returned by server
   // fields contains extra meta data about results, if available
  }
);

  ask by user275157 translate from so

Answer 1

等待大神答复