Re: cannot call a stored procedure from within another stored procedure

[Peter Brawley]

drop table if exists logging;
create table logging( ts timestamp default now(), log_string text );
drop procedure if exists Write_to_log;
drop function if exists Prepare_values_string;
drop procedure if exists test;
delimiter go

CREATE PROCEDURE `write_to_log`(_STRING VARCHAR(5000))
BEGIN
  START TRANSACTION;  -- NEEDS ERROR TRAPPING
  INSERT INTO logging (LOG_STRING) VALUES (_STRING);
  COMMIT;
END;
go

CREATE FUNCTION Prepare_values_string(valueString TEXT) 
      RETURNS varchar(5000) CHARSET latin1
BEGIN
  DECLARE _val TEXT;
  DECLARE _pos INT;
  DECLARE _TAB CHAR;
  DECLARE _COMMA CHAR;
  DECLARE _len INT;
  DECLARE _return_ TEXT;
  SET _TAB = x'0009';
  SET _COMMA := x'0044';
  IF ENDS_WITH(valueString) <> _TAB THEN
    SET valueString = CONCAT(valueString, _TAB);  -- NOT :=
  END IF;
  SET _return_ := '';
  SET _len = 	LENGTH(valueString);
  aWhileLoop : WHILE _len <> 0 DO
   	SET _pos = locate(_TAB, valueString);
    SET _val = substring(valueString, 1, _pos -1);
    SET _val = quote(_val);
    SET _return_ := concat(_return_, _COMMA, _val);
    SET valueString := substring(valueString, _pos +1);
    CALL WRITE_TO_LOG(valueString);
    SET _len :=	LENGTH(valueString);
  END WHILE aWhileLoop;
  RETURN concat('(', _return_, ')');
END;
go

create procedure test() 
begin
  select * from logging;
  call Write_to_log("this is a test");
  select "After call to write_to_log", ts, log_string from logging;
end;
go
delimiter ;
call test();
+----------------------------+---------------------+----------------+
| After call to write_to_log | ts                  | log_string     |
+----------------------------+---------------------+----------------+
| After call to write_to_log | 2021-10-23 13:58:16 | this is a test |
+----------------------------+---------------------+----------------+

If you're on Linux, be careful about capitalising & proper-casing database object names.



Edited 1 time(s). Last edit at 10/23/2021 01:08PM by Peter Brawley.