On Wednesday 28 August 2002 15:32, Svein Erling Tysvaer wrote:

> Your stored procedure doesn't appear to be a typical selectable procedure.
> Have you tried using EXECUTE PROCEDURE rather than SELECT? I think
> selectable stored procedures normally contain a FOR SELECT clause and may
> return several rows, whereas you mostly do delete and insert. You say
> nothing appear in your table - have you remembered to commit? Are you sure
> that you are not hiding any important errors with your statement WHEN ANY?
> I'm sorry to say that my knowledge of Php is even worse than my knowledge
> of Spanish (I think I know between three and five Spanish words), so I
> cannot contribute much.

Hi, Erling, and thanks.

The thing is that I have just woken up from bed since yesterday I went to bed
at 05:00 am after "fighting" with this problem. Not only this but the real
thing is that I finally solved the problem, but now I cannot remember how I
did it. :-D

Maybe after having lunch... X'-D

About using EXECUTE PROCEDURE or SELECT statement, well, I have always used
SELECT statements, but I will probably have a look at EXECUTE soon. Here you
are the final version of my procedure, which is working fine with no need to
start and commit transactions in Php code (it was suposed to be automatically
done, but you know when things go wrong you start trying everything):

---------------------------------------------------
SET TERM ^! ;
CREATE PROCEDURE NEW_GROUP (P_EDICOD SmallInt, P_PARCOD Integer, P_PASSWD
VarChar(255), P_GRPNAM VarChar(50), P_CMNPSW VarChar(255)) returns (P_RESULT
SmallInt) AS
/*
Procedure: NEW_GROUP

Author : Primetime
Date : 27/08/2002 23:47:57
Purpose : To insert any give group for any give edition
*/

DECLARE VARIABLE VAR_COUNT INTEGER;
DECLARE VARIABLE VAR_GRPCOD INTEGER;
BEGIN
BEGIN
P_RESULT = 0;
/* Comprobamos que el número de participante y el password pasados por
parámetro sean válidos para la edición actual. */
SELECT COUNT(PAR_PARCOD)
FROM PARTICIPANTS_EDITION, PARTICIPANTS
WHERE (PAE_PARCOD = PAR_PARCOD)
AND (PAR_PARCOD = :P_PARCOD)
AND (PAR_PASSWD = :P_PASSWD)
INTO :VAR_COUNT;

/* Si hemos encontrado un participante dado de alta en la edición pasada
por parámetro que tenga esa contraseña, procedemos al alta del grupo. */
IF (VAR_COUNT > 0) THEN
BEGIN

/* Borramos aquellos grupos que, para la edición pasada por parámetro,
es participante sea fundador. De esta manera, los miembros que pertenecían
a este grupo quedan huérfanos automáticamente. */
DELETE FROM GROUPS
WHERE (GRP_PARCOD = :P_PARCOD)
AND (GRP_EDICOD = :P_EDICOD);

/* Borramos aquellos grupos de los cuales el participante pasado por
parámetro sea miembro en la edición pasada por parámetro. */
DELETE FROM GROUPS_PARTICIPANTS_EDITION
WHERE (GPE_PARCOD = :P_PARCOD)
AND (GPE_EDICOD = :P_EDICOD);

/* Insertamos un nuevo registro en la tabla GROUPS y la correspondiente
relación padre-hijo en la tabla GROUPS_PARTICIPANTS_EDITION. */
VAR_GRPCOD = GEN_ID(GEN_GRP_GRPCOD, 1);

INSERT INTO GROUPS (GRP_GRPCOD, GRP_GRPNAM, GRP_PASSWD, GRP_EDICOD,
GRP_PARCOD)
VALUES (:VAR_GRPCOD, :P_GRPNAM, :P_CMNPSW, :P_EDICOD, :P_PARCOD);

INSERT INTO GROUPS_PARTICIPANTS_EDITION (GPE_GRPCOD, GPE_PARCOD,
GPE_EDICOD)
VALUES (:VAR_GRPCOD, :P_PARCOD, :P_EDICOD);

/* P_RESULT vale cero en este momento. */
END
ELSE
BEGIN
P_RESULT = 1;
END
WHEN ANY DO
P_RESULT = 2;
END
SUSPEND;
END

^!
SET TERM ; ^!
---------------------------------------------------

As you will notice, there aren't many changes, but the SUSPEND. I believe it
was a matter of the Php + Firebird, but I swear I cannot remember how many
things I changed yesterday night X'-DDD

Hmmmm.... Yes! Yes! Yes! I remember! Now I remember! It was the size of the
password field!!! Yes, it was varchar(20) and it had to be varchar(255) due
to MD5 sum. :-)

P.D.: by the way, I have also noticed that my Firebird Super-Server
installation on Debian Linux (I have not tried Firebird on M$ Windows),
sometimes gets "angry" after hitting some errors. Despite of I correct the
problem, it does not start working fine until I reboot the (Firebird) server,
which is quite an annoying thing for a Linux guy. I mean, it's not to do with
"you forgot to give a value to that variable", but "SUSPEND" and "CONSTRAINTS
checks" things. Anyway, just let me have lunch and I will probably look at
things differently after it :-)

--
Jaume Andreu Sabater Malondra
jsabater@...
Registered linux user #209072

"Ubi sapientas ibi libertas"