Run a SQL query with parameter from script called via api

Question

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!

Markus Weiss-Ehlers · Accepted Answer

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 
 
 Create a predefined SQL statement in the Designer that is using the SP 
 Assign a permission group to the predefined SQL statement that is allowed to use it 
 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

Markus Weiss-Ehlers · Answer

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()

Markus Weiss-Ehlers · Answer

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": "{
 \"Person1\": {
 \"FirstName\": \"Ford\",
 \"LastName\": \"Prefect\",
 \"BUN\": \"FORDP\",
 \"EmployeeNumber\": \"\"
 }
}" } 
 
 With 8.2 it will be possible to return this instead: 
 
 { 
 "Person1": { "FirstName": "Ford", "LastName": "Prefect", "BUN": "FORDP", "EmployeeNumber": "" } }

Run a SQL query with parameter from script called via api

Top Replies

How-to call a stored procedure from a script

Use-Case

Obstacles

How-to call a stored procedure from a script

Use-Case

Obstacles

Solution

Steps to provide a Predefined SQL statement