Error Re: declaring integer in MySQL stored procedure

Question

I'm new to stored procedures and am trying to implement the solution to the question @ Hierarchical Query in MySQL II. I posted my code below. I immediately got hung up on the third line. It looks like my question is answered @ Error declaring integer variable inside MySQL stored function, but I still can't get it to work.

For my first experiment, I want to query the entire taxonomic tree by designating 1 (the animal kingdom) as the integer.

I've been looking at some stored procedure tutorials, but it isn't yet clear to me exactly how it works, and I may have the cart before the horse. As I understand it, a stored procedure can be queried via MySQL or PHP, and I would prefer to do it in PHP. I don't understand if the code below is a stored procedure query or something I have to do before I can write queries. I've created the practice table (t).

So maybe I should ask my question another way: If I have already have a practice table with a field for Parent ID's, and I want to learn how to query that table with PHP, do I still need to mess with the code below in order to create a "stored procedure"? Or can I create a stored procedure with a query written in PHP?

DELIMITER $$
create procedure showHierarchyUnder
(
SET i = 1;
)
BEGIN
 declare bDoneYet boolean default false;
 declare working_on int;
 declare next_level int;
 declare theCount int;
CREATE temporary TABLE xxFindChildenxx
(   N int not null,
    processed int not null,
    level int not null,
    parent int not null
);
set bDoneYet=false;
insert into xxFindChildenxx (N,processed,level,parent) select  theId,0,0,0;
while (!bDoneYet) do
    select count(*) into theCount from xxFindChildenxx where processed=0;

    if (theCount=0) then 
        set bDoneYet=true;
    else
        SELECT N,level+1 INTO working_on,next_level FROM xxFindChildenxx where processed=0 limit 1;
        insert into xxFindChildenxx (N,processed,level,parent)
        select N,0,next_level,parent
        from t
        where parent=working_on;
        update xxFindChildenxx set processed=1 where N=working_on;
    end if;
end while;
delete from xxFindChildenxx where N=theId;
select level,count(*) as lvlCount from xxFindChildenxx group by level;
drop table xxFindChildenxx;
END
??

No, I got it from http://stackoverflow.com/questions/33248361/hierarchical-query-in-mysql-ii I had to delete all the comments before it would work, then I got hung up on the integer. I'm using phpMyAdmin, by the way. — , Oct 23 '15 at 01:33
phpMyAdmin is just a client, just like mysql workbench, navicat, etc...You clearly should be studying about the basic of stored procedure before trying to edit one...a simple googling "stored procedure mysql" and clicking on the first result would have told you that your syntax was incorrect... — BK435, Oct 23 '15 at 01:38
It was very clear that you did not write the procedure yourself since you declared certain variables, and set them in the procedure..yet you tried setting one without declaring it after in a very improper way...that was just an FYI — BK435, Oct 23 '15 at 01:41

score 0 · Accepted Answer · answered Oct 23 '15 at 01:21

This will create the stored procedure for you. In order to set a variable in a stored procedure you must first declare it and only then can you set it after you have declared all your variables. Notice how the SET i = 1; is not in between the () anymore and i has been declared i int;. Looks like you were trying to pass in a variable like create procedure showHierarchyUnder(IN i int(10)) but that would be expecting a value when the procedure is called. You can still do that but if i will always be 1, there is no point. In the future when in doubt just go to dev.mysql.

DELIMITER //
create procedure showHierarchyUnder()
BEGIN
 declare bDoneYet boolean default false;
 declare working_on int;
 declare next_level int;
 declare theCount int;
 declare i int;
 SET i = 1;
CREATE temporary TABLE xxFindChildenxx
(   N int not null,
    processed int not null,
    level int not null,
    parent int not null
);
set bDoneYet=false;
insert into xxFindChildenxx (N,processed,level,parent) select  theId,0,0,0;
while (!bDoneYet) do
    select count(*) into theCount from xxFindChildenxx where processed=0;

    if (theCount=0) then 
        set bDoneYet=true;
    else
        SELECT N,level+1 INTO working_on,next_level FROM xxFindChildenxx where processed=0 limit 1;
        insert into xxFindChildenxx (N,processed,level,parent)
        select N,0,next_level,parent
        from t
        where parent=working_on;
        update xxFindChildenxx set processed=1 where N=working_on;
    end if;
end while;
delete from xxFindChildenxx where N=theId;
select level,count(*) as lvlCount from xxFindChildenxx group by level;
drop table xxFindChildenxx;
END//
DELIMITER ;

Thx, but I'm now getting a 1064 error pointing at the very first line - "DELIMITER." If I replace the double slashes - // - with $, I get the same result. So I can't create a stored procedure named Uno, and I can't create one named Dos. But when I go back and try create Uno again using a different delimiter, it says Uno has already been created. So maybe some of those 1064 errors are "false flags" that I can just ignore? — , Oct 23 '15 at 01:31
It looks like the next step is to simply learn how to query a stored procedure with PHP. That way I can tell if a stored procedure has actually been created. Is there a way to list all existing stored procedures so I can somehow delete the ones that are faulty or are no longer being used? — , Oct 23 '15 at 01:34
The syntx should work..created the procedure for me...not sure what you are doing wrong and yes...how about you just google how to query for stored procedures in this time... — BK435, Oct 23 '15 at 01:38
I checked your answer as correct, because it looks like it's creating stored procedures in spite of the error messages. One more question: Do stored procedures have some kind of cost if they aren't being queried? In other words, if you do a lot of experiments, creating stored procedures that you're never going to use, is it necessary to delete them, or can they be ignored as insignificant? — , Oct 23 '15 at 01:43

Error Re: declaring integer in MySQL stored procedure

1 Answers1

Linked