Introduction

The Scripted SQL connector provides a lot of flexibility in the interaction with a database resource. There is one script for each type of operation.
This wiki page shows all the steps needed to configure a Scripted SQL resource identified by the following connection parameters:

In order to link a Scripted SQL resource there are pre-requisites and configuration steps to be satisfied.

Pre-Requisites

1. Download JDBC driver (MySQL JDBC driver in this case) and include it into your JEE container classpath.
2. Install OpenICF ScriptedSQL connector bundle.
3. Implementation of custom groovy SQL scripts.

Configuration steps

1. Configure Scripted SQL connector instance.
2. Configure Scripted SQL resource.

Configure Scripted SQL connector instance

Please, use this page as example for your configuration.

1. Open your browser and access to the Syncope administration console (http://host:port/syncope-console).
2. Log into the interface by providing the following credentials:
   1. Login: admin
   2. Password: password
3. Click on the Resources tab.
4. Click on the Connectors sub-tab.
5. Click on Create new Connector button (configuration modal page appears).
   
6. Click on tab General.
   1. Provide Display name (e.g. 'Sql Scripted Connector').
   2. Select the correct Bundle name (org.forgerock.openicf.connectors.scriptedsql.ScriptedSQLConnector).
   3. Select the correct Bundle version (1.1.0.1).
7. Click on tab Configurations and provide connector instance configuration.
   User: root
   Password: password
   Host: localhost
   TCP Port: 3306
   Database: HR
   JDBC Driver: com.mysql.jdbc.Driver
   JDBC Connection URL: jdbc:mysql://%h:%p/%d
   clearTextPasswordToScript: true
   reloadScriptOnExecution: true
   createScriptFileName: /opt/scriptsql/groovy/CreateScript.groovy
   updateScriptFileName: /opt/scriptsql/groovy/UpdateScript.groovy
   deleteScriptFileName: /opt/scriptsql/groovy/DeleteScript.groovy
   searchScriptFileName: /opt/scriptsql/groovy/SearchScript.groovy
   syncScriptFileName: /opt/scriptsql/groovy/SyncScript.groovy
   
8. Click on tab Capabilities.
   
9. Check needed capabilities as shown at Connector instance configuration.
10. Click on Save button.

Configure Scripted SQL resource

1. Click on the Resources tab.
2. Click on the Resources sub-tab.
3. Click on Create new Resource button (configuration modal page appears).
   
4. Click on Resource Details tab.
5. Choose from the list the correct Connector instance name.
6. Provide the following information
   1. Name: Sql Scripted Resource
   2. Connector: Sql Scripted Connector
7. Click on Schema Mappings tab.
   
8. Click on Add button to insert attributes mapping.
9. Click on Save button.

Implementation of custom groovy SQL scripts

Every action script receives a set of parameters and an attribute map. The set of parameters varies for each action.

• CreateScript.groovy
• UpdateScript.groovy
• DeleteScript.groovy
• SearchScript.groovy
• SyncScript.groovy