sql anywhere cursor dilemma

• fizzleme

  SSCertifiable

  Points: 6906

  More actions

  December 9, 2018 at 3:07 pm

  #376590

  This code runs but  displays one result and stops.  The table has about 40 rows.     I'm a rookie at sql anywhere and haven't done much with cursors.   something obvious I'm sure.
  BTW this is sql anywhere version 11 running on a windows 2008 r2 server.   Help please.   BTW the sql select runs correctly when run independently.

  declare @xray varchar(40)
  /* declare a cursor for the select from sysobjects*/
  declare curs cursor for
      select convert(varchar(40),o.name)
  from sysobjects o
  where type = 'U' and uid = 1
  order by o.name
  /* open the cursor */
  open curs

  /* fetch the first row */
  fetch curs into @xray/* now loop, processing all the rows
  ** @@sqlstatus = 0 means successful fetch
  ** @@sqlstatus = 1 means error on previous fetch
  ** @@sqlstatus = 2 means end of result set reached
  */
  while (@@sqlstatus != 2)
  begin   
      /* check for errors */
      if (@@sqlstatus = 1)
      begin
          print "Error in select"
          return
      end
   select @xray
      /* fetch the next row */
      fetch curs into @xray
  end

  /* close the cursor and return */
  close curs
  return

  When the snows fall and the white winds blow,The lone wolf dies but the pack survives.

  Once you've accepted your flaws, no one can use them against you.

• Eirikur Eiriksson

  SSC Guru

  Points: 182911

  More actions

  December 10, 2018 at 12:08 am

  #2015847

  fizzleme - Sunday, December 9, 2018 3:07 PM

  This code runs but  displays one result and stops.  The table has about 40 rows.     I'm a rookie at sql anywhere and haven't done much with cursors.   something obvious I'm sure.
  BTW this is sql anywhere version 11 running on a windows 2008 r2 server.   Help please.   BTW the sql select runs correctly when run independently.

  declare @xray varchar(40)
  /* declare a cursor for the select from sysobjects*/
  declare curs cursor for
      select convert(varchar(40),o.name)
  from sysobjects o
  where type = 'U' and uid = 1
  order by o.name
  /* open the cursor */
  open curs

  /* fetch the first row */
  fetch curs into @xray/* now loop, processing all the rows
  ** @@sqlstatus = 0 means successful fetch
  ** @@sqlstatus = 1 means error on previous fetch
  ** @@sqlstatus = 2 means end of result set reached
  */
  while (@@sqlstatus != 2)
  begin   
      /* check for errors */
      if (@@sqlstatus = 1)
      begin
          print "Error in select"
          return
      end
   select @xray
      /* fetch the next row */
      fetch curs into @xray
  end

  /* close the cursor and return */
  close curs
  return

  Use @@fetch_status rather than @@sqlstatus!
  😎

  Here is an SQL Server T-SQL example of a cursor that does the same, should work on Sql Anywhere.

  
USE TEEST;
GO
SET NOCOUNT ON;
-- USE THE CORRECT DATA TYPE FOR SYSNAME
DECLARE @OBJ_NAME NVARCHAR(128) = N'';

  -- LIGHTWEIGHT READONLY CURSOR
DECLARE R_SET CURSOR FAST_FORWARD FOR
SELECT
  SOB.[name]
FROM  sys.objects   SOB
WHERE SOB.type   = 'U'
AND  SOB.schema_id = 1
ORDER BY SOB.[name] ASC;

  -- RUN THROUGH THE CURSOR SET UNTIL @@FETCH_STATUS <> 0
OPEN R_SET;
FETCH NEXT FROM R_SET INTO @OBJ_NAME;
WHILE @@FETCH_STATUS = 0
BEGIN
  RAISERROR(@OBJ_NAME,0,0) WITH NOWAIT;
FETCH NEXT FROM R_SET INTO @OBJ_NAME;
END

  -- POST-MORTEM
IF @@FETCH_STATUS = -1 RAISERROR(N'fetch operation unsuccessful.' ,0,0) WITH NOWAIT;
IF @@FETCH_STATUS = -2 RAISERROR(N'Value reserved for future use.',0,0) WITH NOWAIT;

  -- CLEANUP
CLOSE R_SET;
DEALLOCATE R_SET;