How to call a stored procedure from the Generic Database AFX Connector in RSA Identity Governance & Lifecycle

[#1]

Article Number

000037943

Applies To

RSA Product Set: Identity Governance & Lifecycle
RSA Version/Condition: 7.0.x, 7.1.x

 

Issue

This article provides the configuration information required to call a stored procedure from the AFX Generic DB Connector.

Resolution

Stored procedures may be called to execute any of the AFX generic database connector capabilities such as CreateAccount, DeleteAccount, etc. The format of the Stored Procedure call is:
 

Call <name of Stored Procedure>(${parameter_name1},${parameter_name2},...${parameter_nameN})

The Call command is universal across all databases and AFX database connectors, both the AFX generic database connector and the AFX database-specific connectors. 

NOTE: The AFX database-specific connectors have been enhanced to allow the use of the Execute command to call stored procedures. However, to execute a stored procedure from the AFX generic database connector, the Call command must be used.

The following table lists the parameters on the CreateAccount screen:
 

Field Name	Value
Parameter Name	Account
Type	STRING
Default Value	-
Is the parameter required?	Yes
Is the parameter encrypted?	No
Display Name	Account Name
Mapping	${AccountTemplate.AccountName}
Description	Account Name

 

Field Name	Value
Parameter Name	Name
Type	STRING
Default Value	-
Is the parameter required?	Yes
Is the parameter encrypted?	No
Display Name	User Full Name
Mapping	${AccountTemplate.UserFullName}
Description	User Full Name

 

Field Name	Value
Parameter Name	Password
Type	STRING
Default Value	-
Is the parameter required?	Yes
Is the parameter encrypted?	Yes
Display Name	Initial password to reset to
Mapping	${AccountTemplate.Password}
Description	Initial password to reset to

The SQL command to CreateAccount is:
 

Field Name	Value
SQL command	INSERT INTO TESTAFX_ACCOUNT (ACCOUNT,NAME,PASSWORD) VALUES (${Account},${Name},${Password})

Instead of using a SQL command, a stored procedure may be used. The syntax for calling a stored procedure to execute a SQL command is:
 

Field Name	Value
SQL command	CALL sp_CreateAccount(${Account},${Name},${Password})

sp_CreateAccount is a stored procedure name and the parameters are separated by a comma () and enclosed inside the brackets.

An example of a stored procedure for creating an account on SQL Server is shown below:

PROCEDURE [dbo].[ sp_CreateAccount]
@account as varchar(25),
@name as varchar(25),
@password as varchar(25)

as
Insert into myaccount(account,name,password) values(@account,@name,@password)
​​​​