Declare cursor is not executing

Question

I have converted one RPGLE program to SQLRPGLE, i did that many times. strange issues i am facing this time. (1) SQLCODE setting up to 077952576 as soon as program executing, not sure why. (2) when i am running in debug Declare cursor statement is not getting executed, nut other following sql statement getting hit.

  Exec Sql                                                   
     DECLARE My_cursor CURSOR for                           
     select * FROM File1    where FLAG <> 'Y' 
     Order by Field1, Field2, Field3, Field4, Field5;

Please advise, Thanks

Answer 1

DECLARE CURSOR is not an executable statement. It's a compile time statement.

You shouldn't be checking SQLCODE/SQLSTATE after it...

Take a look at this program...

**FREE
ctl-opt main(mymain);
ctl-opt option(*srcstmt);
ctl-opt datfmt(*ISO) timfmt(*ISO);
ctl-opt cvtopt(*NODATETIME);

dcl-proc MyMain;
  dcl-s company char(3);
  dcl-s part    char(25);
  dcl-s desc    char(30);
  dcl-s msg     char(50);
  dcl-s selComp char(3);
  dcl-s myTimestamp timestamp;

  exec sql
    set option
       datfmt=*ISO, timfmt=*ISO;

  selComp = 'A06';
  exsr OpenCursor;
  exsr FetchData;
  exec SQL close C1;

  selComp = 'A15';
  exsr OpenCursor;
  exsr FetchData;
  exec SQL close C1;

  *INLR = *ON;
  return;

  begsr DeclareCursor;
     exec SQL
       declare C1 cursor for
          select pmco#, pmpart, pmdesc
           from pdpmast
           where pmco# = :selComp;
  endsr;

  begsr OpenCursor;
    exec SQL open C1;
  endsr;

  begsr FetchData;
    exec sql fetch next from C1 into :company, :part, :desc;
    msg = company + ':' + part + ':' + desc;
    dsply msg;
  endsr;
end-proc;

Note that this is a working (example) program. But the DeclareCursor subroutine is never actually called! It doesn't need to be, even though a host variable is used in the WHERE clause.

The value of the host variable is use when the OPEN is executed.

Also note that in embedded SQL as shown above, SET OPTION is a compile time statement. In fact, there can only be a single SET OPTION statement in the entire source member and it must be the first SQL statement that the pre-compiler sees.