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!
[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
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 GENERALCREATE 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:
- Changing the stored procedure to return only result sets, not part of an array.
- Changing the stored procedure to use and return cursors result sets.
- 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.
- 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.
- 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.)