Different results of dbproc returning cursor in 7.5. and 7.6 2006-10-18 - By Elke Schmidtke
Back
Hello all,
In our production system we use version 7.5.0.19.
Same days ago I made an update of my development system to 7.6.0.32.
Now I got a problem with a procedure.
The procedure returns a cursor.
If no data are returned on 7.5.0.19 I get the message ?No result?,
on 7.6.0.32 ?General error;-4024 POS(1) ? is produced.
I can avoid the error by making a better (safer) code as shown in the
following text.
But it is a hard work tosearch through all procedures and triggers for
a similar problem.
Elke
CREATE DBPROC PROC_DBV_SCHULEN (IN PNR INT) RETURNS CURSOR AS
VAR BEG1 DATE; END1 DATE; ID1 INT; BEG2 DATE; END2 DATE; ID2 INT;
TRY
DELETE FROM TEMP.DBV_SCHULEN;
CATCH
CREATE TABLE TEMP.DBV_SCHULEN (BEGINN DATE, ENDE DATE, SCHUL_ID
INT);
/* If temporary table is empty after this select, the error *4024 is
produced
To avoid it I added try-cath */
TRY
INSERT INTO TEMP.DBV_SCHULEN
SELECT BEGINN, ENDE, SCHUL_ID FROM DBA.T_DBV
WHERE PERS_NR = :PNR;
CATCH
DECLARE :$CURSOR CURSOR FOR
SELECT 'keine' AS Schule FROM SYSDBA.DUAL;
DECLARE CC CURSOR
FOR SELECT * FROM TEMP.DBV_SCHULEN
ORDER BY BEGINN FOR REUSE;
FETCH CC INTO :BEG1, :END1, :ID1;
WHILE $RC = 0 DO
BEGIN
FETCH CC INTO :BEG2, :END2, :ID2;
IF $RC = 100 THEN
BREAK;
IF ID1 = ID2 AND ADDDATE(END1,1) = BEG2 THEN
BEGIN
UPDATE TEMP.DBV_SCHULEN
SET ENDE = :END2 WHERE SCHUL_ID = :ID1 AND ENDE :END1;
DELETE FROM TEMP.DBV_SCHULEN
WHERE SCHUL_ID = :ID1 AND BEGINN = :BEG2;
END;
SET BEG1 = BEG2;
SET END1= END2;
SET ID1=ID2;
END;
DECLARE :$CURSOR CURSOR FOR
SELECT BEGINN, ENDE, CHR(SCHUL_NR,6)&', '&SCHULNAME&' '&ORT& ' ' &
DECODE (CASE WHEN SCHLIESSDATUM ='2099-12-31' THEN NULL
ELSE SCHLIESSDATUM END , NULL , ' ',
'(Schlie?ung:'&CHR(DAY(SCHLIESSDATUM
))&'.'&CHR(MONTH(SCHLIESSDATUM ))&'.'&CHR(YEAR(SCHLIESSDATUM ))&')')
AS SCHULE
FROM TEMP.DBV_SCHULEN, DBA.T_SCHULE
WHERE TEMP.DBV_SCHULEN.SCHUL_ID = T_SCHULE.SCHUL_ID
ORDER BY BEGINN DESC;
--
MaxDB Discussion Mailing List
For list archives: http://lists.mysql.com/maxdb
To unsubscribe: http://lists.mysql.com/maxdb?unsub=mysql@(protected)
|
|
