Problem with db-migrate executing a CREATE PROCEDURE
LuigiElleBalotta opened this issue · 3 comments
LuigiElleBalotta commented
I'm submitting a...
- Bug report
- Feature request
- Question
Current behavior
The CREATE PROCEDURE won't execute if it isn't on one line
Expected behavior
The query should be executed even if the query is not on one line
Minimal reproduction of the problem with instructions
NOT WORKING QUERY
CREATE PROCEDURE `uspGetAttributes`(IN inDomain NVARCHAR(255), IN inScope NVARCHAR(255), IN inApplication NVARCHAR(255), IN inAttr NVARCHAR(255), IN inRef NVARCHAR(255))
BEGIN
SET @query = CONCAT('SELECT at.domain, at.scope, at.`name`, at.`type`, at.attr, at.`value`, at.mandatory, a.ref, a.`value` as attrValue, a.createdAt, a.updatedAt, a.updatedBy FROM attributetypes at LEFT JOIN attributes a ON at.id = a.attributetypeId WHERE 1=1');
IF inDomain IS NOT NULL THEN
SET @query = CONCAT(@query, ' AND at.domain = ?');
END IF;
IF inScope IS NOT NULL THEN
SET @query = CONCAT(@query, ' AND at.scope = ?');
END IF;
IF inApplication IS NOT NULL THEN
SET @query = CONCAT(@query, ' AND at.`name` = ?');
END IF;
IF inAttr IS NOT NULL THEN
SET @query = CONCAT(@query, ' AND at.attr = ?');
END IF;
IF inRef IS NOT NULL THEN
SET @query = CONCAT(@query, ' AND a.ref = ?');
END IF;
SET @query = CONCAT(@query, ' ORDER BY a.updatedAt DESC, a.createdAt DESC;');
PREPARE stmt FROM @query;
IF inDomain IS NOT NULL AND inScope IS NOT NULL AND inApplication IS NOT NULL AND inAttr IS NOT NULL AND inRef IS NOT NULL THEN
EXECUTE stmt USING inDomain, inScope, inApplication, inAttr, inRef;
ELSEIF inDomain IS NOT NULL AND inScope IS NOT NULL AND inApplication IS NOT NULL AND inAttr IS NOT NULL AND inRef IS NULL THEN
EXECUTE stmt USING inDomain, inScope, inApplication, inAttr;
ELSEIF inDomain IS NOT NULL AND inScope IS NOT NULL AND inApplication IS NOT NULL AND inAttr IS NULL AND inRef IS NULL THEN
EXECUTE stmt USING inDomain, inScope, inApplication;
ELSEIF inDomain IS NOT NULL AND inScope IS NULL AND inApplication IS NULL AND inAttr IS NULL AND inRef IS NULL THEN
EXECUTE stmt USING inDomain;
ELSEIF inDomain IS NULL AND inScope IS NULL AND inApplication IS NULL AND inAttr IS NULL AND inRef IS NULL THEN
EXECUTE stmt;
END IF;
DEALLOCATE PREPARE stmt;
END;
WORKING QUERY
CREATE PROCEDURE uspGetAttributes(IN inDomain VARCHAR(255), IN inScope VARCHAR(255), IN inApplication VARCHAR(255), IN inAttr VARCHAR(255), IN inRef VARCHAR(255)) BEGIN SET @query = CONCAT('SELECT at.domain, at.scope, at.`name`, at.`type`, at.attr, at.`value`, at.mandatory, a.ref, a.`value` as attrValue, a.createdAt, a.updatedAt, a.updatedBy FROM attributetypes at LEFT JOIN attributes a ON at.id = a.attributetypeId WHERE 1=1'); IF inDomain IS NOT NULL THEN SET @query = CONCAT(@query, ' AND at.domain = ?'); END IF; IF inScope IS NOT NULL THEN SET @query = CONCAT(@query, ' AND at.scope = ?'); END IF; IF inApplication IS NOT NULL THEN SET @query = CONCAT(@query, ' AND at.`name` = ?'); END IF; IF inAttr IS NOT NULL THEN SET @query = CONCAT(@query, ' AND at.attr = ?'); END IF; IF inRef IS NOT NULL THEN SET @query = CONCAT(@query, ' AND a.ref = ?'); END IF; SET @query = CONCAT(@query, ' ORDER BY a.updatedAt DESC, a.createdAt DESC;'); PREPARE stmt FROM @query; IF inDomain IS NOT NULL AND inScope IS NOT NULL AND inApplication IS NOT NULL AND inAttr IS NOT NULL AND inRef IS NOT NULL THEN EXECUTE stmt USING inDomain, inScope, inApplication, inAttr, inRef; ELSEIF inDomain IS NOT NULL AND inScope IS NOT NULL AND inApplication IS NOT NULL AND inAttr IS NOT NULL AND inRef IS NULL THEN EXECUTE stmt USING inDomain, inScope, inApplication, inAttr; ELSEIF inDomain IS NOT NULL AND inScope IS NOT NULL AND inApplication IS NOT NULL AND inAttr IS NULL AND inRef IS NULL THEN EXECUTE stmt USING inDomain, inScope, inApplication; ELSEIF inDomain IS NOT NULL AND inScope IS NOT NULL AND inApplication IS NULL AND inAttr IS NULL AND inRef IS NULL THEN EXECUTE stmt USING inDomain, inScope; ELSEIF inDomain IS NOT NULL AND inScope IS NULL AND inApplication IS NULL AND inAttr IS NULL AND inRef IS NULL THEN EXECUTE stmt USING inDomain; ELSEIF inDomain IS NULL AND inScope IS NULL AND inApplication IS NULL AND inAttr IS NULL AND inRef IS NULL THEN EXECUTE stmt; END IF; DEALLOCATE PREPARE stmt; END;
What is the motivation / use case for changing the behavior?
The query should be executed on more lines as in other sql clients like DBEaver or HeidiSQL
Environment
db-migrate version: 0.11.13
plugins with versions: X.Y.Z
db-migrate driver with versions: "db-migrate-mysql": "^2.2.0",
Additional information:
- Node version: 18.13.0
- Platform: Windows
Others:
wzrdtales commented
LuigiElleBalotta commented
Hello, i already read the docs, this is the config:
{
"driver": "mysql",
"host": "localhost",
"port": 3306,
"user": "root",
"password": "root",
"database": "cretinodelcazzo",
"driver": "mysql",
"multipleStatements": true
}
Please don't consider other users stupid :D, reopen @wzrdtales ...
wzrdtales commented
worked according to those users here #625 (comment)