SQL Issue

Alles, was MariaDB und MySQL betrifft, kann hier besprochen werden.

SQL Issue

Postby sheenlim08 » 22. August 2020 05:37

I am having an awuful problem with MySQL Workbench recently managing a MariaDB Instance on XAMP.

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.
sheenlim08
 
Posts: 5
Joined: 22. August 2020 05:10
XAMPP version: 7.4.9
Operating System: Linux Mint 20.2

Return to MariaDB - MySQL

Who is online

Users browsing this forum: No registered users and 9 guests