It used to be that the way to do direct SQL commands in the JDBC driver was via XSLT in a stylesheet. That is, if you needed to get the driver to execute an INSERT or UPDATE command via SQL. Now there are people out there who live and breathe XSLT, and I am impressed by most of them. However, most people would prefer a slightly easier solution.

The good news is, it is pretty easy to issue SQL calls from Policy Builder. All it takes is a thorugh reading of the docs and then understanding them. To save some time, here is a summary of what you need to do to get it working.

Sample Action for Direct SQL Commands

Here is a sample Action that will mostly do it - modify it with specifics of your location. In this case, I was using it in the Subscriber Output Transform rule, which means we are after the Schema mapping rule. So, any reference by the DirXML script to attributes and classes needs to use the native application names, not the eDirectory names - UNLESS you are querying the source directly for some info. In that case, you use the eDirectory schema names. Confused yet? It's not really an issue here, but just a heads up.

<action>
<do-append-xml-element expression="/nds/input" name="jdbc:statement"/>
  <do-set-xml-attr expression="/nds/input/jdbc:statement" name="jdbc:type">
    <arg-string>
      <token-text xml:space="preserve">update</token-text>
    </arg-string>
  </do-set-xml-attr>
  <do-set-xml-attr expression="/nds/input/jdbc:statement"
name="jdbc:transaction-type">
    <arg-string>
      <token-text xml:space="preserve">manual</token-text>
    </arg-string>
  </do-set-xml-attr>
  <do-set-xml-attr expression="/nds/input/jdbc:statement"
name="jdbc:transaction-id">
    <arg-string>
      <token-text xml:space="preserve">2</token-text>
    </arg-string>
  </do-set-xml-attr>
  <do-append-xml-element expression="/nds/input/jdbc:statement"
name="jdbc:sql"/>
  <do-append-xml-element expression="/nds/input/jdbc:statement"
name="jdbc:sql"/>
  <do-append-xml-text expression="/nds/input/jdbc:statement/jdbc:sql[1]">
    <arg-string>
      <token-text xml:space="preserve">INSERT INTO SCHEMA.TABLENAME       </token-text>
      <token-text xml:space="preserve">(PK_SEQUENCE, COLUMN1, COLUMN2)
VALUES ('</token-text>
      <token-local-variable name="SomeLocalVariable"/>
      <token-text xml:space="preserve">', '</token-text>
      <token-attr name="SomeSourceAttribute"/>
      <token-text xml:space="preserve">', 'Some text       value')</token-text>
    </arg-string>
  </do-append-xml-text>
  <do-append-xml-text expression="/nds/input/jdbc:statement/jdbc:sql[2]">
    <arg-string>
      <token-text xml:space="preserve">UPDATE SCHEMA.TABLENAME2 SET
COLUMN1 = '</token-text>
      <token-attr name="SomeSourceAttribute"/>
      <token-text xml:space="preserve">' WHERE PK_SEQUENCE = '</token-text>
      <token-attr name="SomeSourceAttribute2"/>
      <token-text xml:space="preserve">'</token-text>
    </arg-string>
</action>

About the Code

Now to explain...

First off, you need the "jdbc:statement" node under the the "<input>" node, not in the middle of the "<modify>" or "<add> node. This is the first tricky bit.

Thus, the line below adds an XML element just below <input> ...

<do-append-xml-element expression="/nds/input" name="jdbc:statement"/>

Now for correctness, we need to add some XML attributes to that line, which is what the next few lines do.

<do-set-xml-attr expression="/nds/input/jdbc:statement" name="jdbc:type">
  <arg-string>
    <token-text xml:space="preserve">update</token-text>
  </arg-string>
</do-set-xml-attr>
<do-set-xml-attr expression="/nds/input/jdbc:statement"
name="jdbc:transaction-type">
  <arg-string>
    <token-text xml:space="preserve">manual</token-text>
  </arg-string>
</do-set-xml-attr>
<do-set-xml-attr expression="/nds/input/jdbc:statement"
name="jdbc:transaction-id">
  <arg-string>
    <token-text xml:space="preserve">2</token-text>
  </arg-string>
</do-set-xml-attr>

You should set the type to "update", because apparently Oracle can trigger a loop that will eat CPU cycles if you do not. Also set the transaction type to manual (for some reason I cannot recall now) and give it a transaction-id.

For each SQL statement you want to make, an UPDATE, INSERT, whatever, you need an XML element for each one, so make sure to add enough. My example has two, so we need this line twice.

<do-append-xml-element expression="/nds/input/jdbc:statement" name="jdbc:sql"/>

Finally we get to the actual good stuff, and we can append some XML text to the last two elements we added. The first example is an INSERT:

<do-append-xml-text expression="/nds/input/jdbc:statement/jdbc:sql[1]">
  <arg-string>
    <token-text xml:space="preserve">INSERT INTO SCHEMA.TABLENAME     </token-text>
    <token-text xml:space="preserve">(PK_SEQUENCE, COLUMN1, COLUMN2)
VALUES ('</token-text>
    <token-local-variable name="SomeLocalVariable"/>
    <token-text xml:space="preserve">', '</token-text>
    <token-attr name="SomeSourceAttribute"/>
    <token-text xml:space="preserve">', 'Some text value')</token-text>
  </arg-string>
</do-append-xml-text>

Note that in the "append" we refer to the [1] element of jdbc:sql. Because we added two, we need to be clear which one we want to insert. Just to be inconsistent, it starts counting at 1, instead of 0 as you might expect. Once you know, it does not matter - but you need to know.

We INSERT INTO the SCHEMA.TABLE, three specific columns with values: PK_SEQUENCE, COLUMN1 and COLUMN2. I am not original with names, sorry.

Then we provide VALUES ('something', 'something', 'something'), where in my example, one is a Local variable, the second is a source attribute value from eDirectory, and the third is a text string. You can do anything you need to build the INSERT statement here, using all the tricks Policy builder gives you. This is where the power of IDM really shines. You can leverage variables, source attributes, whatever you need - and use it to build your SQL statements. Go wild!

Finally, we have a sample of an UPDATE command in SQL:

<do-append-xml-text expression="/nds/input/jdbc:statement/jdbc:sql[2]">
  <arg-string>
    <token-text xml:space="preserve">UPDATE SCHEMA.TABLENAME2 SET
COLUMN1 = '</token-text>
    <token-attr name="SomeSourceAttribute"/>
    <token-text xml:space="preserve">' WHERE PK_SEQUENCE = '</token-text>
    <token-attr name="SomeSourceAttribute2"/>
    <token-text xml:space="preserve">'</token-text>
  </arg-string>
</do-append-xml-text>

It's the same basic story here. We're referencing the [2] element that we added with jdbc:sql, and again, here's an example of updating a different table, with one column value, using PK_SEQUENCE. The primary key comes from an eDirectory attribute.

You can do this in pretty much any rule you need. Just make sure that some kind of event makes it through the filter so they don't all get stripped out.

Let me rephrase that. I like doing this in the Output transform. To get to the output transform, you need to make sure that a modify is happening, that gets through all the rules and mappings. Once that modify is through to the output transform, I would strip it from the document, unless you want it to go through. Otherwise, IDM will optimize the request and send an empty document to the Output
transform.

If you do it in the Command Transform, this will not be an issue.

I hope this helps others going forward. As you can see, it is pretty straightforward - just a bunch of individual steps that need to happen together and in sequence.

Sample Trace - Output Document

Here is a sample trace of what the output document will sort of look like. My example used in this article is contrived, and I could not be bothered setting up a matching contrived environment to generate a real exact trace. So this is a real output, with the values changed by hand to match the example. But that should not matter, as it is the overall format that is important, not the actual value of variables and attributes that we care about.

<nds>
  <source>
    <product version="3.5.0.20070315 ">DirXML</product>
    <contact>Novell, Inc.</contact>
  </source>
  <input>
    <jdbc:statement jdbc:transaction-id="3"
jdbc:transaction-type="manual" jdbc:type="update"
xmlns:jdbc="urn:dirxml:jdbc">
      <jdbc:sql>INSERT INTO SCHEMA.TABLENAME (PK_SEQUENCE, COLUMN1,
COLUMN2) VALUES ('419631', 'something', 'something else')</jdbc:sql>
      <jdbc:sql>UPDATE SCHEMA.TABLENAME2 SET COLUMN1 = 'something'
WHERE PK_SEQUENCE = '91067'</jdbc:sql>
      <jdbc:sql/>
    </jdbc:statement>
  <modify>
    <modify-attr/>
  </modify>
  </input>
</nds>

So you can see that the "jdbc:" set of elements starts right after the <input> element, and before the <modify>. You can see the two <jdbc:sql> tags, one for the INSERT and one for the UPDATE SQL commands. Basically, make sure that the text that comes out in the resulting document is valid SQL.

You will get errors back with the SQL error message if you make a typo or error on the SQL command. Troubleshoot and try again, until you get it right.

Note the <jdbc:statement jdbc:transaction-id="3"> tag. You can set this to anything you would like. The choice of 3 was totally arbitrary. In fact, there is no reason why you couldn't use an
attribute from the user (such as src-dn, perhaps?) to allow you to react to the status message that will come back on the Publisher channel. My example happens to come from the third type of SQL command I was doing, so I labeled each major case with a specific transaction ID so I can handle success and errors when they come back.

I have not tested operation properties and whether or not they will come back on the Publisher channel, for returning information about the event for reacting to status documents. That probably would work, if you left the empty modify in the document. (I THINK the engine needs a DirXML operation and its corresponding Event-id to recognize when to add the operation properties back to the status document).

Responding to the Status documents is probably grist for another article someday by itself!

You can also see the empty modify below the entire <jdbc> tag set. You could probably strip that out if you wanted to, but an empty modify should be mostly harmless.

The flexibility is great. You can leverage all the power of IDM, knowing all that it does about users and other objects in your Identity Vault. It also has great string processing power in the DirXML Script language, plus the ability to query the target database, if you need to, for more info. Then you put all that into the power of SQL to do database-related tasks, and this driver truly is a powerhouse.