Skip to main content

NetIQ IDM - JDBC driver - SQL calls from driver (Publisher channel) using XSLT


Recently I was working on a task where we had to call some SQL statements from publisher channel on a JDBC driver to different tables than the ones driver was configured to sync.

The official documentation suggest to achieve this using jdbc-statement but it only schedules them on the subscriber channel, best suited for calling some SQL for stored procedure.

The way I solved it was using XSLT and enabling Subscriber channel.

On the publisher  Command Transformation Channel , I have following XSLT:

<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:jdbc="urn:dirxml:jdbc" xmlns:query="http://www.novell.com/nxsl/java/com.novell.nds.dirxml.driver.XdsQueryProcessor" version="1.0"> <xsl:param name="srcQueryProcessor" /> <xsl:param name="destQueryProcessor" /> <xsl:template match="node()|@*"> <xsl:copy> <xsl:apply-templates select="node()|@*" /> </xsl:copy> </xsl:template> <!-- ON ADD | MODIFY --> <xsl:template match="modify[@class-name='Group']|add[@class-name='Group']"> <!-- GET ATTRIBUTE VALUE TO USE WITH SQL --> <xsl:variable name="systmeid" select="*[@attr-name='businessCategory']//value" /> <xsl:variable name="query-doc"> <jdbc:statement type="query"> <jdbc:sql> <xsl:text>SELECT DISTINCT OPERATIONAL_OWNER_ID FROM idm_application_list WHERE SYSTEM_ID =</xsl:text> <xsl:value-of select="$systmeid" /> </jdbc:sql> </jdbc:statement> </xsl:variable> <!-- GET SQL RESULT SET --> <xsl:variable name="result" select="query:query($srcQueryProcessor,$query-doc)" /> <!-- SAVE SQL RESULT INTO TEMP ATTRIBUTE --> <xsl:copy> <xsl:apply-templates select="node()|@*" /> <add-attr attr-name="OperationOwnerId"> <xsl:for-each select="$result//jdbc:value"> <xsl:variable name="row" select="." /> <value type="string"> <xsl:value-of select="$row" /> </value> </xsl:for-each> </add-attr> </xsl:copy> </xsl:template> </xsl:stylesheet>


Actually  I am calling a SQL statement and result of the statement is saved into a temp attribute called  OperationOwnerId,  now this temp attribute is accessible to dirxml policy, remember when you have worked out finished with OperationalOwnerId attribute, just remove it from the current operation.   I have a policy with following rule on Publisher Command Transformation next to XSLT


<rule> <description>Clean up temporary attributes out of the operation for OperationOwnerId</description> <conditions> <and> <if-class-name mode="nocase" op="equal">Group</if-class-name> <if-op-attr name="OperationOwnerId" op="available" /> </and> </conditions> <actions> <do-strip-op-attr name="OperationOwnerId" /> </actions> </rule>
Labels:

Comments

Post a Comment

Popular posts from this blog

Experience writing a Java based DirXML Driver

Based on the customer project, I wrote a DirXML driver which provision users through Novell Identity Manager 3.5.1 to their company intranet portal ( A Plone System). The portal exposed the RESTful API interfaces. So I started looking first at the Novell SOAP driver to see if it fit our needs. But while reading the driver documentation i felt it required too much XSLT knowledge + more customization and testing on the driver. And again it used the Apache HttpClient, Which is more a HttpClient rather then it targets to any specific protocol implementation. So If you could build SOAP messages at your own so it would help you in transporting these message back and forth between IDM and Application. The Novell SOAP driver comes up with two built in configurations "SPML and DSML", but in my case none of them were suitable. I had always wished to write my own DirXML driver at my own, so I thought why not just take this opportunity to fulfill my wish and at the same time get s...
1 comment
Read more

NetIQ IDM - JDBC statemens using policy builder

Few examples of using JDBC statements using dirxml policies On the Output policy: Handling matching policies with operation-data support: < rule > < description > [DB] Convert Query to DDL doc </ description > < comment name = "author" xml:space = "preserve" > Maqsood Ali Bhatti </ comment > < comment name = "version" xml:space = "preserve" > 5 </ comment > < comment name = "lastchanged" xml:space = "preserve" > Dec 20, 2017 </ comment > < conditions > < and > < if-operation mode = "case" op = "equal" > query </ if-operation > </ and > </ conditions > < actions > < do-append-xml-element expression = ".." name = "jdbc:statement" /> < do-append-xml-element expression = "../jdbc:statement[las...
Post a Comment
Read more

Novell IdM 3.6 : Creating custom Subscriber PostProcessor Java extension for Delimited Text Driver Part 1

I had chance to write a custom PostProcessor extension for a Delimited Text Driver for Novell Identity Manager,  " t he PostProcessor extension should execute the code under another Windows Domain User account. So I decided to run the driver with Remote Loader Configuration, running the DirXML windows Service under another Windows Domain User Account. " Software and settings in my setup: * Novell Designer 3.0.1 (build  Jan 5, 2009) * Novell Identity Manager 3.6 / Windows Server 2003 R2 * Novell iManager 2.7.2 * Delimited Text Driver 3.6 ver 2.0 (imported configuration  file from Designer 3.0.1) * And the Delimited Text Driver was going to run on the remote server (Remote Loader),  * Java IDE was  (IntelliJIDEA) i started first with digging into documentation of the driver, and i came a cross this section: Using Java Interfaces to Customize File Processing: http://www.novell.com/documentation/idm36drivers/delimited/index.html?page=/documentation/idm36drivers/delimited/data/bkt...
1 comment
Read more