Run a SQL query with parameter from script called via api

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
  • 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
      
  • Hi Markus, thanks for fast reply!

    I've already reat this topic, but seems like i still not understand something.

    SQL query returns the table, do I need to define these columns in reader.Read() section? If so - where I can see an example?

    Seems like script working fine and starting a query but I've got a NULL result in script tester, because there are no any columns defined inside Reader, right?

  • You can access the columns in the reader using either the index or the name of the column as described here https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/retrieving-data-using-a-datareader (or in several other online articles around accessing data using DataReader).

    For example:

    reader.GetString(0)

    reader.GetInt32(1)

    reader("columnname").ToString()

  • Developed script works fine, however ther is another challenge: is there a way to make a script output like JSON or Dictionary-like instead of a String? For now, output always looks like a string in Visual Studio.

  • Currently, the scripts can only return one parameter, that needs to be convertible to a string.

    And the result will be escaped so that it can be put into the JSON result.

    In the upcoming version 8.2 you will be able to instruct the API to return a raw result, for example the raw JSON or XML string.

    Sample:

    The REST API returns currently this structure. Please note the escaped JSON
    {
      "result": "{\n \"Person1\": {\n \"FirstName\": \"Ford\",\n \"LastName\": \"Prefect\",\n \"BUN\": \"FORDP\",\n \"EmployeeNumber\": \"\"\n }\n}"
    }
    With 8.2 it will be possible to return this instead:
    {
      "Person1": {
        "FirstName": "Ford",
        "LastName": "Prefect",
        "BUN": "FORDP",
        "EmployeeNumber": ""
      }
    }
  • Thank you, Markus!

    So if I understood correctly, for now we're only able to build following output with version 8.1.1:

    {
      "result": "{\n \"Person1\": {\n \"FirstName\": \"Ford\",\n \"LastName\": \"Prefect\",\n \"BUN\": \"FORDP\",\n \"EmployeeNumber\": \"\"\n }\n}"
    }

Reply Children