1. I have this SQL code in the MySQL Model file, and i can forward engineer the changes successfully to my test lab.
- Code: Select all
CREATE DEFINER=`root`@`localhost` PROCEDURE `generateCugPlanReport`(
IN reportName VARCHAR(255),
OUT cugPlanReportID INT)
BEGIN
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
ROLLBACK;
END;
START TRANSACTION;
SET @reportName = '';
SET @timeStamp = CURRENT_TIMESTAMP;
IF reportName = '' THEN
SET @reportName = CONCAT('Scheduled Generated Report - ', (SELECT @timeStamp));
ELSE
SET @reportName = CONCAT(reportName, ' - Unscheduled Generated Report - ', (SELECT @timeStamp));
END IF;
INSERT INTO mydb.cug_plan_reports(name, timestamp)
VALUES((SELECT @reportName), (SELECT @timeStamp));
SET @planReportID = LAST_INSERT_ID();
INSERT INTO mydb.cug_plan_reports_cuginstancesnapshots(cug_plan_reports_id, displayplannametext, displayplandescriptiontext, cost)
SELECT (SELECT @planReportID), planname, plandescription, cost FROM mydb.cug_plans;
INSERT INTO mydb.cug_plan_reports_chargecodes (cug_plan_reports_id, displaycodetext, displaydescriptiontext)
SELECT (SELECT @planReportID), code, description FROM mydb.charge_codes;
INSERT INTO mydb.cug_plan_reports_entitysnapshots (
cug_plan_reports_id,
displayusertext,
displayuserpositiontext,
displaycompanytext,
displaycugnumbertext,
displaycugplantext,
displaycugplancost,
displaychargecodetext
)
SELECT
(SELECT @planReportID),
CONCAT(user.lname, ", ", user.fname) AS 'User',
user.position AS 'Position',
company.name AS 'Company',
cugNumbers.simnumber AS 'CUG Number',
cugPlans.planname AS 'CUG Plan',
cugPlans.cost AS 'CUG Cost',
chargeCodes.code AS 'Charge Code'
FROM mydb.site_user_assignments siteUserAssignment
INNER JOIN mydb.users user ON siteUserAssignment.users_id = user.id
INNER JOIN mydb.companies company ON siteUserAssignment.companies_id = company.id
INNER JOIN mydb.cug_assignments cugAssignment ON cugAssignment.users_id = user.id
INNER JOIN mydb.cug_numbers cugNumbers ON cugAssignment.cug_numbers_id = cugNumbers.id
INNER JOIN mydb.cug_plans cugPlans ON cugAssignment.cug_plans_id = cugPlans.id
INNER JOIN mydb.charge_codes chargeCodes on cugAssignment.charge_codes_id = chargeCodes.id
GROUP BY User
ORDER BY User;
SET cugPlanReportID = (SELECT @planReportID);
COMMIT;
END;
When I query the procedure using the below
MariaDB [mydb]> call generatePhonePlanReport("Test", @phonePlanReportID);
Query OK, 16 rows affected (0.013 sec)
However I do not see any inserts done on the table.
The solution I do to overcome this is to edit the procedure on the database (alter) then save it without making any changes to it. The code previde before workbench commits the changes are below.
- Code: Select all
USE `mydb`;
DROP procedure IF EXISTS `generateCugPlanReport`;
DELIMITER $$
USE `mydb`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `generateCugPlanReport`(
IN reportName VARCHAR(255),
OUT cugPlanReportID INT)
BEGIN
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
ROLLBACK;
END;
START TRANSACTION;
SET @reportName = '';
SET @timeStamp = CURRENT_TIMESTAMP;
IF reportName = '' THEN
SET @reportName = CONCAT('Scheduled Generated Report - ', (SELECT @timeStamp));
ELSE
SET @reportName = CONCAT(reportName, ' - Unscheduled Generated Report - ', (SELECT @timeStamp));
END IF;
INSERT INTO mydb.cug_plan_reports(name, timestamp)
VALUES((SELECT @reportName), (SELECT @timeStamp));
SET @planReportID = LAST_INSERT_ID();
INSERT INTO mydb.cug_plan_reports_cuginstancesnapshots(cug_plan_reports_id, displayplannametext, displayplandescriptiontext, cost)
SELECT (SELECT @planReportID), planname, plandescription, cost FROM mydb.cug_plans;
INSERT INTO mydb.cug_plan_reports_chargecodes (cug_plan_reports_id, displaycodetext, displaydescriptiontext)
SELECT (SELECT @planReportID), code, description FROM mydb.charge_codes;
INSERT INTO mydb.cug_plan_reports_entitysnapshots (
cug_plan_reports_id,
displayusertext,
displayuserpositiontext,
displaycompanytext,
displaycugnumbertext,
displaycugplantext,
displaycugplancost,
displaychargecodetext
)
SELECT
(SELECT @planReportID),
CONCAT(user.lname, ", ", user.fname) AS 'User',
user.position AS 'Position',
company.name AS 'Company',
cugNumbers.simnumber AS 'CUG Number',
cugPlans.planname AS 'CUG Plan',
cugPlans.cost AS 'CUG Cost',
chargeCodes.code AS 'Charge Code'
FROM mydb.site_user_assignments siteUserAssignment
INNER JOIN mydb.users user ON siteUserAssignment.users_id = user.id
INNER JOIN mydb.companies company ON siteUserAssignment.companies_id = company.id
INNER JOIN mydb.cug_assignments cugAssignment ON cugAssignment.users_id = user.id
INNER JOIN mydb.cug_numbers cugNumbers ON cugAssignment.cug_numbers_id = cugNumbers.id
INNER JOIN mydb.cug_plans cugPlans ON cugAssignment.cug_plans_id = cugPlans.id
INNER JOIN mydb.charge_codes chargeCodes on cugAssignment.charge_codes_id = chargeCodes.id
GROUP BY User
ORDER BY User;
SET cugPlanReportID = (SELECT @planReportID);
COMMIT;
END$$
DELIMITER ;
calling this procedure then inserts the results.
I tried to copy the code lines from DELIMITER $$ unto the end DELIMITER ;
but the SQL parser on mysql workbench is saying
"DELIMITER" is not valid at this position, Expecting CREATE
I need guidance, on how I can proceed so that i dont have to alter the procedure after I deployed it from the models workbench file.