0

I'm learning how to work with MySQL's stored procedures and am getting snowed by error messages that I can't seem to fix. Someone gave me a script to create a stored procedure here. When I paste it into phpMyAdmin > SQL, I got this error message: #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DELIMITER' at line 1

Yet it does create a stored procedure - though it doesn't work. At one point, I had one that actually worked, but I haven't been able to recreate it. Another frequent error is 1054 Unknown column (theld). I checked this discussion, but my code is already wrapped in BEGIN/END.

So I wondered if it's possible to do a workaround by simply editing code in phpMyAdmin to make it work. I modified the code to target a different table - gz_life_mammals - also replacing the field Parent with ParentID. (gz_life_mammals already has a text field named Parent).

Now when I go into phpMyAdmin > Procedures > showMammals > Execute, I get the same 1054 error message. If I click Edit routine > Parameters, the values for Direction and Type are IN and INT; everything else is blank.

Is it possible to fix this through phpMyAdmin, or is there a better software program for the job? Sorry for the long explanation.

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,
ParentID int not null
);
set bDoneYet=false;
insert into xxFindChildenxx (N,processed,level,ParentID) 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,ParentID)
    select N,0,next_level,ParentID
    from gz_life_mammals
    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
Community
  • 1
  • 1
  • See also [Delimiters in MySQL](http://stackoverflow.com/a/10259528/541091). The `DELIMITER` keyword is not a standard part of MySQL, but rather a client feature of Workbench and the CLI client. To do the same in PHPMyAdmin, I believe there's an input setting to define the alternative delimiter (which you must do in order for the internal `;` to work in the SP definition) – Michael Berkowski Oct 23 '15 at 22:17
  • It isn't clear where `theId` is supposed to come from. It is referenced twice in the SP definition, but not defined as an input parameter. Was it deleted from among your DECLAREs? – Michael Berkowski Oct 23 '15 at 22:19
  • That's another thing that's really confusing; I see people using both // and ?? as delimiters. When I click SQL to paste in a stored procedure script, there's a "Delimiter" box at the bottom that displays a semicolon by default. My stored procedure script ends in a semicolon, so I assumed that's the standard. I think I originally created a working SP by deleting "END" or the last delimiter, though I can't recreate it now. –  Oct 23 '15 at 22:21
  • Good catch Re: theID; I see the same anomaly in the stored procedure script I was given. I'll have to see if I can find an earlier version that illustrates how to handle that. –  Oct 23 '15 at 22:24
  • Ah, here's the original discussion; the answer features theID. -- http://stackoverflow.com/questions/33248361/hierarchical-query-in-mysql-ii –  Oct 23 '15 at 22:26
  • Examine the example SP in the answer I linked. In that case, you would enter `$$` into the delimiter field and remove the two `DELIMITER` statements to run it in PMA. Then all the `;` _inside_ the procedure are not used to accidentally end the procedure, but you do need a terminating delimiter after the final `END`, which is why that one has `END$$` to indicate the entire statement is finished and ready to execute. – Michael Berkowski Oct 23 '15 at 22:26
  • In Drew's answer, `theId` was defined as an input parameter `theId int`. So the id to act on would be passed into `CALL spname(theId)` – Michael Berkowski Oct 23 '15 at 22:28

0 Answers0