Stored procedure 2

App Building
1

I need to get a MySQL Stored Procedure to work.

Here is what I have come up with:

DELIMITER //
CREATE PROCEDURE pbackup1(IN mContinue)
BEGIN
let mContinue = "YES";
while (mContinue = "YES") {
   SELECT @CCNT1 := COUNT(Check01) FROM Invoices02 where Userno = {{ localStorage.values.Userno }} and Check01 = 1 and EXTRA1 = "  ";

  IF @CCNT1 > O THEN
    @mInvnbr := SELECT Invnbr FROM Invoices02 WHERE Userno =  {{ localStorage.values.Userno }}  AND Check01 = 1 and EXTRA1 = "  ";
    update Invoices02 set EXTRA1 =  "X" where Invnbr = @mInvnbr;
   ELSE
      mContinue = 'NO';
   END IF;
}
END; //
DELIMITER ;

I am getting an error message about the first few lines in this program.

Any advice would be appreciated.

Mike

2

I have done some more work on this with MySQL Workbench and have come up with code that works in the MySQL Workbench environment as below. But it will not run in Retool. Further I get only general debug comments that are useless.

Any suggestions?

Mike

DROP PROCEDURE IF EXISTS pbackup1 ;

DELIMITER //

CREATE PROCEDURE pbackup1
BEGIN

DECLARE mContinue TINYTEXT DEFAULT " ";
DECLARE count1 INT DEFAULT 1;
DECLARE CCNT1  INT DEFAULT 1;

set mContinue = "YES";

WHILE mContinue = "YES" DO 
  
set CCNT1 := (SELECT COUNT(Check01) FROM Invoices02 WHERE EXTRA1 = "  "  );
      
  IF CCNT1 > 0 THEN
    set @mInvnbr := (SELECT Invnbr FROM Invoices02 WHERE Check01 = 1 and EXTRA1 = "  ");
    
    UPDATE Invoices02 SET EXTRA1 = 'X' WHERE Invnbr = @mInvnbr;
  ELSE
      SET mContinue = 'NO';
  END IF;
END WHILE; 
  
END //

DELIMITER ;
3

Hey @mdsmith1! :wave:

Defining a procedure will be a little different when we do it via a Retool query because we don't have to worry about delimiters. In fact, trying to utilize a delimiter was throwing errors for me when doing some testing. The following worked for me, though:

image
image680×1376 87.4 KB

Generally speaking, the primary use case for delimiters is when we're defining a procedure via the mysql command line. I hope that helps! Let me know if you run into any additional issues.

4

Darren:

For some reason I can't seem to copy and paste your code.

Could you reply again in a format that I am able to copy.

Thanks.

Mike

1 Like
5

Of course!

DROP PROCEDURE IF EXISTS pbackup1;

CREATE PROCEDURE pbackup1 ()
BEGIN 
DECLARE mContinue TINYTEXT DEFAULT " ";
DECLARE count1 INT DEFAULT 1;
DECLARE CCNT1 INT DEFAULT 1;

SET mContinue = "YES";

WHILE mContinue = "YES" DO
SET
  CCNT1 := (
    SELECT
      COUNT(Check01)
    FROM
      Invoices02
    WHERE
      EXTRA1 = 5
  );

IF CCNT1 > 0 THEN
SET
  @mInvnbr := (
    SELECT
      Invnbr
    FROM
      Invoices02
    WHERE
      Check01 = 1
      AND EXTRA1 = 5
  );

UPDATE
  Invoices02
SET
  EXTRA1 = 10
WHERE
  Invnbr = @mInvnbr;

ELSE
SET
  mContinue = 'NO';

END IF;

END
WHILE;
END;
6

Thanks Darren:

I will look at this tomorrow.

Mike

1 Like
7

Darren:

Here is the exact code I am using:

update Invoices02 set EXTRA1 = " " ;

DROP PROCEDURE IF EXISTS pbackup1;

CREATE PROCEDURE pbackup1 ()

BEGIN 
DECLARE mContinue TINYTEXT DEFAULT " ";
DECLARE count1 INT DEFAULT 1;
DECLARE CCNT1 INT DEFAULT 1;

SET mContinue = "YES";

WHILE mContinue = "YES" DO
SET
  CCNT1 := (
    SELECT
      COUNT(Check01)
    FROM
      Invoices02
    WHERE
   Check01 = 1 and EXTRA1 = "  " )
  ;

IF CCNT1 > 0 THEN
SET
  @mInvnbr := (
    SELECT
      Invnbr
    FROM
      Invoices02
    WHERE
      Check01 = 1
      AND EXTRA1 = " "  
  );


UPDATE Invoices02 SET EXTRA1 = "X" WHERE  Invnbr = @mInvnbr;

ELSE
SET
  mContinue = 'NO';

END IF;

END WHILE;
END;

This code does not work. I use GODADDY to watch the Invoices02 Table.

I don't think there is any result for @mInvnbr.

The console.log feature only works on JS so I am unable to use it in MySQL code and that is unfortunate.

Mike

8

Sorry, I should have clarified - as written, this code will define a procedure but not actually execute it. As such, you only need to run this particular query once. Whenever you want this procedure to execute, you'll need to run the query CALL pbackup1();.

Let me know if the Invoices02 table updates appropriately when you call the procedure!

9

Darren:

Yes it works !!!

I added the

Call pcbackup();

code at the end and now it works perfectly.

This is really helpful to me. I have been struggling to get Stored Procedures to work in a number of forums for over a year and I have not been able to get to first base.

I will be able to use this in a lot of places.

Thank you so much.

I will mark this as a solution. Thanks again.

Mike

1 Like