Problems with cursors

[Former Member]

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

[VolkerBarth]

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...

[Former Member]

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 😞

[VolkerBarth]

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?

[Former Member]

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

[Breck_Carter]

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.

[VolkerBarth]

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...