[RBASE-L] - Re: Converting from pointer to fetch

[A. Razzak Memon]

At 11:51 AM 6/19/2009, Stas p. Gawel wrote:

I am converting an rbase command file from an older version of rbase to
rb8 but I do not know how to completely convert the pointer command.
This is the old syntax involving the pointer command

SET POINTER #1 STAT1 FOR ICMAIN WHERE +
      INHOUSE = "Y" SORTED BY ITEM#
  WHILE STAT1 = 0 THEN
      SET VAR ITEM# = ITEM# IN #1
      SET VAR COSTLBS = COSTLBS IN #1
      SET VAR VALUE = VALUE IN #1
      SET VAR INWEIGHT = INWEIGHT IN #1
      SET VAR DATERCVD = DATERCVD IN #1
      SET VAR INHOUSE  = INHOUSE IN #1
   NEXT #1 STAT1
  ENDWHILE

This is as far as I got.

DECLARE cursor1 CURSOR FOR SELECT itemnumber, +
iccostlbs,icvalue, inweight, datereceived, inhouse +
      FROM icmain WHERE inhouse CONTAINS 'Y' +
      ORDER BY itemnumber ASC
  WHILE STAT1 = 0 THEN
      FETCH FROM cursor1 INTO itemnumber, iccostlbs, +
icvalue, inweight, datereceived, inhouse
  ENDWHILE

I am having trouble with what STAT1 is and what it should be converted
to. Also, I receive the error message -ERROR- The cursor [cursor1] is
already defined. (708) but I have checked and my syntax appears to be
right for the declare and fetch commands.

Note that some of the coulumn names changed from the older version of
the table and the newer one though the table name is still the same and
that this is not the whole program.

Glad to see you here, Stas!

Nice to see young graduates with 4.0+ GPAs on this list.

First, here is the correct syntax to DECLARE a CURSOR (POINTER as Legacy):

-- start
SET ERROR MESSAGE 705 OFF
DROP CURSOR c1
SET ERROR MESSAGE 705 ON
DECLARE c1 CURSOR FOR SELECT column1, column2,column3 +
FROM [tablename] +
WHERE [clause]
OPEN c1
FETCH c1 INTO +
  vVar1 INDIC ivVar1, +
  vVar2 INDIC ivVar2, +
  vVar3 INDIC ivVar3
WHILE SQLCODE <> 100 THEN
  -- Do what you have to do
FETCH c1 INTO +
  vVar1 INDIC ivVar1, +
  vVar2 INDIC ivVar2, +
  vVar3 INDIC ivVar3
ENDWHILE
DROP CURSOR c1
CLEAR VARIABLES iv%
-- end

So, in your specific case, using your example, note the following:

01. Pre-Define all variables with appropriate data types before
    the WHILE loop.

02. When converting the LEGACY database and command files, make
    sure to update the table/column names to avoid any conflict
    with R:BASE RESERVED words, such as column name "VALUE".

03. Avoid table/column names with special characters, such as "#".

03. Sample Code:

    -- start
    SET VAR vItemNo TEXT = NULL
    SET VAR vCostLbs CURRENCY = NULL
    SET VAR vItemValue CURRENCY = NULL
    SET VAR vDateRcvd DATE = NULL
    SET VAR vInHouse TEXT = NULL
    SET ERROR MESSAGE 705 OFF
    DROP CURSOR c1
    SET ERROR MESSAGE 705 ON
    DECLARE c1 CURSOR FOR SELECT +
    ITEM#,COSTLBS,VALUE,INWEIGHT,DATERCVD,INHOUSE +
    FROM ICMAIN ORDER BY ITEM#
    OPEN c1
    FETCH c1 INTO +
      vItemNo INDIC ivItemNo, +
      vCostLbs INDIC ivCostLbs, +
      vItemValue INDIC ivItemValue, +
      vDateRcvd INDIC ivDateRcvd, +
      vInHouse  INDIC ivInHouse
    WHILE SQLCODE <> 100 THEN
      -- Do what you have to do
    FETCH c1 INTO +
      vItemNo INDIC ivItemNo, +
      vCostLbs INDIC ivCostLbs, +
      vItemValue INDIC ivItemValue, +
      vDateRcvd INDIC ivDateRcvd, +
      vInHouse  INDIC ivInHouse
    ENDWHILE
    DROP CURSOR c1
    CLEAR VARIABLES iv%
    -- End

Hope that helps!

Very Best R:egards,

Razzak.