The resolution to this issue is to define an AFX output parameter which will be used to store the value or values returned by the stored procedure defined in the query field of the output parameter in those capabilities that have a
Command Output Parameters option. Below is an example of a connector capability with the
Command Output Parameters option:
Please note the resolution to this issue requires a minimum RSA Identity Governance & Lifecycle version of 6.9.1 or 7.0.0. Prior to 6.9.1, output parameters did not support stored procedures. This functionality was introduced as part of enhancement request ACM-53863.
A detailed example on how to do define the output parameter is below. Please see the section entitled
Use Stored Procedures in Connector in the
RSA Identity Governance & Lifecycle - MySQL Application Guide for more details. Although this is a MySQL Application Guide, the information on using stored procedures applies to all AFX database connectors.
EXAMPLE:
Below is an example of a stored procedure which returns a value that needs to be stored so that it may be used in later operations and/or other connector capabilities. To do this, an AFX output parameter is added to the capability under the Command Output Parameter section. In the Query field a stored procedure is defined that when executed, returns the value and appropriately stores it in the given mapping field.
- Create the stored procedure which returns some value.
In this example a stored procedure has been created which takes
User as an Input parameter and returns
UserID based on the
User provided.
DELIMITER //
CREATE PROCEDURE GetUserID(IN con VARCHAR(20))
BEGIN
select UserID from mysql.user where User=con;
END//
DELIMITER ;
- Create the AFX Input parameter required to pass into the stored procedure. In this case it should be User.
- Create an AFX Output parameter which will be used to store the value returned by the above stored procedure. In this case, this is UserID.
- Call the stored procedure:
- In the Query field of the output parameter (AccountID), call the stored procedure as:
CALL db1. GetUserID ('User')
- The value returned as part of the above query execution is stored in the appropriate mapping field defined for this output parameter.
- This stored output parameter can be used in subsequent capabilities.
Engineering ticket ACM-75945 has been raised to request this functionality in the product. However, the best way to request enhancements is to go to RSA Ideas for RSA Identity Governance & Lifecycle and submit a request. For information on how to log a request for enhancement, please see RSA Knowledge Base Article 000036416 -- How to log a request for enhancement (RFE) for RSA Identity Governance & Lifecycle.
