Using the JDBC IDM driver, you may find you need more power than just simply synchronizing attributes.

One of the more complex tasks to perform in SQL from the JDBC IDM driver is using Stored Procedures. For instance, you may need to execute a procedure and get a value back. A common example is where the database uses a stored procedure to generate the next value of the primary key when adding a row. In principle, the driver allows you to specify which stored procedure to call to do this task for you, except it only allows for one stored procedure. In a less optimal situation, where multiple tables are being synchronized, and where a different stored procedure is needed for each table, this can get tricky.

Calling the Stored Procedure

One method is to call the stored procedure via embedded SQL statements. It is quite possible to do this in XSLT; but there are sufficient commands in DirXML Script, as of Identity Manager 3.5.1, to
do this in Policy Builder.

Three things are needed here:

• An XML document that calls the Stored procedure using the correct formatting

• The destCommandProcessor to send the document

• Some way to get the returned value out

Here is a sample DirXML Script set of rules that will do this.

<do-set-local-variable name="JDBC-CALL" scope="policy">
	<arg-node-set>
		<token-xml-parse>
			<token-global-variable name="CALL-STORED-PROC"/>
		</token-xml-parse>
		</arg-node-set>
</do-set-local-variable>
<do-set-local-variable name="JDBC-RESULT">
	<arg-node-set>
		<token-xpath expression="command:execute($destCommandProcessor,$JDBC-CALL)"/>
	</arg-node-set>
</do-set-local-variable>
<do-set-local-variable name="primary-key" scope="policy">
	<arg-string>
		<token-xpath expression="$JDBC-RESULT//jdbc:value/text()"/>
	</arg-string>
</do-set-local-variable>

Amazing what three simple Actions can do!

Preparing the XML Document

The first step is get the XML Document ready.

<do-set-local-variable name="JDBC-CALL" scope="policy">
	<arg-node-set>
		<token-xml-parse>
			<token-global-variable name="CALL-STORED-PROC"/>
		</token-xml-parse>
		</arg-node-set>
</do-set-local-variable>

It is possible to synthesize the XML document using Policy Builder on the fly, but it is not simple. And if you use the exact same document each time, it's not really worth it. We used a Global Configuration Value (GCV) to store the XML document. It looks like this:

<nds>
	<input xmlns:jdbc="urn:dirxml:jdbc">
		<jdbc:statement event-id="0">
			<jdbc:call-procedure jdbc:name="IDM.IDM_SMSYSGETNEXTRECNUMDB">
				<jdbc:param>
					<jdbc:value>0</jdbc:value>
				</jdbc:param>
			</jdbc:call-procedure>
		</jdbc:statement>
	</input>
</nds>

What is kind of neat is in the trace: when this happens, it really sticks out to the eye. The XML is tabbed, and it looks like an arrow formation of geese going by. It's very easy to pick out visually.

So the first Action, reads this GCV, and runs it through the XML Parse token. XML Parse is new in IDM 3.5.1; it's designed to take text and convert it the internal representation that the engine uses for XML documents - a nodeset, which is really a DOM document. It used to be that in earlier versions you needed to use a Java class to convert it to a DOM document. Now it is an easy to use token in Argument builder. Ah, progress - gotta love it!

The following line:

<jdbc:call-procedure jdbc:name="IDM.IDM_SMSYSGETNEXTRECNUMDB">

is the part that will do the actual call, so drop in the name of your stored procedure here. If it needs an input value, then add it in the <jdbc:value>0</jdbc:value> line.

Sending the Document to the Shim

Next, you need to send the document to the driver shim to process. You can use the destCommandProcessor for that, and it's as easy as this:

<do-set-local-variable name="JDBC-RESULT">
	<arg-node-set>
		<token-xpath expression="command:execute($destCommandProcessor,$JDBC-CALL)"/>
	</arg-node-set>
</do-set-local-variable>

We set the result to a local variable so that we can process the results. You will need to add a namespace declaration for command, to be mapped to the command processor Java class. When you use an XPATH statement in either iManager or Designer, there should be a little button on the right hand side of the field that is for adding a namespace declaration. In principle, in IDM 3.5.1 I was sure that the destCommandProcessor and srcCommandProcessor were included by default or implicitly, but I have run into instances where it needs to be defined explicitly. That's very easy to do ...

1. Use either the little button next to the XPATH expression line, or edit the XML and in the <policy> tag, change it from <policy> to <policy xmlns:command="http://www.novell.com/nxsl/java/com.novell.nds.dirxml.driver.XdsCommandProcessor">.

(which is all the namespace declaration tool does, anyway).

2. Give the Command processor the variable, which holds our XML document as an XML nodeset ($JDBC-CALL) and wait for its return.

Getting the Value from the Returned Document

Finally, you get the needed value out of the returned document. The JDBC-RESULT local variable holds the returned document from the shim as a nodeset. So we just XPATH-out what we need.

<do-set-local-variable name="primary-key" scope="policy">
	<arg-string>
		<token-xpath expression="$JDBC-RESULT//jdbc:value/text()"/>
	</arg-string>
</do-set-local-variable>

The return document looks like:

<nds dtdversion="2.0" ndsversion="8.x" xmlns:jdbc="urn:dirxml:jdbc">
<source>
	<product build="20070626_0243" instance="APP-JDBC"
version="3.5.1">DirXML Driver for JDBC</product>
	<contact>Novell, Inc.</contact>
</source>
<output>
	<jdbc:out-parameters event-id="0" jdbc:number-of-params="1">
		<jdbc:param jdbc:name="NEXT_WONUM" jdbc:param-type="INOUT"
jdbc:position="1" jdbc:sql-type="java.sql.Types.DECIMAL">
			<jdbc:value>363052</jdbc:value>
		</jdbc:param>
	</jdbc:out-parameters>
	<status event-id="0" level="success"/>
</output>
</nds>

The result is held in the ajdbc:value tag, on this line:

<jdbc:value>363052</jdbc:value>

So, we use the XPATH of $JDBC-RESULT//jdbc:value/text(). This says, for the variable JDBC-RESULT ($JDBC-RESULT), look for any occurence (the "//" part) of the node jdbc:value, and then select the text() of that node.

There you have it. The primary-key local variable now holds the value needed, so you can use it as your environment demands. This is nice and easy, with nary a smidgen of XSLT involved - barely even any Java!