寻找包装在用户定义的SQL函数中的外部SQL存储过程中的任何OS/400 API的工作示例 [英] 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).接下来，我将尝试将BINARY(85)用于RECEIVER_VARIABLE，但是从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字符串的任何示例 API错误代码参数是一个通常需要使用此示例的示例.我将与ERROR_CODE相关的代码注释掉，因为它生成了错误CPF3CF1如果重新激活该代码，则为错误代码参数无效".如果有人可以告诉我们ERROR_CODE二进制字符串数据结构的形成方式出了什么问题，请让我知道. CHAR(16)和BINARY(16)都适用于ERROR_CODE结构.我已经测试了当前如何形成ERROR_CODE的技术将结果映射到表中，并在十六进制模式下使用DSPPFM查看表结果，使其看起来像"binary(hex(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
;
推荐答案
我在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而不是modifies 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 proc的示例.它不处理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.
这两个proc在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:
输出区域以字符显示结构，可以看到整数子字段与字符子字段混合在一起.根据需要解构结构.我可能会创建一个附加的proc，以该结构作为输入并返回各个结构元素.
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函数中的外部SQL存储过程中的任何OS/400 API的工作示例的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
