寻找包装在外部 SQL 存储过程中的任何 OS/400 API 的工作示例，该存储过程包装在用户定义的 SQL 函数中 [英] Looking for a working example of any OS/400 API wrapped in an external SQL stored procedure wrapped in a user defined SQL function
问题描述
目前有两个问题:
1) 下面用外部 SQL 存储过程包装 OS/400 API 的示例，该存储过程进一步包装在 SQL 用户定义的表函数中，编译和运行都没有错误，但它返回空白和零为作业名称(即当前作业)传递*"时的作业信息.任何有关原因的提示将不胜感激. 注意:如果我传递了一个不存在的作业，QUSRJOBI api 会正确地抛出一个错误，因此代码的行为部分正确.如果我传递正确的活动作业名称、作业用户和作业编号，则不会发生错误，但仍会返回空白和零.我已经为 RECEIVER_VARIABLE 尝试了 CHAR(85) 和 VARCHAR(85).接下来我将针对 RECEIVER_VARIABLE 尝试 BINARY(85)，但从 BINARY 转换回 CHAR 和 INT 返回列可能会很困难.
1) The below example of wrapping an OS/400 API with an external SQL stored procedure which is further wrapper in a SQL user defined table function both compiles and runs without error, but it returns blanks and zeroes for the job information when passing '*' for the job name (i.e. current job). Any tips on why would be appreciated. Note: If I pass a non-existent job, the QUSRJOBI api correctly throws an error, so the code is behaving partially correct. If I pass a correct active job name, job user, and job number, no error occurs but blanks and zeroes are still returned. I've tried both CHAR(85) and VARCHAR(85) for RECEIVER_VARIABLE. I'll try BINARY(85) next for RECEIVER_VARIABLE, but converting from BINARY back to CHAR and INT return columns might prove difficult.
2) 一些 OS/400 API 参数要求使用数据结构，System i 上 V7R1 的 DB2 SQL 尚不直接支持(即尚不直接支持结构化类型).但是，这篇文章 说它们可以使用 BINARY 字符串来实现，但没有提供示例:(.经过广泛搜索，我找不到仅使用 SQL 对象包装 OS400 api 的示例.如果有人有任何示例说明如何使用 SQL 形成 BINARY 字符串仅由 CHAR 和其他数据类型(尤其是 INT)的混合组成，请发布一个. API 错误代码参数是通常需要的示例.我将 ERROR_CODE 相关代码注释掉，因为它会生成如果重新激活该代码，则会出现错误 CPF3CF1错误代码参数无效".如果有人能说出 ERROR_CODE 二进制字符串数据结构的形成方式有什么问题，请告诉我.我试过了CHAR(16) 和 BINARY(16) 都用于 ERROR_CODE 结构.我已经测试了当前的 ERROR_CODE 是如何形成的技术将结果放入表格中，并在十六进制模式下使用 DSPPFM 查看表格结果使其看起来像二进制(十六进制(ERROR_CODE_BYTES_PROVIDED))"等工作正常.但是，我错过了一些东西.
2) Some OS/400 API parameters call for using data structures, which DB2 SQL at V7R1 on the System i does not yet directly support (i.e. no direct support yet for structured types). However, this article says they can be implemented using BINARY strings, but does not provide an example :(. After extensive searching, I've not been able to find an example of wrapping an OS400 api using ONLY SQL objects. If anyone has any examples of how to form a BINARY string using SQL only that is comprised of a mixture of CHAR and other data types like especially INT, please post one. The API error code parameter is an example where this is commonly needed. I have the ERROR_CODE related code commented out since it generates error CPF3CF1 "Error code parameter not valid" if that code is reactivated. If anyone can tell what is wrong with how the ERROR_CODE binary string data structure is being formed, please let me know. I've tried both CHAR(16) and BINARY(16) for the ERROR_CODE structure. I've tested the current technique of how ERROR_CODE is being formed dumping the results into a table, and viewing the table results using DSPPFM in hex mode makes it look like the "binary( hex( ERROR_CODE_BYTES_PROVIDED ) )" etc. is working correctly. However, I'm missing something.
我知道有很多使用 RPG 来包装 OS/400 api 的示例，但我只想将这些包装器保留为 SQL 代码.
I'm aware that there are lots of examples of using RPG to wrap OS/400 api's, but I want to keep these wrappers as SQL code only.
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:
I use this on i 6.1 to call the 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，对于作为OPM 程序的QUSRJOBI，您可能不希望这样.在这里，CL 语言参数传递可能是更好的可预测性选择.
First, the default is LANGUAGE C, and you probably don't want that for QUSRJOBI which is an OPM program. CL-language parameter passing can be a better choice for predictability here.
此外，您可能希望将其设置为 NO SQL 而不是 modifying SQL data，因为您没有修改 SQL 数据.可能需要删除 SET OPTION 以将事情降到最低.
Also, you probably want to set this as NO SQL rather than modifies SQL data since you aren't modifying SQL data. It might be necessary to remove the SET OPTION in order to get things down to the minimum.
如果您对 M_GET_JOB_INFORMATION 过程进行了这些更改，请查看它是否返回有用的值.如果没有，我们可以深入挖掘.
If you make those changes for your M_GET_JOB_INFORMATION procedure, see if it returns useful values. If it doesn't, we can dig a little deeper.
对于您的特定 API，我使用此代码在 i 6.1 上测试结果:
For your particular API, I used this code to test results on 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 ;
一个基本的包装器是这样创建的:
A basic wrapper was created like so:
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 返回的结构不做任何处理.您最初的问题包括从结构中提取子字段的代码位，所以我认为在这里放置类似代码没有意义.
The wrapper only provides an example of calling the API proc. It does nothing with the returned structure from the API except pass it back out to its caller. Your original question included bits of code to extract sub-fields from a structure, so I didn't see a point to putting similar code here.
这两个过程在 iNav 的运行 SQL 脚本"中进行了测试，以获取有关我正在运行的交互式作业的信息，结果如下所示:
The two procs were tested in iNav's 'Run SQL Scripts' to grab info about an interactive job I was running, and the result looked like this:
输出区域以字符显示结构，可以看到整数子字段与字符子字段混合.根据需要解构结构.我可能会创建一个额外的过程，将结构作为输入并返回单个结构元素.
The output area shows the structure in characters, and the integer sub-fields can be seen mixed with character sub-fields. Deconstruct the structure as needed. I might create an additional proc that takes the structure as input and returns individual structure elements.
这篇关于寻找包装在外部 SQL 存储过程中的任何 OS/400 API 的工作示例，该存储过程包装在用户定义的 SQL 函数中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
