Subject | Stored procedure, ExecSQL and Suspend |
---|---|
Author | Marco Menardi |
Post date | 2002-11-02T16:01:36Z |
Stored procedure, ExecSQL and Suspend
Since I had a problem with a stored procedure returning a value after performing some checks that was not working until I added a "suspend" at the end, I now have some doubts about IBO syntax and Suspend use.
My rule was:
Stored procedure that returns (potentially) multiple rows (selectable stored procedure):
IBO: call them with a select inside IB_Query or IB_Cursor
SP: add a SUSPEND at the end of the code that provides each data row
Stored procedure that returns only return variables:
IBO: call them with a ExecSQL inside a IB_StoredProc
SP: SUSPEND is to be avoided (see Firebird documentation)
(like in the sample in GSG Working the Ropes -> Editing subchapter)
But now seems that something like:
CREATE PROCEDURE GET_CONTO_DIFFERENZIA_GR (
IN_CONTO_ID VARCHAR (6),
IN_CONDOMINIO_ID VARCHAR (6),
IN_ESERCIZIO_ID VARCHAR (6),
IN_DIFF_GRUPPO_RIPARTO CHAR (1))
RETURNS (
OUT_DIFFERENZIA CHAR (1))
AS
DECLARE VARIABLE w_CONTO_ID VARCHAR(6);
DECLARE VARIABLE w_DIFF_GRUPPO_RIPARTO CHAR (1);
BEGIN
OUT_DIFFERENZIA = IN_DIFF_GRUPPO_RIPARTO;
IF (IN_DIFF_GRUPPO_RIPARTO='E') THEN
BEGIN
w_CONTO_ID=substr(IN_CONTO_ID, 1, 2 );
SELECT DIFFERENZIA_GRUPPO_RIPARTO
FROM PIANO_CONTI
WHERE (ESERCIZIO_ID=:IN_ESERCIZIO_ID) AND
(CONDOMINIO_ID=:IN_CONDOMINIO_ID) AND
(CONTO_ID=:w_CONTO_ID)
INTO
:w_DIFF_GRUPPO_RIPARTO;
IF (IN_DIFF_GRUPPO_RIPARTO='E') THEN
OUT_DIFFERENZIA = w_DIFF_GRUPPO_RIPARTO;
END
IF ((OUT_DIFFERENZIA='E') OR (OUT_DIFFERENZIA IS NULL)) THEN
OUT_DIFFERENZIA='N';
SUSPEND; /* ****************************** */
END
Needs the final suspend...
The question is:
What component must I use in IBO for retrieving output values from this kind of SP? What method do I have to call (i.e. ExecSQL, Open...)?
Jason once told about ExecSQL "That method is what you call to execute a non-select SQL statement", is this EXECUTE PROCEDURE GET_CONTO_DIFFERENZIA_GR in my IB_StoredProcedure to be considered a "select" statement? What is the general rule?
Sometime I feel so radically confused...
Thanks a lot
Marco Menardi
Since I had a problem with a stored procedure returning a value after performing some checks that was not working until I added a "suspend" at the end, I now have some doubts about IBO syntax and Suspend use.
My rule was:
Stored procedure that returns (potentially) multiple rows (selectable stored procedure):
IBO: call them with a select inside IB_Query or IB_Cursor
SP: add a SUSPEND at the end of the code that provides each data row
Stored procedure that returns only return variables:
IBO: call them with a ExecSQL inside a IB_StoredProc
SP: SUSPEND is to be avoided (see Firebird documentation)
(like in the sample in GSG Working the Ropes -> Editing subchapter)
But now seems that something like:
CREATE PROCEDURE GET_CONTO_DIFFERENZIA_GR (
IN_CONTO_ID VARCHAR (6),
IN_CONDOMINIO_ID VARCHAR (6),
IN_ESERCIZIO_ID VARCHAR (6),
IN_DIFF_GRUPPO_RIPARTO CHAR (1))
RETURNS (
OUT_DIFFERENZIA CHAR (1))
AS
DECLARE VARIABLE w_CONTO_ID VARCHAR(6);
DECLARE VARIABLE w_DIFF_GRUPPO_RIPARTO CHAR (1);
BEGIN
OUT_DIFFERENZIA = IN_DIFF_GRUPPO_RIPARTO;
IF (IN_DIFF_GRUPPO_RIPARTO='E') THEN
BEGIN
w_CONTO_ID=substr(IN_CONTO_ID, 1, 2 );
SELECT DIFFERENZIA_GRUPPO_RIPARTO
FROM PIANO_CONTI
WHERE (ESERCIZIO_ID=:IN_ESERCIZIO_ID) AND
(CONDOMINIO_ID=:IN_CONDOMINIO_ID) AND
(CONTO_ID=:w_CONTO_ID)
INTO
:w_DIFF_GRUPPO_RIPARTO;
IF (IN_DIFF_GRUPPO_RIPARTO='E') THEN
OUT_DIFFERENZIA = w_DIFF_GRUPPO_RIPARTO;
END
IF ((OUT_DIFFERENZIA='E') OR (OUT_DIFFERENZIA IS NULL)) THEN
OUT_DIFFERENZIA='N';
SUSPEND; /* ****************************** */
END
Needs the final suspend...
The question is:
What component must I use in IBO for retrieving output values from this kind of SP? What method do I have to call (i.e. ExecSQL, Open...)?
Jason once told about ExecSQL "That method is what you call to execute a non-select SQL statement", is this EXECUTE PROCEDURE GET_CONTO_DIFFERENZIA_GR in my IB_StoredProcedure to be considered a "select" statement? What is the general rule?
Sometime I feel so radically confused...
Thanks a lot
Marco Menardi