Executing an SQL statement using an RPG variable as the cursor name

Question

I want to pass cursor and data structure to a procedure, the procedure then populates the structure with the next row from the sql cursor. Is this possible? A template of what I am trying to achive is below.

  *****************************************************
  *
  * Fetch the next row from a cursor
  *
  * @param cursor - the name of the cursor
  * @param structure - the data structure to hold the fields
  *****************************************************
 pfetchNextRow     B
 DfetchNextRow     PI              N
 d cursor                     32767A   varying const
 d structure       DS                  ???????               
  /free 
     exec sql
       fetch next from :cursor  into :structure 
     ;

     if (sqlstate = SQL_SUCCESS);
       return *on;
     else;
       exec sql
         close :cursor;
       return *off;
     endif;

  /end-free
 pfetchNextRow     E

How sould I pass the cursor, and how would I define the data structure parameter?

M4mu5 · Answer 1 · 2011-12-22T11:45:15.693

I Don't know if you've received an answer on the other site, but other can need it.

The cursors exists "GLOBALLY" BUT only in the module where they are declared, you're not in the need to pass it to procedures in this module, the cursor you declared is always available till it's closed or the job ends.

You can do something like this (only if you are in the SAME MODULE):

 P SQLprep_mC      B                   EXPORT
 D SQLprep_mc      PI                  Like(g_retCode)
 D strInSQL                            Like(string_MAX_V)

  /Free
   exec SQL
     SET OPTION
         CLOSQLCSR = *ENDACTGRP;
   exec sql prepare p1 from :strINsql ;
   //.... declare and open
  /End-Free

 P SQLprep_mC      E  



 P SQLfetch_mC_st  B                   EXPORT
 D SQLfetch_mC_st  PI                  Like(g_retCode)
 D    Row                              LikeDs(dsSql_0)
 D    NullI                            Like(sqlNI_0) Dim(DSSql0_nFields)

 D Rowmc_b         DS                  LikeDs(dsSql_0) Based(pNull1)
 D Rowmc           DS                  LikeDs(dsSql_0)
 D pNUll1          s               *   inz(%ADDR(Rowmc))
 D SQLind          s                   Like(sqlNI_0) Based(pNull2)
 D NullImc         S                   like(SQLind) Dim(DSSql_nFields)
 D pNull2          s               *   inz(%ADDR(NullImc))

  /free
     exec SQL fetch next from mC into :Rowmc :NullImc ;
     Row   = Rowmc   ;
     NullI = NullImc ; 
  /end-free

 P SQLfetch_mC_st  E

Those 2 procedures use the same cursor "mC" , and are in the same module. The first prepare, declare and open the cursor, the second fetch the row in RowMC.

As you can see the DS used for the fetch is BASED, the NULLindicator Array too.

All the Like and LikeDS parameter are defined in a copy file as TEMPLATE, like:

 D comfraf       e DS                  extname(comfra00f) QUALIFIED TEMPLATE
 D dsSql_0         DS                  Qualified  TEMPLATE
 D  cid                                like(comfraf.cid      )            
 D  ccap                               like(comfraf.ccap     )            

 D sqlNI_0         s              5I 0 TEMPLATE

Hope this can help someone.

Just want to quickly point out an error in this answer. The DS used for the fetch (`Rowmc`) is not Based. `Rowmc_b` is based, and lays directly on top of `Rowmc`. Using Based was a pre-Template technique to define what is essentially a template as long as you never base it on anything. In your example, `Rowmc_b` and `pNull1` are unnecessary. The same comments apply to the Null Indicator array (`NullImc`) and the structure around it. — jmarkmurphy, Oct 30 '15 at 19:46
thanks @jmarkmurphy, as you can see a 2011 answer, a lto of water has flown under the bridge!!! Thanks for pointing it out, you are really right. At the moment I don't have the way to test a new updated answer, the one posted here is still working but is a little bit redundant, hope to have a way to write and test a new one. Many thanks.For the **BASED** answer is just a misunderstending, I mean the variable to whome the fetch refers to are BASED/templated (based is one of prior-to-6.1 ways to define a TEMPLATE). Right for 'Rowmc_b' and 'pNull1', probabily copyandpast old variables. — M4mu5, Nov 23 '15 at 09:27

score 0 · Answer 2 · answered Jun 15 '11 at 13:25

0

I am not sure that you can dynamically define the cursor. A great place to ask this question is on RPG400-L. There are members of the RPG compiler team on that list who often answers questions like this.

answered Jun 15 '11 at 13:25

Mike Wills

20,959
28
93
149

Thanks, I registered with them – jax Jun 15 '11 at 22:51

Executing an SQL statement using an RPG variable as the cursor name

2 Answers2