Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB SQL » View Thread |
Messages 1 to 8 of 8 total |
Cursor and FETCH FIRST - FETCH NEXT problem |
Wed, Apr 28 2010 6:55 AM | Permanent Link |
Claudio Piffer | 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? |
Wed, Apr 28 2010 6:57 AM | Permanent Link |
Claudio Piffer | I forgot:
I use the 2.03B11 unicode version Thank you very much Best Regards Claudio |
Wed, Apr 28 2010 7:53 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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] |
Wed, Apr 28 2010 8:20 AM | Permanent Link |
Claudio Piffer | 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 |
Wed, Apr 28 2010 9:06 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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] |
Wed, Apr 28 2010 9:17 AM | Permanent Link |
Claudio Piffer | 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 |
Wed, Apr 28 2010 9:58 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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 |
Wed, Apr 28 2010 4:18 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Roy,
<< There are only two ways that is possible. >> Nicely done. -- Tim Young Elevate Software www.elevatesoft.com |
This web page was last updated on Thursday, May 23, 2024 at 03:39 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |