Hi, everyone. I have been following the problem discussed here last week about
suspend on stored procedures. The thing is that I thought I was doing things
right, but after what I am going to explain here, I am in serious doubt.

I have a web page dynamically generated using Php. Here is the code that calls
the stored procedure NEW_GROUP:

[..]
$stmt = "SELECT P_RESULT FROM NEW_GROUP (?, ?, ?, ?, ?)";
$sth = ibase_query ($dbh, $stmt, $current_edition, $P_PARCOD, md5 ($P_PASSWD),
$P_GRPNAM, md5 ($P_CMNPSW));
$row = ibase_fetch_object ($sth);
ibase_free_result ($sth);
if ($row->P_RESULT == 0) [..]

I mean, "the same as always". And here is the stored procedure:

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

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. */
SUSPEND;
END
ELSE
BEGIN
P_RESULT = 1;
SUSPEND;
END
WHEN ANY DO
P_RESULT = 2;
END
{**}
END

Sorry for the comments in Spanish: I have left them so that if someone here
speaks Spanish, then they will be useful to him or her.

Well, the thing is that this procedure is very similar to another one I have,
called NEW_PARTICIPANT, and which, in fact, works fine. My problem is that if
I leave the code like this I get no error, but no row appears on table
GROUPS. But if I add a SUSPEND statement before the last end, where the {**}
appears, then an exception is caught by the WHEN ANY DO statement.

Any hints? Meanwhile I'll check foreign key constraints (cascade both) just in
case they are f*****g with me.

Thanks in advance.

P.S. I am very confused about using SUSPEND statement (where and when). I
mean, I know that I must use EXIT when the stored procedure is executed, and
SUSPEND when selected (which is my case). But I am not sure whether I am
doing things right.


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

"Ubi sapientas ibi libertas"