将光标用于多个搜索条件 [英] 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.

谢谢！亲切的问候，杰斯珀

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屋！