Two days in, and I still can't find the cause of this 1064 error.

I have some DB experience, new to MariaDB. Writing a procedure, but always getting error 1064 iff I use a begin/end block. What am I missing? This code, no problem:

delimiter //
create procedure do_easy ()
select category, leaf_match from reportDef
      where ignore_me = false and leaf_match is not null //
delimiter ;

But, this always gives the 1064 error, always saying "............. for the right syntax to use near '' at line 5"

delimiter //
create procedure do_notEasy ()
begin
select category, leaf_match from reportDef
      where ignore_me = false and leaf_match is not null;
end //
delimeter ;

All I did above was add the begin/end block.

Here's the actual code I'm having the problem with...

delimiter //

create procedure do_leafs ()
modifies sql data
begin 
  declare done boolean;
  declare cur_leafs cursor for
    select category, leaf_match from reportDef
      where ignore_me = false and leaf_match is not null;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
  open cur_leafs;
  done = false;

leafs: loop
  fetch cur_leafs into category, leaf_match;
  -- if done then
    -- leave leafs;
  end if;
  insert into report values (false, category,
    select sum(amount) from WFChkData where regexp_instr(description, leaf_regexp));
-- future: tag the matched wfchkdata rows
end loop;

close cur_leafs;

end //

delimeter ;

Thanks!

Answer Answered by Brian Evans in this comment.

You have several errors in the actual code.

done = false;

Missing SET before done

fetch cur_leafs into category, leaf_match;

category and leaf_match are not defined

insert into report values (false, category,
    select sum(amount) from WFChkData where regexp_instr(description, leaf_regexp));

is an invalid statement. it could be rewritten as an insert select like:

insert into report
    select false, category, sum(amount) from WFChkData where regexp_instr(description, leaf_regexp);

You also spelled delimiter wrong on the final line.

Comments

Comments loading...
Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.