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

Document created by RSA Customer Support Employee on Sep 13, 2019Last modified by RSA Customer Support Employee on Sep 19, 2019
Version 3Show Document
  • View in full screen mode

Article Content

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

 
IssueThis article provides the configuration information required to call a stored procedure from the AFX Generic DB Connector.
ResolutionStored 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 NameValue
Parameter NameAccount
TypeSTRING
Default Value-
Is the parameter required?Yes
Is the parameter encrypted?No
Display NameAccount Name
Mapping${AccountTemplate.AccountName}
DescriptionAccount Name

 

Field NameValue
Parameter NameName
TypeSTRING
Default Value-
Is the parameter required?Yes
Is the parameter encrypted?No
Display NameUser Full Name
Mapping${AccountTemplate.UserFullName}
DescriptionUser Full Name

 

Field NameValue
Parameter NamePassword
TypeSTRING
Default Value-
Is the parameter required?Yes
Is the parameter encrypted?Yes
Display NameInitial password to reset to
Mapping${AccountTemplate.Password}
DescriptionInitial password to reset to


The SQL command to CreateAccount is:
 
Field NameValue
SQL commandINSERT 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 NameValue
SQL commandCALL 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)




 

 


 

 


Attachments

    Outcomes