寻找包装在用户定义的 SQL 函数中包装的外部 SQL 存储过程中的任何 OS/400 API 的工作示例

目前有两个问题：

1) 下面的示例使用外部 SQL 存储过程包装 OS/400 API，该存储过程是 SQL 用户定义的表函数中的进一步包装器，编译和运行都没有错误，但当传递“*”作为作业名称（即当前作业）时，它会返回作业信息的空白和零。任何有关原因的提示将不胜感激。注意：如果我传递了一个不存在的作业，QUSRJOBI api 会正确抛出错误，因此代码的行为部分正确。如果我传递正确的活动作业名称、作业用户和作业编号，则不会发生错误，但仍返回空白和零。我已经为 RECEIVER_VARIABLE 尝试了 CHAR(85) 和 VARCHAR(85) 。接下来我将尝试对 RECEIVER_VARIABLE 使用 BINARY(85)，但从 BINARY 转换回 CHAR 和 INT 返回列可能会很困难。

2) 某些 OS/400 API 参数要求使用数据结构，而 System i 上 V7R1 的 DB2 SQL 尚未直接支持这些数据结构（即尚未直接支持结构化类型）。然而，本文 http://www-01.ibm.com/support/docview.wss?uid=nas8N1017493说它们可以使用 BINARY 字符串来实现，但没有提供示例:(。经过大量搜索，我无法找到仅使用 SQL 对象包装 OS400 api 的示例。如果有人有任何关于如何仅使用 SQL 形成由 CHAR 和其他数据类型（尤其是 INT）混合组成的 BINARY 字符串的示例，请发布一个。API 错误代码参数是一个通常需要此参数的示例。我已注释掉 ERROR_CODE 相关代码，因为如果重新激活该代码，它会生成错误 CPF3CF1“错误代码参数无效”。如果有人知道 ERROR_CODE 二进制字符串数据结构的形成方式有什么问题，请告诉我。我已经尝试了 ERROR_CODE 结构的 CHAR(16) 和 BINARY(16) 。我已经测试了如何形成 ERROR_CODE 将结果转储到表中的当前技术，并在十六进制模式下使用 DSPPFM 查看表结果使其看起来像“binary( hex( ERROR_CODE_BYTES_PROVIDED ) )”等工作正常。但是，我错过了一些东西。

我知道有很多使用 RPG 来包装 OS/400 api 的示例，但我想将这些包装器仅保留为 SQL 代码。

create or replace procedure M_GET_JOB_INFORMATION
               ( out    OUT_RECEIVER_VARIABLE            char(85)
                ,in     IN_LENGTH_OF_RECEIVER_VARIABLE   int
                ,in     IN_FORMAT_NAME                   char(8)
                ,in     IN_QUALIFIED_JOB_NAME            char(26)
                ,in     IN_INTERNAL_JOB_IDENTIFIER       char(16)
           --     ,inout  INOUT_ERROR_CODE                 binary(16)  
               )

  program type main
  external name QSYS/QUSRJOBI

  parameter style general
  not deterministic
  modifies SQL data
  specific M_JOBINFO
  set option dbgview   = *source
            ,commit    = *nc
            ,closqlcsr = *endmod
            ,tgtrls    = V7R1M0 
;


create or replace function M_GET_JOB_INFORMATION_BASIC
                   ( IN_JOB_NAME                 varchar(10)
                    ,IN_JOB_USER                 varchar(10)
                    ,IN_JOB_NUMBER               varchar(6)
                    ,IN_INTERNAL_JOB_IDENTIFIER  varchar(16)
                   )
  returns table( JOB_NAME                 char(10)
                ,JOB_USER                 char(10)
                ,JOB_NUMBER               char(6)
                ,INTERNAL_JOB_IDENTIFIER  char(16)
                ,JOB_STATUS               char(10)
                ,JOB_TYPE                 char(1)
                ,JOB_SUBTYPE              char(1)
                ,RUN_PRIORITY             int
                ,TIME_SLICE               int
                ,DEFAULT_WAIT             int
                ,ELIGIBLE_FOR_PURGE       char(10)
               )

  language SQL
  specific M_JOBINFBF
  not deterministic
  disallow parallel
  no external action
  modifies SQL data
  returns null on null input
  not fenced

  set option dbgview   = *source
            ,commit    = *nc
            ,closqlcsr = *endmod
            ,tgtrls    = V7R1M0
     --       ,output    = *PRINT

begin

declare RECEIVER_VARIABLE             char(85)      default '';          --receives "JOBI0100" format output from API
declare LENGTH_OF_RECEIVER_VARIABLE   int           default 85;          --length of "JOBI0100" Format
declare FORMAT_NAME                   char(8)       default 'JOBI0100';  --basic job information
declare QUALIFIED_JOB_NAME            char(26);
declare INTERNAL_JOB_IDENTIFIER       char(16);
declare ERROR_CODE                    binary(16);  

--ERROR_CODE "ERRC0100" Format:
declare ERROR_CODE_BYTES_PROVIDED               int         default 8;    --Size of API Error Code data structure passed to API
declare ERROR_CODE_BYTES_RETURNED               int         default 0;    --Number of exception data bytes returned by the API
declare ERROR_CODE_EXCEPTION_ID                 char(7)     default '';   --Exception / error message ID returned by the API
declare ERROR_CODE_RESERVED                     char(1)     default '';   --Reserved bytes
declare ERROR_CODE_EXCEPTION_DATA               char(1)     default '';   --Exception data returned by the API

if IN_INTERNAL_JOB_IDENTIFIER = '' then 
   set QUALIFIED_JOB_NAME = char( IN_JOB_NAME, 10 ) || char( IN_JOB_USER, 10 ) || char( IN_JOB_NUMBER, 6 );
   set INTERNAL_JOB_IDENTIFIER = '';
else  
   set QUALIFIED_JOB_NAME = '*INT';
   set INTERNAL_JOB_IDENTIFIER = IN_INTERNAL_JOB_IDENTIFIER;   
end if;

set ERROR_CODE = binary( hex( ERROR_CODE_BYTES_PROVIDED ) ) ||  
                 binary( hex( ERROR_CODE_BYTES_RETURNED ) ) ||  
                 binary( ERROR_CODE_EXCEPTION_ID ) || 
                 binary( ERROR_CODE_RESERVED ) 
          --  ||    binary( ERROR_CODE_EXCEPTION_DATA )
;  

call M_GET_JOB_INFORMATION
        ( RECEIVER_VARIABLE             --out
         ,LENGTH_OF_RECEIVER_VARIABLE   --in
         ,FORMAT_NAME                   --in
         ,QUALIFIED_JOB_NAME            --in
         ,INTERNAL_JOB_IDENTIFIER       --in
      --   ,ERROR_CODE                    --in/out  --Results in error CPF3CF1 "Error code parameter not valid" if code line reactivated
        );

return values( char( substr( RECEIVER_VARIABLE,  8, 10 ), 10 )      --JOB_NAME
              ,char( substr( RECEIVER_VARIABLE, 18, 10 ), 10 )      --JOB_USER
              ,char( substr( RECEIVER_VARIABLE, 28,  6 ),  6 )      --JOB_NUMBER
              ,char( substr( RECEIVER_VARIABLE, 28, 16 ), 16 )      --INTERNAL_JOB_IDENTIFIER
              ,char( substr( RECEIVER_VARIABLE, 50, 10 ), 10 )      --JOB_STATUS
              ,char( substr( RECEIVER_VARIABLE, 60,  1 ),  1 )      --JOB_TYPE
              ,char( substr( RECEIVER_VARIABLE, 61,  1 ),  1 )      --JOB_SUBTYPE
              ,case when substr( RECEIVER_VARIABLE, 64, 4 ) = ''
                    then 0
                    else int( substr( RECEIVER_VARIABLE, 64, 4 ) )
               end                                                  --RUN_PRIORITY
              ,case when substr( RECEIVER_VARIABLE, 68, 4 ) = ''
                    then 0
                    else int( substr( RECEIVER_VARIABLE, 68, 4 ) )
               end                                                  --TIME_SLICE
              ,case when substr( RECEIVER_VARIABLE, 72, 10 ) = ''
                    then 0
                    else int( substr( RECEIVER_VARIABLE, 72, 4 ) )
               end                                                  --DEFAULT_WAIT
              ,char( substr( RECEIVER_VARIABLE, 76, 10 ), 10 )      --ELIGIBLE_FOR_PURGE
             )
;

end    
;
select * from table( M_GET_JOB_INFORMATION_BASIC( '*', '', '', '' ) ) as JOB_INFO
;

我在 i 6.1 上使用它来调用 QDBRTVFD API：

CREATE PROCEDURE SQLEXAMPLE.DBRTVFD ( 
    INOUT FD       CHAR(1024) , 
    IN    SZFD     INTEGER , 
    INOUT RTNFD    CHAR(20) , 
    IN    FORMAT   CHAR(8) , 
    IN    QF       CHAR(20) , 
    IN    "RCDFMT" CHAR(10) , 
    IN    OVRPRC   CHAR(1) , 
    IN    SYSTEM   CHAR(10) , 
    IN    FMTTYP   CHAR(10) , 
    IN    ERRCOD   CHAR(8) ) 
    LANGUAGE CL 
    SPECIFIC SQLEXAMPLE.DBRTVFD 
    NOT DETERMINISTIC 
    NO SQL 
    CALLED ON NULL INPUT 
    EXTERNAL NAME 'QSYS/QDBRTVFD' 
    PARAMETER STYLE GENERAL ;

首先，默认的是LANGUAGE C，您可能不希望 QUSRJOBI 这是一个 OPM 程序。在这里，CL 语言参数传递可能是可预测性的更好选择。

另外，您可能想将其设置为NO SQL而不是modifies SQL data因为您没有修改 SQL 数据。可能需要删除SET OPTION以便将事情降到最低限度。

如果您对 M_GET_JOB_INFORMATION 过程进行这些更改，请查看它是否返回有用的值。如果没有，我们可以更深入地挖掘。

对于您的特定 API，我使用此代码在 i 6.1 上测试结果：

CREATE PROCEDURE SQLEXAMPLE.M_GET_JOB_INFORMATION ( 
    INOUT OUT_RECEIVER_VARIABLE CHAR(85) , 
    IN IN_LENGTH_OF_RECEIVER_VARIABLE INTEGER , 
    IN IN_FORMAT_NAME CHAR(8) , 
    IN IN_QUALIFIED_JOB_NAME CHAR(26) , 
    IN IN_INTERNAL_JOB_IDENTIFIER CHAR(16) , 
    IN IN_ERROR_CODE CHAR(8) ) 
    LANGUAGE CL 
    SPECIFIC SQLEXAMPLE.M_JOBINFO 
    NOT DETERMINISTIC 
    NO SQL 
    CALLED ON NULL INPUT 
    EXTERNAL NAME 'QSYS/QUSRJOBI' 
    PARAMETER STYLE GENERAL ;

一个基本的包装器是这样创建的：

CREATE PROCEDURE SQLEXAMPLE.GENRJOBI ( 
    INOUT JOBI       VARCHAR(85) , 
    IN       QJOB      VARCHAR(26) ) 
    LANGUAGE SQL 
    SPECIFIC SQLEXAMPLE.GENRJOBI 
    NOT DETERMINISTIC 
    MODIFIES SQL DATA 
    CALLED ON NULL INPUT 
    SET OPTION  ALWBLK = *ALLREAD , 
        ALWCPYDTA = *OPTIMIZE , 
        COMMIT = *NONE , 
        DBGVIEW = *LIST , 
        CLOSQLCSR = *ENDMOD , 
        DECRESULT = (31, 31, 00) , 
        DFTRDBCOL = *NONE , 
        DLYPRP = *NO , 
        DYNDFTCOL = *NO , 
        DYNUSRPRF = *USER , 
        RDBCNNMTH = *RUW , 
        SRTSEQ = *HEX   
    P1 : BEGIN 
DECLARE JOBII CHAR ( 85 ) ; 
DECLARE SZJOBI INTEGER ; 
DECLARE FORMATI CHAR ( 8 ) ; 
DECLARE QJOBI CHAR ( 26 ) ; 
DECLARE JOBIDI CHAR ( 16 ) ; 
DECLARE ERRCODI CHAR ( 8 ) ; 
DECLARE STKCMD CHAR ( 10 ) ; 

SET JOBII = X'00000000' ; 
SET SZJOBI = 85 ;
SET FORMATI = 'JOBI0100' ; 
SET QJOBI = QJOB ; 
SET JOBIDI = '                ' ; 
SET ERRCODI = X'0000000000000000' ; 
SET STKCMD = '*LOG' ; 

CALL SQLEXAMPLE . M_GET_JOB_INFORMATION ( JOBII , SZJOBI ,  FORMATI , QJOBI , JOBIDI , ERRCODI ) ; 
CALL SQLEXAMPLE . LOGSTACK ( STKCMD ) ; 

SET JOBI = JOBII ; 

END P1  ;

包装器仅提供调用 API 过程的示例。它对 API 返回的结构不执行任何操作，只是将其传回给调用者。您最初的问题包括从结构中提取子字段的代码，因此我认为没有必要在此处放置类似的代码。

这两个过程在 iNav 的“运行 SQL 脚本”中进行了测试，以获取有关我正在运行的交互式作业的信息，结果如下所示：

输出区域显示字符结构，可以看到整数子字段与字符子字段混合在一起。根据需要解构结构。我可能会创建一个额外的过程，它将结构作为输入并返回单独的结构元素。