Using cursor for multiple search conditions

Question

To begin with I want to apologise for my bad english and it's possible that I miss some crucial information for you guys.

Anyways. I am developing a display file with a subfile to show some records. It works like a charm when showing one select but now i want to develop a search-function for the users. So when the user enters different search-conditions the select will change and the cursor must somehow update with the new select. I am ripping my hair off, I really can't get this to work.

I found a post from "mcpressonine"-forum, there is a guy who got the exact same problem as me and he did somehow solve this but I dont really understand what he did - except from putting the declare cursor in a subprocedure which I also did - without success.

Here is a link to his forum post: His post

I would be very thankful if someone explains what he did. Maybe this guys solutions are no longer valid since his post is 10 years old now. Tell me if the information is not enough and I will correct along the way.

Thanks! Kind regards, Jesper

show your code or at least some examples of what you're trying to do. How do you want the statement to change between runs? What error messages do you get? — Charles, Jul 19 '17 at 21:37
The post just builds an sql statement on the fly. Then runs it. I can see the order by clause is changing right off. — danny117, Jul 24 '17 at 16:02

score 1 · Answer 1 · answered Jul 20 '17 at 15:29

The thing to keep in mind is that the DECLARE statement is not executable. It is a compile time declaration. PREPARE and OPEN are executable.

Below is a fully working program that uses dynamic SQL, note that the DeclareCursor subroutine is never actually called. What matters is the statement, in gSqlStmt, at the time the PREPARE statement is executed.

**FREE
ctl-opt main(mymain);
ctl-opt option(*srcstmt);

dcl-c QUOTE const('''');

dcl-s gSqlStmt varchar(500);

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);

  selComp = 'A06';
  gSqlStmt = 'select pmco#, pmpart, pmdesc'
           + ' from pdpmast'
           + ' where pmco# = ' + QUOTE + selComp + QUOTE;

  exsr OpenCursor;
  exsr FetchData;
  exec SQL close C1;

  selComp = 'A15';
  gSqlStmt = 'select pmco#, pmpart, pmdesc'
           + ' from pdpmast'
           + ' where pmco# = ' + QUOTE + selComp + QUOTE;
  exsr OpenCursor;
  exsr FetchData;
  exec SQL close C1;

  *INLR = *ON;
  return;

  begsr DeclareCursor;
     exec SQL
       declare C1 cursor for S1;
  endsr;

  begsr OpenCursor;
    exec SQL prepare S1 from :gSqlStmt;
    exec SQL open C1;
  endsr;

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

Besides not having any error handling, the above contains the bad practice of directly concatenating an input variable, selComp, into a statement. This is not a good idea in any language due to SQL Injection attacks.

A better version that uses parameter markers is below. Note that the statement doesn't need to change any longer. So I only need to prepare it once. The record selection depends on the value of selComp at the time the OPEN ... USING... statement is called.

**FREE
ctl-opt main(mymain);
ctl-opt option(*srcstmt);

dcl-s gSqlStmt varchar(500);

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);

  gSqlStmt = 'select pmco#, pmpart, pmdesc'
           + ' from pdpmast'
           + ' where pmco# = ?';
  exec SQL prepare S1 from :gSqlStmt;

  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 S1;
  endsr;

  begsr OpenCursor;
    exec SQL open C1 using :selComp;
  endsr;

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

However, dynamic SQL isn't actually needed here. A static statement with a host variable will work just fine. With static SQL, I don't need to PREPARE anything, nor specify selComp on the OPEN. All that is done automatically at compile time.

**FREE
ctl-opt main(mymain);
ctl-opt option(*srcstmt);

dcl-s gSqlStmt varchar(500);

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);

  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;

score 0 · Answer 2 · answered Jul 20 '17 at 08:09

You can declare a cursor only one time in an embedded-SQL program or in a stored-procedure. You can also declare multiple cursors. But the SQL statement linked to that cursor can vary over time.

The advice in the referred post remains valid. You can declare the cursor one time (for example in a stored procedure ), but the statement to which the cursor refers can change over time as long as that cursor is first closed (if it was open) and as long as the new SQL-statement is successfully prepared and the cursor then reopened etc.

it remains to be seen if the OP actually needs dynamic SQL. Depending on what's changing, static may work. — Charles, Jul 20 '17 at 13:17

Using cursor for multiple search conditions

2 Answers2

Linked