SAP SQL Anywhere

Question

Hi,

Am new to Sybase IQ 16.0 database(moving from postgresql). Am trying to create a cursor and fetch data. These are the commands I tried:

prompt>declare curr1 CURSOR FOR select * from test1;

prompt>open curr1;

But am getting the error "Cursor has not been declared" , SQLCODE=-170 ODBC3 state ="24000"?

My doubt is,Can I create a cursor and use it outside the function ? Or, in Sybase there is no way to declare and use cursor outside functions directly?

Machine am working is - Red Hat Enterprise Linux Server release 6.4

Database - Sybase IQ 16.0

Interface - dbisql

Any help is much appreciated.

Thanks, Vaish

Answer 1

1

Can't really tell whether this is different in IQ than in SQL Anywhere, but as SQL Anywhere seems to be used as the IQ query/parsing engine, I guess the following quote from the docs applies here, too:

In a Watcom SQL procedure, trigger, or batch, a DECLARE CURSOR statement must appear with other declarations, immediately following the BEGIN keyword.

That is, you can use cursors outside stored functions and stored procedures but you need to put them in a code block, i.e. something like

begin
   declare curr1 cursor for select * from test1;
   open curr1;
   lp: loop
    fetch next curr1 into <...>;
    if sqlcode <> 0 then leave lp end if;
    ...
   end loop;
  close curr1;
end

The same is true when you use the FOR statement that combines declaring, opening and fetching through a cursor in a handy control structure... - Personally, I prefer FOR mostly all the time over an explicit DECLARE/OPEN/FETCH/CLOSE sequence...

permanent link

answered 30 Apr '14, 03:32

Volker Barth
40.5k●365●556●827
accept rate: 34%

edited 30 Apr '14, 03:38

Thanks for your response. But , unfortunately this is not working in dbisql. Infact, begin and then enter, dbisql says syntax error. And the sybase documentation says declare cursor is not present for ISQL. If you know any technique to retrieve one particular record based on row number then please let me know.

Note: I tried LIMIT and OFFSET, but these 2 keywords are not working in dbisql :-(

(01 May '14, 01:09) Vaish

Replies hidden

What tool are you using? AFAIK, DBISQL for Sybase IQ should behave similar to the same tool for SQL Anywhere, and that does not have a "command prompt" but a full SQL statement editor. So you would not have "enter" the BEGIN statement but would execute the whole code block at once.

Are you using any older command-style tool?

(02 May '14, 03:08) Volker Barth

Am using dbisql to connect to db. And, unfortunately it seems, DECLARE CURSOR statement is not supported outside stored procedure in Sybase IQ16.

(08 May '14, 20:55) Vaish

Replies hidden

Try wrapping your code in a BEGIN END block. That is called a "batch" in dbisql-speak... a BEGIN block without the trappings of a stored procedure.

(09 May '14, 08:33) Breck Carter

Vaish seems to have tried that, to cite from his first comment here:

Infact, begin and then enter, dbisql says syntax error.

What has puzzled me, is the "DBISQL prompt" Vaish has cited in this question and that probably seems to be involved in that test with begin, too.

That doesn't look like the DBISQL I'm familiar with - but I don't use IQ, so possibly that tool is a different one...

(09 May '14, 08:56) Volker Barth

flat view

SAP SQL Anywhere

Problems with cursors

Follow this question

Related questions