时间戳

首页 > 解决方案 > HeidiSQL Not Accepting Stored Procedure

sql - HeidiSQL Not Accepting Stored Procedure

问题描述

So, I basically have this procedure:

CREATE PROCEDURE NotificationLog(IN `Timestamp` BIGINT, IN UnitID INT, IN Content VARCHAR(50), IN `LEVEL` TINYINT, IN Solved INT, IN Sector TINYINT)
MODIFIES SQL DATA
BEGIN
    DECLARE newId INT;
    INSERT INTO `logs` VALUES (NULL, `Timestamp`, `UnitID`);
    SET newId = LAST_INSERT_ID();
    INSERT INTO notificationlogs VALUES (newId, Content, `Level`, Solved, Sector);
END;

But there's an error, when I run it on the Query tab of Heidi SQL 10 (which I'm using to manage the DB), it gives me the error:

SQL Error (1064): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax near " at line 4

Which is the most useless uninformative message ever. I have no idea what's wrong in the syntax because this error tells nothing.

Is there something wrong in the syntax? The docs on MariaDB aren't that helpful for stored procedures with temp variables.

Edit: It seems this is a HeidiSQL problem rather than MariaDB, changed the question title.

标签： sqlstored-proceduresmariadbheidisql

解决方案

我使用 HeidiSQL 的 GUI 工具来创建过程，它生成了以下代码：

CREATE DEFINER=`root`@`localhost` PROCEDURE `CreateNotificationLog`(
    IN `Timestamp` BIGINT,
    IN `UnitID` INT,
    IN `Content` VARCHAR(50),
    IN `Level` TINYINT,
    IN `Solved` BIT,
    IN `Sector` TINYINT


)
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''
BEGIN
    DECLARE newId INT;
    INSERT INTO `logs` VALUES (NULL, `Timestamp`, `UnitID`);
    SET newId = LAST_INSERT_ID();
    INSERT INTO notificationlogs VALUES (newId, Content, `Level`, `Solved`, Sector);
END

虽然这可行，但我仍然想知道我的手写代码有什么问题。

推荐阅读