Two functionalities that work perfectly together.
One of my favorite programming techniques is linking together functionality to make even-more-powerful functionality. An example of this is wrapping an SQL user-defined table function (UDTF) around a System i list API to retrieve a given list through an SQL interface. As an example, let's explore a service program that can be used for exactly that purpose.
SQLLSTAPI
The SQLLSTAPI service program contains two subprocedures. The getMessages subprocedure utilizes the retrieve message API (QMHRTVM) to retrieve a list of message file messages. The getSpooledFileList subprocedure retrieves a list of spooled files through the QUSLSPL API. The full source for the code associated with this tip can be found here.
getMessages
This subprocedure retrieves individual messages from the message file identified by the subprocedure's two parameters (inLibrary and inMsgf). Below is the source for this subprocedure.
//*********************************************************************
// Procedure: getMessages
// Function: Retrieve message descriptions for all messages in a
// given message file. Data is returned as an SQL table
// function.
//*********************************************************************
d getMessages pr
* Input Parameters
d inLibrary 10a Const varying
d inMsgF 10a Const varying
* Output Parameters
d outMsgId 7a
d outMsgSev 2 0
d outMsgDesc 500a varying
d outMsgHelp 500a varying
* NULL Indicators
d niLibr 5i 0
d niMsgf 5i 0
d niMsgId 5i 0
d niMsgSev 5i 0
d niMsgDesc 5i 0
d niMsgHelp 5i 0
* SQL Info
d SQLState 5
d FunctName 517a varying
d SpecName 128a varying
d SQLMsg 70a varying
d CallType 10I 0
//*********************************************************************
// Procedure: getSpooledFileList
// Function: Retrieve a list of spooled files based on supplied
// criteria. Data is returned as an SQL table function.
//*********************************************************************
d getSpooledFileList...
d pr
* Input Parameters
D inpUserId 10a varying
D inpOutQueue 20a varying
D inpFormType 10a varying
D inpUserData 10a varying
* Output Parameters
d outSplfNam 10a
d outSplfnum 10i 0
d outSplfSts 10a
d outFrmTyp 10a
d outOutq 10a
d outPages 10i 0
d outCopies 10i 0
d outJobNam 10a
d outUserNam 10a
d outJob# 6a
* NULL Indicators
d niUserId 5i 0
d niOutQueue 5i 0
d niFormType 5i 0
d niUserData 5i 0
d niSplfNam 5i 0
d niSplfnum 5i 0
d niSplfSts 5i 0
d niFrmTyp 5i 0
d niOutq 5i 0
d niPages 5i 0
d niCopies 5i 0
d niJobNam 5i 0
d niUserNam 5i 0
d niJob# 5i 0
* SQL Info
d SQLState 5
d FunctName 517a varying
d SpecName 128a varying
d SQLMsg 70a varying
d CallType 10I 0
//*********************************************************************
// Procedure: getMessages
// Function: Retrieve message descriptions for all messages in a
// given message file. Data is returned as an SQL table
// function.
//*********************************************************************
p getMessages b EXPORT
d getMessages pi
* Input Parameters
d inLibrary 10a Const varying
d inMsgF 10a Const varying
* Output Parameters
d outMsgId 7a
d outMsgSev 2 0
d outMsgDesc 500a varying
d outMsgHelp 500a varying
* NULL Indicators
d niLibr 5i 0
d niMsgf 5i 0
d niMsgId 5i 0
d niMsgSev 5i 0
d niMsgDesc 5i 0
d niMsgHelp 5i 0
* SQL Info
d SQLState 5
d FunctName 517a varying
d SpecName 128a varying
d SQLMsg 70a varying
d CallType 10I 0
dRetrieveMsg pr ExtPgm('QMHRTVM')
d MsgInfo 32767a Options(*varsize)
d RtnLength 10i 0 Const
d RtnFormat 8a Const
d MsgID 7a Const
d QualMsgF 20a Const
d RplData 32767a Options(*varsize) Const
d RplDataLen 10i 0 Const
d RplSubValue 10a Const
d RtnCtlChar 10a Const
d ErrorCode 500a
d RtvOption 10a Const
d CCSIDCnvTo 10i 0 Const Options(*Omit)
d CCSIDRplDta 10i 0 Const Options(*Omit)
dRTVM0300 ds Qualified
d BytesReturned 10i 0
d BytesAvailable 10i 0
d MsgSev 10i 0
d AlertIndex 10i 0
d AlertOption 9a
d LogPrb 1a
d MsgID 7a
d 3a
d NbrSubValueFormat...
d 10i 0
d CCSIDConvStsText...
d 10i 0
d CCSIDConvRplDta...
d 10i 0
d CCSIDRtnText 10i 0
d OffsetDftRpy 10i 0
d DftRpyRtnLen 10i 0
d DftRpyAvlLen 10i 0
d OffsetMsg 10i 0
d MsgLenRtn 10i 0
d MsgLenAvl 10i 0
d OffsetMsgHlp 10i 0
d MsgHlpRtnLen 10i 0
d MsgHlpAvlLen 10i 0
d OffsetSubVar...
d 10i 0
d SubVarRtnLen...
d 10i 0
d SubVarAvlLen...
d 10i 0
d SubVarElement...
d 10i 0
d 500a Varying
d DftRpy 500a Varying
d Msg 500a Varying
d MsgHlp 500a Varying
d SubVar 500a Varying
d SQL_OK c '00000'
d SQL_EOF c '02000'
d SQL_FILE_NOT_FOUND...
d c '00204'
d ErrorDS ds Qualified
d BytesReturned 10i 0 Inz(%Size(ErrorDS))
d BytesAvailable 10i 0
d MsgID 7a
d 1a
d MsgText 512a
d MsgID s 7a
d LogPrb s 4a
d msgRtv s 10a STATIC
d lastMsg s 7a STATIC
/free
SQLState = SQL_OK;
if calltype = -1;
if niLibr = 1 OR niMsgf = 1;
SQLState=SQL_FILE_NOT_FOUND;
msgRtv = '';
*INLR = *ON;
Return;
ENDIF;
lastMsg = '';
msgRtv = '*FIRST';
else;
msgRtv = '*NEXT';
ENDIF;
RetrieveMsg( RTVM0300
: %Len(RTVM0300)
: 'RTVM0300'
: LastMsg
: inMsgF + inLibrary
: ' '
: 1
: '*NO'
: '*NO'
: ErrorDS
: msgRtv
: 0
: 0 );
If ErrorDS.BytesAvailable <> 0;
exsr setEOF;
Else;
If RTVM0300.MsgID <> '';
outMsgId = RTVM0300.MsgId;
outMsgSev = RTVM0300.MsgSev;
outMsgDesc = %subst( RTVM0300
: RTVM0300.OffsetMsg+1
: RTVM0300.MsgLenRtn);
outMsgHelp = %subst( RTVM0300
: RTVM0300.OffsetMsgHlp+1
: RTVM0300.MsgHlpRtnLen);
lastMsg = outMsgId;
else;
exsr setEOF;
EndIf;
EndIf;
Return;
Begsr setEOF;
outMsgId = '';
outMsgSev = 0;
outMsgDesc = '';
outMsgHelp = '';
SQLState = SQL_EOF;
msgRtv = '';
*InLR = *On;
niMsgID = 1;
niMsgID = 1;
niMsgID = 1;
niMsgID = 1;
lastMsg = '';
ENDSR;
/end-free
p getMessages e
This subprocedure is designed to return a different "record" each time that the routine is called with different parameters. This is how the SQL table function that will later be associated with the subprocedure is able to retrieve an entire record set from the routine. The output parameters sent back by the subprocedure will include the message ID, the message severity value, the message text description, and the message help text. Just a side note: the value returned into the text description and the help text will include any replacement variables in the original form (&1, &2). For purposes of the SQL function, the subprocedure also includes a set of null indicators—one for each of the previously defined parameters, both input and output. A set of SQL information values is also sent back to the caller to indicate the status of the call along with any message information. Each time this subprocedure is called, it will continue to return records until there is no more data available. At that point, it will return an SQL State value of SQL_EOF, which will indicate an end-of-file condition to the calling SQL interface. Note that when the message file identified on the subprocedure's input parameters cannot be found, the routine will return an SQL_FILE_NOT_FOUND condition as the SQL State value.
getSpooledFileList
This subprocedure retrieves information about spooled files based on a supplied set of four parameters. These include the user ID for the user who generated the spooled file, the output queue that contains the spooled file, the form type of the spooled file and the user data specified on the spooled file. The source for this subprocedure is shown below.
//*********************************************************************
// Procedure: getSpooledFileList
// Function: Retrieve spooled file list entries based on specified
// input parameters. Data is returned as an SQL table
// function.
//*********************************************************************
p getSpooledFileList...
p b export
d getSpooledFileList...
d pi
* Input Parameters
D inpUserId 10a varying
D inpOutQueue 20a varying
D inpFormType 10a varying
D inpUserData 10a varying
* Output Parameters
d outSplfNam 10a
d outSplfnum 10i 0
d outSplfSts 10a
d outFrmTyp 10a
d outOutq 10a
d outPages 10i 0
d outCopies 10i 0
d outJobNam 10a
d outUserNam 10a
d outJob# 6a
* NULL Indicators
d niUserId 5i 0
d niOutQueue 5i 0
d niFormType 5i 0
d niUserData 5i 0
d niSplfNam 5i 0
d niSplfnum 5i 0
d niSplfSts 5i 0
d niFrmTyp 5i 0
d niOutq 5i 0
d niPages 5i 0
d niCopies 5i 0
d niJobNam 5i 0
d niUserNam 5i 0
d niJob# 5i 0
* SQL Info
d SQLState 5
d FunctName 517a varying
d SpecName 128a varying
d SQLMsg 70a varying
d CallType 10I 0
//***************************************************************
// Miscellaneous Variables
//***************************************************************
D RRn1 S 5 0
D Spl# S 6a
//***************************************
// Input format for API QUSLSPL
//**************************************
D outFormat S 8a
D usrSpace S 20a static
D listID S 10i 0 static inz(0)
D ofStart S 10i 0 static
D ofLen S 10i 0 static
/FREE
if calltype = -1;
*INLR = *OFF;
usrSpace= 'SPLFLIST QTEMP ';
descTxt = 'SPLF List User Space';
extAttr = ' ';
inzSize = 5000;
intlVal = ' ';
pubAuth = '*CHANGE';
outFormat = 'SPLF0300';
deleteUserSpace( usrSpace
: errorDS);
createUserSpace( usrSpace
: extAttr
: inzSize
: intlVal
: pubAuth
: descTxt
: rplc
: errorDS);
if ErrorDS.BytesAvailable = 0;
User = inpUserID;
OutQ = inpOutQueue;
FormType = inpFormType;
UserData = inpUserData;
retrieveSpooledFileList( usrSpace
: outFormat
: User
: OutQ
: FormType
: UserData
: errorDS
);
ofStart = 1;
ofLen = 140;
retrieveUserSpaceHeader( usrSpace
: ofStart
: ofLen
: headerDS
: errorDS);
if ErrorDS.BytesAvailable <> 0;
exsr setEOF;
return;
endif;
ofStart = headerDS.ListOffset + 1;
else;
exsr setEOF;
return;
endif;
endif;
if niUserId = 1 OR
niOutQueue = 1 OR
niFormType = 1 OR
niUserData = 1;
SQLState=SQL_FILE_NOT_FOUND;
usrSpace = '';
*INLR = *ON;
Return;
ENDIF;
if calltype <= 0;
if listId < headerDS.ListNumber;
listid += 1;
clear Splf0300;
retrieveUserSpaceSpl0300( usrSpace
: ofStart
: ofLen
: Splf0300
: errorDS);
if ErrorDS.BytesAvailable = 0 and SSplfName <> '';
Select;
When SSplfStat = 1;
outSplfsts = 'READY';
When SSplfStat = 2;
outSplfsts = 'OPENED';
When SSplfStat = 3;
outSplfsts = 'CLOSED';
When SSplfStat = 4;
outSplfsts = 'SAVED';
When SSplfStat = 5;
outSplfsts = 'WRITING';
When SSplfStat = 6;
outSplfsts = 'ON HOLD';
When SSplfStat = 7 or SSplfStat = 8;
outSplfsts = 'PENDING';
When SSplfStat = 10;
outSplfsts = 'FINISHED';
When SSplfStat = 11;
outSplfsts = 'SENDING';
When SSplfStat = 12;
outSplfsts = 'DEFERRED';
Endsl;
outSplfNam = SSplfName;
outSplfnum = SSplfNum;
outFrmTyp = SFormType;
outOutq = Soutq;
outPages = SPages;
outCopies = SCopies;
outJobNam = SJObName;
outUserNam = SUserName;
outJob# = SJob#;
SQLState = SQL_OK;
else;
exsr setEOF;
Return;
endif;
ofStart = ofStart + headerDS.EntrySize;
else;
exsr setEOF;
Return;
Endif;
Endif;
Return;
Begsr setEOF;
outSplfNam = '';
outSplfnum = 0;
outFrmTyp = '';
outOutq = '';
outPages = 0;
outCopies = 0;
outJobNam = '';
outUserNam = '';
outJob# = '';
SQLMsg = '';
SQLState = SQL_EOF;
listId = 0;
*InLR = *On;
niSplfNam = 1;
niSplfnum = 1;
niFrmTyp = 1;
niOutq = 1;
niPages = 1;
niCopies = 1;
niJobNam = 1;
niUserNam = 1;
niJob# = 1;
ENDSR;
/END-FREE
p getSpooledFileList...
p e
Note that all of the special values supported by the WRKSPLF command for these parameters are also supported here. The output parameters from this subprocedure include the spooled file name, the spooled file number, the status of the spooled file at the time the subprocedure is called, the form type, the output queue, the number of pages in the spooled file, the number of copies specified on the spooled file, along with the job name, user name, and job number that created the file. As with the getMessages subprocedure, this subprocedure also returns a set of null indicators and SQL status information to be passed back to the caller.
Creating the Functions
Now that we have examined the subprocedures that will act as our SQL functions, we need to execute the commands to create those functions. First, we need to compile to service program that contains these two subprocedures. To do this, execute the two commands below to create the module and service program;
CRTRPGMOD MODULE(mylib/SQLLSTAPI) SRCFILE(mylib/QRPGLESRC)
SRCMBR(SQLLSTAPI) REPLACE(*NO)
CRTSRVPGM SRVPGM(mylib/SQLLSTAPI) MODULE(mylib/SQLLSTAPI)
EXPORT(*ALL)
Now that we have our compiled service program, we need to associate that service program—or more appropriately, the subprocedures contained in that service program—with an SQL user-defined table function (UDTF). To do this, we need to execute an SQL statement. The easiest way to do this is to enter the statements into a source file member for each UDTF that we're creating. Below is the text of the statements to create the getMessages UDTF.
CREATE FUNCTION mylib/GETMESSAGES(Library VARCHAR (10),
MessageFile VARCHAR (10))
RETURNS TABLE ( MsgID CHAR (7 ),
MsgSev DECIMAL (2 , 0),
MsgDesc VARCHAR (500 ),
MsgHelp VARCHAR (500 ))
LANGUAGE RPGLE
EXTERNAL NAME 'mylib/SQLLSTAPI(GETMESSAGES)'
PARAMETER STYLE DB2SQL
DISALLOW PARALLEL
NO SQL
NO FINAL CALL
Note that the function accepts the same two input parameters identified in our getMessages subprocedure and returns the four values that we identified as output parameters from that subprocedure. The external name value is specified in the following format:
library/service program(sub-procedure)
The value "PARAMETER STYLE DB2SQSL" identifies that the program will return and expect the null indicator and SQL info variables. Once you have the source for each of the subprocedures saved into a source member, execute the following commands to run the statements to create the UDTF.
RUNSQLSTM SRCFILE(mylib/QSQLSRC) SRCMBR(GETMSGF)
RUNSQLSTM SRCFILE(mylib/QSQLSRC) SRCMBR(GETSPLF)
Note that these commands assume that you have each of the subprocedures saved into a source file QSQLSRC in the library mylib. The advantage to saving these statements in a source member versus simply executing them from an interactive SQL prompt or from the SQL utility within Operations Navigator is that it makes it easier to recreate the function in the future or on multiple systems. Once you've completed both of these commands, the UDTFs are available for use.
Calling the UDTFs
Now that we've created our UDTFs, we can execute them from anywhere that an SQL command is accepted. This includes the interactive SQL tool (STRSQL) as well as embedded SQL (SQLRPGLE type) programs. This also means that remote applications that access the System i through OLE DB or ODBC connections can call these functions. Below is a sample of calling the getMessages UDTF from interactive SQL.
SELECT * FROM TABLE(mylib/getMessages('QSYS ', 'QCEEMSG ')) Msgs
Note that the Msgs value at the end of the line is an alias that is required when calling a UDTF. The "TABLE" prefix is used to identify that we are calling a user-defined table function. Figure 1 shows a sample of the output generated by this call.
Figure 1: These are the results generated by the getMessages UDTF.
Similarly the getSpooledFileList function can be called as shown below;
SELECT * FROM
TABLE(mylib/getSpooledFileList('*CURRENT ', '*ALL ','*ALL ', '*ALL ')) Splf
Note that in this example the parameters are all passed as special values. In each of the examples given, we may choose to filter results using a WHERE clause. Figure 2 shows an example of the output from this statement.
Figure 2: These are the results from the getSpooledFileList UDTF.
What's Your Function?
Now that we've explored combining functionality like user-defined table functions and system APIs, I hope you'll think of other ways to make use of this concept in your shop.
as/400, os/400, iseries, system i, i5/os, ibm i, power systems, 6.1, 7.1, V7, V6R1
LATEST COMMENTS
MC Press Online