JDBC provision using stored proc

[chandramohan27 (Chandra Mohan S)]

Hi,

I have a use case in JDBC provision where I need to execute different stored proc to complete my create operation.

For example: Executing 1st Stored proc gives me Employee_ID and using that I will be executing my next stored proc… Once I finish executing both stored proc, my create operation is completed.

Has anyone tried achieving this before.

[iamnithesh (Nithesh Rao)]

In my opinion you need a 3rd SP that will do what you want done. You call this 3rd SP from ISC Account Creation operation

[chandramohan27 (Chandra Mohan S)]

@iamnithesh

You mean I need to create a new stored proc that contains 1st and 2nd SP and then invoking directly the new SP from ISC?

[officialamitguptaa (Amit Gupta)]

@chandramohan27 -

Below is a sample illustrating how you can update only the Create operation of your JDBC Provisioning Rule to call multiple stored procedures (SPs).

Note:

1. You must tailor the specific stored procedure calls to match your actual stored procedure names and parameter signatures.
2. This example shows how to capture an Employee_ID from the first stored procedure and use it in the second stored procedure.

import sailpoint.object.Application;
import sailpoint.object.ProvisioningPlan;
import sailpoint.object.ProvisioningPlan.AccountRequest;
import sailpoint.object.ProvisioningPlan.AttributeRequest;
import sailpoint.object.ProvisioningResult;
import sailpoint.object.Schema;
import connector.common.Util;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.CallableStatement;
import java.util.List;

ProvisioningResult result = new ProvisioningResult();

if (plan != null) {
    List<AccountRequest> accounts = plan.getAccountRequests();
    if (accounts != null && accounts.size() >= 0) {
        for (AccountRequest account : accounts) {
            try {
                // ----------------------------------------------------------------
                //                  CREATE Operation
                // ----------------------------------------------------------------
                if (AccountRequest.Operation.Create.equals(account.getOperation())) {
                    
                    //
                    // 1) Call your first stored procedure that returns Employee_ID.
                    //
                    // Example: Suppose the SP is named "spGetEmployeeID" and has an OUT parameter for Employee_ID.
                    // Adjust your call syntax and parameter indexes as per your actual SP.
                    //
                    CallableStatement cstmt = connection.prepareCall("{CALL spGetEmployeeID(?)}");
                    
                    // Suppose the first parameter is an OUT parameter that returns Employee_ID.
                    cstmt.registerOutParameter(1, java.sql.Types.VARCHAR);  // or java.sql.Types.INTEGER, etc.
                    
                    // If your SP has input parameters, set them here:
                    // cstmt.setString(2, someValue);
                    // cstmt.setString(3, someOtherValue);
                    // ...
                    
                    // Execute the first stored procedure
                    cstmt.execute();
                    
                    // Retrieve the Employee_ID from the OUT parameter
                    String employeeID = cstmt.getString(1);
                    cstmt.close();
                    
                    //
                    // 2) Call your second stored procedure, supplying Employee_ID or other parameters.
                    //
                    // Example: Suppose the second SP is "spInsertUser" which takes an Employee_ID plus other attributes
                    // (like firstName, lastName, email, status, userName).
                    //
                    CallableStatement cstmt2 = connection.prepareCall("{CALL spInsertUser(?,?,?,?,?,?)}");
                    
                    // cstmt2 signature example: (IN employeeID, IN firstName, IN lastName, IN email, IN status, IN userName)
                    cstmt2.setString(1, employeeID);
                    
                    // From your attributes in the plan, set the subsequent parameters:
                    List<AttributeRequest> attributes = account.getAttributeRequests();
                    
                    // We'll hold these values to set into the SP
                    String firstNameVal = null;
                    String lastNameVal  = null;
                    String statusVal    = null;
                    String emailVal     = null;
                    String userNameVal  = null;
                    
                    for (AttributeRequest attribute : attributes) {
                        if ("firstName".equalsIgnoreCase(attribute.getName())) {
                            firstNameVal = String.valueOf(attribute.getValue());
                        } else if ("lastName".equalsIgnoreCase(attribute.getName())) {
                            lastNameVal = String.valueOf(attribute.getValue());
                        } else if ("status".equalsIgnoreCase(attribute.getName())) {
                            statusVal = String.valueOf(attribute.getValue());
                        } else if ("email".equalsIgnoreCase(attribute.getName())) {
                            emailVal = String.valueOf(attribute.getValue());
                        } else if ("userName".equalsIgnoreCase(attribute.getName())) {
                            userNameVal = String.valueOf(attribute.getValue());
                        }
                    }
                    
                    // Now set them in your second SP call
                    cstmt2.setString(2, firstNameVal);
                    cstmt2.setString(3, lastNameVal);
                    cstmt2.setString(4, emailVal);
                    cstmt2.setString(5, statusVal);
                    cstmt2.setString(6, userNameVal);
                    
                    // Execute the second stored procedure
                    cstmt2.execute();
                    cstmt2.close();
                    
                    
                    // Mark provisioning result
                    result.setStatus(ProvisioningResult.STATUS_COMMITTED);

                }
            } catch (Exception e) {
                throw new RuntimeException(e);
            }
        }
    }
}

return result;

Key Points & Adjustments

1. Stored Procedure Calls

• Use CallableStatement for stored procedures (e.g., {CALL spGetEmployeeID(?)}).
• Adjust the syntax ({call ...}, parameter count, OUT vs. IN parameters) to match your actual SP definitions.

2. Obtaining the ID from First SP

• In this example, we capture an Employee_ID from the first SP using an OUT parameter.
• That ID is then passed into the second SP.

3. Attributes

• If your stored procedures need more or fewer attributes, adapt the calls accordingly.
• You can still retrieve them using account.getAttributeRequests() inside the Create operation.

With this approach, the create logic is fully driven by the two (or more) stored procedure calls, matching your stated use case of “Executing 1st Stored proc (to get Employee_ID) and then 2nd Stored proc (using that Employee_ID)”.

Hope this helps.