How To

How do I provision DATE fields in IDM 5 and OpenIDM 4.x using the scripted SQL connector?

Last updated Apr 7, 2021

The purpose of this article is to provide assistance with provisioning DATE fields in IDM/OpenIDM using the scripted SQL connector.


This article has been archived and is no longer maintained by ForgeRock.

Provisioning DATE fields

To provision DATE fields using the scripted SQL connector, you must use the nativeType: string.

In this example, we are provisioning a DATE field called newDate, which will update a corresponding newDate column in the MySQL users table (where newDate is of type DATE). This example is based on sample3 with the following notable changes:

  1. Add mapping details for the newDate field to the sync.json file (located in the /path/to/idm/conf directory): { "source" : "newDate", "target" : "newDate" },
  2. Define the newDate field in the account properties section in your provisioner configuration file (for example, provisioner.openicf-scriptedsql.json), which is located in the /path/to/idm/conf directory, ensuring you use nativeType: string: "newDate" : { "type" : "string", "nativeName" : "newDate", "nativeType" : "string", },
  3. Include the newDate column in the users CREATE TABLE section in the ResetDatabaseScript.groovy file (located in the /path/to/idm/tools directory) to ensure the newDate column is created: sql.execute(""" CREATE TABLE users( id int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY, uid char(32) NOT NULL, password char(128), firstname varchar(32) NOT NULL DEFAULT '', lastname varchar(32) NOT NULL DEFAULT '', fullname varchar(32), email varchar(128), organization varchar(32), newDate DATE, timestamp timestamp DEFAULT now() ); """)
  4. Include the newDate column in the users INSERT INTO section in the ResetDatabaseScript.groovy file to ensure the newDate column is populated: sql.execute(""" INSERT INTO users ( uid, password, firstname, lastname, fullname, email, organization, newDate, timestamp )
  5. Add the newDate attribute to the ACCOUNT ObjectClass in the SyncScript.groovy file (located in the /path/to/idm/tools directory) to ensure it is synced during LiveSync: case ObjectClass.ACCOUNT: sql.eachRow("select * from users where timestamp > ${tstamp}", { row -> def cararray = [] def userid = as Integer sql.eachRow("SELECT * FROM car WHERE users_id = ${userid}", { car -> if (car.year) { cararray.add([ year: car.year, make: car.make, model: car.model ]) } }); handler({ syncToken row.timestamp.getTime() CREATE_OR_UPDATE() object { id row.uid uid as String attribute 'uid', row.uid attribute 'fullname', row.fullname attribute 'firstname', row.firstname attribute 'lastname', row.lastname attribute 'email', attribute 'cars', cararray attribute 'organization', row.organization attribute 'newDate', row.newDate as String } }) })
  6. Add the newDate attribute to the ACCOUNT ObjectClass in the SearchScript.groovy file (located in the /path/to/idm/tools directory) to ensure it is synced during reconciliation (this section requires multiple changes): case ObjectClass.ACCOUNT: def dataCollector = [ uid: "", cars: [] ] def handleCollectedData = { if (dataCollector.uid != "") { handler { uid id dataCollector.uid attribute 'uid', dataCollector.uid attribute 'fullname', dataCollector.fullname attribute 'firstname', dataCollector.firstname attribute 'lastname', dataCollector.lastname attribute 'email', attribute 'organization', dataCollector.organization attribute 'newDate', dataCollector.newDate as String attributes'cars', } } } def statement = """ SELECT, u.uid, u.fullname, u.firstname, u.lastname,, u.organization, u.newDate, u.timestamp, c.year, c.make, c.model FROM users u LEFT OUTER JOIN car c ON c.users_id = ${where} ${orderBy} ${limit} """ sql.eachRow(statement, whereParams, { row -> if (dataCollector.uid != row.uid) { // new user row, process what we've collected handleCollectedData(); dataCollector = [ id : as String, uid : row.uid, fullname: row.fullname, firstname: row.firstname, lastname: row.lastname, email:, organization: row.organization, newDate: row.newDate, cars : [ ] ] } if (row.year) {[ year: row.year, make: row.make, model: row.model ]) } lastTimestamp = row.timestamp lastId = resultCount++ }); handleCollectedData(); break

See Also

FAQ: Scripts in IDM

Samples Guide › Using the Connector Bundler to Build a ScriptedSQL Connector

Connectors Guide › Groovy Connector Toolkit

Related Training


Related Issue Tracker IDs

OPENIDM-2460 (JAVA_TYPE_DATE nativeType not supported)

OPENICF-352 (Decouple the native type naming from a specific language, or use a more high level naming scheme)

Copyright and Trademarks Copyright © 2021 ForgeRock, all rights reserved.