Hi,

I am very confusing of use the cursor with FETCH FIRST or FETCH NEXT.

I have this two stored procedure:

******
PROCEDURE "prErogazioneProdottiInErrore" (IN "Magazzino" INTEGER, IN "Utente" INTEGER)
BEGIN
 DECLARE _id INTEGER;
 DECLARE _box VARCHAR(5);
 DECLARE _Posizione INTEGER;
 DECLARE _codiceProdotto VARCHAR(20);
 DECLARE selectCursor CURSOR FOR selectStmt;
 PREPARE selectStmt FROM 'SELECT
     MagazzinoProdotti.ID,
     MagazzinoProdotti.BOX,
     MagazzinoProdotti.Posizione,
     MagazzinoProdotti.CodiceProdotto
   FROM
     MagazzinoProdotti
   WHERE
     (MagazzinoProdotti.IDMagazzino = ?) AND (MagazzinoProdotti.Stato = ''E'')';
 CALL prEmptyErogazioneCorrente();
 OPEN selectCursor USING Magazzino;
 FETCH FIRST FROM selectCursor (ID, BOX, Posizione, CodiceProdotto) INTO _id, _box, _posizione, _codiceProdotto;
 WHILE NOT EOF(selectCursor) DO
   CALL prInsertErogazioneCorrente(Utente, _id, Magazzino, NULL, 10, NULL, _codiceProdotto, NULL, NULL, _box, _posizione, False, NULL, NULL);
   FETCH FIRST FROM selectCursor (ID, BOX, Posizione, CodiceProdotto) INTO _id, _box, _posizione, _codiceProdotto;
 END WHILE;
 CLOSE selectCursor;
 UNPREPARE selectStmt;
END

*******

PROCEDURE "prErogazioneProdottiInScadenza" (IN "Magazzino" INTEGER, IN "Utente" INTEGER)
BEGIN
 DECLARE _id INTEGER;
 DECLARE _box VARCHAR(5);
 DECLARE _Posizione INTEGER;
 DECLARE _codiceProdotto VARCHAR(20);
 DECLARE selectCursor CURSOR FOR selectStmt;
 DECLARE nGiorniAvvisi INTEGER;
 SET nGiorniAvvisi = GiorniAnticipoAvvisoProdottiInScadenza();
 PREPARE selectStmt FROM 'SELECT
     MagazzinoProdotti.ID,
     MagazzinoProdotti.BOX,
     MagazzinoProdotti.Posizione,
     MagazzinoProdotti.CodiceProdotto
   FROM
     MagazzinoProdotti
   WHERE
     (MagazzinoProdotti.IDMagazzino = ?) AND ((MagazzinoProdotti.DataScadenza - CURRENT_DATE) DAY <= ?)';
 CALL prEmptyErogazioneCorrente();
 OPEN selectCursor USING Magazzino, nGiorniAvvisi;
 FETCH FIRST FROM selectCursor (ID, BOX, Posizione, CodiceProdotto) INTO _id, _box, _posizione, _codiceProdotto;
 WHILE NOT EOF(selectCursor) DO
   CALL prInsertErogazioneCorrente(Utente, _id, Magazzino, NULL, 9, NULL, _codiceProdotto, NULL, NULL, _box, _posizione, False, NULL, NULL);
   FETCH NEXT FROM selectCursor (ID, BOX, Posizione, CodiceProdotto) INTO _id, _box, _posizione, _codiceProdotto;
 END WHILE;
 CLOSE selectCursor;
 UNPREPARE selectStmt;
END

*******

These two stored procedures are similar. The only difference are the type of record that are managed.

My problem is in the managed of the cursor:

In first stored proc I nedd to use the FETCH FIRST for correctly execution of the procedure while in the second stored proc I need to use the FETCH NEXT for correctly execution of the procedure.

What is the reason that forces me to use FETCH FIRST in the first case and in the second FETCH NEXT?

I forgot:

I use the 2.03B11 unicode version

Thank you very much

Best Regards

Claudio

Claudio


Its difficult to tell exactly since some of your code is "hidden" behind a CALL, but my guess would be that in the first case you have a sensitive result set are altering something in the WHERE clause eg

    (MagazzinoProdotti.IDMagazzino = ?) AND (MagazzinoProdotti.Stato = ''E'')';

and in the second case any changes you make aren't affecting the WHERE clause eg

     (MagazzinoProdotti.IDMagazzino = ?) AND ((MagazzinoProdotti.DataScadenza - CURRENT_DATE) DAY <= ?)';


Roy Lambert [Team Elevate]

Roy,

thank you very much for your answer.

The call procedure is this:

PROCEDURE "prInsertErogazioneCorrente" ( IN "pIDUtente" INTEGER, IN "pIDMagazzinoProdotti" INTEGER, IN "pIDMagazzino" INTEGER, IN "pIDReparto" INTEGER, IN "pIDTipoMovimento" INTEGER, IN "pIDPrenotazione" INTEGER, IN "pCodiceProdotto" VARCHAR(20), IN "pQuantitaRichiesta" INTEGER, IN "pQuantitaErogata" INTEGER, IN "pBOX" VARCHAR(5), IN "pPosizione" INTEGER, IN "pProcessato" BOOLEAN, IN "pBOXRaster" VARCHAR(5), IN "pPosizioneRaster" INTEGER)
BEGIN
 DECLARE updateSTMT STATEMENT;
 if pIDReparto > 0 THEN
   PREPARE updateSTMT FROM 'INSERT INTO ErogazioneCorrente (
     IDUtente,
     IDMagazzinoProdotti,
     IDMagazzino,
     IDReparto,
     IDTipoMovimento,
     IDPrenotazione,
     CodiceProdotto,
     QuantitaRichiesta,
     QuantitaErogata,
     BOX,
     Posizione,
     Processato,
     BOXRaster,
     PosizioneRaster)
   VALUES (
     ?,
     ?,
     ?,
     ?,
     ?,
     ?,
     ?,
     ?,
     ?,
     ?,
     ?,
     ?,
     ?,
     ?)';
   EXECUTE updateSTMT USING
     pIDUtente,
     pIDMagazzinoProdotti,
     pIDMagazzino,
     pIDReparto,
     pIDTipoMovimento,
     pIDPrenotazione,
     pCodiceProdotto,
     pQuantitaRichiesta,
     pQuantitaErogata,
     pBOX,
     pPosizione,
     pProcessato,
     pBOXRaster,
     pPosizioneRaster;
 ELSE
   PREPARE updateSTMT FROM 'INSERT INTO ErogazioneCorrente (
     IDUtente,
     IDMagazzinoProdotti,
     IDMagazzino,
     IDTipoMovimento,
     IDPrenotazione,
     CodiceProdotto,
     QuantitaRichiesta,
     QuantitaErogata,
     BOX,
     Posizione,
     Processato,
     BOXRaster,
     PosizioneRaster)
   VALUES (
     ?,
     ?,
     ?,
     ?,
     ?,
     ?,
     ?,
     ?,
     ?,
     ?,
     ?,
     ?,
     ?)';
   EXECUTE updateSTMT USING
     pIDUtente,
     pIDMagazzinoProdotti,
     pIDMagazzino,
     pIDTipoMovimento,
     pIDPrenotazione,
     pCodiceProdotto,
     pQuantitaRichiesta,
     pQuantitaErogata,
     pBOX,
     pPosizione,
     pProcessato,
     pBOXRaster,
     pPosizioneRaster;
 END IF;
 UNPREPARE updateSTMT;
END


and this insert an record in another table.

I need to loop over all records in the cursor and for every record insert an new record in the table ErogazioneCorrente whit call the stored proc     

CALL prInsertErogazioneCorrente(Utente, _id, Magazzino, NULL, 10, NULL, _codiceProdotto, NULL, NULL, _box, _posizione, False, NULL, NULL);

In the first stored proc the loop of all record is possible with the FETCH FIRST. If I call FETCH NEXT only the odd record are processed.


Thank you very much

Claudio

Claudio


>In the first stored proc the loop of all record is possible with the FETCH FIRST. If I call FETCH NEXT only the odd record are processed.

There are only two ways that is possible.

The first is the one I've suggested that you have a sensitive cursor and the elements that make up the WHERE clause are being affected so as to take a row out of the selection. Maybe I'm being baffled by the names but I can't see anywhere where you UPDATE, DELETE or do anything to MagazzinoProdotti to cause this. Somewhere, somehow the table is being affected. There could be a trigger or some other process updating the table as you are working.

A couple of things to try:

1. declare the cursor as INSENSITIVE
2. if possible wrap the procedure in a restricted transaction

The second one way it could happen would be a bug in the engine, but that would affect both procedures so I'm pretty certain its not the case here.

Roy Lambert [Team Elevate]

Roy

THANK YOU VERY MUCH!!!!!!!

You're absolutely right!!. The "problem" is an trigger!

sorry, I was convinced that the cursor was insensitive by default and this is the reason why I did not understand the behavior

Your explanation has opened my eyes!

THANKS!!!

Claudio

Claudio


No problem. I've been caught by this behaviour many times with tables so I know what to look for these days

Roy Lambert

Roy,

<< There are only two ways that is possible. >>

Nicely done.

--
Tim Young
Elevate Software
www.elevatesoft.com