I have created an MYSQL procedure.
it is successfully executing on MYSQL manually.
But when I am executing at the time of the plugin install it will give me an error.
This is the MYSQL procedure.
DELIMITER $$
CREATE PROCEDURE `NH_SP_GetProductIds`
(
`tmpId` INT,
`StoreId` INT
)
READS SQL DATA
SQL SECURITY INVOKER
BEGIN
SET @sql_command = '';
drop temporary TABLE if exists ProductIdsTable;
CREATE TEMPORARY TABLE ProductIdsTable (
ProductId INT
);
SET @sql_command = '
INSERT INTO ProductIdsTable (ProductId)
SELECT DISTINCT
p.Id AS ProductId
FROM Product p';
PREPARE sql_do_stmts FROM @sql_command;
EXECUTE sql_do_stmts;
DEALLOCATE PREPARE sql_do_stmts;
END$$
DELIMITER ;
Inside Install method
var filePath = _fileProvider.MapPath("~/Plugins/{plugin}/Procedure/MySQL.StoredProcedure.sql");
if (!_fileProvider.FileExists(filePath))
return;
data.ExecuteSqlScript(_fileProvider.ReadAllText(filePath, Encoding.Default));
I am getting this error.
Parameter '@sql_command' must be defined.