在多个搜索条件下使用光标 [英] Using cursor for multiple search conditions

问题描述

首先，我想为我的英语不好而道歉，并且有可能我错过了一些重要信息给你们。

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.

我在 mcpressonine论坛上找到了一个帖子，有个男人和我完全一样的问题，他确实以某种方式解决了这个问题，但是我真的不理解他的所作所为-除了将声明游标放在我也做过的子过程中-没有成功。

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

如果有人解释他做了什么。也许这家伙的解决方案不再有效，因为他的职位已经10岁了。
告诉我信息是否足够，我将一路纠正。

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.

谢谢！
此致Jesper

Thanks! Kind regards, Jesper

推荐答案

要记住的是 DECLARE 语句不可执行。这是一个编译时声明。 PREPARE 和 OPEN 是可执行的。

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.

下面是一个完全使用动态SQL的程序，请注意，实际上从未调用 DeclareCursor 子例程。重要的是执行 PREPARE 语句时在gSqlStmt中的语句。

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;

除了没有错误处理外，上面的方法还包含直接连接输入变量selComp的错误做法，变成声明。由于SQL注入攻击，在任何语言中都不是一个好主意。

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.

下面是使用参数标记的更好版本。请注意，该语句不再需要更改。所以我只需要准备一次。记录的选择取决于 OPEN ... USING ... 语句被调用时selComp的值。

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;

但是，这里实际上并不需要动态SQL。带有宿主变量的静态语句可以正常工作。使用静态SQL，我不需要任何 PREPARE ，也不需要在 OPEN 上指定selComp。所有这些都是在编译时自动完成的。

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;

这篇关于在多个搜索条件下使用光标的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！