Execute plain SQL in script

Question

Hi, 
 is it possible to execute sql in a script like asked four years ago in thread [1] in OI version 8.1.4? 
 I testet the script in version 8.1.4 .and i get the error message in [2]. 
 That means, the code fails to execute: 
 Dim conData As ConnectData = DbApp.Instance.Connect(ConnectionString) 
 
 [1] 
 https://www.oneidentity.com/community/identity-manager/f/forum/421/execute-plain-sql-in-script 
 
 [2] 
 ErrorMessages = (2021-02-03 10:39:45.550) [810222] Error executing script 'CCC_UTIL_CSVExport_Generic_Sep'. [System.ArgumentException] Keyword not supported: '[c]adacslbed2e0crbpt0s/xyudtrdq9feqhgrultwbc/peyk ...

Markus Weiss-Ehlers · Answer

The solution of the mentioned thread only works if the database is not encrypted. That's why you see the error because the connection string is encrypted. 
 But it is not the recommended way of doing so as your code would not work if it is executed via an application server. 
 Instead, create a stored procedure that is delivering the required results. 
 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 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 
 
 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

Endpoint Privilege Management

Execute plain SQL in script

Top Replies