000035509 - In RSA Identity Governance and Lifecycle an AFX Database Connector capability executing a Stored Procedure with OUT parameter is not working.

Document created by RSA Customer Support Employee on Sep 15, 2017•Last modified by RSA Customer Support Employee on Sep 15, 2017
Version 3Show Document
  • Comment • 0
  • View in full screen mode

Article Content

Article Number000035509
Applies ToRSA Product Set: RSA Identity Governance and Lifecycle
RSA Version/Condition: All
IssueIf you have an AFX Database Connector, you expect its capability to be able to call a Stored Procedure with IN and OUT parameter(s) but it is not working because the Stored Procedure OUT parameter is not supported by RSA Identity Governance and Lifecycle's AFX Database Connectors (MySQL, MS SQL, etc.). 
When the AFX Database Connector capability executes a Stored Procedure with the OUT parameter (@out_param) in the query field the following error displays:

exec dbo.MyStoredProcedure @user_first_name=${FirstName},@user_last_name=${LastName},@out_param=@out_var output
java.lang.IllegalArgumentException: No SQL Strategy found for SQL statement

When AFX Database Connector capability executes a Stored Procedure with the OUT parameter (@out_param) in the query field, it expects declaring the output variable, but when you include the declaration, it shows the following error:

declare @out_var;
call dbo.MyStoredProcedure @user_first_name=${FirstName},@user_last_name=${LastName},@out_param=@out_var output
Invalid escape sequence declare

 
CauseCurrently, RSA Identity Governance and Lifecycle's AFX Database Connector does not support calling Stored Procedures with OUT parameters.  The product supports calling Stored Procedures with IN parameters only from an AFX Database Connector.
WorkaroundAs a workaround:
  1. After removing the Stored Procedure's OUT parameter and references to it, create an AFX output parameter which will be used to store the value returned by the Stored Procedure.
  2. The workaround is only possible for capabilities that have 'Output' parameters, e. g., 'Create An Account' capability.

The workaround is documented on page 36 of the RSA Identity Governance and Lifecycle - MySQL Application Guide.


The RSA Identity Governance and Lifecycle AFX Database Connector supports calling a Stored Procedure with IN parameters only. 

  • If there is a stored procedure that only has IN parameters, our AFX Database Connector supports calling it as mentioned in Example01 of the MySQL Application Guide.
  • If there is a scenario that needs to return a value as a result of calling the Stored Procedure (Not using OUT parameters), it can be achieved as mentioned in Example02 of the MySQL Application Guide.
To summarize, RSA Identity Governance and Lifecycle's AFX Database Connector does not support calling a Stored Procedure with OUT parameters.
A request for enhancement (ACM-75945) has been raised to accommodate this request.

Attachments

    Outcomes