• State Verified Answer
  • Replies 8 replies
  • Subscribers 93 subscribers
  • Views 2522 views
  • Users 0 members are here
Options
Related

Run a SQL query with parameter from script called via api

Pavel Antonov
over 2 years ago

Hi, everyone!

I want to implement following solution:

1. Create a custom SQL query which returns all of employee assignments, such as ad groups, entitlements, requests and relations between these objects with filtering by personnelnumber.

2. Create a script  which should call a SQL query with parameter (personnelnumber) which should be transferred to this SQL query.

3. Call the script via OIM api/script/<scriptname>

4. Return the data loaded by SQL query in response.

I've already created a sql query and tested it in API designer, so parameter works fine and the query works as expected. Already read the tech doc and topics here related to the scripts, api and sql queries and still not understand how to build a process which allow me to achieve the described goal.

Here is the sample of the script:

Public Function CCC_TestSQL(ByVal TN As String) As String

Dim runner = Session.Resolve(Of VI.DB.DataAccess.IStatementRunner)()
runner.SqlExecute("CCC_TestSQL", {QueryParameter.Create("TN", 42)})

End Function

How can I pass a TN parameter to the SQL query here?

Thanks in advance!

Parents
  • +1 over 2 years ago

    How-to call a stored procedure from a script

    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 a 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
Reply
  • +1 over 2 years ago

    How-to call a stored procedure from a script

    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 a 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
Children