Problema con SP (Stored Procedure)

[Punchi]

Saludos!

He estado atrapado durante horas y horas en el mismo problema... para que vean de que trata:

Código:

/*==================================================*/
create table user
(
   ID_USER              int not null auto_increment,
   FIRSTNAME            varchar(50) default NULL,
   LASTNAME             varchar(50) default NULL,
   primary key (ID_USER)
)
type = InnoDB;

/*==================================================*/
create table student
(
   ID_USER              int not null,
   ADVTYPE              varchar(50),
   primary key (ID_USER)
)
type = InnoDB;

/*==================================================*/

alter table student add constraint FK_USER_STUDENT foreign key (ID_USER)
      references user (ID_USER) on delete restrict on update restrict;

No tengo idea como crear un procedimiento almacenado que me permita registrar los datos de manera automatica. Un pseudocodigo que NO funciona:

Código:

USE rania
DELIMITER //

DROP PROCEDURE IF EXISTS `test`//

CREATE PROCEDURE `test`()

BEGIN;
INSERT INTO user (FIRSTNAME, LASTNAME) VALUES (`nombre`, `apellido`);
INSERT INTO student (ID_USER, ADVTYPE) VALUES (SELECT LAST ID_USER,`asd`);
COMMIT;

END//

Como podran ver, necesito seleccionar el "ultimo ID_USER" para poder realizar la operacion... alguien sabe como?

[gnzsoloyo]

Bueno, en el texto del SP se alcanzan a ver tres cosas:
1. Estás haciendo una inserción en una tabla... pero no has pasado los parámetros al SP, por lo que las variables estarán siempre vacías.
2. Estás llamando a un SELECT para obtener el valor del ultimo ID insertado, cuando ya existe una función de MySQL que te puede dar ese datos en un SP.
3. Estas intentando realizar una operación de actualización a una tabla secundaria en cada INSERT llamado. En sí, puede funcionar, pero estás desaprovechando la potencia del motor, porque una tarea como esa, y que debe hacerse SIEMPRE que haya un INSERT es mejor dejársela a un TRIGGER, y no a un SP.
Además de eso, estás incluyendo un COMMIT, pero no estás iniciando la transacción en ninguna parte (el BEGIN es el inicio del procedimiento, no de la transacción, que debería comenzar con START TRANSACTION;).
Para el caso, el SP debería tener un texto al estilo:

Código:

DELIMITER $$

CREATE PROCEDURE `test`(IN @nombre VARCHAR(50), 
   IN @apellido VARCHAR(50), 
   IN @asd VARCHAR(50))

BEGIN;
DECLARE idUser INT;
INSERT INTO user (FIRSTNAME, LASTNAME) 
            VALUES (@nombre, @apellido);
SET idUser = LAST_INSERT_ID();
INSERT INTO student (ID_USER, ADVTYPE) 
            VALUES (idUser, asd);
END $$

DELIMITER ;

[Punchi]

Gracias gnzsoloyo!

Si, me fijé que me faltan los parámetros, de echo, los insert que tengo que realizar son un poco mas grandes y son mas tablas, pero para ejemplificar puse sólo eso.

Los segundos dos puntos no tenia idea. Me ha costado encontrar informacion sobre procedimientos almacenados para MySQL (y utilizarlos mediante PHP). Quisiera saber si conoces algun recurso en internet para un mayor conocimiento.

Por ultimo estimado, sé que lo correcto son los triggers, pero desconosco la manera de hacerlos funcionar mediante el "BEGIN" y "COMMIT" ya que necesito comprobar que se realizen bien TODAS las inserciones y con mis conocimiento en triggers puedo realizarlas solo de a una.

Una solucion era comprobar cada trigger y si fallaba alguno crear un "DELETE" para las anteriores, pero claramente no es lo mejor...

Si puedes ayudarme con eso te lo agradeceria nuevamente

[Punchi]

Novedades!

Código:

DELIMITER $$

DROP PROCEDURE IF EXISTS `rania`.`SP_ADD_STUDENT` $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `SP_ADD_STUDENT`(firstname VARCHAR(50), lastname VARCHAR(50), email VARCHAR(50), address VARCHAR(50), city VARCHAR(50), state VARCHAR(50), zip VARCHAR(50), phone VARCHAR(50), altphone VARCHAR(50), paydate DATE, advtype VARCHAR(50), id_mpay INT, amount INT, date DATE, id_tmember INT, start_date DATE, end_date DATE, id_tlesson INT, purchased INT, remaining INT)
BEGIN
DECLARE idUser INT;
DECLARE a INT;

INSERT INTO user (FIRSTNAME, LASTNAME, EMAIL, ADDRESS, CITY, STATE, ZIP, PHONE, ALTPHONE, CREATED)
	VALUES (firstname, lastname, email, address, city, state, zip, phone, altphone, CURDATE() );

SET idUser = LAST_INSERT_ID();

INSERT INTO student (ID_USER, PAYDATE, ADVTYPE, ACTIVEINACTIVE)
	VALUES (idUser, paydate, advtype, 1);
INSERT INTO student_mpayment (ID_USER, ID_MPAY, AMOUNT, DATE)
	VALUES (idUser, id_mpay, amount, date);
INSERT INTO student_tmembership (ID_USER, ID_TMEMBER, START_DATE, END_DATE)
	VALUES (idUser,id_tmember,start_date,end_date);
INSERT INTO student_tlesson (ID_USER, ID_TLESSON, PURCHASED, REMAINING)
	VALUES (idUser, id_tlesson, purchased, remaining);

SET a=(SELECT @@error_count);
IF a=0 THEN
COMMIT;
ELSE
ROLLBACK;
END IF;

END $$

DELIMITER ;

Puedo ejecutar perfectamente el SP con esta llamada

Código:

CALL SP_ADD_STUDENT("minombre", "apellido", "[email protected]", "porahi", "santiago", "aasdads", "123456", "12456", "12313132", "2002-01-01", "loalzo", 1, 123, "2004-05-03", 1, "2008-03-01", "2008-05-01", 1, 4, 5)
;

El problema? NO toma ni el COMMIT ni el ROLLBACK

Existe la asociacion llamada "student_mpayment" por ejemplo, que relaciona "student" con "method_payment". El problema es que estoy señalandole a la asociacion que me relacione "id_mpay = 1" de "method_payment" y claramente esta ultima entidad no posee datos y me arroja el error de integridad.

Como anteriormente expuse, el problema es que no hace el ROLLBACK y en la base de datos graba de todas manera informacion en "user" y en "student"

Que estoy haciendo mal???

[gnzsoloyo]

1. Las tablas referenciadas SIEMPRE se cargan primero. Si el dato del campo dependiente no está en la tabla a que hace referencia siempre te va a dar un error porque ese dato debe existir primero. No pueden colocarse al mismo tiempo.
2. No has colocado la línea de START TRANSACTION; por lo que la transacción jamás ha iniciado y la operación se halla en estado AUTOCOMMIT. Mira bien el manual de referencia: 13.4.1. Sintaxis de START TRANSACTION, COMMIT y ROLLBACK

[gpardo]

mmm... estaba buscando otra cosa... , pero van algunas sugerencias:

1. un trigger lo puedes crear con una sintaxis parecida a estas:

Código:

DROP TRIGGER `nombre_trigger`$$
CREATE TRIGGER `nombre_trigger` AFTER INSERT ON `nombre_tabla`
FOR EACH ROW BEGIN
   ... por aquí lo que necesites ...
END$$

2. digamos que php no tiene nada que ver con los triggers / stored procedures. tú armas los disparadores / funciones/ procedimientos que necesitas y MySQL los maneja. desde php lo único que puedes hacer es tirarle consultas a la base de datos, sean estas de tipo "SELECT * FROM ....", "INSERT INTO ..." o "CALL tu_funcion(param1, ..., paramN)"

3. desde php tú sólo debes:
a. mandar la consulta que inicie la transacción: "START TRANSACTION"
b. mandar la consulta con el insert (mysql se encargará del disparador)
c. mandar la consulta que cierre la transacción iniciada: "COMMIT"

4. NO se puede usar ROLLBACK desde un disparador, he escrito una manera de emular eso en: http://gustavopardo.com.ar/node/696. Esto producirá un error que impedirá que se ejecute el COMMIT por lo que ninguna modificación se hará efectiva en la bbdd.

[Punchi]

Gracias gpardo, pero no te entendi ni el punto 3 ni el 4

3) Esto significa mandarlo por separado? las tres? =S
4) Miré tu ejemplo y claramente arroja un error... pero de que me sirve? como lo puedo insertar?

Gracias! ^_^

[gnzsoloyo]

Punchi:
Existen dos formas de manejar la trnasacción:
1. A nivel SP, después de declarar las variables, cursores y Handlers, se debe colocar la sentencia "START TRANSACTION;", de lo contrario estás intentando hacer un COMMIT o un ROLLBACK sin que exista el thread de transacción.
2. A nivel de aplicación (PHP en este caso), como dice Gpardo,
a) Iniciar la transacción con una sentencia la server que la inicie ("START TRANSACTION")
b) Llamar al store procedure, de modo que te devuelva un valor que te indique si terminó bien o no.
c) Según sea la devolución del SP, mandarle al server COMMIT o ROLLBACK.

[Punchi]

jajaja gnzsoloyo, gracias por seguir ayudandome, de echo, ese problema te lo comento en este post http://www.forosdelweb.com/f86/no-fu...commit-572802/, pero ya no hay apuro ^_^

Bien! , gracias, por fin me funciona el código

Código:

DELIMITER $$

DROP PROCEDURE IF EXISTS `SP_ADD_CLIENT` $$
CREATE PROCEDURE `SP_ADD_CLIENT`(firstname VARCHAR(50), lastname VARCHAR(50), email VARCHAR(50))

BEGIN

DECLARE idUser INT;
DECLARE a INT;

SET AUTOCOMMIT=0;
START TRANSACTION;

INSERT INTO CLIENT (FIRSTNAME, LASTNAME, EMAIL) VALUES (firstname, lastname, email);

SET idUser = LAST_INSERT_ID();

INSERT INTO STUDIO (ID_CL, NAME_ST)
	VALUES (idUser, name_st);

INSERT INTO ME_EQUIVOCO (ID_CL, ID_PL, ADDED)
	VALUES (idUser, 1, CURDATE());

SET a=(SELECT @@error_count);
IF a=0 THEN
COMMIT;
ELSE
ROLLBACK;
END IF;

END $$

Claramente no existe la tabla "ME_EQUIVOCO" y me arroja un error el phpmyadmin al momento de hacer un

Código:

CALL SP_ADD_CLIENT("firstname", "lastname", "email");

todo bien, cuando ocurre el error ya no sigue insertandome los datos tanto en "CLIENT" como en "STUDIO" y sólo lo hace cuando no existen errores

gracias!!!!