How to stop while loop on cursor?

[alfer]

I want to go through all records from a cursor in a while loop but I don't know how to stop this loop I have read in my book I shall use SQLSTATE but how? Below is my code. I'm trying to check SQLSTATE S1000 but I still receive error :
ODBC Error: SQLSTATE = S1000, Native error code = -5004
No data found.

I run this procedure in the Pervasive Control Center.

CREATE PROCEDURE MyProc() AS
BEGIN

 DECLARE MyCursor CURSOR FOR SELECT MyField FROM MyTable;
  
 DECLARE :vField char(1);
 DECLARE :vEnd   int;
 SET :vEnd=0; 
 
 OPEN MyCursor;
 FETCH NEXT FROM MyCursor INTO :vField;

 WHILE (:vEnd=0) DO

   PRINT :vField;
   FETCH NEXT FROM MyCursor INTO :vField;

   IF (SQLSTATE='S1000')THEN
     SET :vEnd=1; 
   END IF;
  
 END WHILE;

 CLOSE MyCursor;

END;

 

[mirtheil]

From the Pervasive manual:

CREATE TABLE atable (c1 INT, c2 INT)#
 INSERT INTO atable VALUES (1,1)#
 INSERT INTO atable VALUES (1,2)#
 INSERT INTO atable VALUES (2,2)#
 INSERT INTO atable VALUES (2,3)#
 INSERT INTO atable VALUES (3,3)#
 INSERT INTO atable VALUES (3,4)#
 
CREATE PROCEDURE pp (); 
 BEGIN 
   DECLARE :i INTEGER; 
   DECLARE c1Bulk CURSOR FOR SELECT c1 FROM atable ORDER BY c1 FOR UPDATE; 
   OPEN c1Bulk; 
     BulkLinesLoop: 
     LOOP  
       FETCH NEXT FROM c1Bulk INTO :i; 
        IF SQLSTATE = '02000' THEN 
          LEAVE BulkLinesLoop; 
        END IF;  
       UPDATE  SET c1 = 10 WHERE CURRENT OF c1Bulk; 
     END LOOP; 
     CLOSE c1Bulk; 
 END#
  
CALL pp #
	--Succeeds 
SELECT * FROM atable #
	--Returns 6 rows

WHile not exactly like what you are doing, it does show the usage of SQLSTATE.

info@mirtheil.com
Custom VB and Btrieve development.
Certified Pervasive Developer
Certified Pervasive Technician

 

[alfer]

Is this manual accessible in PDF format? I have Pervasive for Linux and I cannot find any manual in PDF. I have only book about installing Pervasive and help.

 

[mirtheil]

All of the manuals are available in PDF and CHM, according to Pervasive's web site (http://www.pervasive.com/support/technical/psqlv8_docs.asp).

info@mirtheil.com
Custom VB and Btrieve development.
Certified Pervasive Developer
Certified Pervasive Technician

 

[alfer]

Thank You

The solution was good, but my Pervasive... was not patched. After installing Service Pack 4 all problems disappeared.
Thanks