RSA Version/Condition: 6.9.1, 7.0.x, 7.1.x
with an OUT parameter (@out_param) is entered in this field and is subsequently executed (test connector capabilities), the following error is displayed:
@out_param=@out_var output
java.lang.IllegalArgumentException: No SQL Strategy found for SQL statement
If a declare statement is added to the same query, a different error occurs:
call dbo.MyStoredProcedure @user_first_name=${FirstName},@user_last_name=${LastName},
@out_param=@out_var output
Invalid escape sequence declare
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.
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.
