Interacting with a JDBC database without adding driver associations

Recently, I was working on a client site that had the need to insert and read data out of various MS SQL database tables without adding associations to eDirectory objects. The reason for this requirement was that the tables contained temporary data that would get moved from one table to another, by the connected application, as the request was processed. Each insert to a table represented a single request that would move from table to table as each step of the process was completed. In the end, the request would finally rest in a success or error table that the driver would need to read from.

The first challenge was to insert data into the table that would start the process. After some research, it became apparent that the policy builder didn’t have a way to perform this action without assigning an association. Of course, we always have the option of allowing the association to write, and then use a Null driver to remove it, but this would add extra overhead, it is not a clean process, and is not preferred. I really wanted an option that the JDBC driver could handle on its own. Finally, I came across an article written years ago by Cool Solutions writer extraordinaire, Geoffrey Carman. The article discussed the use of writing directly to an Oracle table using direct SQL statements in policy builder. While I was needing to write to an MS SQL database, this still seemed like a viable option, and should be very similar. After a couple minor modifications to the code in his article, I found that I could easily insert a row into the table. Once the direct SQL command runs, all I needed to do was veto all other events, and the driver would not make an association.

I started this process, in the Subscriber channel, by reading in attributes I needed to perform the row insert. I gathered these attributes in the Event Transform and added them as operation property values. In this case, the Matching, Create, and Placement policy sets had no values. The Command Transform is where I performed the SQL insert statement.

Here is an example of the code used to do the insert. It was important that all necessary data was added to the table row, when an insert occurred, so my condition was very tightly controlled by requiring all attributes before allowing the action to be performed.

<rule>
  <description>DirectSQL</description>
  <comment xml_space="preserve">This rule reads in the operational properties defined earlier in the driver, and formats the SQL command to insert the row directly through the SQL statement.</comment>
  <conditions>
    <and>
      <if-op-property mode="regex" name="op-etp-Status" op="equal">.+</if-op-property>
      <if-op-property mode="regex" name="op-etp-TransactionType" op="equal">.+</if-op-property>
      <if-op-property mode="regex" name="op-etp-UserType" op="equal">.+</if-op-property>
      <if-op-property mode="regex" name="op-etp-WorkflowID" op="equal">.+</if-op-property>
      <if-op-property mode="regex" name="op-etp-AccountID" op="equal">.+</if-op-property>
      <if-op-property mode="regex" name="op-etp-priority" op="equal">.+</if-op-property>
      <if-op-property mode="regex" name="op-etp-RoleCN" op="equal">.+</if-op-property>
      <if-op-property mode="regex" name="op-etp-Region" op="equal">.+</if-op-property>
    </and>
  </conditions>
  <actions>
    <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-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 dbo.ss_changelog </token-text>
        <token-text xml_space="preserve">(status , type , user , priority , role)
        VALUES ('</token-text>
        <token-op-property name="op-etp-Status"/>
        <token-text xml_space="preserve">' , '</token-text>
        <token-op-property name="op-etp-Type"/>
        <token-text xml_space="preserve">' , '</token-text>
        <token-op-property name="op-etp-User"/>
        <token-text xml_space="preserve">' , '</token-text>
        <token-op-property name="op-etp-priority"/>
        <token-text xml_space="preserve">' , '</token-text>
        <token-op-property name="op-etp-Role"/>
        <token-text xml_space="preserve">');</token-text>
      </arg-string>
    </do-append-xml-text>
  </actions>
</rule>

Once the insert occurred, I had to veto all actions to prevent the driver from adding the association. Now, when performing a veto-all on a JDBC driver, you can’t just veto all events or the driver will not start. If you do, you will likely get an error similar to this:

Message:  Code(-9005) The driver returned a "fatal" status indicating that the driver 
should be shut down. Detail from driver: <description>Unable to initialize temporary 
query file(s).

The reason for this is that a veto based on any event interferes with the driver’s ability to perform housecleaning, which has a side effect of the driver not starting. So for this condition, I just selected all the events that could come through the driver that might process all the way through and add an association. The veto rule should look something like this:

<rule>
  <description>Veto All</description>
  <comment xml_space="preserve">All events are vetoed in this policy to
  prevent the user object from obtaining an association. </comment>
  <conditions>
    <and>
      <if-operation mode="regex" op="equal">add|add-association|delete|modify|modify-association|remove-association|status|sync|trigger</if-operation>
    </and>
  </conditions>
  <actions>
    <do-veto/>
  </actions>
</rule>

With the Subscriber set as specified above, I had configured the driver to insert records to the table without adding an association. However, I still needed to read in data from a different table on the Publisher channel, to update eDirectory objects. This process had a simpler solution, but it did produce another slight dilemma.

We were using a triggered driver, so when a row was added to the event table, the driver did a type 6 query-back into the table to grab the data from the proper row. At that point, I did all my manipulation in the Event Transform. The dilemma this posed was that because I wasn’t using the add policy sets, I had to match my own objects. So, I did a query for the object I was looking for, and matched it manually. I knew the object had to exist in eDirectory because the request would not have come through the connected app, if I had not written it from eDirectory to the database in the first place. Once I found the object, I read in the source dn and used that to write attribute values, by specifying the DN to use when writing the destination object. The key in writing the attribute value is to use the option to ‘write directly to destination store’. At the end of my Event Transform, I had another veto-all rule, very similar to the one in the Publisher channel. This allowed the driver to write the attribute value, but because it hit a veto-all at the end of the Event Transform, an association was not made to the object in eDirectory. However, because I chose to ‘write directly to the destination store’, the attribute write would still occur after the veto had completed.

<rule>
  <description>Set Attribute Values</description>
  <comment xml_space="preserve">Set attribute values based on previously determined values in this channel.</comment>
  <conditions>
    <and>
      <if-class-name mode="nocase" op="equal">User</if-class-name>
      <if-local-variable mode="regex" name="LV-AttributeValue1" op="equal">.+</if-local-variable>
    </and>
    <and>
      <if-class-name mode="nocase" op="equal">User</if-class-name>
      <if-local-variable mode="regex" name="LV-AttributeValue2" op="equal">.+</if-local-variable>
    </and>
  </conditions>
  <actions>
    <do-if>
      <arg-conditions>
        <and>
           <if-local-variable name="LV-AttributeValue1" op="available"/>
        </and>
      </arg-conditions>
      <arg-actions>
        <do-add-dest-attr-value class-name="User" direct="true" name="Attribute1">
          <arg-dn>
            <token-local-variable name="LV-UserDN"/>
          </arg-dn>
          <arg-value type="string">
            <token-local-variable name="LV-AttributeValue1"/>
          </arg-value>
        </do-add-dest-attr-value>
      </arg-actions>
      <arg-actions/>
    </do-if>
    <do-if>
      <arg-conditions>
        <and>
          <if-local-variable name="LV-AttributeValue2" op="available"/>
        </and>
      </arg-conditions>
      <arg-actions>
        <do-add-dest-attr-value class-name="User" direct="true" name="Attribute2">
          <arg-dn>
            <token-local-variable name="LV-UserDN"/>
          </arg-dn>
          <arg-value type="string">
            <token-local-variable name="LV-AttributeValue2"/>
          </arg-value>
        </do-add-dest-attr-value>
      </arg-actions>
      <arg-actions/>
    </do-if>
  </actions>
</rule>

After all the attributes had been written, the next step was to perform the veto-all action like below.

<rule>
  <description>Veto All</description>
  <comment xml_space="preserve">All events are vetoed in this policy to prevent the user object from obtaining an association. Any attributes that need to be written, must be written directly to the datastore. </comment>
  <conditions>
    <and>
      <if-operation mode="regex" op="equal">add|add-association|delete|modify|modify-association|remove-association|status|sync|trigger</if-operation>
    </and>
  </conditions>
  <actions>
    <do-veto/>
  </actions>
</rule>