How to call Stored Procedures in AS/400

Document created by anton_serbanescu Employee on Nov 4, 2016Last modified by chris_stevens on Jan 4, 2017
Version 2Show Document
  • View in full screen mode

There are new features in certain database engines. Especially IBM AS/400, which is using a new result set type, arrays. There can be different errors when attempting to connect to the database, via Database connector, and via MAP connector call, such as:

 

MAP Function errors:

[Function: Call SCHEMA_NAME.PROCEDURE_NAME1, Sql Lookup (Step 23)]: DB access error! 

and

[Function: Call SCHEMA_NAME.PROC_NAME1 - Empty Parameter Test, Sql Lookup]: Prepare the sql parameters error!; Caused by: java.lang.NullPointerException

or a Database Connector error:

Unable to create data parser, component EMBEDDED|databaseparameterchooser XXXXXXXX does not exist.

 

The errors are generated because the stored procedure returns an array as the result set based on the following line in the stored procedure:

<comment>***  Send result set back from stored proc by array/rows                                                   
<comment>***  Setup record set return for stored procedure call                                                    

      C/EXEC SQL SET RESULT SETS ARRAY :NTBLOCKO FOR :ArrayCount ROWS          

                                 

You can see more details regarding the AS/400 features: IBM Knowledge Center 

 

Calling a stored procedure that returns a result set from a nested procedure

This example shows how a nested stored procedure can open and return a result set to the outermost procedure.

To return a result set to the outermost procedure in an environment where there are nested stored procedures, the RETURN TO CLIENT returnability attribute should be used on the DECLARE CURSOR statement or on the SET RESULT SETS statement to indicate that the cursors are to be returned to the application which called the outermost procedure.

Note: that this nested procedure returns two result sets to the client; the first, an array result set, and the second a cursor result set. Both an ODBC and a JDBC client application are shown below along with the stored procedures.

 

Defining the stored procedures

CREATE PROCEDURE prod.rtnnested () LANGUAGE CL DYNAMIC RESULT SET 2EXTERNAL NAME prod.rtnnested GENERAL
CREATE PROCEDURE prod.rtnclient () LANGUAGE RPGLEEXTERNAL NAME prod.rtnclient GENERAL

CL source for stored procedure prod.rtnnested

PGM                                                                CALL       PGM(PROD/RTNCLIENT)           

ILE RPG source for stored procedure prod.rtnclient

DRESULT           DS                  OCCURS(20)                     D COL1                    1     16A                                  C     1             DO        10            X                 2 0    C     X             OCCUR     RESULT                                 C                   EVAL      COL1='array result set'                C                   ENDDO                                            C                   EVAL      X=X-1 C/EXEC SQL DECLARE C2 CURSOR WITH RETURN TO CLIENT                   C+ FOR SELECT LSTNAM FROM QIWS.QCUSTCDT FOR FETCH ONLY               C/END-EXEC                                                           C/EXEC SQL                                                           C+  OPEN C2                                                          C/END-EXEC                                                           C/EXEC SQL                                                           C+ SET RESULT SETS FOR RETURN TO CLIENT ARRAY :RESULT FOR :X ROWS,   C+  CURSOR C2                                                        C/END-EXEC  C                   SETON                                            LR  C                   RETURN        

 

At the present time, there is no option or feature within Boomi to allow this type of result set. As such, here are some alternatives:

  1. Changing the stored procedure to return only result sets, not part of an array.
  2. Changing the stored procedure to use and return cursors result sets.
  3. Call a intermediate stored procedure that understands how the handle the array result set and cast it or break it down in strings or cursors and send it back to Boomi as result sets without arrays and/or cursors.
  4. Change the stored procedure to temporary store the result set to a table, and then have Boomi access that table to retrieve the rows or results.
  5. Use Data Process shapes and Custom Scripting to handle the arrays coming back from the stored procedure. (This is a bit more complex since the connectivity to the database has to be done in the custom scripting as well.)

Attachments

    Outcomes