Use-Case

You need to retrieve some values from a custom stored procedure inside of a script in One Identity Manager.

Obstacles

• The system will not give you direct access to the database.
• The script has to be executed over an application server connection as well where no direct database connection exists.

Solution

You can execute an SP with the help of the Predefined SQL statements introduced with version 7. This would even work when you are using an application-server connection to the Identity Manager database.

The generic code snippet to use the results of the execution of a predefined SQL statement would look like the following.

Dim runner = Session.Resolve(Of VI.DB.DataAccess.IStatementRunner)()
 
Using reader = runner.SqlExecute("LimitedSqlIdOrUid", {
                                    QueryParameter.Create("Param1", "Value"),
                                    QueryParameter.Create("Param2", 42)})
    While reader.Read()
        ' Do normal IDataReader stuff here
    End While
End Using

Steps to provide a Predefined SQL statement

1. Create a predefined SQL statement in the Designer that is using the SP
   
2. Assign a permission group to the predefined SQL statement that is allowed to use it
   
3. Use the predefined SQL statement in your DialogScript
   

   Dim runner = Session.Resolve(Of VI.DB.DataAccess.IStatementRunner)()
   Using reader = runner.SqlExecute("CCC_getUser", {QueryParameter.Create("accountID", 42)})
       While reader.Read()
           ' Do normal IDataReader stuff here
       End While
   End Using