Can I Use Two Insert Statements with JDBC Connector for Provisioning in ISC?

Identity Security Cloud (ISC) ISC Discussion and Questions
, ,
1

I would like to perform provisioning in ISC using a JDBC connector and specify insert queries for two different tables.

Here is the code snippet I am considering:

// First statement  
statement1 = connection.prepareStatement(sql1);  
statement1.setString(1, req.getValue());  
statement1.setString(2, user_id);  
statement1.executeUpdate();  
statement1.close();  

// Second statement  
statement2 = connection.prepareStatement(sql2);  
statement2.setString(1, req.getValue());  
statement2.setString(2, req.getValue());  
statement2.setString(3, user_id);  
statement2.executeUpdate();  
statement2.close();

Is this a valid approach?
If not, could you suggest a better way to insert data into two different tables within the same transaction?

Thank you in advance!

2

Hi @joyoon00
I can give you some theoretical jdbc suggestions:

  1. Check if data already exists in tables
  2. The setAutoCommit(false) call ensures that the changes made by both statements are not automatically committed to the database.
  3. If both statements succeed, the commit() method is called to apply the changes.
  4. If either statement fails, the rollback() method is called to undo changes from both statements, maintaining database integrity.
  5. Finally, always ensure resources are closed properly in the finally block
3

Hello @joyoon00 , you can do that and yes it is valid, if you want to build a complex logic you can build JAR file and refer that in JDBC PROV rule.

1 Like
4

Hello, sorry for the delayed response.
I’m an amateur, so I’m having difficulty handling JDBC provisioning rule.

In my previous provisioning rule, I used the following commit:
result.setStatus(ProvisioningResult.STATUS_COMMITTED);
However, I lack sufficient knowledge about setAutoCommit(false), commit(), and rollback() methods as you explained above.

Would you be able to provide examples or share any reference documents?

5

You can find documentation around the setAutoCommit, commit, and rollback methods in the java.sql.connection documentation. It’s nothing specific to SailPoint, it’s just a Java class

https://docs.oracle.com/en/java/javase/11/docs/api/java.sql/java/sql/Connection.html

1 Like
6

Probably should start a new thread related to this. :slight_smile: Welcome to the community!

1 Like
7

This should work just fine, but I would remove the “statement1.close();” statement and the “statement2.close();” statement. You shouldn’t need those

1 Like