I am trying to develop a stored procedure to check for a condition which in turn will determine which screen it will put up.
See Below:
DROP PROCEDURE IF EXISTS GetNew;
DELIMITER $$
SELECT COUNT(*) as totnew from Invoices01 where ITEM = 'New Invoice';
CREATE PROCEDURE GetNew(
IN mresult INT ,
OUT mresult2 INT)
BEGIN
if totnew > 0 then set mresult = "NOTFULL";
else
set mresult = "FULL";
end if;
SET @mresult2 = mresult;
END$$
DELIMITER ;
The debugger gets stuck on the 3rd line. I have run this in MySQL Workbench and it works fine.
DROP PROCEDURE IF EXISTS GetNew;
DELIMITER $$
CREATE PROCEDURE GetNew(
INOUT mresult INT ,
OUT mresult2 INT)
BEGIN
DECLARE totnew INT;
SELECT COUNT(*) INTO totnew FROM Invoices01 WHERE ITEM = 'New Invoice';
IF totnew > 0 THEN
SET mresult = 0;
ELSE
SET mresult = 1;
END IF;
SET mresult2 = mresult;
END$$
DELIMITER ;
@mdsmith1 As stated in the message, it's possible the syntax needs to be fixed based on MySQL version running? Perhaps @Tess or @Kabirdas can give more advice
The DELIMITER option is a command for the MySQL command prompt which is a bit different than what you have access to in Retool. The one in the app editor is more specifically for writing queries.
One thing you might try is running three separate queries:
1.
DROP PROCEDURE IF EXISTS GetNew;
2.
CREATE PROCEDURE GetNew(
INOUT mresult INT ,
OUT mresult2 INT)
BEGIN
DECLARE totnew INT;
SELECT COUNT(*) INTO totnew FROM Invoices01 WHERE ITEM = 'New Invoice';
IF totnew > 0 THEN
SET mresult = 0;
ELSE
SET mresult = 1;
END IF;
SET mresult2 = mresult;
END
3.
CALL GetNew(0, @foo);
SELECT @foo;
Does that work?
One thing to note as well as you work with stored procedures in variables is that, due to backend routing with cloud-hosted Retool, any MySQL query might create a new session when you run it meaning you'll use some of the context. It's safest to think that every time you run a query it will be in a new session.
I am showing you 4 screen captures on my attempt on this.
What I am trying to do is run one routine (Temp01b) if Recdays (Receivable Days) is greater than 45 days and run a different routine (Temp01) if Recdays is less than 45 days.
Why try to create the stored procedure from Retool and not create it one time from workbench and call it from Retool? I don't see the need to check if it exists and create it if not existing.
You can call the mysql query/stored procedure from a js query using query.trigger(), do a check on the output, if <45 then Temp01b.trigger() and if >45 Temp01.trigger()
You're trying to call Retool resources to be executed with javascript within you mysql stored procedure, that's not gonna work.
Is there any reason you'd like to use a mysql stored procedure? Looking at you query, it might be a bit overcomplicated and I think it's easier to achieve this within Retool. It can be done in different ways, but most straight forward is to:
create a mysql query eg. unpaidinvoices: select Recdays from Invoices01 where extra4 = " xxxx";
Create a JS query that is being executed on mysql query succes:
create the queries for tempinvoice01b and tempinvoice01.
The mysql query can be a bit tricky as it might return multiple results? If you're sure that you only get a single restult, than you're good, otherwise you might need to iterate over the results in the JS query and execute the if statement for each record. Or limit your results to 1.
When you say TempInvoice01 and TempInvoice01b are running fine - how are you triggering them?
As Marc mentioned, context is really important. If you're using a JavaScript query you can really only reference objects that exist in your model. That can be anything that exists in the state tab of your debug console + some extra built-in utility presets.
So TempInvoice01b.trigger() will only work if TempInvoice01b is a reference to a query that exists in your app. That means that if TempoInvoice01b only exists in SQL then you need to make a Retool query that references it, and then you can call .trigger() on the query you created.
Similarly, Recdays needs to be a reference to a query or component, and you'll need to access its value with either Recdays.value or Recdays.data depending on what it is.
Scott:
I wanted to tell you that I finally got the Stored Procedure to work. I work occasionally with a group called "Freelancers". I got hooked up with a guy in Chile. It cost me $40 but the guy was really good.
If you are interested, I can post the code on this web site.
Its a different thought process, there is no DO WHILE or IF statements.
Anyway, if you are interested, I can post it.
Mike
Scott:
The first attachment is a spread sheet showing what this procedure does. i.e. it starts with a list of Invoice Numbers and it marks all rows with EXTRA1 = X in the Invoice File for the Invoice Numbers in the first file.
The second attachment is the code that accomplishes this.
The procedure is stored in the Data Base (ccprocess) and must be called to do the result.
Once I have the EXTRA1 fields marked with an X, I can now delete rows or transfer rows to backup. It has a lot of uses for my project.
Mike
