Explicit or implicit commit is not allowed in stored function or trigger

[rramy]

Bonjour,

j'obtiens le message d'erreur suivant en reecrivant une procedure stockee en fonction : Explicit or implicit commit is not allowed in stored function or trigger. Je precise que le code de la procedure etait operationnel. Cette erreur semble declenchee par le procedure split :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
CREATE PROCEDURE `split`(d varchar(10), i text)
BEGIN
   DECLARE x INT DEFAULT 1;
   DECLARE y VARCHAR(1000);
   DECLARE z TINYINT UNSIGNED;

   TRUNCATE TABLE fm.split;

   set z = char_length(d);
   while char_length(i)>0 and x>0 do
      set x = instr(i,d);
      if x>0 then set y=left(i,x-1); else set y=i; end if;
      if trim(y) != '' then insert into fm.split (value) values (y); end if;
      set i = substring(i,x+z);
   end while;              
END

Lorsque je remplace le truncate table en gras si dessus par la creation d'une table temporaire equivalente, j'obtiens un fm.split doesn't exist....

PS : Voici la fonction reecrite :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
CREATE FUNCTION `find`(f_uid int(20), f_dir text) RETURNS varchar(13) CHARSET utf8
    DETERMINISTIC
BEGIN
  declare x int(10) DEFAULT 0;
  declare y int(10) DEFAULT 0;
  declare z int(10) DEFAULT 0;
  declare v_fid int(10) DEFAULT 0;
  declare v_did int(10) DEFAULT 0;
  declare v_path varchar(100);
 
  call fm.split('/',f_dir);
  SELECT count(*) INTO z FROM fm.split;
  while x<z do
    SET x=x+1;
    SET y=0;
 
    SELECT value INTO v_path FROM fm.split WHERE id=x LIMIT 1;
    SELECT id INTO y FROM ftp.dirs WHERE uid=f_uid AND did=v_did AND (name LIKE v_path OR path LIKE v_path) LIMIT 1;
 
    IF y>0 then
      SET v_fid = 0;
      SET v_did = y;
    else
      SELECT fid INTO v_fid FROM ftp.links WHERE uid=f_uid AND did=v_did AND (name LIKE v_path OR path LIKE v_path) LIMIT 1;
      SET v_did = 0;
      SET x=z;
    end IF;
  end while;
 
  IF v_fid>0 then
    RETURN concat('f.',v_fid);
  elseif v_did>0 then
    RETURN concat('d.',v_did);
  else
    RETURN NULL;
  end IF;
END