Execute sql statement from within a script

Question

Hi 1IM Gurus, 
 
 We have a template which refers to a custom script, which makes a call to a sequence on our 1IM DB to return a unique number: 
 "SELECT NEXT VALUE FOR NumGenerator" 
 To make this call we used the .net SQL client class: 
 Dim dbConnection As New SqlClient.SqlConnection 
 This included using a decryption method to pass in the DB connection parameters to setup the DB connection: 
 VID_GetValueOfDialogdatabases("ConnectionString") 
 
 All worked fine in normal operation, however we have found if we attempt to load data via data loader which fired this template, an exception would be thrown. 
 It appears that the call to decrypt the DB connection string is executing locally and can not find the key to decrypt the connection string. 
 
 It there a way to execute an SQL statement via the existing Session or Connection object in a 1IM script, therefore removing the need to create an SQL Client?

Markus Weiss-Ehlers · Accepted Answer

It's strongly recommended against to run SQL statements inside of templates for several reasons (would not work via Application Server connection, ...) and there is no option to freely execute SQL statements via the session or connection object. 
 But you may want to utilize the pre-defined SQL statements to call a stored procedure. 
 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

Execute sql statement from within a script

Top Replies

How-to call a stored procedure from a script

Use-Case

Obstacles

Solution

Steps to provide a Predefined SQL statement