Function MySQL con transazioni

[orion91]

Ragazzi ho un problema con mysql e le transazioni.
Dovrei scrivere una funzione che fa delle INSERT ma le committa solo al termine della funzione quando sono sicuro che tutte siano andate a buon fine. Pensavo di usare le transaction e per questo ho creato delle tabelle con INNODB come engine.

Per fare qualche prova ho scritto una funzione molto semplice, questa:

CREATE FUNCTION addProdotto (
	MacroArea INT, 
	Cat INT, 
	nCat VARCHAR(200)
	) RETURNS INT 
BEGIN
	DECLARE lCat INT;
	DECLARE lSub INT;
	
	SET lCat = -1;
	SET lSub = -1;
		
	IF (Cat = -1) THEN
		INSERT INTO categoria (descr, idma) VALUES (nCat, 2);
		SET lcat = LAST_INSERT_ID();
	END IF;
	
	RETURN lCat;
END //

Ho provato ad inserire al suo interno sia SET AUTOCOMMIT = 0, che START TRANSACTION ed ovviamente una COMMIT oppure una ROLLBACK a seconda del risultato della INSERT (il di controllo dello stato l'ho omesso ma comunque va a controllore eventuali eccezioni sollevate dalla INSERT). Ma se inserisco questi elementi mi dice che nelle funzioni non è permesso usare COMMIT inplicite ed esplicite, errore 1455 e 1422 con START TRANSACTION!

Allora mi sono detto: faccio START TRANSACTION prima di chiamare la funzione, quindi fuori di essa, la richiamo, e poi faccio COMMIT o RALLBACK ma niente la INSERT viene eseguita e committata immediatamente! Stessa cosa se setto AUTOCOMMIT a 0 prima della chiamata e poi faccio la COMMIT! Ma niente vengono ingnorati!
(Per testare ho fatto START TRANSACTION; SELECT addProdotto(...); ROLLBACK; ma niente da fare dopo mi ritrovo la riga inserita nella tabella categoria, cosa che non dovrebbe essere vista la ROLLBACK

Come devo fare, sul sito ufficiale di MySQL dalle reference non si capisce niente! Penso non sia nemmeno troppo complicata come cosa ma non so proprio che "pesci" pigliare!
Qualche suggerimento ed esempio sulle TRANSACTION nelle funzioni?

Saluti, Carlo.

[r1cky`]

Credo che purtroppo le transazioni in trigger o stored procedure non siano supportare. Perchè non usare una stored procedure allora?
Potresti fare una cosa del genere (spero non aver sbagliato niente):

CREATE PROCEDURE addProdotto(
	IN MacroArea INT, 
	IN Cat INT, 
	IN nCat VARCHAR(200)
	OUT id INT 
)
BEGIN

	DECLARE lCat INT;
	DECLARE lSub INT;

	DECLARE EXIT HANDLER FOR NOT FOUND
	BEGIN
		ROLLBACK;
		CALL ERROR_IS_HAPPENED();
	END;

	DECLARE EXIT HANDLER FOR SQLEXCEPTION
	BEGIN
		ROLLBACK;
		CALL ERROR_IS_HAPPENED();
	END;

	DECLARE EXIT HANDLER FOR SQLWARNING
	BEGIN
		ROLLBACK;
		CALL ERROR_IS_HAPPENED();
	END;

	START TRANSACTION;   

	SET lCat = -1;
	SET lSub = -1;

	IF (Cat = -1) THEN
		INSERT INTO categoria (descr, idma) VALUES (nCat, 2);
		SET lcat = LAST_INSERT_ID();
	END IF;

	SET id = lcat;
	
	COMMIT;  

END

e per chiamarla

CALL addProdotto(....,@id);
SELECT @id;

Purtroppo usando stored procedure /functions iniziano a vedersi limiti un pochino fastidiosi di mysql. Ad esempio manca una funzione che fa da raise dell'errore e si è costretti ad usare un workaround che consiste nel chiamare una funzione non definita.

[luca200]

scusa ma come la chiami la funzione?

[orion91]

Allora per Luca200 beh la chiamo con la SELECT davanti:

SELECT addProdotto(2,-1,"Test");

Non fare caso ai parametri la funzione che ho postato è incompleta, ci sono più controlli da fare e più parametri di ingresso da valutare, l'ho postata parzialmente proprio per fare più in fretta, ed essendo poi alle prime armi con le funzione e procedure su MySQL ho preferito fare un passo per volta!

Per r1cky: Quindi dovrei secondo te provare con una procedura anziché una funzione?
Solo una cosa, vedo che hai definito delle HANDLER per le varie eccezioni sollevata al cui interno viene fatto un CALL di una procedure, ERROR_IS_HAPPENED(), tale procedura dovrebbe servire per segnalare l'errore giusto? Alla fine potrei semplicemente settare al variabile di OUT ad uno specifico errore, dipendente dal tipo di eccezione, e quindi avere un ritorno in tempo reale dello stato dell'esecuzione. Giusto?

Un ultima considerazione, certo che è strana questa cosa che nelle procedure si può usare la TRANSACTION mentre nelle function e nei trigger no...

Provo un po a fare come suggerito da r1cky e vi aggiorno!
Saluti, Carlo.

[r1cky`]

Per r1cky: Quindi dovrei secondo te provare con una procedura anziché una funzione? Solo una cosa, vedo che hai definito delle HANDLER per le varie eccezioni sollevata al cui interno viene fatto un CALL di una procedure, ERROR_IS_HAPPENED(), tale procedura dovrebbe servire per segnalare l'errore giusto? Alla fine potrei semplicemente settare al variabile di OUT ad uno specifico errore, dipendente dal tipo di eccezione, e quindi avere un ritorno in tempo reale dello stato dell'esecuzione. Giusto?

La funzione ERROR_IS_HAPPENED() è una funzione che non è stata definitita intenzionalmente e serve a fare in modo che in un modo o in un altro venga sollevato un errore. Se a te non serve questo tipo di approcio puoi fare come hai giustamente intuito, ovvero assegnare alla variabile in OUT un certo valore per segnalare l'errore al chiamante.

Un ultima considerazione, certo che è strana questa cosa che nelle procedure si può usare la TRANSACTION mentre nelle function e nei trigger no...

Come ti dicevo nel post precedente mysql ha diversi punti deboli, e questa carenza è uno di essi.

[orion91]

Perfetto r1cky non potevi essere più chiaro di così!

Faccio subito oggi pomeriggio qualche test seguendo il tuo input e vedo quali sono i risultati!

Saluti, Carlo.