Question Re: Problems with cursors in Technology Q&A

Problems with cursors

Former Member — Wed, 30 Apr 2014 01:50:23 GMT

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

Re: Problems with cursors

VolkerBarth — Wed, 30 Apr 2014 03:32:41 GMT

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

Re: Problems with cursors

Former Member — Thu, 01 May 2014 01:09:46 GMT

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 😞

Re: Problems with cursors

VolkerBarth — Fri, 02 May 2014 03:08:49 GMT

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?

Re: Problems with cursors

Former Member — Thu, 08 May 2014 20:55:34 GMT

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

Re: Problems with cursors

Breck_Carter — Fri, 09 May 2014 08:33:42 GMT

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.

Re: Problems with cursors

VolkerBarth — Fri, 09 May 2014 08:56:49 GMT

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