Johann Horvat wrote :
>When I'm executing my DBPROC defined by
>CREATE DBPROC CREATE_NEW_MONTH(IN DATE_ DATE) AS
>VAR CREATED_ROWS INTEGER; LAST_DAY_OF_DATE INTEGER; EMPPK FIXED (15);
> SET CREATED_ROWS = 0;
> SELECT GET_LAST_DAY(:DATE_) INTO :LAST_DAY_OF_DATE FROM SYSDBA.DUAL;
> DECLARE c1 CURSOR FOR
> SELECT PK FROM KAR.VALID_USERS;
> WHILE ($rc = 0) DO
> BEGIN
> FETCH c1 INTO :EMPPK;
> INSERT INTO KAR.MONTHS (DATE_OF_MONTH, EMP_FK, LAST_DAY_OF_MONTH)
> VALUES (:DATE_, :EMPPK, :LAST_DAY_OF_DATE);
> END;
> CLOSE c1;
>(the 'SELECT PK FROM KAR.VALID_USERS' returns 6 rows!)
>by a simple
> CALL CREATE_NEW_MONTH('2005-05-01')
>my SQLStudio (my MAXDB Instance) seems to work a lot for a while and
>then crashes.
>The only wrong line seems to be the INSERT INTO KAR.MONTHS[...]. By
>removing it everything works nice.
>What am I doing wrong or where is my fault...
It seems that a missing check of the $rc variable after the fetch statement causes the
problem.
If table KAR.MONTH has no key definition, this results in an endless loop. Please
correct the
procedure as follows :
CREATE DBPROC CREATE_NEW_MONTH(IN DATE_ DATE) AS
VAR CREATED_ROWS INTEGER; LAST_DAY_OF_DATE INTEGER; EMPPK FIXED (15);
SET CREATED_ROWS = 0;
SELECT GET_LAST_DAY(:DATE_) INTO :LAST_DAY_OF_DATE FROM SYSDBA.DUAL;
DECLARE c1 CURSOR FOR
SELECT PK FROM KAR.VALID_USERS;
WHILE ($rc = 0) DO
BEGIN
FETCH c1 INTO :EMPPK;
IF $rc = 0
THEN
INSERT INTO KAR.MONTHS (DATE_OF_MONTH, EMP_FK, LAST_DAY_OF_MONTH)
VALUES (:DATE_, :EMPPK, :LAST_DAY_OF_DATE);
END;
CLOSE c1;
Best Regards,
Thomas
--
Thomas Anhaus
SAP DB, SAP Labs Berlin
[EMAIL PROTECTED]
http://www.sapdb.org/
_______________________________________________
sapdb.general mailing list
[EMAIL PROTECTED]
http://listserv.sap.com/mailman/listinfo/sapdb.general
--
MaxDB Discussion Mailing List
For list archives: http://lists.mysql.com/maxdb
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
