 | | | strange call DBPROC behaviour | strange call DBPROC behaviour 2006-08-23 - By Xavier CAMIER
Back
MaxDB 7.6.00.12
Hello again,
Here is a strange behaviour of MaxDB when calling a DBPROC from an
another DBPROC with calculations into parameters.
Eg :
/********************************/
MyVar INTEGER;
MyVar=128;
Call MyProc(:MyVar+1,:MySecVar);
/********************************/
The workaround is easy to guess but we lost a lot of time looking for
our mistake...
Kind Regards
Xavier
Here is an example. Our goal was to avoid recursivity in using the
left/right bound method.
/* creates the result test table */
CREATE TABLE "DEV"."AAA"
(
"ID" Integer NOT NULL DEFAULT SERIAL (1),
"TEXTE" Varchar (255) ASCII,
PRIMARY KEY ("ID")
)
/* creates the called DBPROC */
CREATE DBPROC AFFAIRE_COPIE_TOUVR4 (
IN LIGNE_BG INTEGER, OUT LBD FIXED(10)) AS
VAR
RC INTEGER;
TMP FIXED(20,0);
BG INTEGER;
BD INTEGER;
POSIT INTEGER;
IDTLIGNE INTEGER;
TRY
RC = 0;
LBD = 0;
POSIT = LIGNE_BG;
/*calcul de ligne_BG et ligne BD */
BG = POSIT;
BD = POSIT+1;
INSERT INTO DEV.AAA (TEXTE) VALUES ('dans TOUVR 1
LIGNE_BG='&CHR(:LIGNE_BG)&' BG ='&CHR(:BG)&' et BD ='&CHR(:BD));
LBD = BD;
INSERT INTO DEV.AAA (TEXTE) VALUES ('dans TOUVR 2 BG ='&CHR(:BG)&' et
BD ='&CHR(:BD));
CATCH
RC = $RC;
IF RC <> 0 THEN STOP (RC, 'Unexpected error');
/* Creates the calling DBPROC */
CREATE DBPROC AFFAIRE_COPIE_TOUVR2TEST (IN LIGNE_BG INTEGER) AS
VAR
RC INTEGER;
BG INTEGER;
BD INTEGER;
POSIT INTEGER;
I INTEGER;
TRY
POSIT = LIGNE_BG;
/*calcul de ligne_BG et ligne BD */
BG = POSIT;
BD = POSIT+1;
I = 0;
TRY
WHILE I < 8 DO
BEGIN
/*calcul de ligne_BG et ligne BD */
SET BG = POSIT;
SET BD = BG+1;
INSERT INTO DEV.AAA (TEXTE) VALUES ('BG ='&CHR(:BG)&' et BG+1
='&CHR(:BG+1)&' et BD ='&CHR(:BD));
CALL DEV.AFFAIRE_COPIE_TOUVR4 (:BG+1, :BD);
SET POSIT = BD;
SET BD = BD+1;
I = I + 1 ;
END;
CATCH
IF $RC <> 100 THEN STOP ($RC, 'Impossible d''enregistrer l''?l?ment
[AFFAIRE_COPIETOUVR2]');
CATCH
RC = $RC;
IF RC <> 0 THEN STOP (RC, 'Unexpected error');
Usage from SQLStudio :
CALL AFFAIRE_COPIE_TOUVR2TEST(126)
Results into AAA show that everything works fine. BG and BD values are
increased as they should.
Now try :
CALL AFFAIRE_COPIE_TOUVR2TEST(127)
BG and BD are increased by a 10 step when their values = 129. Strange.
Workaround :
When moving the :BG+1 calculation outside the CALL
DEV.AFFAIRE_COPIE_TOUVR4 (:BG+1, :BD) statement, everything works fine.
Regards.
--
MaxDB Discussion Mailing List
For list archives: http://lists.mysql.com/maxdb
To unsubscribe: http://lists.mysql.com/maxdb?unsub=mysql@(protected)
|
|
|